По материалам статьи Don Schlichting: MS SQL Server Distributed Partitioned Views
В этой статье рассматривается использование распределенных секционированных представлений для доступа к множеству серверов MS SQL Server, сконфигурированных как объединение серверов БД.

Введение

В случае, когда необходимо получить дополнительную производительность на сверхбольших базах данных, а ваши хранимые процедуры уже оптимизированы, программное обеспечение является многоуровневым и аппаратные средства модернизированы, настает время для распределения вашей базы данных по нескольким серверам. Для SQL Server это делается путем горизонтального секционирования больших таблиц по множеству серверов. Если разделение таблицы с множеством столбцов на несколько таблиц с меньшим количеством столбцов является вертикальным секционированием, то горизонтальным секционированием считается разделение таблицы с множеством записей на множество таблиц с меньшим количеством записей. Если эти новые таблицы меньшего размера будут размещены на разных серверах, то это называется объединенной базой данных. Здесь используется слово "объединенный", потому что все задействованные серверы могут работать совместно для балансировки нагрузки. Они действуют как некое объединение. Как только ваши данные распределяются по нескольким серверам, для выборки записей становится необходимым новый тип выражений. Эти новые выражения называются распределенными секционированными представлениями. Они используют стандартные выражения SQL вместе с ключевым словом UNION для получения данных со всех распределенных серверов. Выражения DML (INSERT, UPDATE, и DELETE) также могут использоваться при соблюдении нескольких специальных правил, касающихся таблиц, лежащих в основе распределенного секционированного представления. Хотя прирост производительности варьируется в зависимости от используемых приложений, обычно он составляет от 20 до 30%.
Существуют три главные задачи конфигурации. Сначала все серверы соединяются друг с другом посредством создания связанных серверов, потом на каждом сервере создаются таблицы с совпадающей структурой, и в завершение создаются новые представления.
В этом примере мы распределим таблицу "Authors" базы данных "pubs" (поставляемую с MS SQL Server) по двум различным серверам. Используемые при этом правила и процедуры одинаковы, независимо от числа задействованных серверов.

Связанные серверы

Более детальное объяснение связанных серверов находится в предыдущей статье Linked Servers PART1
Первым шагом в создании объединения является связывание всех задействованных серверов друг с другом. Войдите в Query Analyzer под "sa" и установите соединение с первым сервером. Следующий код свяжет второй сервер с алиасом "server2" с первым сервером.

USE master
GO

EXEC sp_addlinkedserver
	@server = 'server2',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'InfoNet'

Параметру @server передается наш алиас. @datasrc - это имя связываемого сервера. Если бы существовало несколько экземпляров сервера, то был бы использован синтаксис ИмяСервера\ИмяЭкземпляра.
Если все представления будут запускаться из-под первого сервера, и ваша учетная запись Windows имеет соответствующие права на обеих машинах, то специальные учетные записи не потребуются. Однако, если другой клиент будет выполнять представления, то может понадобиться создание дополнительных учетных записей. См. Linked Servers PART3 и PRB: Message 18456 from a Distributed Query для более детального объяснения настроек учетных записей связанных серверов и проблем с клиентами. Предположим, что удаленные клиенты могут понадобиться в будущем, поэтому учетная запись связанного сервера, связывающая локальную учетную запись "sa" с удаленной учетной записью "sa", будет использован в качестве примера. В реальной задаче лучше создать новую учетную запись, чем использовать для этого "sa".

EXEC sp_addlinkedsrvlogin 'server2', 'false', 'sa', 'sa', 'secret'

Чтобы проверить связь между серверами, выполните:

SELECT *
FROM server2.pubs.dbo.authors

В результате должны быть выбраны все записи из таблицы "Authors". Теперь мы должны повторить то же самое со второго сервера. Это создаст обратную связь с первым сервером server1. Все то же самое, кроме названия сервера и алиаса. Войдите в Query Analyzer второго сервера под "sa" и выполните:

USE master
GO

EXEC sp_addlinkedserver
	@server = 'server1',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'dons13'
GO

EXEC sp_addlinkedsrvlogin 'server1', 'false', 'sa', 'sa', 'secret'
GO

SELECT *
FROM server1.pubs.dbo.authors

Если бы были задействованы дополнительные серверы, то потребовалось бы связать их все друг с другом. Такую же схему мы бы имели с многодоменными, доверительными соединениями NT. Если бы в нашем объединении было четыре сервера, то была бы реализована следующая схема:

Сервер1 имеет связанные серверы Сервер2, Сервер3 и Сервер4.
Сервер2 имеет связанные серверы Сервер1, Сервер3 и Сервер4.
Сервер3 имеет связанные серверы Сервер1, Сервер2 и Сервер4.
Сервер4 имеет связанные серверы Сервер1, Сервер2 и Сервер3.

При этом нет никакой автоматизации ни при создании, ни при проверке таких обоюдных связей.

Create Table

В этом примере мы будем работать с подмножеством таблицы "Authors" базы данных "pubs". Представим себе, что таблица очень большая и большинство наших запросов используют поиск по фамилии. В этом случае мы могли бы разделить таблицу "Authors" на две части, храня на одном сервере фамилии от A до M, а на другом - от N до Z. Создайте и заполните тестовую таблицу на первом сервере server1.

CREATE DATABASE test
GO

USE test
GO

CREATE TABLE AuthorsAM(
    au_lname varchar(40) NOT NULL,
    au_fname varchar(20) NULL,
        CONSTRAINT CHK_AuthorsAM CHECK (au_lname < 'N')
	)

GO

INSERT INTO AuthorsAM
    (au_lname, au_fname)
    SELECT au_lname, au_fname
    FROM pubs..authors
    WHERE au_lname < 'N'

На втором сервере server2 выполняется практически идентичный код. Ограничение CHECK изменено для нашего нового диапазона фамилий. Заметим, что у таблиц не обязательно должны быть те же имена на разных серверах. Таблица на первом сервере называется "AuthorsAM", а таблица на втором сервере - "AuthorsNZ". Наше представление уладит это.

CREATE DATABASE test
GO

USE test
GO

CREATE TABLE AuthorsNZ(
	au_lname varchar(40) NOT NULL,
	au_fname varchar(20) NULL,
             CONSTRAINT CHK_AuthorsNZ CHECK (au_lname >= 'N')
	)

GO

INSERT INTO AuthorsNZ
	(au_lname, au_fname)
	SELECT au_lname, au_fname
	FROM pubs..authors
	WHERE au_lname >= 'N'

Критической частью кода является ограничение. Ограничение CHECK должно гарантировать, что запись будет расположена в одной-единственной таблице. Во время выполнения нашего представления Query Optimizer использует эти ограничения для определения, какие серверы, какую часть распределенной работы получат.

Распределенные секционированные представления

Последним шагом является собственно создание представлений. Оператор UNION используется для слияния результатов выборки из обеих таблиц в один результирующий набор. См. BOL "Union operator" для более детальных объяснений и правил.
На первом сервере server1:

CREATE VIEW AllAuthors
AS
SELECT * 
FROM AuthorsAM

UNION ALL

SELECT *
FROM server2.test.dbo.AuthorsNZ

GO

На втором сервере server2 код опять практически тот же самый. Меняются только имена таблиц и сервера.

CREATE VIEW AllAuthors
AS
SELECT * 
FROM AuthorsNZ

UNION ALL

SELECT *
FROM server1.test.dbo.AuthorsAM

GO

Простая выборка на первом сервере server1 создает следующий план выполнения:

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

SELECT *
FROM AllAuthors
WHERE au_lname BETWEEN 'F' AND 'W'

Заключение

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

Связи

Мы начнем с создания тестовой среды. Примеры будут создаваться на основе двух машин, на каждой из которых установлен SQL 2000 под Windows 2000. Хотя в наших примерах будут только две машины, те же самые правила подходят для трех и более.
Начните с создания взаимно связанных серверов: Server2 на первом сервере Server1, и Server1 на втором сервере Server2. Установите соединение с первым сервером Server1 как sa и выполните следующий код.

USE master
GO

EXEC sp_addlinkedserver
	@server = 'server2',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'InfoNet'

GO

EXEC sp_addlinkedsrvlogin 'server2', 'false', 'sa', 'sa', 'secret'

GO

SELECT *
FROM server2.pubs.dbo.authors

В результате должны быть возвращены все записи таблицы Authors. Установите соединение со вторым сервером Server2 как sa и выполните следующий код:

USE master
GO

EXEC sp_addlinkedserver
	@server = 'server1',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'dons13'
GO

EXEC sp_addlinkedsrvlogin 'server1', 'false', 'sa', 'sa', 'secret'
GO

SELECT *
FROM server1.pubs.dbo.authors

Таблицы

После создания связанных серверов следующим шагом является создание тестовых таблиц. Представьте таблицу заказов, содержащую гигабайты истории продаж и снижающуюся производительность работы с этой таблицей. Мы разделим эту большую таблицу на две части. Первый сервер Server1 будет хранить продажи с порядковыми номерами меньше 1 000. Заказы с порядковыми номерами больше 1 000 будут храниться на втором сервере Server2.
Выполните следующие выражения на первом сервере Server1 в БД master для создания тестовой таблицы:

CREATE DATABASE test
GO

USE test
GO

CREATE TABLE [dbo].[OrdersUnder] (
	[ord_nbr] [int] NOT NULL ,
	[ord_date] [datetime] NOT NULL ,
	[cust_id] [int] NOT NULL,
	[amount] [money] NOT NULL 	
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrdersUnder] WITH CHECK ADD 
	CONSTRAINT [PK_ord_nbr] PRIMARY KEY  CLUSTERED 
	(
		[ord_nbr]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[OrdersUnder] WITH CHECK ADD 
	CONSTRAINT [CHK_Under] CHECK ([ord_nbr] <= 1000)
GO

На втором сервере Server2 код практически тот же. Меняются только название и ограничение:

CREATE DATABASE test
GO

USE test
GO

CREATE TABLE [dbo].[OrdersOver] (
	[ord_nbr] [int] NOT NULL ,
	[ord_date] [datetime] NOT NULL ,
	[cust_id] [int] NOT NULL,
	[amount] [money] NOT NULL 	
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrdersOver] WITH CHECK ADD 
	CONSTRAINT [PK_ord_nbr] PRIMARY KEY  CLUSTERED 
	(
		[ord_nbr]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[OrdersOver] WITH CHECK ADD 
	CONSTRAINT [CHK_Over] CHECK ([ord_nbr] > 1000)
GO

Необходимо отметить несколько пунктов. Во-первых, требуется ограничение CHECK. Оптимизатор запросов использует это ограничение, чтобы определить, на каком сервере расположены данные. Ограничение CHECK должно позволять данным храниться на одном, и только одном сервере.
Во-вторых, для выражений DML требуется первичный ключ. Выборка данных не требует наличия ключа. Но для операций вставки, обновления и удаления должен присутствовать первичный ключ. Однако первичный ключ не может быть создан на столбце с автоинкрементом. В этом случае будет выведена ошибка:

Server: Msg 4433, Level 16, State 4, Line 1
Cannot INSERT into partitioned view 'ViewName' 
   because table '[TableName]' has an IDENTITY constraint.

Представления

Следующее представление является простой выборкой данных с двух серверов в один результирующий набор. На первом сервере Server1 создайте представление со следующим кодом:

CREATE VIEW AllOrders
AS
SELECT * 
FROM OrdersUnder
UNION ALL
SELECT *
FROM server2.test.dbo.OrdersOver

На втором сервере Server2 код снова практически тот же:

CREATE VIEW AllOrders
AS
SELECT * 
FROM OrdersOver
UNION ALL
SELECT *
FROM server1.test.dbo.OrdersUnder

Существует несколько правил для представлений DML. Чтобы представление было обновляемым, должны возвращаться все столбцы, включенные в первичный ключ. На всех столбцах, не включенных в представление, должны быть разрешены значения NULL.
Протестируйте представление, выполнив простую выборку:

SELECT *
FROM AllOrders

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

Координатор распределенных транзакций

Перед выполнением примеров требуется запустить координатор распределенных транзакций (DTC). DTC управляет выполнением транзакций, когда задействованы несколько различных источников данных. Для Windows 2000 требуется service pack 1.
Откройте сервисы консоли MMC, найдите и запустите DTC с установками по умолчанию.

lazy schema validation

Хотя это не является обязательным требованием, но установка опции спящего режима проверки схемы (lazy schema validation) повышает производительность запросов. Проверка схемы - это проверка удаленной схемы на то, что ее метаданные верны. При установке этой проверки в спящий режим SQL не проверяет верность удаленных метаданных относительно нашего запроса до его выполнения. Если произошло изменение удаленной схемы, то наш запрос вернет ошибку. В нашем случае мы знаем, что удаленная таблица верна и имеет ту же структуру, что и наша локальная таблица. Не проверяя удаленную схему, мы получим повышение производительности.

use master
GO
sp_serveroption 'LocalServerName', 'lazy schema validation', true
GO
sp_serveroption 'server2', 'lazy schema validation', true
GO

Collation Compatible

Если мы дадим SQL информацию, что сортировка и набор символов одинаковы у локального и удаленного серверов, то удаленный сервер сможет принимать участие в сравнениях. В противном случае все данные сначала будут приходить на локальный сервер. Все сравнения в этом случае будут делаться локально, снижая производительность. Этого можно избежать, включив опцию совместимого сопоставления (collation compatible). Эта опция также не является обязательным требованием, а используется для оптимизации.

use master
GO
sp_serveroption 'LocalServerName', 'collation compatible', true
GO
sp_serveroption 'server2', 'collation compatible', true
GO
DML

Начнем со вставки данных в новую пустую таблицу. Следующий код выполнит вставку одной записи:

use test
GO
SET XACT_ABORT  ON
GO
INSERT INTO AllOrders
	(ord_nbr, ord_date, cust_id, amount)
VALUES
	(1001, '01/01/1993', 5, 50.25)

Сама вставка - это операция, которую Transact-SQL выполняет регулярно. Опция XACT_ABORT требуется выражений, которые модифицируют данные. Когда опция установлена, любая ошибка времени исполнения приводит к откату всей транзакции.
Выборка данных из нашего представления подтверждает, что запись была успешно сохранена:

Удаление записей включает те же шаги:

SET XACT_ABORT  ON
GO
DELETE 
FROM AllOrders
WHERE cust_id = 5

Недавно добавленная запись удалена. Отметим, что выражению WHERE не требуется ссылаться на наш первичный ключ или столбец с ограничением CHECK.

Все понемногу

Представление может обращаться к связанной таблице по имени, состоящем из четырех частей (как это сделано в нашем примере), при использовании функции OPENROWSET или функции OPENDATASOURCE.
Вставка и обновление данных не разрешены в таблицах со столбцом timestamp.
Полный список правил работы с представлениями см. в BOL "Секционированные представления".
Хотя у нас теперь имеется две таблицы на двух различных серверах, которые действуют как одна, нет никаких автоматических средств для сохранения или восстановления их как единого целого.

Заключение

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