Como criar um instantâneo para uma publicação de mesclagem com filtros com parâmetros (Programação Transact-SQL de replicação)
Ao gerar instantâneos para publicações usando filtros com parâmetros, você deve, primeiro, gerar um instantâneo padrão ou de esquema, que contenha todos os dados publicados e os metadados do Assinante para a assinatura. Para obter mais informações, consulte Como criar o instantâneo inicial (Programação Transact-SQL de replicação). Após ter criado um instantâneo de esquema, você poderá gerar o instantâneo que contém a partição Assinante específica dos dados publicados.
Ao usar os procedimentos armazenados e o Snapshot Agent, será possível:
Permitir que os Assinantes solicitem geração e aplicação de instantâneos na primeira vez em que forem sincronizados.
Pré-gerar instantâneos para cada partição.
Gerar manualmente um instantâneo para cada Assinante.
Observação sobre segurança Quando possível, solicite que os usuários entrem com as credenciais de segurança no tempo de execução. Se for necessário armazenar credenciais em um arquivo de script, você deverá proteger o arquivo para evitar o acesso não autorizado.
Para criar uma publicação que permita ao Assinantes iniciarem a geração e a entrega de instantâneo
No Publicador do banco de dados de publicação, execute sp_addmergepublication (Transact-SQL). Especifique os seguintes parâmetros:
O nome da publicação para @publication.
Um valor true para @allow_subscriber_initiated_snapshot, o qual habilita os Assinantes a iniciarem o processo do instantâneo.
(Opcional) O número de processos de instantâneo dinâmico que pode executar simultaneamente para @ max_concurrent_dynamic_snapshots. Se o número máximo de processos estiver em execução e o Assinante tentar gerar um instantâneo, o processo será colocado em uma fila. Por padrão não há nenhum limite para o número de processos simultâneos.
No Publicador, execute sp_addpublication_snapshot (Transact-SQL). Especifique o nome da publicação usado na etapa 1 para @publication e as credenciais do Windows Microsoft no qual o Replication Snapshot Agent executa para @job_login e @password. Se o agente usar a Autenticação do SQL Server quando se conectar ao Publicador, você também deverá especificar um valor de 0 para @publisher_security_mode e as informações de logon do Microsoft SQL Server para @publisher_login e @publisher_password. Isso irá criar um trabalho do Snapshot Agent para a publicação. Para obter mais informações sobre como gerar um instantâneo inicial e definir uma agenda personalizada para o Snapshot Agent, consulte Como criar o instantâneo inicial (Programação Transact-SQL de replicação).
.gif "Observação sobre segurança")
Observação sobre segurançaQuando um Publicador é configurado com um Distribuidor remoto, os valores fornecidos para todos os parâmetros, inclusive job_login e job_password, são enviados ao Distribuidor como texto sem formatação. Você deve criptografar a conexão entre o Publicador e o Distribuidor remoto antes de executar esse procedimento armazenado. Para obter mais informações, consulte Criptografando conexões com o SQL Server.
Execute sp_addmergearticle (Transact-SQL) para adicionar artigos à publicação. Esse procedimento armazenado deve ser executado uma vez para cada artigo na publicação. Ao usar filtros com parâmetros, você deve especificar um filtro de linha com parâmetros para um ou mais artigos que usam o parâmetro @subset_filterclause. Para obter mais informações, consulte Como definir e modificar um filtro de linha com parâmetros para um artigo de mesclagem (Programação Transact-SQL de replicação).
Se outros artigos forem filtrados com base no filtro de linha com parâmetros, execute sp_addmergefilter (Transact-SQL) para definir as relações de junção ou lógica entre os artigos. Esse procedimento armazenado deve ser executado uma vez para cada artigo sendo definido. Para obter mais informações, consulte Como definir e modificar um filtro de junção entre artigos de mesclagem (Programação Transact-SQL de replicação).
Quando o Merge Agent solicita que o instantâneo inicialize o Assinante, o instantâneo para a solicitação de partição de assinatura é gerado automaticamente.
Para criar uma publicação e pré-gerar ou atualizar automaticamente instantâneos
Execute sp_addmergepublication (Transact-SQL) para criar a publicação. Para obter mais informações, consulte Como criar uma publicação (Programação Transact-SQL de replicação).
No Publicador, execute sp_addpublication_snapshot (Transact-SQL). Especifique o nome da publicação usada na etapa 1 para @publication e as credenciais do Windows no qual o Snapshot Agent executa para @job_login e @password. Se o agente usar a Autenticação do SQL Server quando se conectar ao publicador, você também deverá especificar um valor de 0 para @publisher_security_mode e as informações de logon do SQL Server para @publisher_login e @publisher_password. Isso irá criar um trabalho do Snapshot Agent para a publicação. Para obter mais informações sobre como gerar um instantâneo inicial e definir uma agenda personalizada para o Snapshot Agent, consulte Como criar o instantâneo inicial (Programação Transact-SQL de replicação).
Observação sobre segurançaQuando um Publicador é configurado com um Distribuidor remoto, os valores fornecidos para todos os parâmetros, inclusive job_login e job_password, são enviados ao Distribuidor como texto sem formatação. Você deve criptografar a conexão entre o Publicador e o Distribuidor remoto antes de executar esse procedimento armazenado. Para obter mais informações, consulte Criptografando conexões com o SQL Server.
Execute sp_addmergearticle (Transact-SQL) para adicionar artigos à publicação. Esse procedimento armazenado deve ser executado uma vez para cada artigo na publicação. Ao usar filtros com parâmetros, você deve especificar um filtro de linha com parâmetros para um artigo usando o parâmetro @subset_filterclause. Para obter mais informações, consulte Como definir e modificar um filtro de linha com parâmetros para um artigo de mesclagem (Programação Transact-SQL de replicação).
Se outros artigos forem filtrados com base no filtro de linha com parâmetros, execute sp_addmergefilter (Transact-SQL) para definir as relações de junção ou lógica entre os artigos. Esse procedimento armazenado deve ser executado uma vez para cada artigo sendo definido. Para obter mais informações, consulte Como definir e modificar um filtro de junção entre artigos de mesclagem (Programação Transact-SQL de replicação).
No Publicador do banco de dados de publicação, execute sp_helpmergepublication (Transact-SQL), especificando o valor @publication da etapa 1. Observe o valor snapshot_jobid no conjunto de resultados.
Converta o valor do snapshot_jobid obtido na etapa 5 para uniqueidentifier.
No Publicador do banco de dados msdb , execute sp_start_job (Transact-SQL), especificando o valor convertido obtido na etapa 6 para @ job_id.
No Publicador do banco de dados de publicação, execute sp_addmergepartition (Transact-SQL). Especifique o nome da publicação da etapa 1 para @publication e o valor usado para definir a partição para @suser_sname se SUSER_SNAME (Transact-SQL) é usado na cláusula de filtro ou para @host_name se HOST_NAME (Transact-SQL) é usado na cláusula de filtro.
No Publicador do banco de dados de publicação, execute sp_adddynamicsnapshot_job (Transact-SQL). Especifique o nome da publicação da etapa 1 para @publication, o valor @suser_sname ou @host_name da etapa 8, e uma agenda para o trabalho. Isso irá criar o trabalho que gera o instantâneo com parâmetros para a partição especificada. Para obter mais informações, consulte Como especificar agendas de sincronização (Programação Transact-SQL de replicação).
.gif "Observação")
ObservaçãoEsse trabalho executa, usando a mesma conta Windows do trabalho de instantâneo inicial definido na etapa 2. Para remover o trabalho de instantâneo com parâmetros e a partição de dados relacionada, execute sp_dropdynamicsnapshot_job (Transact-SQL).
No Publicador do banco de dados de publicação, execute sp_helpmergepartition (Transact-SQL), especificando o valor @publication da etapa 1 e o valor @suser_sname ou @host_name da etapa 8. Observe o valor dynamic_snapshot_jobid no conjunto de resultados.
No Distribuidor, no banco de dados msdb , execute sp_start_job (Transact-SQL), especificando o valor obtido na etapa 9 para @job_id. Isso inicia o trabalho de instantâneo com parâmetros para a partição.
Repita as etapas de 8 a11 para gerar um instantâneo particionado para cada assinatura.
Para criar uma publicação e criar manualmente instantâneos para cada partição
Execute sp_addmergepublication (Transact-SQL) para criar a publicação. Para obter mais informações, consulte Como criar uma publicação (Programação Transact-SQL de replicação).
No Publicador, execute sp_addpublication_snapshot (Transact-SQL). Especifique o nome da publicação usada na etapa 1 para @publication e as credenciais do Windows no qual o Snapshot Agent executa para @job_login e @password. Se o agente usar a Autenticação do SQL Server quando se conectar ao Publisher, você também deverá especificar um valor de 0 para @publisher_security_mode e as informações de logon do SQL Server para @publisher_login e @publisher_password. Isso irá criar um trabalho do Snapshot Agent para a publicação. Para obter mais informações sobre como gerar um instantâneo inicial e definir uma agenda personalizada para o Snapshot Agent, consulte Como criar o instantâneo inicial (Programação Transact-SQL de replicação).
Observação sobre segurançaQuando um Publicador é configurado com um Distribuidor remoto, os valores fornecidos para todos os parâmetros, inclusive job_login e job_password, são enviados ao Distribuidor como texto sem formatação. Você deve criptografar a conexão entre o Publicador e o Distribuidor remoto antes de executar esse procedimento armazenado. Para obter mais informações, consulte Criptografando conexões com o SQL Server.
Execute sp_addmergearticle (Transact-SQL) para adicionar artigos à publicação. Esse procedimento armazenado deve ser executado uma vez para cada artigo na publicação. Ao usar filtros com parâmetros, você deve especificar um filtro de linha com parâmetros para pelo menos um artigo usando o parâmetro @subset_filterclause. Para obter mais informações, consulte Como definir e modificar um filtro de linha com parâmetros para um artigo de mesclagem (Programação Transact-SQL de replicação).
Se outros artigos forem filtrados com base no filtro de linha com parâmetros, execute sp_addmergefilter (Transact-SQL) para definir as relações de junção ou lógica entre os artigos. Esse procedimento armazenado deve ser executado uma vez para cada artigo sendo definido. Para obter mais informações, consulte Como definir e modificar um filtro de junção entre artigos de mesclagem (Programação Transact-SQL de replicação).
Inicie o trabalho de instantâneo ou execute o Replication Snapshot Agent no prompt de comando para gerar o esquema de instantâneo padrão e outros arquivos. Para obter mais informações, consulte Como criar o instantâneo inicial (Programação Transact-SQL de replicação).
Execute o Replication Snapshot Agent novamente no prompt de comando para gerar arquivos (.bcp) para cópia em massa, especificando o local do instantâneo particionado para -DynamicSnapshotLocation e uma ou ambas as seguintes propriedades que definam a partição:
-DynamicFilterHostName - o valor se HOST_NAME (Transact-SQL) é usado.
-DynamicFilterLogin - o valor se SUSER_SNAME (Transact-SQL) é usado.
Repita a etapa 6 para gerar um instantâneo particionado para cada assinatura.
Execute o Merge Agent para cada assinatura para aplicar o instantâneo inicial particionado nos Assinantes, especificando as propriedades a seguir:
-Hostname - o valor usado para definir a partição, se o valor atual de HOST_NAME estiver sendo substituído.
- DynamicSnapshotLocation - o local do instantâneo dinâmico para esta partição.
| Observação |
|---|
Para obter mais informações sobre como programar agentes de replicação, consulte Conceitos dos executáveis do Replication Agent. |
Exemplo
Esse exemplo cria uma publicação de mesclagem com filtros com parâmetros onde Assinantes inicia o processo de geração de instantâneo. Os valores para @job_login e @job_password são passados no uso de valores de script.
-- To avoid storing the login and password in the script file, the value
-- is passed into SQLCMD as a scripting variable. 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".
--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;
SET @publicationdb = N'AdventureWorks2008R2';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';
USE [AdventureWorks2008R2];
-- Enable AdventureWorks2008R2 for merge replication.
EXEC sp_replicationdboption
@dbname = @publicationdb,
@optname = N'merge publish',
@value = N'true';
-- Create new merge publication with Subscriber requested snapshot
-- and using the default agent schedule.
EXEC sp_addmergepublication
@publication = @publication,
@description = N'Merge publication of AdventureWorks2008R2.',
@allow_subscriber_initiated_snapshot = N'true',
@publication_compatibility_level = N'90RTM';
-- Create a new snapshot job for the publication, using the default schedule.
-- Pass credentials at runtime using sqlcmd scripting variables.
EXEC sp_addpublication_snapshot
@publication = @publication,
@job_login = $(login),
@job_password = $(password);
-- Add an article for the Employee table,
-- which is horizontally partitioned using
-- a parameterized row filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table1,
@source_owner = @schema_hr,
@source_object = @table1,
@type = N'table',
@description = 'contains employee information',
@subset_filterclause = N'[LoginID] = HOST_NAME()';
-- Add an article for the SalesPerson table,
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table2,
@source_owner = @schema_sales,
@source_object = @table2,
@type = N'table',
@description = 'contains salesperson information';
-- Add a join filter between the two articles.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table1,
@filtername = @filter,
@join_articlename = @table2,
@join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]',
@join_unique_key = 1,
@filter_type = 1;
GO
-- Start the agent job to generate the full snapshot for the publication.
-- The filtered data snapshot is generated automatically the first time
-- the subscription is synchronized.
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';
EXEC sp_startpublication_snapshot
@publication = @publication;
GO
Esse exemplo cria uma publicação usando um filtro com parâmetros, onde cada Assinante tem sua partição definida ao executar sp_addmergepartition e o trabalho de instantâneo filtrado criado pela execução sp_adddynamicsnapshot_job passando as informações de particionamento. Os valores para @job_login e @job_password são passados no uso de valores de script.
-- To avoid storing the login and password in the script file, the value
-- is passed into SQLCMD as a scripting variable. 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".
--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;
SET @publicationdb = N'AdventureWorks2008R2';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';
USE [AdventureWorks2008R2];
-- Enable AdventureWorks2008R2 for merge replication.
EXEC sp_replicationdboption
@dbname = @publicationdb,
@optname = N'merge publish',
@value = N'true';
-- Create new merge publication.
EXEC sp_addmergepublication
@publication = @publication,
@description = N'Merge publication of AdventureWorks2008R2.',
@allow_subscriber_initiated_snapshot = N'false';
-- Create a new snapshot job for the publication, using the
-- default schedule. Pass credentials at runtime using
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot
@publication = @publication,
@job_login = $(Login),
@job_password = $(password);
-- Add an article for the Employee table,
-- which is horizontally partitioned using
-- a parameterized row filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table1,
@source_owner = @schema_hr,
@source_object = @table1,
@type = N'table',
@description = 'contains employee information',
@subset_filterclause = N'[LoginID] = HOST_NAME()';
-- Add an article for the SalesPerson table,
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table2,
@source_owner = @schema_sales,
@source_object = @table2,
@type = N'table',
@description = 'contains customer information';
-- Add a join filter between the two articles.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table1,
@filtername = @filter,
@join_articlename = @table2,
@join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]',
@join_unique_key = 1,
@filter_type = 1;
GO
-- Start the snapshot agent job.
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';
EXEC sp_startpublication_snapshot
@publication = @publication;
GO
PRINT '*** Waiting for the initial snapshot.';
GO
-- Create a temporary table to store the filtered data snapshot
-- job information.
CREATE TABLE #temp (id int,
job_name sysname,
job_id uniqueidentifier,
dynamic_filter_login sysname NULL,
dynamic_filter_hostname sysname NULL,
dynamic_snapshot_location nvarchar(255),
frequency_type int,
frequency_interval int,
frequency_subday_type int,
frequency_subday_interval int,
frequency_relative_interval int,
frequency_recurrence_factor int,
active_start_date int,
active_end_date int,
active_start_time int,
active_end_time int
)
-- Create each snapshot for a partition
-- The initial snapshot must already be generated.
DECLARE @publication AS sysname;
DECLARE @jobname AS sysname
DECLARE @hostname AS sysname
SET @publication = N'AdvWorksSalesPersonMerge';
SET @hostname = N'adventure-works\Fernando';
WHILE NOT EXISTS(SELECT * FROM sysmergepublications
WHERE [name] = @publication
AND snapshot_ready = 1)
BEGIN
WAITFOR DELAY '00:00:05'
END
-- Create a data partition by overriding HOST_NAME().
EXEC sp_addmergepartition
@publication = @publication,
@host_name = @hostname;
-- Create the filtered data snapshot job, and use the returned
-- information to start the job.
EXEC sp_adddynamicsnapshot_job
@publication = @publication,
@host_name = @hostname;
INSERT INTO #temp (id, job_name, job_id, dynamic_filter_login,
dynamic_filter_hostname, dynamic_snapshot_location,
frequency_type, frequency_interval, frequency_subday_type,
frequency_subday_interval, frequency_relative_interval,
frequency_recurrence_factor, active_start_date, active_end_date,
active_start_time,active_end_time)
EXEC sp_helpdynamicsnapshot_job;
SELECT @jobname = (SELECT DISTINCT job_name FROM #temp WHERE dynamic_filter_hostname = @hostname);
EXEC msdb..sp_start_job @job_name = @jobname;
DROP TABLE #temp;
GO
Esse exemplo cria uma publicação usando um filtro com parâmetros, onde cada Assinante deve ter sua própria partição de dados e trabalho de instantâneo criado pelo fornecimento das informações de particionamento. Um Assinante fornece informações de particionamento, usando parâmetros de linha de comando ao executar, manualmente, os agentes de replicação. Esse exemplo assume que também foi criada uma assinatura para a publicação.
-- To avoid storing the login and password in the script file, the value
-- is passed into SQLCMD as a scripting variable. 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".
--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;
SET @publicationdb = N'AdventureWorks2008R2';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';
USE [AdventureWorks2008R2];
-- Enable AdventureWorks2008R2 for merge replication.
EXEC sp_replicationdboption
@dbname = @publicationdb,
@optname = N'merge publish',
@value = N'true';
-- Create new merge publication.
EXEC sp_addmergepublication
@publication = @publication,
@description = N'Merge publication of AdventureWorks2008R2.',
@allow_subscriber_initiated_snapshot = N'false';
-- Create a new snapshot job for the publication, using the
-- default schedule. Pass credentials at runtime using
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot
@publication = @publication,
@job_login = $(Login),
@job_password = $(Password);
-- Add an article for the Employee table,
-- which is horizontally partitioned using
-- a parameterized row filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table1,
@source_owner = @schema_hr,
@source_object = @table1,
@type = N'table',
@description = 'contains employee information',
@subset_filterclause = N'[LoginID] = HOST_NAME()';
-- Add an article for the SalesPerson table,
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table2,
@source_owner = @schema_sales,
@source_object = @table2,
@type = N'table',
@description = 'contains customer information';
-- Add a join filter between the two articles.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table1,
@filtername = @filter,
@join_articlename = @table2,
@join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]',
@join_unique_key = 1,
@filter_type = 1;
GO
REM Line breaks are added to improve readability.
REM In a batch file, commands must be made in a single line.
REM Run the Snapshot agent from the command line to generate the standard snapshot
REM schema and other files.
SET DistPub=%computername%
SET PubDB=AdventureWorks2008R2
SET PubName=AdvWorksSalesPersonMerge
"C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE" -Publication %PubName%
-Publisher %DistPub% -Distributor %DistPub% -PublisherDB %PubDB% -ReplicationType 2
-OutputVerboseLevel 1 -DistributorSecurityMode 1
PAUSE
REM Run the Snapshot agent from the command line, this time to generate
REM the bulk copy (.bcp) data for each Subscriber partition.
SET DistPub=%computername%
SET PubDB=AdventureWorks2008R2
SET PubName=AdvWorksSalesPersonMerge
SET SnapshotDir=\\%DistPub%\repldata\unc\fernando
MD %SnapshotDir%
"C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE" -Publication %PubName%
-Publisher %DistPub% -Distributor %DistPub% -PublisherDB %PubDB% -ReplicationType 2
-OutputVerboseLevel 1 -DistributorSecurityMode 1 -DynamicFilterHostName "adventure-works\Fernando"
-DynamicSnapshotLocation %SnapshotDir%
PAUSE
REM Run the Merge Agent for each subscription to apply the partitioned
REM snapshot for each Subscriber.
SET Publisher = %computername%
SET Subscriber = %computername%
SET PubDB = AdventureWorks2008R2
SET SubDB = AdventureWorks2008R2Replica
SET PubName = AdvWorksSalesPersonMerge
SET SnapshotDir=\\%DistPub%\repldata\unc\fernando
"C:\Program Files\Microsoft SQL Server\100\COM\REPLMERG.EXE" -Publisher %Publisher%
-Subscriber %Subscriber% -Distributor %Publisher% -PublisherDB %PubDB%
-SubscriberDB %SubDB% -Publication %PubName% -PublisherSecurityMode 1 -OutputVerboseLevel 3
-Output -SubscriberSecurityMode 1 -SubscriptionType 3 -DistributorSecurityMode 1
-Hostname "adventure-works\Fernando" -DynamicSnapshotLocation %SnapshotDir%
PAUSE
Consulte também