By: Douglas Correa | Updated: 2018-07-31 | Comments (3) | Related: > Azure
Problem
You need to keep all of your data online in the event that you need to access the data. But some of this data is warm data (needed frequently) and other is cold data (needed occasionally). In this tip we look at how we can achieve this using the new stretch database feature in SQL Server and Azure.
Solution
SQL Server 2016 introduced the Stretch Database option to migrate your cold data transparently and securely to the Microsoft Azure cloud. To use this feature, you must have access to a Microsoft Azure subscription and permissions to be able to communicate with Azure.
In this tip, I’m going to use a demo database to show the entire process. Run the T-SQL below to create the database.
CREATE DATABASE [demo] CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'demo',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\demo.mdf',
SIZE = 8192KB,
FILEGROWTH = 65536KB
)
LOG ON
(
NAME = N'demo_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\demo_log.ldf',
SIZE = 8192KB,
FILEGROWTH = 65536KB
);
GO
Next, create the table to use in this example.
DROP TABLE IF EXISTS Product
GO
CREATE TABLE Product(
[ProductID] BIGINT IDENTITY(1, 1) NOT NULL,
[Name] NVARCHAR(50) NOT NULL,
[SafetyStockLevel] SMALLINT NOT NULL,
[StandardCost] DECIMAL(12, 4) NOT NULL,
[Discontinued] BIT NOT NULL,
[ModifiedDate] DATETIME NOT NULL,
CONSTRAINT [PK_Product_ProductID]
PRIMARY KEY CLUSTERED ([ProductID] ASC)
) ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX idx_Product_Discontinued ON dbo.Product (Discontinued) INCLUDE (Name, ModifiedDate)
GO
Enable Stretch Database in SQL Azure
If you don’t have an Azure server configured, you can let SQL Server create it for you by running a wizard in SQL Server Management Studio. To start the wizard right click on the database and select Tasks > Stretch > Enable to start.
In the image below, you can see the requirements to configure a Stretch Database.
The first step allows you to choose what table will be stretched. Check the table and click “Entire Table” to create the filter predicate that will separate your cold and warm data.
You can see a warning saying the primary key will be enforced only locally and not on the table in Azure. Clicking “Entire Table” will show the window below. This window creates a function to select the rows to stretch. In this example I have a column called Discontinued to mark cold data and all rows with the value 1 will be stretched.
The next steps are to create or use an existing server on Azure and set the credentials.
I’m using a public IP for this example only and is recommended to set your subnet range IP.
After successfully complete the tasks, it creates an external data source to Azure from the on-premises SQL Server.
The image below shows a different icon for the stretched database and the external data source is created.
Using T-SQL is another way to configure a stretch database and after running the wizard to enable the stretch database feature you can enable and disable tables creating your own function predicate.
I’m going to use the same demo database to show the entire process using T-SQL.
CREATE DATABASE [demo] CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'demo',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\demo.mdf',
SIZE = 8192KB,
FILEGROWTH = 65536KB
)
LOG ON
(
NAME = N'demo_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\demo_log.ldf',
SIZE = 8192KB,
FILEGROWTH = 65536KB
);
GO
Create the table.
DROP TABLE IF EXISTS Product
GO
CREATE TABLE Product(
[ProductID] BIGINT IDENTITY(1, 1) NOT NULL,
[Name] NVARCHAR(50) NOT NULL,
[SafetyStockLevel] SMALLINT NOT NULL,
[StandardCost] DECIMAL(12, 4) NOT NULL,
[Discontinued] BIT NOT NULL,
[ModifiedDate] DATETIME NOT NULL,
CONSTRAINT [PK_Product_ProductID]
PRIMARY KEY CLUSTERED ([ProductID] ASC)
) ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX idx_Product_Discontinued ON dbo.Product (Discontinued) INCLUDE (Name, ModifiedDate)
GO
Next, to enable stretch database we create a master key and a credential.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='pa$$w0rd';
GO
CREATE DATABASE SCOPED CREDENTIAL my_demo_credential
WITH IDENTITY = 'douglas' , SECRET = 'pa$$w0rd' ;
GO
The next step is enable the stretch database. The ALTER DATABASE will take a while to run, because it will connect to the Azure server defined in the script and create the external data source.
ALTER DATABASE demo
SET REMOTE_DATA_ARCHIVE = ON
(
SERVER = 'stretchserver-demo123456xx.database.windows.net' ,
CREDENTIAL = my_demo_credential
) ;
GO
Enable stretch table in SQL Server Azure
With the database enabled, the next step is to enable the table. First, let’s create a function predicate to select the eligible rows to send to Azure. I created a function with a parameter to receive the table column. In this case, the function will send the discontinued rows to Azure and keep the rest on the local server.
CREATE OR ALTER FUNCTION dbo.fn_DiscontinuedRows(
@column1 BIT
)RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS is_eligible WHERE @column1 = 1
GO
The script below will enable the table and SQL Server will start moving the rows from on-premises to the cloud.
ALTER TABLE dbo.[Product] SET (
REMOTE_DATA_ARCHIVE = ON(
FILTER_PREDICATE = dbo.fn_DiscontinuedRows([Discontinued])
, MIGRATION_STATE = OUTBOUND
)
);
GO
Performance and Monitoring a Stretch Database in Azure
SQL Server provides a dashboard to see the stretch database state. In the dashboard you can see the stretched tables, how many rows are local and on Azure, if there are any issues and more.
Also, you can use these system views to get more information.
SELECT * FROM sys.remote_data_archive_databases SELECT * FROM sys.remote_data_archive_tables SELECT * FROM sys.dm_db_rda_migration_status
One of the benefits of a stretch database is to have less data to query. You can keep only the actual year locally for example to improve query run time. The next image shows the execution plan when querying data from Azure and local.
When the query selects only local data the execution plan changes and we don’t use the Remote Query operator.
Running the procedure sp_spaceused you can see how many rows are remote using the parameter REMOTE_ONLY.
Requirements and limitations
- Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.
- You can't UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a stretch-enabled table or in a view that includes stretch-enabled tables.
- You can't INSERT rows into a stretch-enabled table on a linked server.
- Memory-optimized and replicated tables.
- Tables that contain FILESTREAM data, use Change Tracking or Change Data.
- Data types such as timestamp, sql_variant, XML, geography or columns that are Always Encrypted.
- Check and default constraints or foreign key constraints that reference the table.
- XML, full-text, spatial, clustered columnstore and indexed views that reference the stretch-enabled table.
- You can't create an index for a view that includes stretch-enabled tables.
- Filters on SQL Server indexes are not propagated to the remote table.
- Trying to update rows migrated to Azure will show an error message because you can’t change the rows already on Azure and it is necessary to disable the stretch table and get the rows from Azure first.
Querying the table without a connection to Azure will show the error below.
Disable stretch tables in SQL Azure
To update a row or change the filter predicate it is required to disable the stretch table first. Once enabled all the rows eligible are moved to Azure and to disable you can bring back the data from Azure or leave it there. To disable and bring the data back run the script below.
ALTER TABLE Product SET (
REMOTE_DATA_ARCHIVE (
MIGRATION_STATE = INBOUND
)
)
Or, to leave the data in Azure, run the script.
ALTER TABLE Product SET (
REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY (
MIGRATION_STATE = PAUSED
)
)
After disabling the stretch table, you can make changes then enable again with the steps shown above.
Conclusion
The Stretch Database is a form of partitioning, but unlike SQL Server table partitioning, there's no need to restructure tables to make it work.
Separating old data will improve performance by reading less data. In my opinion, this is a great feature because is an easy way to archive data and it also involves cost because the Azure SQL Database is necessary but pays for itself with the performance gain.
Next Steps
- Stretch Database (MSDN)
- Check out the tip Identity Candidate Tables for SQL Server 2016 Stretch Databases
About the author
Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-07-31
