SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Mirroring and Initialize from LSN doesn't work


DB Mirroring and Initialize from LSN doesn't work

Author
Message
Matt_Mara
Matt_Mara
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 51
Hi everyone first post here so please go easy, always used this site for it's great resources but now require some help! Smile

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.
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 792
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.
Matt_Mara
Matt_Mara
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 51
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 Sad
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 792
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 Sad

Are there other opinions about this note? Or is this note independent of using LSN and only valid for really initializing using backups?
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 792
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......
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search