How to setup and use a SQL Server Stretch Database

By:   |   Updated: 2018-07-31   |   Comments (1)   |   Related: More > 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.

stretch database

In the image below, you can see the requirements to configure a Stretch Database.

 stretch database wizard

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. 

stretch database wizard

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.

stretch database wizard
stretch database wizard

The next steps are to create or use an existing server on Azure and set the credentials.

stretch database wizard
stretch database wizard
stretch database wizard

I’m using a public IP for this example only and is recommended to set your subnet range IP.

stretch database wizard
stretch database wizard

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.

sql server object explorer

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.

stretch database monitor

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			
query results

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.

query plan

When the query selects only local data the execution plan changes and we don’t use the Remote Query operator.

query plan

Running the procedure sp_spaceused you can see how many rows are remote using the parameter REMOTE_ONLY.

query results

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.
query error

Querying the table without a connection to Azure will show the error below.

query error

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


Last Updated: 2018-07-31


get scripts

next tip button



About the author
MSSQLTips author Douglas Correa Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Saturday, January 26, 2019 - 9:19:04 AM - Marios Philippopoulos Back To Top

Hi Douglas,

Thanks for the article!

For some reason, I am not able to get the table created on the Azure server. The remote db gets created, but the table is not.

Looking at the stretch-db monitor screen, I see Error Number 7320, Error State 1, but it is not clear what is wrong.

Any ideas?

Thanks,

Marios



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools