Voice of the DBA

Author: way0utwest

Looking Back at 2016

We’re coming to the end of a crazy year. 2016 has seemed to be one of the craziest of my life with world events like Brexit and the US election as well as an astounding number of data breaches. More people who impacted my life passed in 2016 than in any other year I can remember, and I traveled far, far too much this year. Quite a change from the beginning of the year when the Denver Broncos won SuperBowl 50. 2016 has also been a very interesting year in the data world.

Certainly the release of SQL Server 2016 was exciting for many of us. For the first time since 2012, or really since 2008, I thought this was a true, major release of the platform. I was surprised and pleased by the amount of features added and improvements made to this version. I very much liked to see the inclusion of a number of security features. While some of these need some maturity and work, they do bring us some additional capabilities that I think start to help us implement better data protection for our database systems.

We’ve also seen a few things I’ve written about for years coming true in the SQL Server world. We have a Linux version in CTP status, due to be released next year. Whether adding a Linux edition is a good idea or not remains to be seen, but I am glad that Microsoft is making an attempt to port SQL Server to other host platforms. With SQL Server 2016 SP1, we also have a common programming surface, allowing almost all of the T-SQL features that were previously only in Enterprise edition to be used in other editions. This means we’re closer to paying for SQL Server based on the scale of data we process. I think this is a good move that makes sense for Microsoft and customers. While some might lament the hardware limits on Standard Edition, I think they are fine. I just wish it wasn’t sure a big jump to move from Standard to Enterprise, or there were an option in between the two.

The cloud has grown tremendously in 2016, in many areas, but certainly for data. While AWS and Azure grew in size, they also lowered prices for users. It’s not clear how much of this usage is just for database work, but I certainly think that more and more organizations are looking at moving a portion of their data to the cloud. When you can store data cheaply and scale your query computing up and down, this starts to look like a viable option for some workloads. I don’t know that I think most RDBMSes used for on-premise applications make sense in the cloud, but some do, especially when your customer base is distributed and your workload has predictable spikes.

I think the idea of cloud databases for analytics and warehousing makes more sense. Those are the workloads that require larger hardware for peak workload levels and become expensive for local systems. Getting your data to the cloud is a challenge, but I suspect that data movement, gateways, and other innovative ETL (or ELT) solutions are coming. The Azure SQL Data Warehouse is a very interesting product to me, as is the Azure Data Lake, and I look forward to seeing how people start to use these solutions in the future. Certainly the cloud is going to continue to play an interesting role for data professionals in the future.

This was an interesting year of hardware for me. The DevOps movement has said that we should treat servers like cattle, not pets. I’ve started to try and do this with hardware as well. I got a new laptop (VAIO Z Canvas) in 2016, and after setting up my old one with Chocolatey, I did the same with the new laptop, becoming productive with my new machine in a few hours. It helps to have various distributed data services like Evernote, Dropbox, and remote Git Repos, but I suspect many of you have similar services inside of your organization. When I rebuilt my desktop this year, I was using it about an hour after I rebooted the new hardware thanks to Chocolatey. This really make me rethink of my individual machines as cattle. Provided I have some good way to remotely keep various data accessible. That brings me to another interesting issue.

Data breaches were an issue in 2016, as in years past. They seem to be occurring on a regular basis and increasing in size, though it’s hard to determine whether or not the impact to individuals is greater. The Yahoo breaches were incredible in size, with over 1 billion accounts affected. However, other security issues are just as worrisome. The DDOS attack on DYN shut down a number of sites, but what if a more subtle attack managed to change DNS entries. I’d worry that as more of our data is accessible through public networks, the compromise of credentials could lead to more data loss issues.

However, one of the most common issues with computer security has been shown to be out of date software with vulnerabilities where patches have been available. To me, this means we could thwart a significant number of breaches by keeping software up to date with patches. This brings to mind plenty of other issues, but ensuring our platforms are patched seems to be important. Perhaps ensuring we can patch our application software quickly if there are issues from patching platforms is one way to improve security.

Perhaps one of the items that I think dramatically changed in 2016 was the growth of data analysis. Whether we look at the call for data scientists, R analysis of data, visualizations with tools like Power BI, big data or something else, it seems like these were important topics in 2016. This might be the first year where I think that business intelligence truly took a leap forward with tools designed to make analysis easier, and lower the bar. This is interesting, and perhaps profitable, for the average data professional to use in their jobs, but all these tools and options for analyzing data don’t necessarily mean that we will better analyze data. I suspect that many of the initiatives started by individuals and organizations will be abandoned in the short term because the experiments aren’t well designed, and the effort to cleanse and prepare the data for some predictive analytics is greater than what most companies want to invest in this project. However, there are more tools and ways for people to begin their journey to better understanding statistical methods and use them for analysis. The data professional of the future (say 10 years from now) will have a better understanding of data analysis techniques as they begin their careers. Just as we expect most data professionals to have some understanding of relational databases and SQL today.

It’s been a long year, and I, for one, am glad to see it ending. Some great memories and trips, but too much travel for me. I only took 16 work trips (and 5 personal ones), but three of those were over two weeks and I spent about 80 nights in hotels. Hopefully I can substantially lower those numbers in 2017.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 11.1MB) podcast or subscribe to the feed at iTunes and Libsyn

December 29, 2016
Post TDE–Getting Unencrypted Backups

I saw a question posted recently about someone that had disabled TDE and was still having issues restoring a backup. This doesn’t seem like that should be an issue, but it can be. A little testing shows how.

Let’s assume I have an encrypted TDE database. If I run a query, I can see the status as 3, which is encrypted.

If I take a backup at this point, the backup will be encrypted, and to restore this on another instance, I’d need to first restore the certificate. I don’t want to do that, so let’s remove encryption. This is a simple command

ALTER DATABASE TDE_Primer

SET ENCRYPTION OFF;

This runs quickly.

If I now query for encryption, I see this.

A one means that this is an unencrypted database, but a DEK (Database Encryption Key) exists. If I were to detach and examine this database file with a hex editor, the pages would be decrypted.

I’ll now take a backup and move that to another instance. Once I’ve copied that over, I’ll try to restore the backup. In T-SQL, I’ll see this:

Why is this? The database was decrypted, as was the backup. In fact, if I open my backup file in a hex editor, I can see row data.

The Problem

When SQL Server goes to restore the file, it reads part of the header. In here, the process must detect the DEK and try to decrypt that key. However, since this new instance does not have the certificate, this doesn’t work and an error is thrown, despite not needing the key since the data isn’t encrypted.

The issue here is the DEK still exists in the source database.

The Solution

Let’s fix this. I’ll return to my first instance and the original database that was TDE encrypted and now is not. I can issue this:

DROP DATABASE ENCRYPTION KEY

Once I do this, it completes quickly. This is a standard DDL command, but one that’s not often used.

Once I do this, I’ll take another backup and return to the second instance. Now when I try the restore, I see this:

If you’re having issues restoring a database that used to be TDE encrypted, try removing the DEK and then backing it up.

December 28, 2016
Back After the Break

Refreshed and back to work today. After a week at Redgate Software for SQL in the City, I took a week to travel in Ireland with my family. In that time, I really ignored almost all email and didn’t manage SQLServerCentral at all.

Well, I did fix one article bug and replied to a couple comments, but mostly I ignored things. Even over Christmas and yesterday, back in the US, I tried to really ignore most work. I spent about an hour doing some SPAM cleanup, but that was early morning because my body clock was still on GMT time.

However, I’m back at it today. Up at almost a normal time (no kids to get to school, so slightly late), I wandered to the office, made coffee, and got back to editor duties.

I feel nice and refreshed. Being disconnected in Ireland for most of a week, I really had a nice chance to relax and not think or worry about computers and technology. I even avoided most Twitter/news on my phone, just getting away.

Now it’s time to get back on track, and I’m a little overwhelmed as I’ve been out of touch for almost two weeks. Feels like I have a lot of editing to do and plenty of scheduling and certainly need to get back to learning more and improving myself.

Hope everyone else had a great holiday break.

December 27, 2016
Syntactic Sugar

There are all sort of features and enhancements that Microsoft can make to the SQL Server platform. If you look around Connect, you’ll see suggestions for improvements, such adding common checks, as well as additions like adding virtual tables. I’m sure many of you would like to see simple things, like Regex added to the T-SQL language. In fact, you might find that some of these small changes, which we can code around or build, should just be added. After all, if Dynamic Data Masking can be added, shouldn’t some other simple features be included, such as helping us solve the “string or binary data truncated” error?

These handy, useful, simple changes to the platform are often called syntactic sugar. These are changes that are simple, many of us could easily code them, but they make development easier. Or even administration in the case of the SQL Server platform. These are not necessarily expanding the power or capability of the platform, but they can make working on the system more enjoyable.

Should Microsoft create more syntactic sugar for SQL Server? Certainly they do at times, but perhaps not as much as many of us would like. The thing to keep in mind is that making changes to the SQL Server platform can be very difficult and time consuming. Adding small features that might be helpful, while enticing, can slow the evolution and development of more core product features. Or they can cause more problems than we might expect in other parts of the platform. Would you rather have something like DDM that makes obfuscating some data easier, or a more robust replication engine that recovers from more problems? Do you want stronger security features like Always Encrypted, or more robust Always On features, or is it more important to get Regex added? I think we might have differing opinions here.

These can be really hard questions, and certainly I think our feedback can help influence Microsoft. After all, if there is a nagging issue that is constantly causing issues, then maybe it’s worth a syntactic sugar improvement, even if this takes resources away from some other area. The one thing I hear from Microsoft over and over is that specific business cases and issues are more important than complaints. Express the issues you have with the platform in terms of workarounds, developer time lost, or specific performance issues rather than just a “I don’t like this” or something “doesn’t work as expected.”

Ultimately Microsoft is a business, and they do look to add new features regularly to the platform to increase sales. I get that, and I try to temper my requests and complaints. I like to see them focus a portion of resources on core improvements to systems that work, and while I think this does happen, I’d like to see a bit more improvement in existing features. Certainly our backup and log reading systems have improved over time. SSMS is decoupled and being updated regularly. There is more work to be done, and if lots of us provide specific feedback, I’m sure we will see even more core improvement over time.

Steve Jones

The Voice of the DBA Podcast

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

December 27, 2016