February 2, 2012 at 2:20 pm
Hello experts!
Here is the setup - I have 3 servers - main db server as publisher of 10 databases, stand alone distributor and reporting server as subscriber to all 10 databases. All servers are 64-bit Windows 2008 with SQL Server 2008 Standard. Some of the replicated databases are large and if subscription needed to be re-initialized it would take up to 24 hours from start to finish. We have a DR site in a different location and use CA XOSoft block synchronization replication to keep main DB server and DR server synchronized. When we need to fail over to DR site we use DNS redirection to point all applications to DR server without physically changing it's name.
I need to add my reporting server to this setup and minimize downtime as much as possible. At this moment I am at a total loss - as far as I know I cannot keep replication going from DR site because all servers will have different physical names. I cannot run synchronization from DR publisher to DR subscriber because it will take 24 hours to get all data up to date. What are my options and am I wrong about replication?
I will take any suggestion or brain storm ideas.
Thanks a lot!
February 2, 2012 at 2:40 pm
Typically most SAN replication includes the option of KEEP_REPLICATION in their database recovery models. With that being said the best way that I can think of that you can keep things moving is to rename the DR server once you bring things online there.
One other method would be to run scripts for the publications and subscriptions on the DR server once that is online. You would just have to be sure to NOT initialize the subscription when you create them. Additionally prior to running the creation scripts you would have to run clean-up, or drop scripts on the subscriber to remove the old replication objects, metadata, etc. Ultimately this is a pretty messy and potentially painful solution. If your replication environment is small then it is probably manageable though.
Let me know if that doesn't make sense.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 2, 2012 at 7:16 pm
David Benoit (2/2/2012)
Typically most SAN replication includes the option of KEEP_REPLICATION in their database recovery models. With that being said the best way that I can think of that you can keep things moving is to rename the DR server once you bring things online there.One other method would be to run scripts for the publications and subscriptions on the DR server once that is online. You would just have to be sure to NOT initialize the subscription when you create them. Additionally prior to running the creation scripts you would have to run clean-up, or drop scripts on the subscriber to remove the old replication objects, metadata, etc. Ultimately this is a pretty messy and potentially painful solution. If your replication environment is small then it is probably manageable though.
Let me know if that doesn't make sense.
David,
We don't use SAN replication, CA XOSoft is kind of like SQL Server replication but on a block level. So, that is out.
I am not sure what you meant by script but not initialize. If I script publisher and subscriber, I will have a script to drop/create replication. If I drop it, I am back to re-initializing the whole thing. If I didn't drop it, then what? I am confused, sorry........ Renaming the servers is an option in a true DR case (if our main site is 100% out), but we do tests and drills, so the main site is still there and we run syncronization back from DR in order to not loose data.
I am sorry if I confused you.
Thanks for the reply, maybe something else would work?
February 2, 2012 at 10:11 pm
Ok, I understand the DR replication solution you are using.
For the replication suggestion, you would create scripts for your publication and subscriptions. However you would change the @sync_type property in the sp_addsubscription section to "replication support only" which will push all the replication components needed but will NOT initialize the data. So, the data at the subscribers would stay the same. Any new inserts, updates, deletes would be replicated. There are some caveats to this in that any transactions that hadn't made it all the way to the subscriber at the point of failover to the DR site would be missing. So, some manual sync will most likely be necessary if you have a very active environment.
Again, you will have to do some work to get scripts together to clean up the subscriber as well prior to recreating the subscriptions. If you do your research, test your scripts - or at least the method - , you should be ok.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply