Geekly Articles each Day
📜 ⬆️ ⬇️

SQL Server 2016 Stretch Database



The first release of SQL Server 2016 took place on June 1 , which brought to the familiar development a large number of innovations, including the long-announced Stretch Database technology that allows you to dynamically transfer hot and cold data from SQL Server to Azure .

From a marketing point of view, the Stretch Database is very well advertised. They cited rational arguments that, as historical data is accumulated in the database, the complexity and cost of its operation increases. And they offered a reasonable solution - automatic transfer of archived data as they become obsolete in the “cloud”. Honestly, I liked the idea.
')
Beginning with SQL Server 2016 RC0, I started testing Stretch Database technology on two projects that I help develop. The first is the working time tracker for which the OLTP load is characteristic, the second is the internal project with a DW load.

On a simple example, consider how everything works. The first thing we need to do is to allow the use of the Stretch Database , since by default this functionality is disabled on the server:

EXEC sp_configure 'remote data archive' , '1' GO RECONFIGURE GO

There is no need to restart the server. Next, create a test database:

 USE [master] GO IF DB_ID('StretchDB') IS NOT NULL BEGIN ALTER DATABASE StretchDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE StretchDB END GO CREATE DATABASE StretchDB /* COLLATE Cyrillic_General_CI_AS */ GO USE StretchDB GO CREATE TABLE dbo.ErrorLog ( LogID INT IDENTITY PRIMARY KEY , PostTime DATETIME NOT NULL DEFAULT GETDATE() , UserName NVARCHAR(100) NOT NULL , ErrorMessage NVARCHAR(MAX) COLLATE Ukrainian_BIN NOT NULL ) GO INSERT INTO dbo.ErrorLog (UserName, ErrorMessage) VALUES (N'sergeys', N'Azure row')

And we call the Stretch Database wizard from SSMS 2016 :



We are warned in advance to successfully set up Stretch Database you need to have:




In the next step of the wizard, the first disappointment awaits us:



We cannot transfer data from our table to Azure due to “some” limitations in the Stretch Database technology. We list the main ones ( in bold, I highlighted the restrictions that are not taken into account in the certificate):

Placing data in Azure is not supported for tables that:


In addition, the table can not be used:


As it turned out, our problem was at the DEFAULT constraint. Re-create the table and try again:

 DROP TABLE IF EXISTS dbo.ErrorLog CREATE TABLE dbo.ErrorLog ( LogID INT IDENTITY PRIMARY KEY , PostTime DATETIME NOT NULL , UserName NVARCHAR(100) NOT NULL , ErrorMessage NVARCHAR(MAX) COLLATE Ukrainian_BIN NOT NULL ) GO INSERT INTO dbo.ErrorLog (PostTime, UserName, ErrorMessage) VALUES (GETDATE(), N'sergeys', N'Azure row')

Now the wizard allows to select a table:



But warns that: Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints . For this reason, it is desirable for Stretch tables to create a PRIMARY KEY using the IDENTITY field, or, at worst, use UNIQUEIDENTIFIER .

Next, we are asked to authorize and automatically create a SQL Server in Azure :



Let's manually create a SQL Server on the Azure Portal :



And select the created server in the wizard:



Let's set a master password, which we still need in the future:



We indicate the exceptions to be added to the firewall:



And here we are at the finish line:



But when setting up, we are protected by another restriction about which, alas, no one wrote in the help:



The log was the following error:

 Configure Stretch on the Database StretchDB Status : 'Failed' Details : Task failed due to following error: Alter failed for Database 'StretchDB': 'Cyrillic_General_CI_AS' is not a supported collation. ALTER DATABASE statement failed.

When creating the database, I clearly did not indicate a coluscheon and it turned out that Cyrillic_General_CI_AS is not supported, however, like many others. As a result of testing, it turned out that Stretch Database will not work if collation is used in database properties:


For everything to work stably, it is advisable to use only SQL Server collations . I did not check them all, but when using SQL_Latin1_General_CP1_CI_AS I did not have any problems:

 ALTER DATABASE StretchDB COLLATE SQL_Latin1_General_CP1_CI_AS

After changing the COLLATE for the base, re-launch the wizard and a miracle:



Now let's try to track how data migrates to Azure via a monitor:



or a separate system view sys.dm_db_rda_migration_status :

 SELECT * FROM sys.dm_db_rda_migration_status WHERE table_id = OBJECT_ID('dbo.ErrorLog') AND database_id = DB_ID()

In RC3 there was a bug and if the COLLATE columns did not match the COLLATE database, then the data was not transferred to Azure , sys.dm_db_rda_migration_status was constantly growing and was not cleared.

In our example, one of the columns has a COLLATE , which is not the same as what is set in the database properties. Because of this, the data transfer periodically will fall by mistake:

 migrated_rows start_time_utc end_time_utc error_number error_severity error_state -------------- -------------------- -------------------- ------------ -------------- ----------- 0 2016-06-15 15:44:41 2016-06-15 15:45:09 NULL NULL NULL 0 2016-06-15 15:45:16 2016-06-15 15:45:16 NULL NULL NULL 0 2016-06-15 15:45:16 2016-06-15 15:45:58 1205 13 55 0 2016-06-15 15:45:59 NULL NULL NULL NULL

But after several such unsuccessful attempts will be successful:

 migrated_rows start_time_utc end_time_utc error_number error_severity error_state -------------- -------------------- -------------------- ------------ -------------- ----------- 0 2016-06-15 15:46:21 2016-06-15 15:46:21 NULL NULL NULL 1 2016-06-15 15:46:21 2016-06-15 15:46:27 NULL NULL NULL 0 2016-06-15 15:47:56 2016-06-15 15:47:56 NULL NULL NULL 0 2016-06-15 15:47:56 NULL NULL NULL NULL

From this we can conclude that the bug in RTM was not fully fixed, so it is highly desirable to set the same COLLATE for all columns as for the base.

At this stage, we tried one of the ways to create a Stretch table. By the way of the script, all this is done much easier and faster:

 USE StretchDB GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'VasyaPupkin12' GO CREATE DATABASE SCOPED CREDENTIAL azure WITH IDENTITY = N'server_name', SECRET = N'VasyaPupkin12' GO ALTER DATABASE StretchDB SET REMOTE_DATA_ARCHIVE = ON ( SERVER = 'server_name.database.windows.net', CREDENTIAL = azure ) GO ALTER TABLE dbo.ErrorLog SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND))

The main thing is to pre-set permissions for our Azure server:



Now let's see what changes have occurred with the table ...

Whenever the data gets into the table, it is physically located on the local server for some time, and then automatically transferred to Azure . This can be easily seen using Live Query Statistics features implemented in SSMS 2016 :


After 5-10 seconds:



If you look at the execution plan, then all the functionality of the Stretch Database is no longer a secret: a linked server and a separate component are created that feeds data into Azure .

What restrictions do we get:


It turns out that we can only insert data into the table:

INSERT INTO dbo.ErrorLog (PostTime, UserName, ErrorMessage)
VALUES (GETDATE (), N'sergeys ', N'Local row')

Because any DELETE, UPDATE, TRUNCATE operation will fail with an error:

Msg 14893, Level 16, State 1, Line 6
It can be used.

Let's try to make Stretch a table with a filter that determines what data should be on the local machine and which can be moved to Azure .

To do this, return all the data from Azure:

 ALTER TABLE dbo.ErrorLog SET (REMOTE_DATA_ARCHIVE (MIGRATION_STATE = INBOUND))

Create a function and a column that will store a sign that this data can be migrated to the cloud:

 CREATE FUNCTION dbo.fn_stretchpredicate(@IsOld BIT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS is_eligible WHERE @IsOld = 1 GO ALTER TABLE dbo.ErrorLog ADD IsOld BIT GO

When re-placing the table in the cloud, specify the filter:

 ALTER TABLE dbo.ErrorLog SET ( REMOTE_DATA_ARCHIVE = ON ( FILTER_PREDICATE = dbo.fn_stretchpredicate(IsOld), MIGRATION_STATE = OUTBOUND ) )

After the team worked try to see what has changed. Firstly, the conditions of migration are not set down yet, you can do anything with local data. Delete or update:

 UPDATE dbo.ErrorLog SET IsOld = 0

and when we need to selectively transfer to Azure :

 UPDATE TOP(1) dbo.ErrorLog SET IsOld = 1

The main thing to remember: what is already in the cloud can no longer be returned by simple UPDATE :

 Msg 14875, Level 16, State 1, Line 14 DML operation failed because it would have affected one or more migrated (or migration-eligible) rows.

Let's see how much space our data takes:

 EXEC sys.sp_spaceused 'dbo.ErrorLog', @mode = 'LOCAL_ONLY' EXEC sys.sp_spaceused 'dbo.ErrorLog', @mode = 'REMOTE_ONLY' 

 name rows reserved data index_size unused -------------- ----- ---------- ------ ------------- -------- dbo.ErrorLog 1 72 KB 8 KB 8 KB 56 KB name rows reserved data index_size unused -------------- ----- ---------- ------ ------------- -------- dbo.ErrorLog 1 144 KB 8 KB 24 KB 112 KB

Now we will try to use the filter in our queries to make accessing Stretch tables more efficient:

 SELECT * FROM dbo.ErrorLog WHERE IsOld = 0 SELECT * FROM dbo.ErrorLog WHERE IsOld = 1

If you look at the execution plan, in the first case you do not need to make a connection to the linked server, which should work an order of magnitude faster:



Have we really found an option when using Stretch Database will be justified? In practice, not everything is so good, because both requests are processed equally slowly due to a bug ( upd 07/26/2016: Update # 1 is necessary to fix KB3174812 ):

 Table 'ErrorLog'. Scan count 1, logical reads 2, physical reads 0, .... SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1225 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. Table 'ErrorLog'. Scan count 1, logical reads 2, physical reads 0, .... SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1104 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

For any access to the Stretch table, you are connected to Azure :





Because of this behavior, any queries involving Stretch will slow down or fall on a timeout. The limitations I left in reserve will seem ridiculous against the background of the performance problem described above:


Small results

As already said above, before that on two projects I tried to use the Stretch Database . In the tracker lived a large table with segments of working time. Appeal to it is quite active, so Stretch Database is not elementary due to its poor performance. They struggled with the size of this table very simply: they deleted all foreign keys and re-created the table with the cluster COLUMNSTORE index (as a result, they squeezed the table 12 times).

On an internal project, a feint with section switching came in handy, which I liked to use. The meaning is as follows ... two tables are created ( A and B ). We work actively with table A and then switch the section to B , which is a Stretch table:

 DROP TABLE IF EXISTS A GO CREATE TABLE A (val INT PRIMARY KEY) GO INSERT INTO A SELECT 1 GO DROP TABLE IF EXISTS B GO CREATE TABLE B (val INT PRIMARY KEY) GO ALTER TABLE B SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND)) GO ALTER TABLE A SWITCH TO B GO SELECT * FROM A SELECT * FROM B

In fact, we are distinguished between historical and operational data, and it turns out that we are getting around the performance bug (although there are still disadvantages to this solution - switching the section to a non-empty table will not work).

A big post came out and it's hard to read, but here all the problems I encountered in two or three months working with the Stretch Database are collected. For this reason, I hope that these works will be useful to someone in the future. If he has a desire to supplement him with something, I will be glad to comment.

Everything was tested on Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) .

If you want to share this article with an English-speaking audience:
SQL Server 2016 Stretch Database

Source: https://habr.com/ru/post/303346/

More articles:


All Articles