Hi everyone first post here so please go easy, always used this site for it's great resources but now require some help!
For some reason we cannot initialize from LSN in a mirrored setup.
We have 3 servers and push replicate from A to B, SRVB and SRVC are setup with database mirroring.
SRVA – Publisher
SRVB – Principal
SRVC – Mirror
Basically We have followed the MS document (http://go.microsoft.com/fwlink/?LinkId=213051) that is hyperlinked from this webpage http://msdn.microsoft.com/en-GB/library/ms151799.aspx
In the document it contradicts itself saying that we shouldn’t allow the ‘Allow initialization from backup files’ option but then later in the document it says that we should allow this in order to initialize from LSN ??
So to test this replication was running, I manually failed over B to C.
On C I ran the code below to get the latest LSN,
SELECT transaction_timestamp, *
WHERE publisher = ‘xxxxxxxx’
AND publisher_db = 'xxxxxxx'
AND publication = 'xxxxxxxx';
Once I had the transaction_timestamp I went back over to the Publisher server (SRVA) and ran the below using the transaction_timestamp I got from the previous step
@publication = N'xxxxxxxxx',
@subscriber = N'xxxxxxxx',--This needs to be set to the new subscriber/what you have just failed over to.
@destination_db = N'xxxxxxxxxxx',
@subscription_type = N'push',
@sync_type = N'initialize from LSN',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@subscriptionlsn = 0x0001D3FC0000030B0001000000000000
@publication = N'xxxxxxxxxxxx',
@subscriber = N'xxxxxxxxxxx',--This needs to be set to the new subscriber/what you have just failed over to
@subscriber_db = N'xxxxxxxxx',
@subscriber_security_mode = 0,
@subscriber_login = N'xxxxxx',
@subscriber_password = N'xxxxxxxxxxxxxx',
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@dts_package_location = N'Distributor'
So when doing that we originally got the error about allowing to init from backup so we set that to true as the document was contradicting itself, when running the above it then produced this error.The transactions required for synchronizing the subscription with the specified log sequence number (LSN) are unavailable at the Distributor. Specify a higher LSN.
Does anyone know if we did something wrong here? The next step would have been to drop the previous subscription but we didn’t get that far, just a little worrying that we followed the MS whitepaper but it didn’t work.
Any help is appreciated, thanks.