Scripting Replication

Question

I'd like to know how to automate the creation of transactional replication.

in a test environment, I often need to restore Prod data. The test data is replicated to another server for reporting (and testing of said reports). All of this is pretty simple to script - but I need to re-enable the replication.

I know I can script out the existing replication and then reapply it after the restore, but this is problematic if there are additional (or missing) objects in the restored database.

Basically - I want to create a process that will create the basic plumbing for replication, then go through every table in the database, add it as an article, then do the same for procs, views, etc.

with a bit of effort (and time), I assume I could write something that would do the job, I just dont want to re-invent the wheel...

thx.

Answer 1

Automating the creation of transactional replication in SQL Server involves several steps. Below is a process you can follow, which includes scripting the replication setup and ensuring all database objects are correctly added as articles in the publication.

Step-by-Step Process

1. Restore the Production Data: Ensure your test environment has restored the latest production data.

2. Recreate Replication: Recreate the replication setup by defining the distributor, creating the publication, and adding the articles. This process can be automated using SQL Server Management Studio (SSMS) or SQL scripts.

Here are example scripts that can help you automate this process.

1. Configuring the distributor - the script below configures the server as a distributor. (in many cases, the Publisher(production) and Distributor are the same server):

-- Set the distributor
EXEC sp_adddistributor @distributor = 'DistributorServerName', @password = 'DistributorPassword';
-- Configure the distribution database
EXEC sp_adddistributiondb @database = 'distribution', @security_mode = 1;
-- Configure the publisher
EXEC sp_adddistpublisher @publisher = 'PublisherServerName',
 @distribution_db = 'distribution',
 @working_directory = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData',
 @security_mode = 1;

2. Create the publication - the script below creates a publication:

-- Create the publication
EXEC sp_addpublication @publication = 'TestPublication',
 @status = N'active',
 @allow_push = N'true',
 @allow_pull = N'true',
 @allow_anonymous = N'true',
 @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';
-- Add articles to the publication (example for tables)
DECLARE @article NVARCHAR(255);
DECLARE articles_cursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
OPEN articles_cursor;
FETCH NEXT FROM articles_cursor INTO @article;
WHILE @@FETCH_STATUS = 0
BEGIN
 EXEC sp_addarticle @publication = 'TestPublication',
 @article = @article,
 @source_owner = 'dbo',
 @source_object = @article,
 @type = N'logbased',
 @schema_option = 0x000000000803509F,
 @identityrangemanagementoption = N'manual';
 FETCH NEXT FROM articles_cursor INTO @article;
END;
CLOSE articles_cursor;
DEALLOCATE articles_cursor;

3. Add stored procedures, views, and other objects - the script below adds these objects as articles:

-- Add stored procedures as articles
DECLARE @proc NVARCHAR(255);
DECLARE procs_cursor CURSOR FOR
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';
OPEN procs_cursor;
FETCH NEXT FROM procs_cursor INTO @proc;
WHILE @@FETCH_STATUS = 0
BEGIN
 EXEC sp_addarticle @publication = 'TestPublication',
 @article = @proc,
 @source_owner = 'dbo',
 @source_object = @proc,
 @type = N'logbased',
 @schema_option = 0x000000000803509F,
 @identityrangemanagementoption = N'manual';
 FETCH NEXT FROM procs_cursor INTO @proc;
END;
CLOSE procs_cursor;
DEALLOCATE procs_cursor;
-- Add views as articles
DECLARE @view NVARCHAR(255);
DECLARE views_cursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS;
OPEN views_cursor;
FETCH NEXT FROM views_cursor INTO @view;
WHILE @@FETCH_STATUS = 0
BEGIN
 EXEC sp_addarticle @publication = 'TestPublication',
 @article = @view,
 @source_owner = 'dbo',
 @source_object = @view,
 @type = N'logbased',
 @schema_option = 0x000000000803509F,
 @identityrangemanagementoption = N'manual';
 FETCH NEXT FROM views_cursor INTO @view;
END;
CLOSE views_cursor;
DEALLOCATE views_cursor;

4. Create the subscription - Finally, the below script creates the subscription. The subscriber is the test environment.

-- Add the subscription
EXEC sp_addsubscription @publication = 'TestPublication',
 @subscriber = 'SubscriberServerName',
 @destination_db = 'SubscriberDBName',
 @subscription_type = N'Push',
 @sync_type = N'automatic',
 @article = N'all',
 @update_mode = N'read only',
 @subscriber_type = 0;
-- Add the push subscription job
EXEC sp_addpushsubscription_agent @publication = 'TestPublication',
 @subscriber = 'SubscriberServerName',
 @subscriber_db = 'SubscriberDBName',
 @job_login = null,
 @job_password = null,
 @subscriber_security_mode = 1,
 @frequency_type = 64,
 @frequency_interval = 1,
 @frequency_relative_interval = 0,
 @frequency_recurrence_factor = 0,
 @frequency_subday = 8,
 @frequency_subday_interval = 1,
 @active_start_time_of_day = 0,
 @active_end_time_of_day = 235959,
 @active_start_date = 20220101,
 @active_end_date = 99991231,
 @enabled_for_syncmgr = N'False';

You can automate these scripts using SQL Server Agent Jobs. Create jobs that run these scripts in sequence. Ensure error handling and logging are in place to monitor the process.

However, it would be best if you had these considerations:

1. Security: Ensure your scripts handle security appropriately, especially in the production environment.

2. Schema Changes: If schema changes are frequent, ensure that your script dynamically handles these changes.

3. Testing: Thoroughly test your automation in a non-production environment before deploying it.