How-to: Enable SQL Server 2016 Stretch DatabaseGain more space on your local SQL server by storing cold data in Azure
Microsoft SQL Server 2016 has been released on June 2016. One of the new features in SQL Server 2016 are the stretch databases. These databases allow you to put complete tables or selective records in a cloud instance of SQL Server while the other tables are still running on premise. You can use this feature to put cold data in the cloud. In this tutorial I will explain you how to enable “stretching” on a database.
Configure your Azure Subscription
Before we dive in Microsoft SQL Server 2016 we need to configure the Azure subscription to support the SQL stretch database. For this tutorial I used a MSDN subscription that was not compatible with stretch databases by default. I guess there are subscriptions which are already compatible with stretch databases. In case your subscription has the “Microsoft.SQL” namespace registered; you don’t have to execute this step. To make an Azure subscription compatible for stretch databases, you need te register the Microsoft.SQL namespace. You can do this by running the following Powershell commands:
To demonstrate this step I created a very simple database with one table in it. You can follow the steps below to convert your normal database to a stretch database.
- In the SQL Management Studio navigate to your database, open the context menu and click for: Tasks > Stretch > Enable.
- A wizard will open. In the first page of the wizard an introduction text is showed. Click Next to go to the second page of the wizard.
- In this step you need to sign in with your Azure account. Click on Sign In and login with your credentials.
- Here you need to select the subscription in which you want to create the stretch database. You need to select your region. In my case I choose West Europe (cause I live in the Netherlands). You also need to give some information about the stretch database (server) that will be created in Azure. Please fill in the server name, server admin login and password. Click on Next when all the fields are filled in correctly
- In this next step you need to specify a key that is used to encrypt the database master key DMK. Please use a strong password. After typing and retyping the password, click on Next.
- In this page of the wizard you can configure the firewall for the stretch database in Azure. I my case I chose for “Use source SQL Server Public IP.” Which whitelisted the IP-address on which my SQL Server is communicating with stretch database server. If you are ready, click on Next.
- A summary is showed. Click on finish to start the configuration!
All done! Your database is ready to stretch to Azure!
Configure table to store data in stretch database
In the previous step we converted a local database to a stretch database. This step only prepared our database and Azure to do stretching. Stretching needs to be configured per table. For this demo we assume that al data stored in our only table is cold; so it can be migrated to the cloud. Use the following steps to configura a table to store and migrate data to a stretch database:
- In the SQL Management Studio navigate to your table, open the context menu en click for: Stretch > Enable.
- A wizard will popup. You can click Next to skip the introduction.
- Check the table that you want to store data in the cloud (stretch database) and click on Next.
- A summary is displayed. Click on finish to enable stretching.
The data in the table is now stored in Azure!
By clicking through the wizards in SQL Server 2016, you have created a stretch database in Azure. When you store more (cold) data in the stretch database in Azure, the size of this database will increase. In Azure you pay for the size of the database. Pleas visit https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/ for the pricing of the stretch database.