Help needed on Log Shipping scenario
-
I am getting ready to embark on a huge project that is basically moving all servers for an application to new hardware. This includes 12 sites, and each site has 3 database servers. I'm sure I will be asking for expert advice on the migration, but for now my question is related to log shipping. Part of the project includes adding a server as a hot standby in case one of the database servers die. They want to use log shipping to keep the hot standby in synch with the production OLTP databases, but I'm confused on how one server is going to act as a standby for 3 servers. Is it possible/feasible to do this? Would I need to create three instances on the hot standby, one for each of the three database servers? To make matters even more complex 2 of the 3 database servers use transactional replication both as subscriber and publisher. I know in log shipping with replicated databases, when changing the secondary to the primary server includes renaming the server, and this obviously will not work. I feel like I'm way over my head on this, and could really use some advice and a sanity check to see if this is even possible.
TIA,
Michelle
-
This was removed by the editor as SPAM
-
Hi Michelle,
Well - you do sound like you're going to be in for an interesting ride!
Can you log-ship from 3 separate servers to one standby server? Well, yes you can, so long as the databases all have different names and you have enough disk space. It could make the configuration setup a little confusing though, as the directory structures won't be exactly the same on the primaries as on the standby.
I think you have a much bigger problem knowing that 2 of your servers are themselves involved in 2-way replication. This wouldn't prevent you log-shipping to your warm-standby, but if you ever had to fail-over to your warm-standby server then re-synching the replication gets interesting! You don't make it clear whether you mean that a single database can act as both a publisher and a subscriber, or whether a single database is only ever one or the other. If each database is only involved in 1-way replication, then replication re-synching isn't too bad - you just take a fresh snapshot of the publisher and push this out to each subscriber. If any database is both a publisher AND a subscriber, though, then I'm not quite sure how you would re-synch that. There may be utilities within replication that allow this to be done automatically (I'm not familiar with SQLServer replication to know that off the top of my head) - hopefully someone else will be able to assist with that.
-
Do you mean you have one log shipping standby server at each site?
How your log shipping etc. works all depends upon how failover is dealt with. If you use client re-direction so that the log shipping server does not need renaming in failover then yes you could use one server or multiple instances - your choice. I looked into using a multi-instanced failover for several log shipping servers but it was unsuitable at the time as I had to rename the server rather then client connections it didn't work as the instance name and default name didn't match. IF all your servers use instances it may be easier.
Usually log shipping is best on a one to one server.
As for Replication - well you just can't redirect or failover replicated or log shipped replicated databases ( or whatever variation ) Replication is far less easy to change. I'd suggest you probably need to sort out the distributor cos if that fails so does your replication and I don't know of any way of failover over a distributor.
I suspect the proposed solution doesn't match the requirement, as you're unsure that makes me think the proposer of the solution doesn't know if what is being asked is possible or not , bit like blind leading the blind ( no offence intended ). You need to get a clear specification of what is required and the scenarios under which it should provide the required service / failover. Then you need to figure out if it's actually possible. I'm somewhat doubtful to be honest.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply