По материалам статьи 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.
При этом нет никакой автоматизации ни при создании, ни при проверке таких обоюдных связей.
В этом примере мы будем работать с подмножеством таблицы "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' |
Т.к. настройка объединения серверов не является быстровыполнимой задачей, то увеличение производительности путем использования распределенных секционированных представлений на больших таблицах может сделать такое решение более выгодным. В следующей статье эта тема будет продолжена обсуждением требований к DML на объединении серверов и методов оптимизации.