NO, that is certainly not a typo. I really did intend to say SQL Server 2005 and SQL Server 2008. Note that a previous blog post mentioned that you only have less than a year before SQL Server 2005 is out of extended support. So, why, you might ask, would I still be talking about features from a product that is almost on its way out of the door?

For one, just because SQL Server 2005 is expired doesn’t mean we all would be getting rid of it. SQL Server MVP and Microsoft Certified Master Brent Ozar (blog | Twitter) highlighted in one of his previous blog posts that SQL Server 2005 is even more popular than SQL Server 2014. And that’s just the reality. Businesses will take some time to upgrade their database infrastructures to the latest and greatest version. So, as the caretakers of the data infrastructure, we still need to support and maintain these databases.

But the main focus of this blog post is for us to be informed that some of these “old features” have paved the way for those that have been made available in the latest versions of SQL Server. When one of the developers in the SQL Server product group explained a feature introduced in SQL Server 2012 and one in SQL Server 2014, I thought, “these developers are geniuses.” It’s like watching a TV series where the latest episode built on top of the previous one. Here are some of those “old features” that you need to be familiar with and why:

1. Database mirroring. This feature was first introduced in SQL Server 2005 but wasn’t fully supported until SQL Server 2005 Service Pack 1 was released. It was a bit flaky when SQL Server 2005 RTM was released but improved as time goes on. Think of database mirroring as real time log shipping – transaction log records applied to the principal (or the primary/main) database are sent to the mirror (or secondary) database and persisted on disk thru the transaction log file. The best thing about database mirroring is that it is available on Standard Edition. Unfortunately, this feature has been deprecated in favor of Availability Groups in SQL Server 2014.

   But why should you need to be familiar with this feature? Because this became the foundation of  the Availability Groups feature in SQL Server 2012. The underlying principles and concepts are the same but was made even better. In fact, I almost got into trouble telling people when Availability Groups was introduced that it is not a new technology. It is an improved version of existing technologies – database mirroring running on top of Windows Server Failover Clustering. If you have a fair understanding of how these two technologies work, it would be easy to grasp how Availability Groups work. That’ll make it easier to design an Availability Group architecture that will meet your recovery objectives and service level agreements. Unfortunately, Availability Groups is only available in Enterprise Edition for both SQL Server 2012 and SQL Server 2014.

2. Database snapshots. This feature was also introduced in SQL Server 2005 but required Enterprise Edition. A database snapshot is a read-only, static view of the source database. They are commonly used for running reports off of the primary database or a mirrored database, as a backup for performing administrative tasks and even unexpected user error. I’ve used database snapshots in the past as a rollback option when applying application-related updates on databases because it is definitely cheaper to take and restore from a database snapshot than a full database backup.

   How it works is that the database snapshot creates a sparse file – a feature in the NTFS file system – with no user data and where the corresponding database disk space has not been allocated. When the original database gets updated, a copy of the updated data page when the snapshot was taken will be copied into the sparse file so that the snapshot will look like database when the snapshot was taken. This process is referred to as copy-on-write operation. All unmodified data pages will be read from the original database and not the snapshot.

   But why should you need to be familiar with this feature? Because this became the foundation of  the Availability Groups readable secondary replica feature in SQL Server 2012. If you intend to use an Availability Group secondary replica to offload read-only workloads on the primary database, understanding database snapshots will help you plan for capacity and performance. Since the Availability Group secondary replica is a read-only copy of the database that is in continuous recovery mode, we won’t be able to read anything off of it – technically. The only way to do so is to take a snapshot and read from the snapshot – just like how we would do it in a database mirroring configuration. Availability Group readable secondary replica provides a way to automatically create snapshots internally within the database so that we don’t have to do it ourselves. But we do need to be careful when implementing Availability Group readable secondary replicas. That’s because, besides using database snapshots, they rely on another “old feature.”

3. Row Versioning. This feature was also introduced in SQL Server 2005. It implements the READ_COMMITTED_SNAPHOT isolation level. In a previous blog post, I mentioned how this feature helps with the implementation of the Availability Group readable secondary replica and why you should be aware of the storage and performance impacts that you may experience if you decide to implement this feature.
4. Filestream. This feature was introduced in SQL Server 2008. When it was first introduced, I knew that it may have something to do with SharePoint and its Remote BLOB Storage feature. How it works is that you can store non-relational data like images, files and videos in the Windows file system but access them via SQL Server.

   But why should you need to be familiar with this feature? Because SQL Server 2014’s In-Memory OLTP  (or Hekaton, if you still want to call it that) feature uses filestream. SQL Server runs the checkpoint process on a regular basis to reduce recovery time and maintain transaction durability. If a SQL Server 2014 and higher database implements the in-memory OLTP feature, the checkpoint process will write files – called checkpoint streams – in the memory-optimized filegroup. If you look at the syntax in creating an in-memory OLTP database, you’ll see that it requires a specific filegroup for the in-memory tables.

   CREATE DATABASE SampleDB
ON
PRIMARY(NAME = [SampleDB_data],
FILENAME = 'C:\Data\SampleDB_data.mdf', size=500MB),
FILEGROUP [SampleDB_mod_fg] CONTAINS MEMORY_OPTIMIZED_DATA
(NAME = [SampleDB_mod_dir],
FILENAME = 'S:\data\SampleDB_mod_dir')

   LOG ON (name = [SampleDB_log], Filename=‘L:\log\SampleDB_log.ldf’, size=500MB)
   

   If you open the folder defined in the filegroup, you will see a familiar file system structure – that of the filestream feature – one that has been there since SQL Server 2008.

While we SQL Server DBAs prefer working with the latest and greatest versions of the product, the business has its own pace when it comes to upgrade and migration. We might not see SQL Server 2012 or SQL Server 2014 any time soon, we can always look at some of the features from the earlier versions that paved the way for the implementation of the new ones. I know that you’ve indicated “Inefficient/outdated tools and technologies” as one of your biggest challenge at work based on this survey. I guess we should start seeing the beauty wrapped within the obsolete technologies to better understand the new ones.