Sunday, April 21, 2019

A brief note on version...

With Microsoft's move away from the Service Pack model, SQL Server has seen more frequent updates. This is generally a good thing, but it does pose a problem for those of us who write about it. Information gets out of date. New functionality comes in, old functionality is deprecated, documentation for older versions disappears, and the great cycle of technology continues. Many of us (like me!) write these blogs in our free time, to educate the community and ourselves, and it can be difficult to go back and maintain older posts to ensure they're up-to-date.

I have both Windows and Linux versions of SQL Server (at the time of this writing, my lab VMs are SQL Server 2017 CU14 on Windows 10 and Ubuntu Server 18.04 LTS; I have spun up a container or twelve in my time as well, and of course there's Azure SQL Database, and...), and I keep them up-to-date as new cumulative updates are released. I'll do my best to post version information as I write, but I probably won't go back and update old posts unless there's a really big change. Even then, I'll probably write a new post instead.

If you need historical version information, go directly to Microsoft SQL Server Versions List, do not pass GO, do not collect $200. Maybe toss 'em a couple bucks. You can also now find documentation for previous versions of SQL Server on the SQL Server previous versions documentation page.

Tracking Data Changes - Introduction

Unless you're managing a static archive database, the data in your databases will change. Odds are, at some point you're going to need to know about these changes for one reason or another. Perhaps you're using a data warehouse and need to retain history, or you need to keep track of slowly-changing dimensions. Perhaps you have an application that needs the latest data, and you want an easy way to feed it without querying the entire table for timestamps or update flags. Perhaps you have audit requirements and need to keep detailed change history.

Whatever the reasons, SQL Server has a few options to satisfy various use cases. Your options range from the 'roll up your sleeves and do it yourself' triggers, to Change Tracking, Change Data Capture, and Temporal Tables.  In this series, I'll show you these options and how to use them. We'll begin with Change Tracking, then move on to Change Data Capture, Temporal Tables, and conclude with triggers.

If you're using a platform other than SQL Server, please refer back to your RDBMS's documentation (or - let's be honest here - StackOverflow followed by your search engine of choice) for information about tracking data changes. Many platforms have similar functionality to handle this need, but if nothing else triggers are almost always a viable option, if a bit more manual work than we might like.

In SQL Server, some of the functionality depends on your version and edition. The following table provides a quick summary:

Tool Debut Edition
Change Tracking SQL Server 2008 All
Change Data Capture SQL Server 2008 Standard as of SQL Server 2016 SP1, Enterprise prior
Temporal Tables SQL Server 2016 All
Triggers Back in the dim mists All

Here is a (very) quick overview of the various options:
Change Tracking:
  • Uses an internal versioning mechanism to track changes
  • Just tells you that a change happened (it can tell you WHICH column was changed) - no history
  • Snapshot isolation, so be mindful of TempDB
  • Reasonably tolerant of schema changes
  • Requires a primary key
  • Automatically cleans up after itself
Change Data Capture:
  • Uses a log reader agent (hello sp_replcmds!) to capture change data
  • Can give you full change history
  • Does not play nicely with schema changes 
  • Allows two capture instances per table
  • Highly configurable - capture only columns you want
  • Automatically cleans up after itself
Temporal Tables:
  • Also requires a primary key
  • Also gives you the full change history
  • Uses period columns in the source table to feed the history table
  • Easy to query
  • In SQL Server 2017, introduced the capability to clean up after itself
Triggers:
  • Offer you amazing flexibility in designing your tracking
  • Let you capture metadata and stuff the other solutions may not allow
  • Require you to manage everything from soup to nuts
There is a lot more about each of these methods, of course, and in the course of this series I'll walk you through each in some detail. Please check back soon for an introduction to Change Tracking!