Notes on SQL

Having prepared the Distributor in the previous article it is now possible to create a Subscription.

First of all create a test database and table on the Publisher (Publisher-A):

CREATE DATABASE PublisherDB; 
GO 

USE PublisherDB; 
GO 

CREATE SCHEMA ReplTest; 
GO 

CREATE TABLE ReplTest.PublicationTable( 
	PK				BIGINT IDENTITY, 
	EmailAddress	VARCHAR(50), 
	DOB				DATE, 
	DateCreated		DATETIME DEFAULT GETDATE(), 
 CONSTRAINT PK_PublicationTable PRIMARY KEY CLUSTERED 
( 
	PK ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
)  

GO 
  

A database is also required on the Subscriber (Subscriber-C):

CREATE DATABASE SubscriberDB; 
GO 

USE SubscriberDB; 
GO 

CREATE SCHEMA Repl; 
GO 

CREATE TABLE Repl.SubscriptionTable( 
	PK				BIGINT, 
	EmailAddress	VarChar(50), 
	DOB				Date, 
	DateCreated		DateTime, 
	ReplDatecreated	DateTime DEFAULT GETDATE(), 
 CONSTRAINT PK_SubscriptionTable PRIMARY KEY CLUSTERED 
( 
	PK ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 

GO 

Now back to the Publisher. This server needs to be configured as a Publisher. The steps used to create the Distibution Server have already associated this server with the Distributor but now this server needs to be configured as a Publisher and also associated with the Distributor (Server B needs to know about Server A and then Server A needs to know about Server B as a seperate step):

use master  
exec sp_adddistributor @distributor = N'9999-PC\Distributor-B', @password = N'Spr0uts' 
GO 

The response should be ‘You have updated the Publisher property ‘active’ successfully.’

Having configured the server to allow Publications, the database itself needs to be configured for publishing:

exec sp_replicationdboption @dbname = N'PublisherDB', @optname = N'publish', @value = N'true' 
GO 

And Log Reader Agents need to be created:

exec PublisherDB.sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1 
GO 
exec PublisherDB.sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1 
GO 

The response from this will notify you of two Jobs, although the exact names may vary:
“Job ‘9999-PC\Publisher-A-PublisherDB-1’ started successfully.
Job ‘[9999-PC\Publisher-A].5’ started successfully.”

Now create the Publication:

use PublisherDB; 
exec sp_addpublication @publication = N'Test_Publication', @description = N'Transactional publication of database ''PublisherDB''.', @sync_method = N'native', @retention = 0, 
@allow_push = N'false', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', 
@snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', 
@allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', 
@independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', 
@allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', 
@enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' 
GO 

If you’ve used the Wizard to generate the scripts, the next command adds the publication snapshot. Where I work snapshots aren’t used because the databases are rather large and the lock such a command takes would make database access somewhat problematic for the hundreds of users that connect every minute.
So we’ll skip the ‘sp_addPublication_Snapshot’ command and move onto the next step – granting access to specified accounts.

We now need to create the various accounts and permissions that this Replication will require:

First, the Distributor needs access to this Publisher:

CREATE LOGIN [NT SERVICE\SQLAgent$DISTRIBUTOR-B] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] 
GO 

And to the DB:

USE PublisherDB; 
GO 

CREATE USER [NT SERVICE\SQLAgent$DISTRIBUTOR-B] FOR LOGIN [NT SERVICE\SQLAgent$DISTRIBUTOR-B] 
GO 

ALTER ROLE [db_owner] ADD MEMBER [NT SERVICE\SQLAgent$DISTRIBUTOR-B] 
GO

Various accounts involved with the Replication process also require access:

exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'sa' 
GO 
exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'NT AUTHORITY\SYSTEM' 
GO 
exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'NT Service\SQLAgent$PUBLISHER-A' 
GO 
exec sp_grant_publication_access @publication = N'Test_Publication', @login = N'NT Service\SQLAgent$DISTRIBUTOR-B' 
GO 

Now we tell the Replication process which table we want to Replicate. Within Replication the tables are referred to as ‘Articles’, borrowing the terms from the age-old process of newspapaer publishing. For this Article we’re only going to be interested in copying new rows across. For now we don’t care about updating or deleting data, so there’ll only be one Stored Procedure defined – against the parameter ‘@ins_cmd’. You specify the name and then the process of creating the Article takes care of generating the code:

exec sp_addarticle @publication = N'Test_Publication', @article = N'Test_Publication_Article', @source_owner = N'ReplTest', @source_object = N'PublicationTable', @type = N'logbased', 
@description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x0000000000030002, @identityrangemanagementoption = N'none', 
@destination_table = N'SubscriptionTable', @destination_owner = N'Repl', @status = 24, @vertical_partition = N'false', 
@ins_cmd = N'CALL [dbo].[sp_MSins_Repl_SubscriptionTable]', @del_cmd = N'NONE', @upd_cmd = N'NONE' 
GO 

At this point you can right-click on ‘Replication/Local Publications’ and see the Properties, confirmimg the table is set up for Replication:

Going back to the Distributor, the Distributor needs to have the login details for the Subscriber, as this is a Pull Subscription and it will be the responsibility of the Subscriber to transfer the data from the Distributor:

USE [master] 
GO 
CREATE LOGIN [NT SERVICE\SQLAgent$SUBSCRIBER-C] FROM WINDOWS WITH DEFAULT_DATABASE=[master] 
GO 
ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\SQLAgent$SUBSCRIBER-C] 
GO 
USE [distribution] 
GO 
CREATE USER [NT SERVICE\SQLAgent$SUBSCRIBER-C] FOR LOGIN [NT SERVICE\SQLAgent$SUBSCRIBER-C] 
GO 
USE [distribution] 
GO 
ALTER ROLE [db_owner] ADD MEMBER [NT SERVICE\SQLAgent$SUBSCRIBER-C] 
GO 

On the Subscriber the Pull Subscription now needs to be created:

use SubscriberDB 
GO 
exec sp_addpullsubscription @publisher = N'9999-PC\Publisher-A', @publication = N'Test_Publication', @publisher_db = N'PublisherDB', @independent_agent = N'True', 
@subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0 

As well as the Agent that will be responsible for it:

exec sp_addpullsubscription_agent @publisher = N'9999-PC\Publisher-A', @publisher_db = N'PublisherDB', @publication = N'Test_Publication', 
@distributor = N'9999-PC\Distributor-B', @distributor_security_mode = 1, @distributor_login = N'distributor_admin', @distributor_password = N'Spr0uts', 
@enabled_for_syncmgr = N'False', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 1, @active_start_time_of_day = 30, 
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @alt_snapshot_folder = N'', 
@working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0 
GO 

Now the custom Stored Procedure needs to be created within the Subscriber, to process the inserts. To do this, the Stored Procedure is generated from the Publisher with the command:

Use PublisherDB; 
GO 

EXEC sp_scriptpublicationcustomprocs 'Test_Publication' 

The respnse from this:

can then be copied and pasted into the Subscriber and executed against the Subscriber DB:

Finally, from the PublisherDB we need to active the Subscription:

exec sp_addsubscription @publication = N'Test_Publication', @subscriber = N'9999-PC\Subscriber-C', @destination_db = N'SubscriberDB', @subscription_type = N'Pull', @sync_type = N'None', 
@article = N'all', @update_mode = N'read only', @subscriber_type = 0 
GO 

A word of warning regarding the parameter ‘@sync_type’. Where I work, the use of ‘none’ is commonplace (because we don’t replicate schema changes automatically – we control it ourselves) and if you look at the documentation it is deprecated, so a better alternative is ‘replication support only’. However, when you generate the scripts from an existing Publication using the Wizard, you may find that the parameter has ‘automatic’ set against it – regardless of the value you initially created.
Replication has lots of lovely surprises like that.

At this point, from SSMS on the Subscriber you should be able to expand ‘Replication/Local Publications’ and see the name of the Publication and the Subscriber details:

A basic Pull Subscription has now been set up. In following articles I’ll cover how to test and monitor this, with basic steps on what to look for when it goes wrong.