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'';
 '