June 5, 2010 at 2:27 pm
Are there any fundamental issues or things I should know in advance when using transactional replication w/ updateable subscriptions and mirroring at the same time? I heard that there can be issues when you have to failover to the mirror and then when you fail back to the original primary that something needs to be done with replication. is that true, has anyone tried it? I have three dev servers that are currently setup as such to begin testing our application:
Server 1 is mirrored to Server 2 (Local Mirror)
Server 1 is replicating to Server 3 (Remote Subscriber) - Setup as a DR server mostly
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 22, 2010 at 12:56 pm
SQLJocky (6/5/2010)
Are there any fundamental issues or things I should know in advance when using transactional replication w/ updateable subscriptions and mirroring at the same time? I heard that there can be issues when you have to failover to the mirror and then when you fail back to the original primary that something needs to be done with replication. is that true, has anyone tried it? I have three dev servers that are currently setup as such to begin testing our application:Server 1 is mirrored to Server 2 (Local Mirror)
Server 1 is replicating to Server 3 (Remote Subscriber) - Setup as a DR server mostly
Okay, so after doing some testing I have come to the following conclusions:
If setup properly there are no issues to running trans replication w/ updatable subscriptions and mirroring at the same time. What happens is that when the principal has to failover to the mirror, the mirror then becomes the new principal and takes over publishing articles to the remote distributor. Once the original principal comes back online and the mirror catches up, you can fail back and replication takes over automatically. NOW, this assumes that it was a manual (purposeful) failover done without using a forced failover method...such as when you need to do updates to your principal server and want to stay online.
IF on the other hand you have to force failover because the principal is dead suddenly (or you are working with automatic failover) then you have to be aware that there can be data loss if you break mirroring after forcing the failover. And replication will not publish changes until you 1) restore the original principal so mirroring can catch up, or 2) kill mirroring. Once the principal is back online and mirroring has synced (assuming you did not kill mirroring) then replication will also catch up automatically. You can then fail back to the original principal and all should be smooth sailing.
Here's the catch! You have to setup replication in a very specific manner utilizing failover options in the setup script. For my scripts on how to accomplish this, please see http://www.sqlservercentral.com/scripts/Replication/70508/
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 22, 2010 at 3:39 pm
Did you test updating the "subscriber" when mirror server took over?
I am not 100% but I think it will fail!
* Noel
June 22, 2010 at 3:53 pm
Sure did. It works fine as long as it was a controlled failover. If it was an automatic failover or forced manual failover, it holds the transactions until the original principal is back online. Again, assuming that you don't have to kill mirroring or replication because of a permanent failure.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 23, 2010 at 9:26 am
SQLJocky (6/22/2010)
Sure did. It works fine as long as it was a controlled failover. If it was an automatic failover or forced manual failover, it holds the transactions until the original principal is back online. Again, assuming that you don't have to kill mirroring or replication because of a permanent failure.
Can you define "... controlled failover ..." ?
* Noel
June 23, 2010 at 9:40 am
Controlled Failover: Failover done manually through use of the 'Failover' button in Mirroring Monitor (or equivalent T-SQL), typically used to failover in the event that you want to do system upgrades/updates on the principal while still providing database access to end users.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 25, 2010 at 11:49 am
SQLJocky (6/23/2010)
Controlled Failover: Failover done manually through use of the 'Failover' button in Mirroring Monitor (or equivalent T-SQL), typically used to failover in the event that you want to do system upgrades/updates on the principal while still providing database access to end users.
You are using "QUEUED" updating subscribers not "IMMEDIATE" updating subscribers, right?
If is queued I can see that it can work if its is immediate it has to fail!
* Noel
June 25, 2010 at 11:55 am
Correct. Queued.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 25, 2010 at 12:28 pm
SQLJocky (6/25/2010)
Correct. Queued.
Now it makes sense and in fact BOL is a bit non-clear(cryptic) when telling you that your queued transactions will only reach the primary after you fail back.
🙁
* Noel
June 25, 2010 at 12:33 pm
Thank you for making that distinction for others that may read this. I hadn't thought to include that.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 25, 2010 at 12:44 pm
SQLJocky (6/25/2010)
Thank you for making that distinction for others that may read this. I hadn't thought to include that.
Actually thank *you* for bringing up this issue which is not very well known!
😎
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply