Распределенные секционированные представления MS SQL Server. Часть 2

ПУБЛИКАЦИИ  

По материалам статьи Don Schlichting: MS SQL Server Distributed Partitioned Views Part 2
Перевод Виталия Степаненко

Во второй статье рассматривается использование распределенных секционированных представлений для операций вставки, обновления и удаления.

Содержание

Введение

В 1 части был произведен обзор основ распределенных секционированных представлений, объединенных баз данных, и горизонтального секционирования. В качестве примеров были созданы связанный сервер, секционированная таблица и представление, использующее оператор UNION. Во второй части будет показан новый пример, демонстрирующий выражения DML (INSERT, UPDATE и DELETE).

[В начало]

Связи

Мы начнем с создания тестовой среды. Примеры будут создаваться на основе двух машин, на каждой из которых установлен 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

Объяснение вышележащего кода см. в 1 части.

[В начало]

Таблицы

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

[В начало]

Перевод Виталия Степаненко  2004г.

ПУБЛИКАЦИИ
Скачать электронную карту Ангарска бесплатно
Сайт управляется системой uCoz