Voice of the DBA

Author: way0utwest

KDiff3

As I write more code, especially database code, and I collaborate with others (or myself), I find that I have the need to merge code more and more. It’s inevitable, and since I work across a couple machines, I even find that I need to merge my own code at times.

One way to do this well is use a merge tool of some sort. KDiff3 is a popular one, that’s free, and it’s one that integrates nicely with SQL Source Control, which I also use for various tasks.

If you install KDiff3 (use “choco instal kdiff3”) then you get a basic tool that allows you to compare files. If you start it, you’ll get a simple interface that lets you select multiple files and view them together in one interface. If you don’t use version control, apart from making a mistake, you also probably manage code like this.

Or you might have this:

Either way creates problems. If you have multiple people doing this, then you have other issues.

KDiff gives you a nice interface to see the differences between files. As you can see below, I have the changes marked in each file.

I can edit the files directly, or choose to merge them together. If I do the merge, I’ll get a third window that has the merged code.

As you can see, if I click the merge area, I can choose the code from either of the original files.

On the right is a scroll bar that lets me know where in the file the differences are.

Software developers think nothing of these types of tools, but DBAs aren’t as easily used to using tools like this. I’d suggest that you download kdiff3 and give it a chance in trying to reconcile any code between team members or servers.

And start using Version Control. It’s easy and incredibly useful.

April 18, 2016
The Hidden Trigger

I ran across a post recently where someone asked about using a trigger for a particular issue. There were quite a few responses, but one person brought up the fact that triggers have various downsides, all of which I agreed with. However I didn’t see one mentioned, which is a big one with me.

Triggers are hidden.

It’s not that triggers are hidden from DBAs by default, after all, there’s a triggers folder under each table in the Object Explorer, but how often do you open that folder? It’s just not obvious when a trigger exists on a table. The use of triggers is too uncommon, and it’s easy to forget they exist, even if you’re the person that added them to a database.

This is really my one big complaint about triggers. I can’t easily determine when a trigger is firing, which sometimes leads to issues. I’ve troubleshot strange things in SQL Servers, only to realize hours later that a trigger is firing based on some action.

Perhaps it’s me, but do many of you think about triggers first when something strange happens in SQL Server? I think I’ve gotten better over the years at looking for them, but I still get stymied and waste time trying to debug issues. I really wish that a “triggers” tab would appear in SSMS, maybe after the Execution Plan tab, whenever a trigger fired. It could have a copy of inserted and deleted, or even just the trigger code. That alone might clue me in quicker.

I think triggers can be valuable in database systems, but because they seem to be so rarely used, I’ve grown to distrust and dislike them over the years.

Steve Jones

The Voice of the DBA Podcast

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

April 18, 2016
End to End Always Encrypted in SQL Server 2016
Abstract: Protecting our data from unauthorized access becomes more and more important all the time, however it has been difficult to ensure sensitive data is encrypted in SQL Server. The new Always Encrypted feature in SQL Server 2016 makes this much simpler for developers and DBAs with a framework for protecting data from the client, across networks, and inside of the database. This new feature allows for limiting access to the data, even from the DBAs and sysadmins that may control the database instance itself. Learn how to implement and use Always Encrypted in your applications.

Length: 60-75 Minutes
- Demos:
- Always Encryption Setup
- Working with Data in a client application
- Certification creation and transfer
- Certificate Rotation
Powerpoint Deck: End to End Always Encrypted.pptx

Code: Github
April 16, 2016
What’s Your Downtime?

That’s the question this week: What’s your downtime?

I thought about this as I read this piece on Azure’s downtime being greater than its rivals in 2014. You do need to provide an email to read the article, but essentially the article builds on tracking from CloudHarmony to show that Azure had more downtime for the year, with about 54 hours. In Europe, that high water mark was 5.97 hours for compute and 1.31 hours for storage, so the European Azure cloud is doing better than the others.

That’s pretty good. Certainly individual machines went down, and services were unavailable for short periods of time during failover, but keeping a hardware platform up around 5 hours of downtime a year is good. I’m not sure that many of my companies have done this, though to be fair, mostly it’s been patches from Microsoft that caused downtime for Windows machines.

However, let’s look at your database instances. Do you know what your downtime is? I’d encourag you to track it, and be sure that you report on it. Or at least have the data, in case the boss asks. I don’t know what our SSC total downtime is, but I can tell you that our cluster rebooted in Aug 2015 and Mar 2016, brief outages for some patching. In the past, I’ve typically seen our database instances run the better part of a year, usually only coming down for a Service Pack.

If you have monitoring in place, and you should, then I hope you have some idea of downtime for your main databases. If you can share, see if you might set the high water mark for the SQLServerCentral community in the last year.

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.

April 15, 2016