Voice of the DBA

Author: way0utwest

The SQL Source Control Frequent Updates Channel
One of the things we do at Redgate is release software often. It’s not just that we want you to release your software often (and develop better), but we do it ourselves. Different teams release at different cadences, which is both cool, and hard to keep track of. The SQL Prompt team is really amazing, releasing hundreds of times in the last year. A pace of almost 4 times a week!

SQL Source Control is a product I’ve been using fairly often lately. There is a team working on updating the code regularly, and I recently found out that they had two different streams of software releases. This is so the development team can get early feedback on some new features, but also so you can get access to the new features, performance improvements, and bug fixes as soon as they’re available.

As a result, we have a normal, mainstream channel for releases, and a frequent release channel. The frequent channel offers new updates every Wednesday whereas the normal channel releases are less regular. The releases tend to be every few months, depending on the feature set being worked on. The regular channel also rolls multiple frequent releases into one larger update.

Enabling Frequent Release

If you’re interested in getting frequent releases, then open SQL Source Control (SOC) and then click the help item. The “Check for updates” item is there, which is what you want to select.

The update screen appears, and you may or may not have updates available. I don’t, as I updated this morning. However in the upper right corner is a “Configure visible updates” button. Click that.

Once you do that, you’ll get a dialog that tells you that you might get more updates, every few weeks and get new versions. If you check the box, as I have, you get updates quicker.

One note. This isn’t for beta versions. This is for code we are sure is ready, but we’re hoping to refine how it works and this gives us a chance to essentially have a additional test audience view the code and let us know. This is over and above the automated CI process we use and rigorous testing already completed by the development team.

My experience is that I turned this on in early September to get the new Git support. I got the release with push/pull about three weeks before others (this has since been released to the regular channel). Since then, I’ve gotten two other releases that changed minor items, the same ones available to everyone else on the frequent release channel. The next regular channel release isn’t due until early 2016.

If you want to see more frequent changes, enable the updates. If not, you’ll get updates on the normal schedule you do now. If you’re trying to understand what version you’re on, or what the features are, we have release notes:
- Frequent Release Channel – https://documentation.red-gate.com/display/SOC4/SQL+Source+Control+4.1+Frequent+Updates+release+notes
- Regular Channel – http://documentation.red-gate.com/display/SOC4/Release+notes+and+other+versions
And if you want to shape the product, please vote on UserVoice for the things that you’d like to see in the future.
November 5, 2015
Crossing Azure Borders

The speed at which Azure is evolving actually amazes me at times. I know that the Azure platform is a focus for most, if not all, groups at Microsoft. The breadth of changes occurring every quarter continue to enhance capabilities to the point where Azure is a viable platform for more and more applications all the time. It’s not for everyone, but it does work well for more and more organizations all the time.

I’ve been watching the Azure SQL database evolve, and been doing a little work with it for a few years. It has grown from a fairly limited RDBMS to a full-featured database, albeit with limitations. Some of those issues have meant that tools or techniques I’ve been familiar with haven’t worked well, if at all.

One of those items was the ability to query across databases. Previously I’ve had to open two connections and pull data to a client to combine it. However just recently that with the elastic database query in Azure now supports queries across databases. This is cool, as we can now build applications that easily query across shards, or even just across different databases without requiring additional client work.

The list of features in an on-premise SQL Server instance that aren’t in the Azure SQL Database continues to shrink. While there’s more work to be done, I think we can start to experiment with systems and see where the Azure platform might support our needs. Certainly our business people like the ongoing rather than capital expense nature of cloud platforms.

We should be sure we understand where, and where not, we could use an Azure SQL Database. I’m curious if any of you have had success with Azure and what types of applications you use the platform to host.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

November 5, 2015
Baseball Analytics

I love baseball. In fact, for the last ten years I’ve participated in an adult league each summer, playing 12-20 games each year against other 40 year olds. While we play the same game with the same rules as the professionals, we have a much different outlook on the game, with many of us enjoying the competition and distraction, but often viewing avoiding injury as a higher priority than winning.

Baseball is an interesting game as the professional season is so long and for over a century there have been a huge amount of statistical data captured about each team. Prior to the fantasy sports popularity explosion, people had leagues that competed against each other solely based on this statistical data. I even wrote a program with a friend on an Apple II that allowed us to set up teams of random players, read in statistics, and then compete against each other using pitching v batting statistics, just showing us text results.

I ran across an interesting post from a consultant that examined a hypothesis he had about winning baseball teams playing better at the end of the season. Using a free data set, the R language, and a Jupyter Notebook, he was able to perform an analysis that proved he was right. Whether or not you agree with him, I would guess that this analysis didn’t take that long to complete, and was fun as well.

With R coming to SQL Server and the increasing call from organizations to perform more complex analysis on our data, I think this is a great way to get started. In fact, I’m thinking that I should load up some of the datasets myself and see what kinds of things I can prove or disprove about my knowledge of the game. If nothing else, looking at baseball stats is probably a fun way to brush up my statistics skills.

BTW: Congratulations to the Kansas City Royals for their World Series win (and to the #sqlfamily in KC).

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and LibSyn.

November 4, 2015
Adding Extended Properties
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

One of the things I needed to do recently was add some extended properties to objects. I got the idea of using them from John McClusky at SQL Bits. He had a great presentation on tSQLt that’s worth watching.

In any case, I wanted to add, and update, extended properties. I had used SSMS to do this, but it’s cumbersome. I decided to experiment and see how the T-SQL code works. My browsing of Books Online showed me there are a few procedures used, one each for adding, updating, and deleting properties. I decided to start with sp_addextendedproperty.

This procedure takes some interesting, rather unintuitive arguments. Name and value are easy to understand. These are the name of the property and it’s assigned value. One thing to note is that value is a sql_variant, which should work fine for most situations, but CASTing may be required.

However the next arguments are level 0, 1, and 2, with a type and name for each. Those didn’t make much sense at first. In fact, as I wrote a few scripts, I had to keep looking up the meanings. Essentially we have three classifications of objects. The outer containers, the objects, and the dependent objects. I’ll explain them below.

The level0 type is essentially the class of object. Is this an Assembly, a Contract, a Schema, etc. For my purposes, this has always been a schema, but certainly you could add properties to the other classes if you needed them.

The level1 is the object type that we usually work with: table, view, function, procedure. For me this is pretty much been table, view or procedure, but certainly function is something I’d use as well.

The level2 is the dependent object: the trigger, the column, the parameter, the constraint. These I haven’t really used, but I certainly think that adding in properties for indexes, triggers, etc are valuable.

Adding a property is easy. For example, one of the items I add is a PK exception for heap tables. To do that (for the SalesHeader_Staging table), I’d run this.
```
EXEC sys.sp_addextendedproperty 
  @name = 'PKException',
  @value = 1, -- sql_variant
  @level0type = 'schema', -- varchar(128)
  @level0name = 'dbo', -- sysname
  @level1type = 'table', -- varchar(128)
  @level1name = 'SalesHeader_Staging' -- sysname
  ;
GO
```
I can see this easily in SSMS.

Properties are great ways to add additional information to an object in SQL Server, though I certainly wish they were more visible in objects.

SQLNewBlogger

I knew there was a procedure to do this, and a quick search on extended properties got me to the BOL reference. I was experimenting with adding the properties while working on this, and I had to research the meanings of the parameters a bit, so this took about 20 minutes to get ready for publication.

Reference

A few items from BOL

sp_addextendedproperty – https://msdn.microsoft.com/en-us/library/ms180047.aspx
November 4, 2015