SQL Server 2016 Stretch Database

Первого июня состоялся релиз SQL Server 2016, который привнес в привычную разработку большое число нововведений, среди которых уже давно анонсированную технологию Stretch Database, позволяющую динамически переносить «горячие» и «холодные» данные из SQL Server в Azure.

С точки зрения маркетинга, Stretch Database очень хорошо распиарили. Приводили разумные аргументы, что по мере накопления в базе исторических данных возрастает сложность и стоимость её эксплуатации. И предлагали разумное решение — автоматический перенос архивных данных по мере устаревания в «облако». Честно признаюсь, идея мне понравилась.

Начиная с SQL Server 2016 RC0 я начал тестировать технологию Stretch Database на двух проектах, которые помогаю развивать. Первый — это бесплатный трекер рабочего времени TMetric, для которого характерна OLTP нагрузка, второй — внутренний проект с DW нагрузкой.

На простом примере, рассмотрим как все работает. Первое, что нам нужно сделать, — это разрешить использования Stretch Database, поскольку по умолчанию на сервере эта функциональность отключена:

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

Перезагружать сервер при этом не требуется. Далее создадим тестовую базу:

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')

И вызовем из SSMS 2016 мастер Stretch Database:

Нас заранее предупреждают, чтобы успешно настроить Stretch Database нужно иметь:

• привилегии админа на сервере (все делалось с правами sysadmin)
• действующую Azure подписку
• разрешить доступ для SQL Server в интернет

На следующем шаге мастера, нас ожидает первое разочарование:

Мы не можем переместить данные из нашей таблицы в Azure из-за «каких-то» ограничений в технологии Stretch Database. Перечислим основные из них (жирным я выделил ограничения, которые не учтены в справке):

Размещение данных в Azure не поддерживается для таблиц которые:

• содержат более 1,023 столбца или более 998 индексов
• содержат FILESTREAM данные
• участвуют в репликации или использует Change Tracking или Change Data Capture
• оптимизированы для размещения в памяти (In-Memory таблицы)
• имеют Always Encrypted столбцы

Кроме того, в таблице нельзя использовать:

• все пользовательские типы данных (CLR, user-defined types)
• TEXT, NTEXT, IMAGE
• TIMESTAMP
• SQL_VARIANT
• XML
• GEOMETRY, GEOGRAPHY, HIERARCHYID
• SYSNAME
• COLUMN_SET
• COMPUTED столбцы
• SEQUENCE в столбце
• DEFAULT и CHECK констрейнты
• Full-Text Search, XML и Spatial индексы
• не должно быть индексных представлений на таблицу
• внешние ключи которые ссылаются на таблицу (например, для дочерней таблицы OrderDetail включить Stretch можно, но для родительской уже нельзя Order)

Как оказалось, наша проблема была в DEFAULT констрейнте. Пересоздадим таблицу и попробуем снова:

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')

Теперь мастер разрешает выбирать таблицу:

Но предупреждает, что: Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data. По этому причине, желательно для Stretch таблиц создавать PRIMARY KEY по IDENTITY полю либо, на худой конец, использовать UNIQUEIDENTIFIER.

Далее нам предлагается авторизоваться и автоматически создать SQL Server в Azure:

Давайте вручную создадим SQL Server на Azure Portal:

И выберем созданный сервер в мастере:

Зададим мастер пароль, который еще понадобиться нам в будущем:

Укажем исключения, которые нужно добавить в фаервол:

И вот мы на финишной прямой:

Но при настройке нас постерегает очередное ограничение про которое в справке увы никто не написал:

В логе оказалась следующая ошибка:

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.

При создании базы я явно не указал коллейшен и оказалось, что Cyrillic_General_CI_AS не поддерживается, впрочем как и многие другие. В результате тестирования выяснилось, что Stretch Database не будет работать, если в свойствах базы используется collation:

• любой Windows collations (Latin1_General_100_CI_AS, ...)
• любой AI и BIN collations
• есть избирательные проблемы с CS collations

Чтобы все стабильно работало желательно использовать исключительно SQL Server collations. Все я их не проверял, но при использовании SQL_Latin1_General_CP1_CI_AS у меня не возникло никаких проблем:

ALTER DATABASE StretchDB COLLATE SQL_Latin1_General_CP1_CI_AS

После изменения COLLATE для базы, заново запускаем мастер и о чудо:

Теперь попробуем отследить как данные мигрируют в Azure через монитор:

либо отдельное системное представление 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()

В RC3 был баг и если COLLATE столбцов не совпадал с COLLATE базы, то данные не переносились в Azure, sys.dm_db_rda_migration_status постоянно роста и при этом не очищялась.

В нашем примере один из столбцов имеет COLLATE, который не совпадает с тем, что установлено в свойствах базы. Из-за этого пересылка данных переодически будет падать по ошибке:

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

Но после нескольких таких вот неудачных попыток будет проходить успешно:

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

Из этого можно сделать вывод, что баг в RTM пофиксили не до конца, поэтому крайне желательно задавать для всех столбцов такой же COLLATE, что и для базы.

На этом этапе мы попробовали один из способов создания Stretch таблицы. К слову скрипта это все делается намного проще и быстрее:

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))

Главное при этом заранее задать разрешения для нашего Azure сервера:

Теперь посмотрим какие изменения произошли с таблицей…

Всякий раз, когда данные попадают в таблицу, то они некоторое время физически находятся на локальном сервере, а после автоматически переносятся в Azure. Это можно легко увидеть с помощью Live Query Statistics фичи реализованной в SSMS 2016:

После 5-10 секунд:

Если посмотреть на план выполнения, то вся функциональность Stretch Database уже не является тайной: создается линкед-сервер и отдельный компонент, который переливает данные в Azure.

Какие ограничения мы получаем:

• 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

Получается, что мы можем только вставлять данные в таблицу:

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

Потому, что любая операция DELETE, UPDATE, TRUNCATE завершится с ошибкой:

Msg 14893, Level 16, State 1, Line 6
Table '...' cannot be a target of an update or delete statement because it has the REMOTE_DATA_ARCHIVE option enabled without a migration predicate.

Попробуем сделать Stretch таблицу с фильтром определяющим какие данные должны быть на локальной машине, а какие можно переместить в Azure.

Для этого возвращаем все данные из Azure:

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

Создаем функцию и столбец, который будет хранить признак что эти данные можно мигрировать в облако:

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

При повторном размещения таблицы в облаке указываем фильтр:

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

После того как команда отработала пробуем посмотреть что изменилось. Во первых пока не проставлено условия миграции, можно делать с локальными данными все что угодно. Удалять или обновлять:

UPDATE dbo.ErrorLog
SET IsOld = 0

и когда нам необходимо избирательно переносить в Azure:

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

Главное помнить: то что уже находится в облаке уже нельзя обратно возвратить путем простого 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.

Посмотрим сколько места занимают наши данные:

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

Теперь попробуем использовать фильтр в наших запросах, чтобы сделать обращение к Stretch таблицам более эффективным:

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

Если посмотреть на план выполнения, то в первом случае не нужно делать соединение к линкед-серверу, что должно работать на порядок быстрее:

Неужели мы нашли вариант, когда применение Stretch Database будет оправданным? На практике не все так хорошо, потому что оба запроса отрабатывают одинаково медленно по причине бага:

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.

При любом обращение к Stretch таблице идет подключение к Azure:

Из-за такого поведения любые запросы с участием Stretch таблицы будут тормозить или падать по тайм-ауту. Ограничения, которые я оставил про запас, будут казаться смешными на фоне описанной выше проблемы с производительностью:

• 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

Небольшие итоги

Как уже сказал выше, перед этим на двух проектах я старался использовать Stretch Database. В TMetric обитала большая таблица с отрезками рабочего времени. Обращение к ней идет достаточно активно, поэтому Stretch Database элементарно не прижился из-за своей низкой производительности. С размером этой таблицы поборолись очень просто: удалили все внешние ключи и пересоздали таблицу с кластерным COLUMNSTORE индексом (в итоге сжали таблицу в 12 раз).

На внутреннем проекте пригодился финт с переключением секций, который мне понравилось использовать. Смысл в следующем… создаются две таблицы (А и B). С таблицей А работаем активно и потом переключаем секцию на B, которая является Stretch таблицей:

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

По факту, нас идет разграничение между историческими и оперативными данными и получается, что баг с производительностью мы красиво обходим.

Пост большой вышел и его тяжело читать, но тут собраны все проблемы с которыми я столкнулся за два-три месяца работы с Stretch Database. По этой причине, я надеюсь, что данные труды будут кому-то полезны в дальнейшем. Если его есть желание чем-то его дополнить, то буду рад комментариям.

Все тестировалось на Microsoft SQL Server 2016 (RTM) — 13.0.1601.5 (X64).
Планы выполнения брал из SSMS 2016.

Теперь пару слов о мероприятиях на лето

Уже в эту субботу (18 июня) в Киеве пройдет вторая конференция украинского Azure сообщества — AzureDAY 2016. Лично я уже взял день отпуска и жд билеты, чтобы посетить это мероприятие.

Автор: AlanDenton

Источник