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


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

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

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

  • Сохраняется постоянная копия шагов, выполненных при развертывании топологии репликации.

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

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

    Примечание по безопасностиПримечание по безопасности

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

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

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

Сценарии репликации могут быть созданы следующими способами:

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

  • Сценарии языка 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'AdventureWorks'; 

-- 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 AdventureWorks 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=AdventureWorks
SET SubDb=AdventureWorksReplica
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\100\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

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

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

  • Настройка публикации и распределения

  • Изменение свойств издателя и распространителя

  • Отключение публикации и распространения

  • Создание публикаций и определение статей

  • Удаление публикаций и статей

  • Создание подписки по запросу

  • Изменение подписки по запросу

  • Удаление подписки по запросу

  • Создание принудительной подписки

  • Изменение принудительной подписки

  • Удаление принудительной подписки

  • Синхронизация подписки по запросу