Основные понятия системных хранимых процедур репликации

Статья
05/08/2013

В SQL Server программный доступ ко всем настраиваемым пользователями функциональным возможностям в топологии репликации предоставляется системными хранимыми процедурами. Безусловно, хранимые процедуры могут выполняться отдельно с использованием среды Среда SQL Server Management Studio или программы командной строки sqlcmd, но может оказаться более удобным написание файлов скриптов Transact-SQL, предназначенных для выполнения задач репликации в логической последовательности.

Задачи репликации со сценарной поддержкой предоставляют следующие преимущества:

Сохраняется постоянная копия шагов, выполненных при развертывании топологии репликации.
Используется единственный скрипт для настройки нескольких подписчиков.

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

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

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

Создание скриптов репликации

С точки зрения репликации скрипт представляет собой ряд, состоящий из одной или нескольких инструкций Transact-SQL, в котором каждая инструкция выполняет хранимую процедуру репликации. Скрипты — это текстовые файлы, часто имеющие такое расширение файла, как SQL, которые могут быть вызваны на выполнение с помощью программы sqlcmd. После вызова файла скрипта эта программа выполняет инструкции SQL, хранящиеся в файле. Аналогичным образом скрипт может храниться как объект запроса в проекте среды Среда SQL Server Management Studio.

Для создания скриптов репликации могут применяться следующие способы.

Создание скрипта вручную.
Использование средств создания скриптов, предусмотренных в мастерах репликации.
Среда SQL Server Management Studio. Дополнительные сведения см. в разделе Создание сценариев репликации.
использование объектов RMO для формирования программным путем скрипта создания объекта RMO.

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

Скрипты языка Transact-SQL содержат один или несколько пакетов. Команда GO означает конец пакета. Если скрипт языка Transact-SQL не содержит команд GO, то он выполняется как единый пакет.
Если в одном пакете должно быть выполнено несколько хранимых процедур репликации, то после первой процедуры все последующие процедуры в пакете должны быть указаны с предшествующим ключевым словом EXECUTE.
Все хранимые процедуры в пакете должны быть откомпилированы до выполнения пакета. Но после компиляции пакета и создания плана выполнения ошибки времени выполнения могут происходить или не происходить.
Если скрипты создаются для настройки конфигурации репликации, то должна использоваться проверка подлинности Windows для исключения необходимости хранить учетные данные безопасности в файле скрипта. При необходимости хранения учетных данных в файле скрипта этот файл следует защитить от несанкционированного доступа.

Образец скрипта репликации

Следующий скрипт может быть выполнен для установки на сервере средств публикации и распределения.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
-- Specify the replication working directory.
SET @directory = N'\\' + $(DistPubServer) + '\repldata';
-- Specify the publication database.
SET @publicationDB = N'AdventureWorks2012'; 

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB, 
    @security_mode = 1;
GO

-- Create a Publisher and enable AdventureWorks2012 for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher, 
    @distribution_db=@distributionDB, 
    @security_mode = 1;
GO

Затем этот скрипт может быть сохранен локально в качестве instdistpub.sql, чтобы его можно было выполнить один или несколько раз, если это потребуется.

Предыдущий скрипт включает переменные скрипта программы sqlcmd, которые используются во многих образцах кода репликации в электронной документации по SQL Server. Переменные сценария определены с использованием синтаксиса $(MyVariable). Значения переменных могут быть переданы в скрипт в командной строке или в среде Среда SQL Server Management Studio. Дополнительные сведения см. в следующем подразделе данного раздела, «Выполнение скриптов репликации».

Выполнение скриптов репликации

Для выполнения скрипта репликации после его создания может быть использован один из следующих способов.

Создание файла SQL-запроса в среде SQL Server Management Studio

Файл скрипта репликации Transact-SQL может быть создан как файл SQL-запроса в проекте среды Среда SQL Server Management Studio. После записи скрипта может быть создано соединение с базой данных для этого файла запроса и скрипт вызван на выполнение. Дополнительные сведения о том, как создать сценарий Transact-SQL с использованием среды Среда SQL Server Management Studio, см. в разделе Редакторы запросов и текста (среда SQL Server Management Studio).

Чтобы можно было использовать скрипт, который включает переменные скрипта, необходимо запустить среду Среда SQL Server Management Studio в режиме sqlcmd. В режиме sqlcmd редактор запросов воспринимает дополнительный синтаксис, характерный для sqlcmd, такой как :setvar, который используется для значений переменных. Дополнительные сведения о режиме работы служб sqlcmd см. в разделе Изменение скриптов SQLCMD при помощи редактора запросов. В следующем скрипте :setvar используется для предоставления значения переменной $(DistPubServer).

:setvar DistPubServer N'MyPublisherAndDistributor';

-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);

--
-- Additional code goes here
--

Использование программы sqlcmd в командной строке

Следующий пример показывает, как используется командная строка для выполнения файла скрипта instdistpub.sql с применением программы sqlcmd:

sqlcmd.exe -E -S sqlserverinstance -i C:\instdistpub.sql -o C:\output.log -v DistPubServer="N'MyDistributorAndPublisher'"

В этом примере параметр -E указывает, что используется проверка подлинности Windows при соединении с SQL Server. Если используется проверка подлинности Windows, то отсутствует необходимость хранить имя пользователя и пароль в файле скрипта. Имя и путь к файлу скрипта задаются параметром -i, а имя выходного файла — параметром -o (если используется этот параметр, то вывод программы SQL Server записывается в указанный файл, а не на консоль). Программа sqlcmd позволяет передавать переменные скрипта в скрипт Transact-SQL во время выполнения с помощью параметра -v. В этом примере программа sqlcmd заменяет каждое вхождение переменной $(DistPubServer) в скрипте значением N'MyDistributorAndPublisher' перед выполнением.

Примечание
Параметр -X позволяет запретить использование переменных сценария.

Автоматизация задач с применением пакетного файла

Применение пакетного файла позволяет автоматизировать задачи администрирования репликации, задачи синхронизации репликации и другие задачи с помощью одного и того же пакетного файла. В следующем пакетном файле программа sqlcmd используется для удаления и повторного создания базы данных подписки, а также добавления подписки слиянием, выполняемой по запросу. Затем в этом файле предусмотрен вызов агента слияния для синхронизации новой подписки:

REM ----------------------Script to synchronize merge subscription ----------------------
REM -- Creates subscription database and 
REM -- synchronizes the subscription to MergeSalesPerson.
REM -- Current computer acts as both Publisher and Subscriber.
REM -------------------------------------------------------------------------------------

SET Publisher=%computername%
SET Subscriber=%computername%
SET PubDb=AdventureWorks2012
SET SubDb=AdventureWorks2012Replica
SET PubName=AdvWorksSalesOrdersMerge

REM -- Drop and recreate the subscription database at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE master IF EXISTS (SELECT * FROM sysdatabases WHERE name='%SubDb%' ) DROP DATABASE %SubDb%"
sqlcmd /S%Subscriber% /E /Q"USE master CREATE DATABASE %SubDb%"

REM -- Add a pull subscription at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE %SubDb% EXEC sp_addmergepullsubscription @publisher = %Publisher%, @publication = %PubName%, @publisher_db = %PubDb%"
sqlcmd /S%Subscriber% /E /Q"USE %SubDb%  EXEC sp_addmergepullsubscription_agent @publisher = %Publisher%, @publisher_db = %PubDb%, @publication = %PubName%, @subscriber = %Subscriber%, @subscriber_db = %SubDb%, @distributor = %Publisher%"

REM -- This batch file starts the merge agent at the Subscriber to 
REM -- synchronize a pull subscription to a merge publication.
REM -- The following must be supplied on one line.
"\Program Files\Microsoft SQL Server\110\COM\REPLMERG.EXE"  -Publisher  %Publisher% -Subscriber  %Subscriber%  -Distributor %Publisher%  -PublisherDB  %PubDb% -SubscriberDB %SubDb% -Publication %PubName% -PublisherSecurityMode 1 -OutputVerboseLevel 1  -Output  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 1 -Validate 3

Сценарная поддержка общих задач репликации

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

Настройка публикации и распределения
Изменение свойств издателя и распространителя
Отключение публикации и распространения
Создание публикаций и определение статей
Удаление публикаций и статей
Создание подписки по запросу
Изменение подписки по запросу
Удаление подписки по запросу
Создание принудительной подписки
Изменение принудительной подписки
Удаление принудительной подписки
Синхронизация подписки по запросу

См. также

Справочник

Хранимые процедуры репликации (Transact-SQL)

Основные понятия

Основные понятия программирования репликации

Создание сценариев репликации

Поделиться через