We know that Stretch Database is a solid feature of SQL Server 2016 which is for securely migrating cold data to Azure with remote query processing abilities. Once we enable this feature for a table, SQL Server silently and transparently moves/migrates data to Azure SQL Database. We can stretch large transaction tables with large amounts of historical data to get the benefit from enabling them for stretch.
Run Stretch Database Advisor in SQL Server 2016 is a standalone utility to let users to select their source database(s), which they want to analyze for identifying potential tables for stretching it to the cloud (Azure SQL Database).
Things to Know before Starting on Utilizing Stretch
When we enable the Stretch feature for a table, SQL Server transparently migrates data from an on premise local table to a table in Microsoft Azure. SQL Server handles it by creating a Linked Server locally, which has a remote endpoint as target to move data to, as shown in figure below from product documentation.
To understand the Stretch database features, data can be classified into two categories:
Hot Data: This type of the data is not supposed to move to Azure SQL Database or they are supposed to be in local on-premise table(s) only because this type of data will participate in other transactional process.
Cold Data: Stretch database offers cost-effective online cold data which is still to be migrated to Azure SQL Database based on configurations defined. In this way, the entire table always online and accessible.
How to Enable Database for Stretch
After considering all the limitations of Stretch database, the current user must be a member of the db_owner group and CONTROL DATABASE permissions is needed for enabling stretch on database level. To configure a database for Stretch Database, right click on the database in SQL Server Management Studio as given below-
After clicking on the enable button, it will run the Enable Database for Stretch Wizard as given below:
Click on Next button to move on Select tables to choose the tables which we want to stretch as given below:
In the above screen, we can see the purpose of the each column. If we want to provide a filter function to select rows to migrate then optionally provide a row filter will work on it.
Click on Next button to move on Configure Azure where we should sign in to Microsoft Azure with a Microsoft account as given below:
After signing on Microsoft Azure, you have two options to choose create new server or existing server as given below:
Create new server – In this option, we need to create a login and password for the server administrator. There is an option to use a federated service account for SQL Server to communicate with the remote Azure server.
Existing server – In this option, we can select the existing Azure server by using authentication method.
After choosing your suitable options, click on the next button to secure credentials. We must have to have a database master key to secure the credentials that Stretch Database uses to connect to the remote database. If a database master key already exists, enter the password for it.
If you don’t have master key (DMK) then you can generate it by using the below command:
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
---- Alter database master key
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003';
After passing the database master key, click Next button to Select IP address. There, use the subnet IP address range (recommended), or the public IP address of our SQL Server, to create a firewall rule on Azure that lets SQL Server communicate with the remote Azure server as given below:
After clicking on Next button, Summary will appear on the Enable Database for Stretch Wizard window as given below:
Now, we have done all the necessary stuffs and time to finalize the things. Click on the Finish button to complete the Stretch Wizard as given below:
Now, we have enabled our database for stretch. Click on the Close button to exit from Enable Database for Stretch wizard.