Voice of the DBA

Category: Blog

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
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
Looking Back at Summit 2015

Last week was the 2015 PASS Summit in Seattle. It’s the largest SQL Server specific gathering of data professionals in the world, and it’s an exciting week. If you work with SQL Server in the US and have the opportunity to attend, I’d recommend you go. If you’re in Europe, I prefer SQL Bits, but either of these events is exciting and inspiring.

The week began for me with SQL in the City 2015 – Seattle. As I work for Redgate I was tasked with presenting the event. The day started with my helping our CEO deliver the keynote. We’d done this a few weeks earlier in London, so we’d had some practice. I think the opening went well, and I enjoyed it.

Immediately after I had a session on database version control. There were lots of great questions, so many that I ran a bit long. However it was good to see some great advocates of VCS in the audience as well as people interested in the topic.

I also ran a panel on DevOps and smoother database development. I often don’t like panels, but this is a great topic, and with a number of consultants on stage, we had some good discussions. I would like to see more of these at events, especially with people that are building software successfully. They have great stories to tell.

The Monday night networking dinner went well. It was a bit crowded at the Yard House, but Andy and I spoke with the managers Tuesday and we have some different plans for next year. However we had over 150 people stop by, which is a great crowd.

It’s always a pleasure catching up with friends who I rarely see in person. Especailly the great Pinal Dave, one of the few that’s been to the ranch.

Tuesday was a quiet day for me. I went to the SQL Saturday organizer meeting, seeing some changes coming to the SQL Saturday site as well as concerns and requests from organizers. With most of the people in Seattle at a pre-conference session on Tuesday, things were quiet. I also had some rehearsals for my testing talk to go over, so I spent the afternoon in my room going over demos.

I did take a break for #sqlyoga with Daniel de Sousa. I have come to enjoy yoga, and would like to see some organizations at other events. It’s just as enjoyable as a #sqlrun. It was the two of us and lots of young people in downtown Seattle, but a great break.

Speaking of which, Wednesday morning I met up with Allen White and a few others for a morning #sqlrun along the water. This has been a bit of a tradition for many people, and we had a good group of 20 or so jogging along.

The rest of Wednesday was a few lightning talks at the Redgate booth and a couple live practice sessions of my talk in my hotel room. Not very exciting, but this was the end of a stressful month with lots of travel.

Both Tuesday and Wednesday night have evening events at the convention center. Tuesday is the opening reception, with lots of people milling around a large room with some appetizers and drinks. Wednesday is in the expo hall, with dinner and drinks and the chance for vendors to meet attendees. It was a large crowd both nights, with the chance to see lots of friends in the SQL community and meet new people.

Thursday had me watching Dr. Dewitt and Dr. Nehme’s keynote on my laptop while I did a little other work. I have to say that the live keynote was probably better, but this was an interesting topic on IoT. You can rewatch it if you missed it. Then I went to a DocumentDB session, supposed to show case studies of who’s using the technology. We heard a little of that, but not much technical detail, and the session went sideways with lots of antagonistic, almost angry, questions from the attendees. I really wish SQL Server people wouldn’t be so upset about NoSQL technologies. They work in places, so let’s discuss where things work well.

Lunch had me in the expo hall for a bit before taking our Argenis Without Borders 2.0 picture. We raised $25k for Doctors Without Borders, which is amazing. Thanks to everyone that participated.

My session on testing T-SQL code went well. Lots of questions, lots of people. I walked in a few minutes before my start time to find 150 or so people in the room, which was amazing. I thought I’d get 15 people to talk testing, and I’m glad so many people are interested in the topic. Hopefully I inspired a few people to start testing code.

That was really the end of the event for me. I had missed most of lunch, so I went for a short snack before the Friends of Redgate dinner. A late night hour with the Varigence Biml crowd before getting to bed. Then I was off Friday for home, capping off 18 days of travel for the month.

This was a quiet conference for me. With pressures to deliver a good presentation for the first time, I spent more time than I expected getting prepared. However it was great to see so many friends and fellow #sqlfamily people at the event, and I enjoyed the conversations and pictures on Twitter throughout.

I missed being there Friday, hearing about everything online, but I was also worn out from the month. My apologies if you wanted to say hi and couldn’t find me. Thanks if you took a few minutes to chat with me.

The event comes back to Seattle next year, and hopefully I’ll spend more time at the event.

November 3, 2015
DLM Dashboard moves to 50 Databases – For FREE

I love DLM Dashboard. It’s the product that I’ve written myself, in various ways since SQL Server 6.5. My versions weren’t as pretty, often scripts that ran queries, returning loads of data in text format, from queries, trace, or Extended Events.

However there’s been a limitation that affected even me. When DLM Dashboard was released, it allowed you to monitor 5 databases. That increased to 10 if you registered, but that still wasn’t enough. I have 4-5 databases, many in a few environments, that I’m trying to monitor.

I had a meeting with the DLM Dashboard team a few weeks ago, and we discussed a number of items, including the limitations. We debated the topic, and I got word recently that the limitation has been raised.

DLM Dashboard now allows you to monitor 50 databases, which covers quite a few of your environments, and quite a few complete pipelines. I’d urge you to give it a try and download the product today.

We understand some of you might need more, or that there are other features you’ll want. The team wants to hear from you on UserVoice, which you can also get to by clicking the "Give Feedback" link in the app.

This is one of the best free products I’ve seen in a long time, and I’m excited that Redgate built it.

Now I don’t have to at any other job in the future.

November 3, 2015