“The Only Thing That Is Constant Is Change”

Configuring Log shipping for high availability of databases set for transactional replication.

Posted by database-wiki on April 11, 2016

Problem Description:

Steps to configure log shipping for the high availability of databases that are
configured for replication.
To identify the databases needed for log shipping when we have the publisher and
distributor on the same server. This should also give the steps to be taken care
when the publisher server goes down to make sure the subscribers connect to the standby server as publisher.


On Machine A – Publisher
1) Created database with one table
2) Created transactional publication with this table as article
3) Enabled the sp_replicatedboption ‘publsiherdb’,’sync with backup’,’true’
4) Enabled the sp_replicatedboption ‘distribution’, ‘sync with backup’,’true’
5) Enabling the “sync with backup” on the publisher database will ensure that the
log reader agent will not transfer the records to the distribution database until
the transaction log backup job of the publisher database is run.
6) Enabling the “sync with backup” on the distribution database will ensure that
the distribution agent will not transfer the records from the distribution database
to the subscriber database until the transaction log backup job of the distribution
database is run.
7) Scripted out the publication and the replication jobs from the Generate script
option of SQL EM that can be used later at the time of crash recovery.
8) Setup log shipping for the publisher database on Machine A to the Machine B
which is standby using the SQL EM maintenance plan option. You can specify 15
minutes as transaction log backup frequency and load and copy delay to 0 minutes.
Ensure a retry of 3 attempts are setup for all jobs.
9) Ensure copy job starts before the load which should be by default. You can setup
the monitoring server as the standby server itself. They have to follow all
recommendations for the log shipping like the transaction logs from publisher
should be placed on a UNC share that can be accessed by the standby etc. We can
point them to the BOL doc on this.

On Machine B – Standby
1) Configure the distributor which will create distribution database. It is better
we do this at this setup stage just to avoid the downtime of the replication setup
in case of the publisher server crash.

2) If we are planning to use named subscriptions, enable the subscriber server
through the Replication->Configuring publishing subscribers option->properties.
This will ensure that the subscriber server is not only registered at MachineA but
also at MachineB which will one day be the publisher.

On MachineC – Subscriber machine
1) Created pull subscription for the publication
2) Run the snapshot agent and distribution agent.
3) This will create the database on the subscriber machine and will put it in sync
with publisher
4) Script the subscription through the SQL EM “Generate script” option and also
script the distribution agent job.

From Machine-A
1) Insert few records and let the transaction log backup schedule start which will
backup the transaction log for publisher db.
2) At this time we can find that this record will also be carried across to the
standby server when the load and copy job schedule starts on the standby server.
3) Once this is done the log reader which is in continuous mode will transfer the
transactions to the distribution db.
4) Start the distribution agent and it will take these transactions to the
5) Confirm all these by verifying the record.

Publisher Server crash scenario
Take active transaction log back if available or the last transaction log backup
that is present at the publisher and apply it to the subscriber as follows.

restore log publisherdb with recovery, keep_replication

This will ensure that the replication settings of the publisher database are
retained with the standby database.
Now take the full back up of the distribution database from the publisher and
restore it onto the subscriber.

This distribution database should also be restored because this will have the
MSPublications table from which the Replication monitor will identify the list of
publications and other information. Also the transactions will be first transferred
to the distribution database before they propagate to the subscriber. If we do not
restore the distribution database we will miss these transactions.

Once this is done the publisher db will now be available on the standby server with
all the publications and replication information. At this time the standby will
only be missing the replication agent jobs.

We now need to run the script that we have collected at the publisher end for the
replication jobs and execute on the standby machine on msdb database. If we plan to
leave the same server name on the standby and point the users and applications to
this new server name then we have to rename the server part in the job scripts to
reflect the new server name and then execute them using edit – find -replace. We
could as well restore msdb but scripting the jobs would be easier.

Now the standby server will be a publisher server with replication and agents

On Machine C -Subscriber
If the standby server is already renamed to the old publisher server by taking away
the publisher server from network and changing the sql server name to reflect this
by doing sp_dropserver and sp_addserver ‘servername’, ‘(local)’ commands then it
will not have any problem connecting to the new publisher and everything should
work fine by now.

However if the standby server is not renamed and you want subscribers to connect
using this new standby server name then we have to do the additional step below on
the subscriber end.

Delete the distribution agent job on the subscriber.

Modify the script that we created for this job earlier and then change the
publisher server name and other details to point to the standby server name by
doing an edit find – replace.

Create a snapshot folder on the standby server with the same name that we used to
have on the old publisher server. Share it with required permissions. Because if at
all we happen to run the snapshot agent at a later point of time it will try to
look for this share which used to exist on old publisher server but not present
now. This step can as well be done in the setup of the standby server.

Once this is done the distribution agent can be run and it will synchronize.

We can temporarily cleanup the log shipping copy and load jobs on standby as it is
now publisher and no more log shipping is involved.

I tested all these steps and everything was working fine.

Additional recommendation
In the scenario that I tested I did not log ship the distribution database.

However we can recommend the customer to log ship the distribution database which
will reduce the time to get the distribution database ready. This way we need to
only apply the last transaction log back and the down time would be very less.

For log shipping the distribution database we need to change the recovery model of
the distribution database to FULL because by default distribution database will be
in SIMPLE recovery mode and we can’t log ship a database unless it is changed to
full or bulk logged.

I was able to get the setup working by log shipping the distribution database

Once we break the log shipping we need to run the clean up commands to clean up log
shipping jobs. You can script this from SQL EM.

On Primary
exec msdb.dbo.sp_delete_log_shipping_secondary @secondary_server_name = N’KANNANM’,
@secondary_database_name = N’test’
exec msdb.dbo.sp_delete_log_shipping_primary @primary_server_name =
N’KPVRAVI1′,@primary_database_name = N’test’, @delete_secondaries = 1
exec msdb.dbo.sp_delete_log_shipping_database N’test’
UPDATE msdb.dbo.sysdbmaintplans set log_shipping = 0 WHERE plan_name =
IF NOT EXISTS (SELECT * FROM msdb..log_shipping_databases) AND NOT EXISTS (SELECT *
FROM msdb..log_shipping_plans) exec msdb.dbo.sp_remove_log_shipping_monitor
IF NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries) AND NOT EXISTS
(SELECT * FROM msdb.dbo.log_shipping_secondaries) AND EXISTS (SELECT * FROM
msdb.dbo.sysusers WHERE NAME = N’log_shipping_monitor_probe’) AND EXISTS (SELECT *
FROM master.dbo.syslogins WHERE NAME = N’log_shipping_monitor_probe’)execute

On Secondary
select plan_id from msdb.dbo.log_shipping_plan_databases where destination_database
= N’test’
SELECT source_database FROM msdb.dbo.log_shipping_plan_databases where
destination_database = N’test’ AND plan_id =
exec msdb.dbo.sp_delete_log_shipping_plan_database @destination_database =
SET NOCOUNT ON DECLARE @sourcedb sysname DECLARE @destdb sysname DECLARE @planid
UNIQUEIDENTIFIER SELECT @sourcedb = N’test’, @destdb = N’test’, @planid =
msdb.dbo.log_shipping_plan_databases WHERE plan_id = @planid AND source_database =
@sourcedb) DELETE FROM msdb.dbo.log_shipping_plan_history WHERE plan_id = @planid
AND destination_database = @destdb ELSE DELETE FROM
msdb.dbo.log_shipping_plan_history WHERE plan_id = @planid AND source_database =
if NOT EXISTS(select * from msdb.dbo.log_shipping_plan_databases where plan_id =
N’5617B5AB-EE1F-49FF-850C-48B49A98BD5E’) exec msdb.dbo.sp_delete_log_shipping_plan
IF NOT EXISTS (SELECT * FROM msdb..log_shipping_databases) AND NOT EXISTS (SELECT *
FROM msdb..log_shipping_plans) exec msdb.dbo.sp_remove_log_shipping_monitor

Note: The entire process has to be thoroughly tested at your end before putting into production.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: