Горизонтальное масштабирование базы данных реального проекта с помощью SQL Azure Federations. Часть 3: Миграция

Итак, мы продолжаем процесс миграции базы данных на использование SQL Azure Federations. Как вы помните, в прошлый раз мы решили, по какой таблице и по какому полю мы будем разбивать базу данных на шарды. Давайте же наконец это сделаем!

Миграция

Итак, будем разбивать базы данных по таблице аккаунтов (Account), поскольку данные, хранящиеся в ней и связанные с ней логически друг с другом не пересекаются. Поскольку у нас есть скрипт создания базы данных, попробуем адаптировать его для использования SQL Azure Federations.

Будем считать, что база данных уже создана в Windows Azure Management Portal либо через SQL Server Management Studio.

Откроем скрипт создания объектов в базе данных.
USE xPenses
GO

IF EXISTS (SELECT name FROM sysobjects where name = N'Operation')
 DROP TABLE Operation

Первое, что необходимо убрать это использование операции USE, т. к. это одно из основых ограничений SQL Azure. Одна база данных — одно соединение. Вместо этого добавим запросы на создание федерации:

-- A database must be selected before executing this statement
CREATE FEDERATION Accounts(AccountId BIGINT RANGE)
GO

USE FEDERATION Accounts(AccountId = 1) WITH RESET, FILTERING = OFF
GO

Обратите внимание, что допустим, подключившись с помощью SSMS к SQL Azure Server необходимо выбрать базу данных из списка, для выполнения запроса.



Таким образом мы создадим новую федерацию, данные которой распределяются по значению идентификатора аккаунта (Account ID). Обратите внимание, что на данный момент никаких таблиц в базе не создано, то есть поле AccountId не связано ни с каким набором данных в реальных таблицах. Имя поля также может отличаться от имени поля таблицы, по которому будет производиться распределение.

Здесь мы можем увидеть еще одно, логичное, ограничение SQL Azure Federations. Поле, по которому будет будет осуществляться распределение должно иметь тип INT, BIGINT, UNIQUEIDENTIFIER и VARBINARY.

После создания федерации, нам необходимо выбрать первый шард, в который мы начинаем вносить данные. То есть шард, хранящий данные первого аккаунта (AccountId = 1).

Смотрим наш скрипт далее. Нам необходимо модифицировать создание таблицы аккаунтов таким образом, чтобы SQL Azure знал, что данные именно этой таблицы по полю Id будут распределяться по шардам.

CREATE TABLE Account (
 [Id] INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
 [EntityId] INTEGER NOT NULL FOREIGN KEY REFERENCES Entity(Id),
 [Currency] NVARCHAR(3)
)


//Таким образом скрипт создания таблицы превратится в следующий:
CREATE TABLE Account (
 [Id] BIGINT NOT NULL,
 [EntityId] INTEGER NOT NULL FOREIGN KEY REFERENCES Entity(Id),
 [Currency] NVARCHAR(3),
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
 (
 [Id] ASC
 )
) FEDERATED ON (AccountId= Id)

Итак, что же изменилось? Тип поля ID стал BIGINT. Кроме этого, мы лишились возможности автоматической генерации значения для этого поля при вставке новой записи. Это является еще одним из ограничений SQL Azure Federations. Однако сохраняется возможность использования ключевого слова DEFAULT. Это к примеру будет полезно, если тип поля ID будет UNIQUEIDENTIFIER. В этом случае мы можем объявить поле таким образом:
[Id] UNIQUEIDENTIFIERNOT NULL DEFAULT NEWID()

Тогда при вставке новых записей в таблицу, нам нет необходимости указывать ID создаваемой записи. При работе с остальными типами эта логика должна быть реализована на уровне приложения.

Следующее, на что стоит обратить внимание — это объявление основного ключа таблицы. Нам необходимо явно указать то, что создаваемый ключ будет кластерным.

Последнее, что необходимо сделать, это указать с помощью ключевого слова FEDERATED ON, что данная таблица будет являться federated. Данные в ней мы будем разбивать по полю ID.

Итак, с создание таблицы аккаунтов мы разобрались. Едем дальше. Как видно из схемы базы данных, таблица аккаунтов является родительской по отношению к таблицам «кредитная карта» и «банковский счет».


Увеличить

То есть таблицы BankAccount и CreditCard имеют внешний ключ на таблицу Account. Поскольку таблица аккаунтов теперь у нас является федерируемой, то невозможно обеспечить целостность ссылок одной таблицы на другую.

Это является еще одним ограничением SQL Azure Federations. На таблицы, по которым происходит разбивка данных (federated table), невозможно ссылаться в других таблицах. То есть необходимо будет убрать все foreign key из таблиц, ссылающихся на таблицу аккаунтов(Account).

Таким образом скрипт создания таблицы, к примеру CreditCard, вместо вида:

CREATE TABLE CreditCard (
 [Id] INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
 [AccountId] INTEGER NOT NULL FOREIGN KEY REFERENCES Account(id),
 [Type] NVARCHAR(MAX)
 CONSTRAINT CreditCardType CHECK (
 [Type] = 'Visa'
 OR [Type] = 'MasterCard'
 OR [Type] = 'JCB'
 OR [Type] = 'AmericanExpress'),
 [Number] NVARCHAR(MAX)
)

//Примет вид:
CREATE TABLE CreditCard (
 [Id] INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1),
 [AccountId] BIGINT NOT NULL,
 [Type] NVARCHAR(MAX)
 CONSTRAINT CreditCardType CHECK (
 [Type] = 'Visa'
 OR [Type] = 'MasterCard'
 OR [Type] = 'JCB'
 OR [Type] = 'AmericanExpress'),
 [Number] NVARCHAR(MAX)
)

То есть целостность ссылок записей из таблицы кредитных карточек на таблицу аккаунтов ложится на плечи логики приложения.

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

А вот процедура по добавлению нового аккаунта (AddAccount), требует небольших изменений. Итак рассмотрим исходный код этой процедуры:

CREATE PROCEDURE AddAccount(
 @Name NVARCHAR(MAX),
 @Description NVARCHAR(MAX),
 @Currency NVARCHAR(3),
 @Instrument NVARCHAR(MAX),
 @Type NVARCHAR(MAX),
 @Number NVARCHAR(MAX)
)
AS
 INSERT INTO Entity VALUES (@Name, @Description)
 DECLARE @EntityId INTEGER = (SELECT Id FROM Entity WHERE Name = @Name AND Description = @Description)

 INSERT INTO Account VALUES (@EntityId, @Currency)

 IF (@Instrument = 'BankAccount')
 BEGIN
 INSERT INTO BankAccount VALUES (
 (SELECT Id FROM Account WHERE Account.EntityId = @EntityId),
 @Type,
 @Number
 ) END

 IF (@Instrument = 'CreditCard')
 BEGIN
 INSERT INTO CreditCard VALUES (
 (SELECT Id FROM Account WHERE Account.EntityId = @EntityId),
 @Type,
 @Number
 ) END
GO

На самом деле, изменения, которые необходимо внести, достаточно очевидны. Поскольку мы лишились возможности автоматической генерации значения поля ID для аккаунта, эта логика ложится на плечи логики приложения. То есть нам необходимо внести изменения в заголовок процедуры:

CREATE PROCEDURE AddAccount(
 @AccountId BIGINT,
 @Name NVARCHAR(MAX),
 @Description NVARCHAR(MAX),
 @Currency NVARCHAR(3),
 @Instrument NVARCHAR(MAX),
 @Type NVARCHAR(MAX),
 @Number NVARCHAR(MAX)
)
AS
 INSERT INTO Entity VALUES (@Name, @Description)
 DECLARE @EntityId INTEGER = (SELECT Id FROM Entity WHERE Name = @Name AND Description = @Description)

 INSERT INTO Account VALUES (@AccountId, @EntityId, @Currency)
 ...
GO

Соответственно вставка записей в таблицу аккаунтов, вместо кода:
EXEC AddAccount 'Cash', 'Everyday cash account', 'USD', NULL, NULL, NULL

Теперь будет принимать на вход еще один параметр (ID создаваемого аккаунта):
EXEC AddAccount 1, 'Cash', 'Everyday cash account', 'USD', NULL, NULL, NULL

Наверное следующий логичный вопрос будет следующим: возможно ли в тело процедуры добавить команду использования нужной нам федерации (USE FEDERATION)? Поскольку, если нам пришел ID создаваемого аккаунта, то мы знаем с какой федерацией нам необходимо работать, а значит можем сразу перейти к использованию нужного шарда:
USE FEDERATION Accounts(AccountId = @AcccountId) WITH RESET, FILTERING = OFF
GO

К сожалению, если вы так сделаете, то SSMS выдаст вам ошибку. Все дело в том, чтопроцедура AddAccount хранится в конкретном шарде, а значит возможности использования USE FEDERATION у нас нет. Кроме этого использование USE FEDERATION вообще невозможно в процедурах. Код по переключению федераций должен быть расположен «уровнем выше».

На этом изменения, которые необходимо сделать в скрипте создания базы данных закончены. Мы можем выполнить его без ошибок. В результате будет создана одна root база данных (federation root) и один шард (federation member).

Разбиение на шарды

Все, что нам осталось сделать — это собственно говоря произвести scaling базы данных. То есть отделить данные относящиеся к одному аккаунту от данных другого аккаунта.

Для этого создадим отдельный скрипт:

-- Scaling out the federation
USE FEDERATION ROOT WITH RESET
GO

ALTER FEDERATION Accounts SPLIT AT (AccountId = 2)
GO

Первое, что делает этот скрипт — это переходит к использованию federation root, то есть работает в рамках базы данных xPenses, поскольку информация о федерациях (метаданные) хранятся именно там.

Затем мы указываем, что хотим разбить федерацию с именем Accounts, начиная со значения поля AccountId равного 2. То есть данные первого аккаунта остаются в уже созданном шарде, а данные остальных аккаунтов переносятся на следующий шард. Обратите также внимание на то, что мы нигде не указываем, что данные разбиваются по таблице аккаунтов. Мы работаем только с метаданными базы данных xPenses!



Итак, выполним эту команду и… Вполне вероятна ситуация, когда обновив окно Object Explorer вы увидите следующее:



Вместо одного нового шарда мы получили… Аж целых 3! На самом деле ничего необычного в этом нет. Все дело в том, что данные хранящиеся в рамках первого шарда копируются в соответствии со значением AccountId. То есть для аккаунта с идентификатором ID равным 1, необходимо скопировать данные в один шард, с ID равным 2 — в другой. Естесственно для этого необходимо время. После того, как SQL Azure перераспределит данные по шардам, мы увидим что у нас действительно осталась база данных с метаданными федераций и два шарда.



Теперь если нам необходимо допустим разбить данные на 3 шарда, к примеру данные аккаунта с ID равным 3 перенести в отдельный шард, нам достаточно будет выполнить следующую команду:
ALTER FEDERATION Accounts SPLIT AT (AccountId = 3)
GO

Заключение

Мы рассмотрели процесс миграции скрипт создания базы данных при использовании SQL Azure Federations. Как видно, большинство острых углов обходятся достаточно просто. Однако довольно большая часть логики базы данных должна быть перенесена «выше». Мы остановились на уровне базы данных. В реальных проектах крайне рекомендуется прежде чем начать миграцию в SQL Azure Federations произвести тщательный анализ предметной области и архитектуры базы данных.