SQL Server Brain Basher of the Week #053 - Data Archiving

We archive data when we see that database has many records, they never get updated and they are rarely read. With traditional archiving way, we take out data and store them in a different database or store them as text files. In most cases, instead of storing exact structure of records, we summarize and store them. However, the traditional archiving process is not sufficient enough for modern requirements.

There are instances you need to read old archived data along with new data. There are instances you need analyze old data for getting some information. Unfortunately, with traditional method, it takes long time for setting up and reading data, and in some cases, not all required information is not available.

Here is the question for the week.

What is the new SQL Server 2016 feature that supports data archiving, addressing new requirements?

The new feature is Stretch Database. This feature allows you to migrate old data into Azure and still be able to query data. It does not require any changes to applications and codes written, end user sees as if they query a on-premise data table.

When configure a table as a Stretch Table, it can be set to "stretch" the entire table or partially, using an inline function that filters data. Once enabled, this feature set some restrictions to the table. You cannot UPDATE/DELETE archived data and uniqueness does not force for archived data via UNIQUE or PRIMARY KEY constraints. For more info on limitations, read: https://msdn.microsoft.com/en-us/library/mt605114.aspx.

You can see whether your table can be stretched or not using Stretch Database Advisor in Microsoft SQL Server 2016 Upgrade Advisor. If the Advisor says that the table can be stretched, then you can go ahead and stretch it.

The steps for making the table stretched is simple.

1. Check and see whether table can be stretched, using the Advisor.
2. Right-click on the table, click on the Stretch menu item and follow the pages in the wizard.
3. You can either select the entire table or click on Entire Table for filtering. If you click on it, you get a screen like below. You can set the function as you want (see below image).
4. Next screen is for connecting with Azure. Enter your credentials for log in.
5. There is an option for SQL Server selection. You can either create a new one or use an existing one. Fill necessary details and submit.

Now your table is stretched.