June 14, 2013 at 4:58 am
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, *
FROM dbo.MSreplication_subscriptions
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
EXEC sp_addsubscription
@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
exec sp_addpushsubscription_agent
@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'
GO
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.
July 3, 2013 at 5:30 am
Hi,
same problem here but no solution too 🙁
At least I can commit that there might be something wrong with Microsoft's whitepapers.
I have an Always On Group on SQL 2012, getting it's data via replication from a SQL 2008 R2.
In the case of a failover the whitepaper tells us to create a subscription on the new primary node and use the lsn taken from MSreplication_subscriptions for initializing. I'm getting the same error.
July 3, 2013 at 7:00 am
Agreed!
We were in that position before, we removed AG to cut on costs and then went to a mirrored solution on standard edition.
No one seems to want to touch this with a barge pole and some mods over at msdn said they'd get back to me but haven't 🙁
July 3, 2013 at 7:30 am
I think I have bad news :ermm: I found this note in books online (/http://technet.microsoft.com/en-us/library/ms151705.aspx):
When restoring a backup, you must ensure that the backup came from the Publisher if you want the Subscriber to automatically synchronize. The log sequence number (LSN) values in the backup (which are used to set the point at which to start synchronizing) are specific to the Publisher.
It's for SQL 2012 but I'm sure it's the same for 2008(R2)
I'm not sure but I think it means:
You HAVE to create the subscription database using a backup so that the LSNs between publisher and subscriber can be matched.
In my case, I created a new database on the subscriber and pushed a new snapshot.
When now using the LSN to initialize the new subscription it won't be found on the publisher. Never. This means that the way of initializing using a LSN simply does not and will not work for my subscribers 🙁
Are there other opinions about this note? Or is this note independent of using LSN and only valid for really initializing using backups?
July 4, 2013 at 1:12 am
I did some testing and figured it out.
It does not matter if you initialized the subscribers using restore a database, snapshot or whatever.
As we know: initializing the subscription using initialize from LSN needs the publication property allow_initialize_from_backup set to true.
BUT: :crying:
You have to set the allow_initialize_from_backup-option before creating the subscription(s)!
If the option is set to false when creating the subscriptions, you can change the option afterwards but initializing using LSN just won't work.
Now I have 2 questions:
1) Books online says when setting allow_initialize_from_backup to true, you should set immediate_sync to true too. Why? I neet the immediate sync to false when adding new articles! What are the consequences? :unsure:
2) Does this really mean that I have to reinitialize my subscriptions just to be ready for a failover? :w00t: :exclamation:
hmmmm......
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply