December 12, 2014 at 9:49 am
I am planning to setup replication to a principle mirrored server with high safety automatic failover. When automatic failover of the mirrored server happens, does the replication will work? Do we have to make any changes?
December 15, 2014 at 1:17 am
Hello,
If i understand correctly, you are planning on a mirrored subscriber. if that is the case, and there is a failover of the subscription database, then you would have to remove the old subsciption to the original principal and create a new subscription to the new principal db using the "initialize from lsn" option, so that you do not have to reinitialize the whole subscription and only get the changes after it has failed over.
we can get the lsn which was last replicated successfully from MSreplication_subscriptions, transaction_timestamp column
so when creating the new subscription to the new principal use the above value to fill @subscriptionlsn
exec sp_addsubscription @publication = N'test', @subscriber = N'testa', @destination_db = N'test',
@subscription_type = N'Push', @sync_type = N'initialize from lsn', @article = N'all',
@update_mode = N'read only', @subscriber_type = 0,@subscriptionlsn=0x0000001A000000890003000000000000
other things to consider,
1) You need to have a publication that can be initialized from backups
sp_changepublication @publication ='test',@property ='allow_initialize_from_backup',@value ='true'
2) you need to set the minimum retention period in distribution properties. This will prevent the deletion of transactions that are delivered to the principal but that didnt make to the mirror. this is only required when you require to do a force failover and there are transaction in the principal that did not get replicated to mirror
sp_changedistributiondb @database ='distribution',@property ='min_distretention',@value=2
December 15, 2014 at 10:28 am
jesijesijesi (12/15/2014)
Hello,If i understand correctly, you are planning on a mirrored subscriber. if that is the case, and there is a failover of the subscription database, then you would have to remove the old subsciption to the original principal and create a new subscription to the new principal db using the "initialize from lsn" option, so that you do not have to reinitialize the whole subscription and only get the changes after it has failed over.
we can get the lsn which was last replicated successfully from MSreplication_subscriptions, transaction_timestamp column
so when creating the new subscription to the new principal use the above value to fill @subscriptionlsn
exec sp_addsubscription @publication = N'test', @subscriber = N'testa', @destination_db = N'test',
@subscription_type = N'Push', @sync_type = N'initialize from lsn', @article = N'all',
@update_mode = N'read only', @subscriber_type = 0,@subscriptionlsn=0x0000001A000000890003000000000000
Thanks a lot !!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply