Wait For Service Pack 1

Conventional wisdom tells us that when Microsoft releases a new version of any server product, we should wait until Service Pack 1 before deploying it to production.

This hasn’t been true for a while now, since Microsoft recommended that Cumulative Updates for SQL Server carry the same confidence:

SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.

However, Service Pack 1 itself has been mired in some controversy. Microsoft didn’t make things any easier for us with SQL Server 2012, or 2014. Both versions had issues with their respective Service Pack 1.

Fortunately, SQL Server 2016 has broken the cycle, and along with all of the fixes in Cumulative Updates 1–3, and a security fix, we get better feature parity between Enterprise Edition and lower editions, including Standard, Web, Express and LocalDB.

There are some restrictions, of course, but the idea is that developers and vendors can write T-SQL for features that now appear across the board.

SQL Server 2016 Service Pack 1 now includes the following features for Enterprise, Standard, and Web Edition:

  • In-Memory OLTP
  • In-Memory Columnstore
  • Always Encrypted
  • Partitioning
  • Data Compression
  • Change data capture
  • And more!

If you want to take advantage of these features, but you use an older version of SQL Server, you will need to upgrade to SQL Server 2016 with Service Pack 1, but I think this is a no-brainer.

The good news is licences have cost the same since SQL Server 2012, and Standard Edition is almost a quarter of the price of Enterprise Edition.

I maintain that this is the most exciting release of SQL Server since 2005. If you want to upgrade, contact us and we’ll help. We will even upgrade you from SQL Server 6.5.

Compressed and Encrypted Backups on the Cheap

We back up our databases regularly. We store them on a secure network location so that if the database drive fails, we can still satisfy the requirements for a rapid disaster recovery time (RTO, or Recovery Time Objective).

Some of us store these backups off-site as well, as an added security measure, either on tape or using cloud storage solutions (Azure Blob Storage, Amazon S3).

And we encrypt these backups, right?


SQL Server 2016 has some very neat features for encrypting databases, tables, rows, backups, and even in-memory stuff.

But what if we are still slogging away on the ancient SQL Server 2008 or 2008 R2 Standard Edition, and we need to compress and encrypt our backups, using industry standard AES-256 encryption?

Enter 7-Zip. It is free, open-source, and governed by the LGPL licence.

7-Zip Logo

The GUI (referred to as the 7-Zip File Manager) is installed by default. It looks something like this:

7-Zip File Manager GUI

7-Zip File Manager GUI

Additionally, there is a command-line version (7za.exe), which has the same features as the 7-Zip GUI. You can get the command-line version by downloading the 7-Zip Extra package.

This command-line tool can be used for scripting in batch files or PowerShell scripts, which means we can automate a whole lot of things with our backups. There is both a 32-bit and a 64-bit version available.

7-Zip (a) [64] 15.14 : Copyright (c) 1999-2015 Igor Pavlov : 2015-12-31

Usage: 7za <command> [<switches>...] <archive_name>
       [<file_names>...] [<@listfiles...>]

[Tip: I highly recommend using the default 7z archive format, instead of Zip. It provides a significant disk savings over the Zip format and requires about the same time to compress at the Normal compression level.]

The scenario

Let’s assume that we have a limited budget. We can afford SQL Server Standard Edition and 1TB of off-site storage, in the form of Azure Blob Storage, Dropbox, or Google Drive, to name just a few.

We can use 7-Zip on the command line to produce an encrypted file, even if we don’t want to compress it (7-Zip can create what I call “container” files, which are simply an encryption wrapper around the file) and push that to our Dropbox sync folder.

Remember that SQL Server 2008 R2 and higher allows us to compress backups in Standard Edition. It makes very little sense to compress an already-compressed backup.

Command-line switches

I use this page as a reference for 7-Zip command line examples. Of particular interest is -mx0, (which will store, but not compress, the source file), and -pSECRET (which allows the use of a password, where SECRET is the password).

Putting the pieces together

Step 1: Back up our databases (with compression if available)

Step 2: Copy the files to a secure network location

Step 3: Use 7-Zip to encrypt (and maybe compress) these files

Step 4: Copy the encrypted files to off-site storage

There are myriad tools to perform these steps, and there’s no single correct way to do it, though I prefer PowerShell for a number of reasons. It’s modern, it is still updated, and it can interact with a number of Windows features in a consistent way, which means we can automate a lot of our everyday tasks.

For example, if we stay on the Microsoft stack with Azure, we can use PowerShell to iterate through the backup directory, encrypting each backup file, then copy it to Azure using a tool like AzCopy, or even my Azure Blob Storage Sync tool.


We don’t need the latest and greatest (and expensive) tools to implement best practices. Using 7-Zip, Dropbox and PowerShell, we can easily ensure that our databases are secure in more ways than one.

Modifying Temporal Tables – A Primer

This is part three of the Temporal Tables series. You can read parts one and two here.

Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history table—populating it with triggers or stored procedures—it works the same way.

This week, we are going to look at how to modify a temporal table that already has data in the history table.


Modifying a temporal table can potentially break the audit trail.

In other words, you will be able to modify historic data. Be cognisant of this.

The safest way to control this is by securing the ALTER TABLE command in your production environment, using GRANT ALTER.

In our Account table example, let’s grant privileges to the Admin role (which must exist on the instance, of course).

-- Only allow a certain role to perform ALTER
GRANT ALTER ON OBJECT::[dbo].[Account] TO [AdminRole];

With that in mind, we can continue.

Modify my temporal table

Let’s say we need to make the [AccountName] column in our example table longer, to support 2000 Unicode characters.

Temporal tables are linked to their history tables in a manner similar to views created with SCHEMABINDING.

This means that no changes can be made to either table without breaking the temporal link first.

To do this, we have to run an ALTER TABLE command with the SET SYSTEM_VERSIONING parameter set to OFF.

[Edit: The syntax changed for the latest CTP 3.3, and the command requires brackets around the SYSTEM_VERSIONING statement.]

-- Disable System Versioning on Account table

Now we can modify our primary table and history table with the same change.

-- Modify our primary and history tables
ALTER TABLE [dbo].[Account]
ALTER TABLE [History].[Account]

Once we have made our modifications, we simply set the SYSTEM_VERSIONING parameter to ON and link back to our history table.

ALTER TABLE [dbo].[Account]
   SET (
         HISTORY_TABLE = [History].[Account],

The DATA_CONSISTENCY_CHECK parameter should be enabled, as recommended by Microsoft, to ensure that the history table follows the correct structure and format as the primary table.


Because it’s that easy to make changes to temporal tables, it is just as easy to break the audit chain. Make sure you are not breaking any compliance rules.

If you have to change a temporal table, it may make more sense in your audit policy to create a new history table, active from that change, and retain the old history table untouched.

Next week we will look at when it is appropriate to use temporal tables and some factors to consider around making that choice.

End of Life for Microsoft Windows XP and Server 2003: Six-One or Up

Five months from now, Microsoft will stop supporting Windows XP. As Tim Rains points out, running Windows XP after this date will effectively be an open invitation for attack. XP will, in his words, be “zero day forever”.

As you know, Windows XP is the client version of Windows Server 2003, and includes much of the same code. Windows Server 2003 R2 was an incremental update that improved some security aspects, but it’s still the same code base. That will reach its end of life in July 2015, just over 18 months from now.

Why is this important? Simply put, XP and 2003 R2 are old. Security which was state of the art ten years ago, is no longer good enough. It is too expensive, both for you and Microsoft, to stay on antiquated software.

While I am a vociferous supporter of “use what works” and “if it ain’t broke, don’t fix it”, I must add a qualifier: XP and 2003 are broken. Their internals are just not up to the challenge of modern attacks.

The same goes for perennial whipping-boy, SQL Server 2000. Up until two months ago, I was still actively supporting this product at one particular client, but my goal was to upgrade them as soon as possible. Notwithstanding the huge benefit in new features, upgrading to a newer version offers more modern attack mitigation.

I was affected by SQL Slammer in 2003, because I didn’t apply an earlier patch released by Microsoft. I vowed that would never happen again.

It’s time. It’s time to think seriously about security, to harden your defences, to upgrade your software.

If you’re on Windows XP, get on to Windows 7 or Windows 8. Windows 7 with Service Pack 1 is under mainstream support until 2015, and extended support until January 2020. Windows 8 and 8.1 (considered the same product for support purposes) will reach end of life in January 2018 (mainstream) and January 2023 (extended).

As for the Windows Server products, the least you should be running on is Windows Server 2008 R2, which is on the same retirement timeline as Windows 7 SP1.

If it helps, I have this simple reminder: Six-One or Up. The Windows kernel had a major increment to version 6.0 with Vista and Server 2008, and 6.1 for Windows 7 and Server 2008 R2. In other words, version 6.1 is the lowest Windows kernel you should be running anywhere in your environment. If in doubt, type ver in any command prompt on a Windows machine. On this client, I see Microsoft Windows [Version 6.1.7601].