Re-initialization with Log Sequence Numbering (LSN) for SQL Server Failover and Failback.


Log Squence Numbering (LSN) : Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

The following are the pre-requisites for performing Re-intialization with LSN

1. Distributor Server: It is the server that contains the distribution database and stores meta data, history data and transactions
2. Publisher Server: It is the server that makes data available for replication for subscribers.
3. Subscriber: It is the server that receives replicated data. we will have two subscribers in Failover. (i.e. Old subscriber and New Subscriber)
a. Old Subscriber: Server which is in failed state/Disaster state
b. New Subscriber: Server to which failover will be performed using LSN.
4. Publisher DB: It is the source of data to be replicated on the publisher server.
5. Subscriber DB: It is the destination database residing on subscriber server and receives replicated data.
6. Publication: Its refers to publication name property of publisher.
 
Steps for Performing Re-initialization with LSN: 
1. Finding the LSN number from the publisher. 
select @Query = '
SELECT * FROM '+@subscriber_db+'.dbo.MSreplication_subscriptions
WHERE publisher = '''+@publisher+
''' AND publisher_db = '''+@publisher_db+
''' AND publication = '''+@publication+''';'

2. Validate the LSN with distributor. 
SELECT @Query = '
IF EXISTS(SELECT *
FROM MSpublications p
JOIN master..sysservers srv
ON srv.srvid = p.publisher_id
JOIN MSpublisher_databases d
ON d.publisher_id = p.publisher_id
JOIN MSrepl_transactions trans
ON trans.publisher_database_id = d.id

WHERE p.publication = '''+ $(publication) +'''
AND p.publisher_db = '''+ $(publisher_db) +'''
AND srv.srvname = '''+ $(publisher) +'''
AND xact_seqno = '+ $(LSN) +'
AND '+ $(LSN) +' > p.min_autonosync_lsn)
BEGIN
PRINT ''The LSN: '+ $(LSN) +' is correct''
END
ELSE
BEGIN
PRINT ''The LSN: '+ $(LSN) +' is wrong''
END

'
3. Add subscription for the new subscriber on the publisher.
select @Query = '
EXEC sp_addsubscription
@publication = N'''+$(publication)+''',
@subscriber = N'''+$(subscriber)+''',
@destination_db = N'''+$(subscriber_db)+''',
@subscription_type = N'''+$(subscription_type)+''',
@sync_type = N''initialize from LSN'',
@article = N''all'',
@update_mode = N''read only'',
@subscriber_type = 0,
@subscriptionlsn = '+$(LSN)+';
'
4. Adding subscription agent at the new subscriber
a. For Pull based replication
select @Query = '
EXEC sp_addpullsubscription
@publisher = N''' + $(publisher) + ''',
@publication = N''' + $(publication) + ''',
@publisher_db = N''' + $(publisher_db) + ''',
@independent_agent = N''True'',
@subscription_type = N''pull'',
@description = N'' '',
@update_mode = N''read only'',
@immediate_sync = 0;

EXEC sp_addpullsubscription_agent
@publisher = N''' + $(publisher) + ''',
@publisher_db = N''' + $(publisher_db) + ''',
@publication = N''' + $(publication) + ''',
@distributor = N''' + $(distributor) + ''',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 15,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_end_date = 99991231
'
b. For Push based replication
select @Query = '
EXEC sp_addpushsubscription_agent
@publication = N''' + $(publication) + ''',
@subscriber = N''' + $(subscriber) + ''',
@subscriber_db = N''' + $(subscriber_db) + ''',
@job_login = NULL,
@job_password = NULL,
@subscriber_security_mode = 1,
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 15,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_end_date = 99991231,
@dts_package_location = N''Distributor'';
'
5. Drop old subscriptions
select @Query = '
EXEC sp_dropsubscription
@publication = N''' + $(publication)+''',
@subscriber = N''' + $(subscriber)+''',
@destination_db = N''' + $(subscriber_db)+''',
@article = N''all'';
'




Comments (0)

Skip to main content