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:
USE datatools;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
GO
---- Alter database master key
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003';
GO
|
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.
After doing this, stretch database
lets us move or archive our cold data from a local SQL Server database to the
Azure SQL Database transparently.
References: https://msdn.microsoft.com/en-us/library/dn935011.aspx
No comments:
Post a Comment