SQLUNINTERRUPTED

Its been a long time since I have written a blog and what better way to start again than by writing about one of the most interesting features being introduced in SQL Server. In a series of blogs over the next few weeks, I will try and get into the details of how Stretch Databases are implemented and some of the inner workings of the feature to watch out for.

Before, we delve into the feature its important to understand that SQL 2016 is still in the CTP phase and some of the things can change once the product RTM’s. I will blog about the changes if any in another post when it happens.

Ok, so without wasting any more time, Stretch Databases, as the name suggests, stretches your databases/tables to a remote storage, while masking the implementation details from the end user. You continue to access the table in the same way as before , while SQL Server internally traverses the local and the remote storage to get the requested data set. In the current release, the Stretch option moves the entire table to the remote storage, which is a V12 Azure SQL Database (Standard S3 Tier).

Use cases for Stretch Databases

Stretch Databases are useful in scenarios where there is a ton of transactional data, which needs to be stored in your environment for historical querying or maybe government regulations. You only query/work with a small subset of data in these tables (mostly latest transaction data) on a frequent basis. Even with the modern day archiving, manageability and compression techniques (like ColumnStore Indexes, partitioning, using combination of tables/views) the storage cost or the man hours required to update/change your application are prohibitively high, when compared to the Azure Storage utilized by the Azure SQL Database.

Implementing Stretch Databases

Implementing Stretch Databases is really simple. In order to enable a database for Stretch, the ‘Remote Data Archive’ configuration option needs to be set. This can be done using the sp_configure command

   1: sp_configure 'remote data archive', 1

   2: Reconfigure

Next, right click on the DB, on which stretch needs to be enabled, go to task->‘Enable Database for Stretch’. This would launch the wizard for stretch configuration. Sign in with your Azure credentials

The window let’s you choose the subscription in case there are more than one. The next screen allows you to select the Stretch Settings (essentially the location of the Azure SQL Database, the admin login for the WASD server and setting the IP exception rule for your current SQL Server IP).

Once you have made sure the settings are current and the click on finish, the following steps are executed on the SQL Server and the Azure SQL Database.

1. Provision a New SQL Azure Database Server (fixed naming convention)
2. Configure the firewall exceptions for the WASD server
3. Create a Credential on the current SQL Environment for the WASD
4. Create a linked Server to the Azure SQL Database Server
5. Create the SQL Azure Database.

Stretch DB creation creates a log in the “\Users\<current_user>\AppData\Local\SQL Server\Stretch Database to SQL Azure” folder, which can be used for troubleshooting purposes.

Once the option is enabled for the database, the following entries are logged in the SQL Error Log.

   1: 2015-06-20 23:15:44.89 spid51      Setting database option remote_data_archive to ON for database 'StretchDB_Test'.

   2: 2015-06-20 23:15:49.88 spid51      Setting database option remote_data_archive to ON for database 'StretchDB_Test'.

The Wizard does not allow you to choose an existing WASD server, but if creating through T-SQL, you can choose an existing WASD server.

   1: /* Enabling Stretch Database Using T-SQL */

   2:  

   3: /* Step 1 -> Enable Firewall Exceptions on the Azure SQL DB */ 

   4: -- Can be performed through the Azure Portal/Commands on the sp_set_firewall_rule Extended SP on the master DB of the WASD Server

   5:  

   6: /* Step 2 -> Create the Credentials for the WASD Server (linked Server would use these credentials */ 

   7: CREATE CREDENTIAL [tnkll47icl.database.windows.net] 

   8: WITH IDENTITY = '<ServerAdmin>', SECRET = '<Password>'

   9:  

  10: /* Step 3 -> Enable the Stretch DB Option */ 

  11: ALTER DATABASE [StretchDB_Test] SET REMOTE_DATA_ARCHIVE = ON 

  12: (SERVER = N'<SQL Azure Database Server')

In the next post, I will talk about the implementation details of stretch the tables within a database and other system SP’s/functions/DMV’s as part of the Stretch DB implementation.