Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Sets the configuration and security information used by synchronization triggers of immediate updating subscriptions when connecting to the Publisher. This stored procedure is executed at the Subscriber on the subscription database.
When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).
Under certain conditions, this stored procedure can fail if the Subscriber is running Microsoft SQL Server 2005 Service Pack 1 or later, and the Publisher is running an earlier version. If the stored procedure fails in this scenario, upgrade the Publisher to SQL Server 2005 Service Pack 1 or later.
sp_link_publication [ @publisher = ] 'publisher' , [ @publisher_db = ] 'publisher_db' , [ @publication = ] 'publication' , [ @security_mode = ] security_mode [ , [ @login = ] 'login' ] [ , [ @password = ]'password' ] [ , [ @distributor = ] 'distributor' ]
sp_link_publication is used by immediate updating subscriptions in transactional replication.
sp_link_publication can be used for both push and pull subscriptions. It can be called before or after the subscription is created. An entry is inserted or updated in the MSsubscription_properties (Transact-SQL) system table.
For push subscriptions, the entry can be cleaned up by sp_subscription_cleanup (Transact-SQL). For pull subscriptions, the entry can be cleaned up by sp_droppullsubscription (Transact-SQL) or sp_subscription_cleanup (Transact-SQL). You can also call sp_link_publication with a NULL password to clear the entry in the MSsubscription_properties (Transact-SQL) system table for security concerns.
The default mode used by an immediate updating Subscriber when it connects to the Publisher does not allow a connection using Windows Authentication. To connect with a mode of Windows Authentication, a linked server has to be set up to the Publisher, and the immediate updating Subscriber should use this connection when updating the Subscriber. This requires the sp_link_publication to be run with security_mode = 2. When using Windows Authentication, security account delegation must be supported.
-- 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". -- Execute this batch at the Subscriber. DECLARE @publication AS sysname; DECLARE @publicationDB AS sysname; DECLARE @publisher AS sysname; DECLARE @login AS sysname; DECLARE @password AS nvarchar(512); SET @publication = N'AdvWorksProductTran'; SET @publicationDB = N'AdventureWorks2012'; SET @publisher = $(PubServer); SET @login = $(Login); SET @password = $(Password); -- At the subscription database, create a pull subscription to a transactional -- publication using immediate updating with queued updating as a failover. EXEC sp_addpullsubscription @publisher = @publisher, @publication = @publication, @publisher_db = @publicationDB, @update_mode = N'failover', @subscription_type = N'pull'; -- Add an agent job to synchronize the pull subscription, -- which uses Windows Authentication when connecting to the Distributor. EXEC sp_addpullsubscription_agent @publisher = @publisher, @publisher_db = @publicationDB, @publication = @publication, @job_login = @login, @job_password = @password; -- Add a Windows Authentication-based linked server that enables the -- Subscriber-side triggers to make updates at the Publisher. EXEC sp_link_publication @publisher = @publisher, @publication = @publication, @publisher_db = @publicationDB, @security_mode = 0, @login = @login, @password = @password; GO USE AdventureWorks2012 GO -- Execute this batch at the Publisher. DECLARE @publication AS sysname; DECLARE @subscriptionDB AS sysname; DECLARE @subscriber AS sysname; SET @publication = N'AdvWorksProductTran'; SET @subscriptionDB = N'AdventureWorks2012Replica'; SET @subscriber = $(SubServer); -- At the Publisher, register the subscription, using the defaults. USE [AdventureWorks2012] EXEC sp_addsubscription @publication = @publication, @subscriber = @subscriber, @destination_db = @subscriptionDB, @subscription_type = N'pull', @update_mode = N'failover'; GO