May 17, 2012 at 10:31 am
We are looking for the best solution for high availability and disaster recovery. We have are main production SQL Server databases and we want to replication to two other servers. One server is in the same building, however, the other server is in another location. We are currently mirroring to the off-site server, but want to add the third server into the environment. Is it possible to mirror a database to multiple SQL instances on different servers?
May 17, 2012 at 10:35 am
What's your RPO, what's your RTO? What's your budget? What are your requirements for failover?
There is no 'best' HA/DR solution, it's dependent on your requirements
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2012 at 10:37 am
melissa.dougherty (5/17/2012)
We are looking for the best solution for high availability and disaster recovery. We have are main production SQL Server databases and we want to replication to two other servers. One server is in the same building, however, the other server is in another location. We are currently mirroring to the off-site server, but want to add the third server into the environment. Is it possible to mirror a database to multiple SQL instances on different servers?
Not until you move to SQL Server 2012 where you get AlwaysOn.
Your choices right now are a bit more limited. You could look at log shipping if you need another copy of your database. The other choice, if a second on-site copy of the database isn't needed to cluster your servers for HA locally.
May 17, 2012 at 11:05 am
We really don't have a budget.... we would need to justify the need and see if they will approve. We need to recover within an 8 hour window, with minimal data loss. That is why I'm not sure I like the log shipping, even if you run transaction log backups every 15 minutes, you have the potential of losing up to 15 minutes of data.
May 17, 2012 at 11:08 am
melissa.dougherty (5/17/2012)
We really don't have a budget.... we would need to justify the need and see if they will approve. We need to recover within an 8 hour window, with minimal data loss. That is why I'm not sure I like the log shipping, even if you run transaction log backups every 15 minutes, you have the potential of losing up to 15 minutes of data.
Define minimal loss of data. This is, of course, something that your business users have to decide. Maybe 15 minutes is too much, so maybe you run the t-log backups every 5 minutes.
May 17, 2012 at 11:09 am
Are you using syncronous or asyncronous mirroring to your off-site database?
May 17, 2012 at 11:25 am
I will need to meet with higher ups to determine amount of exceptable data loss.... we are running mirroring in a synchronous mode, we do not use the Witness.
I don't see an answer for my question about more than one mirror site. Can one principle point to multiple mirror instances?
May 17, 2012 at 11:34 am
melissa.dougherty (5/17/2012)
I will need to meet with higher ups to determine amount of exceptable data loss.... we are running mirroring in a synchronous mode, we do not use the Witness.I don't see an answer for my question about more than one mirror site. Can one principle point to multiple mirror instances?
Actually, I answered that in my first post:
Not until you move to SQL Server 2012 where you get AlwaysOn
May 17, 2012 at 11:37 am
melissa.dougherty (5/17/2012)
I will need to meet with higher ups to determine amount of exceptable data loss.... we are running mirroring in a synchronous mode, we do not use the Witness.I don't see an answer for my question about more than one mirror site. Can one principle point to multiple mirror instances?
Using syncronous means that your mirror database will be current. If you lose your principal database and force a failover to the mirror, you will be up with minimal data loss. You could then resume log shipping from the mirror (now principal) database to your log shipping database.
May 17, 2012 at 11:48 am
Cool.... so to confirm what you're saying. My production (principle) server pushes log shipping to server B while pushing mirroring to server C. How much of an impact on performance for a 64 bit Windows server with plenty of RAM?
May 17, 2012 at 12:08 pm
melissa.dougherty (5/17/2012)
Cool.... so to confirm what you're saying. My production (principle) server pushes log shipping to server B while pushing mirroring to server C. How much of an impact on performance for a 64 bit Windows server with plenty of RAM?
Don't know. If your mirroring to on off-site server is working well, adding log shipping really shouldn't add that much of a burden.
You will have to test it to be sure.
May 17, 2012 at 12:43 pm
Ok, these are some of the possibilities, all allow for different RTO and RPO and failover options. One thing I will say is that 8 hours is sufficiently generous that it allows for just about anything. I'm far more used to < 1 hour for an RTO.
Cluster the local servers, mirror to DR
Cluster the local servers, log ship to DR
Cluster the local servers, replicate to DR
Mirror the local servers, geo-cluster to DR
Mirror the local servers, log ship to DR
Mirror the local servers, replicate to DR
... several more less useful options are also available
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply