You can enable the Stretch DB feature by using Transact-SQL only. As you saw in the previous section, to enable Stretch DB, you need to create and secure communication infrastructure between our local database and the Azure server. Therefore, you need to accomplish the following three tasks:
- Create a database master key to protect server credentials
- Create a database credential
- Define the Azure server
The following code creates a database master key for the sample database Mila:
USE Mila; CREATE MASTER KEY ENCRYPTION BY PASSWORD='<very secure password>'; --you need to put your password here
Next, we create a credential. This is saved authentication information that is required to connect to external resources. You need a credential for only one database; therefore you should create a database-scoped credential:
CREATE DATABASE SCOPED CREDENTIAL MilaStretchCredential WITH IDENTITY = 'Vasilije', SECRET = '<very secure password>'; --you need to put your password here
Now you can finally enable the Stretch DB feature by using the ALTER DATABASE statement. You need to set REMOTE_DATA_ARCHIVE and define two parameters: Azure server and just-created database scoped credential. Here is the code that can be used to enable the Stretch DB feature for the database Mila:
ALTER DATABASE Mila SET REMOTE_DATA_ARCHIVE = ON ( SERVER = '<address of your Azure server>, CREDENTIAL = [MilaStretchCredential] );
With this action, you have created an infrastructure, necessary for communication between your local database and the Azure server that will hold the stretched data. Note that this action can take a few minutes. When I executed the command, it took more than three minutes:
The next and final step is to select and enable tables for stretching.