Skip to content

Latest commit

 

History

History
197 lines (124 loc) · 20.9 KB

upgrade-replication-scripts-replication-transact-sql-programming.md

File metadata and controls

197 lines (124 loc) · 20.9 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords dev_langs monikerRange
Upgrade replication scripts (Replication SP)
Learn how to use Replication Stored Procedures to upgrade the scripts used to programmatically configure a replication topology.
MashaMSFT
mathoma
03/14/2017
sql
replication
how-to
updatefrequency5
scripts [SQL Server replication], upgrading
upgrading SQL Server, replicated databases
upgrading replication applications
replication [SQL Server], scripting
replication [SQL Server], upgrading
upgrading replicated databases
TSQL
=azuresqldb-mi-current||>=sql-server-2016

Upgrade Replication Scripts (Replication Transact-SQL Programming)

[!INCLUDEsql-asdbmi] [!INCLUDEtsql] script files can be used to programmatically configure a replication topology. For more information, see Replication System Stored Procedures Concepts.

Important

Although you are not required to upgrade scripts that are executed by members of the sysadmin role, we recommend that you modify existing scripts as described in this topic. Specify an account that has minimum permissions for each replication agent as described in the "Permissions Required By Agents" section of the topic Replication Agent Security Model.

These security improvements, which enable more control over permissions by allowing you to explicitly specify the [!INCLUDEmsCoName] Windows accounts under which replication agent jobs are executed, affect the following stored procedures in existing scripts:

  • sp_addpublication_snapshot:

    You should now supply the Windows credentials as @job_login and @job_password when executing sp_addpublication_snapshot (Transact-SQL) to create the job under which the Snapshot Agent runs at the Distributor.

  • sp_addpushsubscription_agent:

    You should now execute sp_addpushsubscription_agent (Transact-SQL) to explicitly add a job and supply the Windows credentials (@job_login and @job_password) under which the Distribution Agent job runs at the Distributor. In versions of [!INCLUDEssNoVersion] before [!INCLUDEssVersion2005], this was done automatically when a push subscription was created.

  • sp_addmergepushsubscription_agent:

    You should now execute sp_addmergepushsubscription_agent (Transact-SQL) to explicitly add a job and supply the Windows credentials (@job_login and @job_password) under which the Merge Agent job runs at the Distributor. In versions of [!INCLUDEssNoVersion] before [!INCLUDEssVersion2005], this was done automatically when a push subscription was created.

  • sp_addpullsubscription_agent:

    You should now supply the Windows credentials as @job_login and @job_password when executing sp_addpullsubscription_agent (Transact-SQL) to create the job under which the Distribution Agent runs at the Subscriber.

  • sp_addmergepullsubscription_agent:

    You should now supply the Windows credentials as @job_login and @job_password when executing sp_addmergepullsubscription_agent (Transact-SQL) to create the job under which the Merge Agent runs at the Subscriber.

  • sp_addlogreader_agent:

    You should now execute sp_addlogreader_agent (Transact-SQL) to manually add the job and supply the Windows credentials under which the Log Reader Agent runs at the Distributor. In versions of [!INCLUDEssNoVersion] before [!INCLUDEssVersion2005], this was done automatically when a transactional publication was created.

  • sp_addqreader_agent:

    You should now execute sp_addqreader_agent (Transact-SQL) to manually add the job and supply the Windows credentials under which the Queue Reader Agent runs at the Distributor. In versions of [!INCLUDEssNoVersion] before [!INCLUDEssVersion2005], this was done automatically when a transactional publication that supported queued updating was created.

In the security model introduced in [!INCLUDEssVersion2005], replication agents always make connections to the local instance of [!INCLUDEssNoVersion] with Windows Authentication using the credentials supplied in @job_name and @job_password. For information about the requirements of Windows accounts used when running replication agent jobs, see Replication Agent Security Model.

Important

When possible, prompt users to enter security credentials at runtime. If you store credentials in a script file, ensure that the file itself is secured.

To upgrade scripts that configure a snapshot or transactional publication

  1. In the existing script, before sp_addpublication (Transact-SQL), execute sp_addlogreader_agent (Transact-SQL) at the Publisher on the publication database. Specify the Windows credentials under which the Log Reader Agent runs for @job_name and @job_password. If the agent will use [!INCLUDEssNoVersion] Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the [!INCLUDEssNoVersion] login information for @publisher_login and @publisher_password. This creates a Log Reader Agent job for the publication database.

    [!NOTE]
    This step is only for transactional publications and is not required for snapshot publications.

  2. (Optional) Before sp_addpublication (Transact-SQL), execute sp_addqreader_agent (Transact-SQL) at the Distributor on the distribution database. Specify the Windows credentials under which the Queue Reader Agent runs for @job_name and @job_password. This creates a Queue Reader Agent job for the Distributor.

    [!NOTE]
    This step is only required for transactional publications that support queued updating subscribers.

  3. (Optional) Update the execution of sp_addpublication (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

  4. After sp_addpublication (Transact-SQL), execute sp_addpublication_snapshot (Transact-SQL) at the Publisher on the publication database. Specify @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @job_password. If the agent will use [!INCLUDEssNoVersion] Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the [!INCLUDEssNoVersion] login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

  5. (Optional) Update the execution of sp_addarticle (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

To upgrade scripts that add subscriptions to a snapshot or transactional publication

  1. After executing the stored procedure that creates the subscription, ensure that you execute the stored procedure that creates a Distribution Agent job to synchronize the subscription. The stored procedure that you use will depend on the type of subscription.

To upgrade scripts that configure a merge publication

  1. (Optional) In the existing script, update the execution of sp_addmergepublication (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

  2. After sp_addmergepublication (Transact-SQL), execute sp_addpublication_snapshot (Transact-SQL) at the Publisher on the publication database. Specify @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @job_password. If the agent will use [!INCLUDEssNoVersion] Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_modeand the [!INCLUDEssNoVersion] login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

  3. (Optional) Update the execution of sp_addmergearticle (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

To upgrade scripts that add subscriptions to a merge publication

  1. After executing the stored procedure that creates the subscription, ensure that you execute the stored procedure that creates a Merge Agent job to synchronize the subscription. The stored procedure that you use will depend on the type of subscription.

Examples

A. SQL Server 2000 script to create a transactional publication

The following is an example of a [!INCLUDEssVersion2000] script that creates a transactional publication for the Product table. This publication supports immediate updating with queued updating as failover. Default parameters have been removed for readability.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_1.sql":::

B. SQL Server 2005 and later script to create a transactional publication

The following is an example of upgrading the previous script, which creates a transactional publication, to run successfully for [!INCLUDEssVersion2005] and later versions. This publication supports immediate updating with queued updating as failover. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_2.sql":::

C. SQL Server 2000 script to create a merge publication

The following is an example of a [!INCLUDEssVersion2000] script that creates a merge publication for the Customers table. Default parameters have been removed for readability.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_3.sql":::

D. SQL Server 2005 and later script to create a merge publication

The following is an example of the previous script, which creates a merge publication, upgraded to run successfully for [!INCLUDEssVersion2005] and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_4.sql":::

E. SQL Server 2000 script to create a push subscription to a transactional publication

The following is an example of a [!INCLUDEssVersion2000] script that creates a push subscription to a transactional publication. Default parameters have been removed for readability.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_5.sql":::

F. SQL Server 2005 and later script to create a push subscription to a transactional publication

The following is an example of the previous script, which creates a push subscription to a transactional publication, upgraded to run successfully for [!INCLUDEssVersion2005] and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_6.sql":::

G. SQL Server 2000 script to create a push subscription to a merge publication

The following is an example of a [!INCLUDEssVersion2000] script that creates a push subscription to a merge publication. Default parameters have been removed for readability.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_7.sql":::

H. SQL Server 2005 and later script to create a push subscription to a merge publication

The following is an example of the previous script, which creates a push subscription to a merge publication, upgraded to run successfully for [!INCLUDEssVersion2005] and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_8.sql":::

I. SQL Server 2000 script to create a pull subscription to a transactional publication

The following is an example of a [!INCLUDEssVersion2000] script that creates a pull subscription to a transactional publication. Default parameters have been removed for readability.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_7.sql":::

J. SQL Server 2005 and later script to create a pull subscription to a transactional publication

The following is an example of the previous script, which creates a pull subscription to a transactional publication, upgraded to run successfully for [!INCLUDEssVersion2005] and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_9.sql":::

K. SQL Server 2000 script to create a pull subscription to a merge publication

The following is an example of a [!INCLUDEssVersion2000] script that creates a pull subscription to a merge publication. Default parameters have been removed for readability.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_10.sql":::

L. SQL Server 2005 and later script to create a pull subscription to a merge publication

The following is an example of the previous script, which creates a pull subscription to a merge publication, upgraded to run successfully for [!INCLUDEssVersion2005] and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

:::code language="sql" source="../codesnippet/tsql/upgrade-replication-scri_11.sql":::

See Also

Create a Publication
Create a Push Subscription
Create a Pull Subscription
View and Modify Replication Security Settings
MSSQL_ENG021797
MSSQL_ENG021798
Replication System Stored Procedures Concepts
Upgrade Replicated Databases