Compartir por

Conceptos de procedimientos almacenados del sistema de replicación

  • Artigo

Se aplica a: SQL Server Azure SQL Instancia administrada

En SQL Server, el acceso mediante programación a toda la funcionalidad configurable por el usuario en una topología de replicación se proporciona mediante procedimientos almacenados del sistema. Aunque los procedimientos almacenados se pueden ejecutar individualmente mediante SQL Server Management Studio o la utilidad de línea de comandos sqlcmd, puede resultar beneficioso escribir archivos de script transact-SQL que se pueden ejecutar para realizar una secuencia lógica de tareas de replicación.

Las tareas de replicación para scripting proporcionan las ventajas siguientes:

  • Se mantiene una copia permanente de los pasos que se usan para implementar la topología de replicación.

  • Se usa un único script para configurar varios suscriptores.

  • Se instruye rápidamente a los nuevos administradores de bases de datos permitiéndoles evaluar, entender, cambiar o solucionar problemas del código.

    Importante

    Los scripts pueden ser fuente de vulnerabilidades de la seguridad, ya que pueden invocar funciones del sistema sin la intervención ni el conocimiento del usuario y contener credenciales de seguridad en texto simple. Antes de usarlos, compruebe los aspectos siguientes de la seguridad de los scripts.

Crear scripts de replicación

Desde el punto de vista de la replicación, un script es una serie de una o varias instrucciones Transact-SQL donde cada instrucción ejecuta un procedimiento almacenado de replicación. Los scripts son archivos de texto, a menudo con la extensión .sql, que se pueden ejecutar utilizando la utilidad sqlcmd. Cuando se ejecuta un archivo de script, la utilidad ejecuta las instrucciones de SQL almacenadas en él. Del mismo modo, un script se puede almacenar como un objeto de consulta en un proyecto de SQL Server Management Studio.

Los scripts de replicación se pueden crear de las maneras siguientes:

  • Cree el script manualmente.

  • Use las características de generación de script que se proporcionan en los asistentes de replicación o

  • SQL Server Management Studio. Para más información, consulte Scripting Replication.

  • Utilice Replication Management Objects (RMO) para generar mediante programación el script y crear un objeto RMO.

Al crear manualmente los scripts de replicación, tenga presente las consideraciones siguientes:

  • Los scripts de Transact-SQL tienen uno o varios lotes. El comando GO señala el final de un lote. Si un script de Transact-SQL no tiene ningún comando GO, se ejecuta como un solo lote.

  • Al ejecutar varios procedimientos almacenados de la replicación en un único lote, después del primer procedimiento, la palabra clave EXECUTE debe preceder todos a los procedimientos subsiguientes en el lote.

  • Todos los procedimientos almacenados en un lote deben compilarse antes de que se ejecute un lote. Sin embargo, una vez compilado el lote y creado un plan de ejecución, un error de tiempo de ejecución puede aparecer o no.

  • Al crear scripts para configurar la replicación, debería utilizar la autenticación de Windows para evitar almacenar las credenciales de seguridad en el archivo de script. Si debe almacenar las credenciales en un archivo de script, proteja el archivo para evitar el acceso no autorizado.

Ejemplo de script de replicación

El script siguiente se puede ejecutar para configurar la publicación y distribución en un servidor.

-- 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'AdventureWorks2022';   
  
-- 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 AdventureWorks2022 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

Este script puede guardarse entonces localmente como instdistpub.sql para que se pueda ejecutar o volver a ejecutar cuando sea necesario.

El script anterior incluye variables de scripting sqlcmd , que se usan en muchos de los ejemplos de código de replicación en los Libros en pantalla de SQL Server. Las variables de scripting se definen con la sintaxis $(MyVariable). Los valores de las variables se pueden pasar a un script en la línea de comandos o en SQL Server Management Studio. Para obtener más información, consulte la sección siguiente en este tema, "Ejecutar scripts de replicación".

Ejecutar scripts de replicación

Una vez creado, un script de replicación se puede ejecutar de alguna de las maneras siguientes:

Crear un archivo de SQL Query en SQL Server Management Studio

Un archivo de script transact-SQL de replicación se puede crear como un archivo de consulta SQL en un proyecto de SQL Server Management Studio. Una vez escrito el script, se puede realizar una conexión a la base de datos para este archivo de consulta y se puede ejecutar el script. Para obtener más información sobre cómo crear scripts de Transact-SQL mediante SQL Server Management Studio, vea Editores de consultas y texto (SQL Server Management Studio) .

Para usar un script que incluya variables de scripting, SQL Server Management Studio debe ejecutarse en modo sqlcmd . En el modo sqlcmd, el Editor de consultas acepta una sintaxis adicional concreta de sqlcmd, como :setvar, que se usa como valor de una variable. Para obtener más información sobre el modo sqlcmd, vea Modificar scripts SQLCMD con el Editor de consultas. En el script siguiente se usa :setvar para proporcionar un valor para la variable $(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  
--

Usar la utilidad sqlcmd desde la línea de comandos

El ejemplo siguiente muestra cómo se usa la línea de comandos para ejecutar el archivo de script instdistpub.sql mediante la utilidad sqlcmd:

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

En este ejemplo, el -E modificador indica que se usa la autenticación de Windows al conectarse a SQL Server. Al usar la autenticación de Windows, no hay necesidad de almacenar un nombre de usuario y una contraseña en el archivo de script. El modificador especifica el -i nombre y la ruta de acceso del archivo de script y el modificador especifica el nombre del archivo de salida (la -o salida de SQL Server se escribe en este archivo en lugar de la consola cuando se usa este modificador). La sqlcmd utilidad permite pasar variables de scripting a un script de Transact-SQL en tiempo de ejecución mediante el -v modificador . En este ejemplo, sqlcmd reemplaza cada instancia de $(DistPubServer) en el script con el valor N'MyDistributorAndPublisher' antes de la ejecución.

Nota:

El modificador -X deshabilita las variables de scripting.

Automatizar tareas en un archivo por lotes

Mediante un archivo por lotes, las tareas de administración de replicación, las tareas de sincronización de replicación y otras diversas se pueden automatizar en el mismo archivo por lotes. El archivo por lotes siguiente usa la utilidad sqlcmd para quitar y volver a crear la base de datos de suscripciones y agregar una suscripción de extracción de mezcla. A continuación, el archivo invoca al agente de mezcla para sincronizar la nueva suscripción:

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\130\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

Incluir en script tareas de replicación comunes

Las siguientes son algunas de las tareas de replicación más comunes que se pueden incluir en scripts utilizando procedimientos almacenados del sistema:

  • Configurar la publicación y la distribución

  • Modificar las propiedades del distribuidor y del publicador

  • Deshabilitar la publicación y la distribución

  • Crear publicaciones y definir artículos

  • Eliminar publicaciones y artículos

  • Crear una suscripción de extracción

  • Modificar una suscripción de extracción

  • Eliminar una suscripción de extracción

  • Crear una suscripción de inserción

  • Modificar una suscripción de inserción

  • Eliminar una suscripción de inserción

  • Sincronizar una suscripción de extracción

Consulte también

Conceptos de la programación de replicación
Procedimientos almacenados de replicación (Transact-SQL)
Crear script para la replicación