November 17, 2011 at 2:49 pm
Here's the scenario:
2 SQL Servers - SQL01 and SQL02 (multiple instances per server - about 60 instances total)
1 SQL Mirror - SQLM01 (houses all mirroring for primary SQL Servers)
SQL01 and SQL02 both mirror to the one server (multiple instances) for Production and serve as the witness for each other. We need to do a DR solution and I would like to create a tertiary mirror so that SQL01 and SQL02 mirror again to another site OR that the primary mirror (SQLM01) mirrors itself off again to that same other site (on an opposite coast for a true DR solution). I'm attempting to avoid log shipping because the platform will have a few app servers that will need to be warm so in case we have a DR event we can have them online (along with SQL Server) very quickly (like within 15 mins if possible - no more than 1 hour for sure). Now when I say DR event I mean true DR - the primary data center is offline and will not be back anytime soon (fire, flood, tornado, hurricane, earthquake, meteorite, nuclear missile, etc). There is enough redundancy built-in to the primary datacenter that these are the only scenarios where we would have to failover to the DR site (and even some of these have been accounted for at the Production facility - it is tier III, SAS70 Type II, DoD certified).
I am admittedly not a SQL expert. I have some folks telling me it has to be log shipping but there has to be another way to handle this that is far more efficient than that. Is it possible to have a synchronous mirror for the Production system while at the same time also having an asynchronous mirror to the DR site? Any thoughts, opinions, suggestions - all appreciated. Thanks!
November 17, 2011 at 2:52 pm
SAN Level mirroring? / SRDF?
You cannot mirror one database to multiple instances.
November 17, 2011 at 4:09 pm
Yeah so not going to mirror at the SAN - the traffic/bandwidth requirements to mirror that clear across the country to another datacenter, not to mention the costs involved, isn't justifiable. If log shipping is the only option then I'll have to do it but then will have to roll them up on a regular basis so I can quickly switch if the time came.
Anyone else have any better ideas?
November 18, 2011 at 12:08 am
2 SQL Servers - SQL01 and SQL02 (multiple instances per server - about 60 instances total)
1 SQL Mirror - SQLM01 (houses all mirroring for primary SQL Servers)
2 Servers, 60 Instances, Approx. 30 Instance on each Server :w00t:
How many DBs? Assuming 3 DBs per instance so 180 DBs total.
180 DBs mirrored to 1 server (1 instance)... absolutely bad idea. Mirror DB Server is not just a backup. It should be able to take the PROD load in emergency.
I would recommend you to classify your DBs / Instances to few groups (e.g. HR, FIN etc). Then setup mirror servers for them. For 180 DBs I would suggest 5 - 10 Instance / Servers.
OR
Better (& Expensive) Solution would be Failover Clusters.
November 18, 2011 at 2:22 am
Dev (11/18/2011)
Better (& Expensive) Solution would be Failover Clusters.
Why do you say clustering is better and more expensive than mirroring?
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
November 18, 2011 at 2:24 am
christopher.metzger (11/17/2011)
Anyone else have any better ideas?
Log shipping, SAN replication, geo-stretch cluster (you don't want that, complex), wait for SQL 2012 and use AlwaysOn. Mirroring is a single principal and mirror.
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
November 18, 2011 at 2:33 am
GilaMonster (11/18/2011)
Dev (11/18/2011)
Better (& Expensive) Solution would be Failover Clusters.Why do you say clustering is better and more expensive than mirroring?
Better:
Configuring 1 Failover Cluster Vs Managing 30 Mirror DBs
Expensive:
SAN requirement
Microsoft Cluster Server (MSCS) licences
November 18, 2011 at 8:19 am
The environ is vCloud with SQL 2008 R2 Enterprise. Most instances may only have a handful of databases (an ASPState, a tiny Security, a facility - which can get relatively large compared to the other databases, and a Master that doesn't ever change because it's not written to - just used for menus and such, plus some have a AP/GL DB which is also small) but some may have as many as 8+ facilities in an instance. Total database footprint is less than 500GB for 53 clients and about 150 facilities. But we can't consolidate to a single instance because this is a PHI system - so each instance must be per client (plus our application prefers it that way - to do otherwise requires manual config and I'm not all about non-standard installs).
So Clustering I don't think is going to do what I need here. The intent is to provide some sort of failover availability in case something happens to the primary SQL server or instance for that particular client (or set of clients). I expect it will not ever be needed as we've never had an issue in the past with this configuration plus the new datacenter platform is a true Cloud design with auto-failover for the underlying host hardware and it is all being served by an EMC Clariion SAN (all SAS and it has it's own fail-safe capabilities).
The primary design I think is going to be fine. What I'm worried about is the DR piece - whether it should be log shipping from VA to CA or if I can do another mirror so the SQL Server on the other end is more easily available. If I do log shipping then that means I will have to set a schedule for rollup and apply those logs so the DBs stay current and so if a DR event happens then I can get that system online quickly. The App Server VMs will, for the most part, be cold (only a few will be warm and ready to be brought online within a few minutes) - and the only other piece that will be live is another DC that will replicate from the Production platform. The DR site, otherwise, will be constructed to operate independent from the primary and will be maintained as such - but it's a matter of what's the best solution to get the data there (and the only data necessary to replicate will be the Security DB for the instance and then each facility DB - the other DBs will be recreated when the DR site is built).
Does this clarify? I know everyone has an opinion but what I'm looking for is the easiest way to get the data from point A to point B that will give me the fastest turnaround time for getting the DR site online in the event it is needed. 4hour RTO with a 15 minute RPO if this info helps.
November 18, 2011 at 8:21 am
And no - the primary mirror will not have all DBs mirrored to one instance. They will be mirrored on a per instance basis. So yes the primary mirror will have ~60 instances for mirroring but the overhead for that VM should be low since it's not actually being used AND if a failure happens I doubt both Primaries will go offline at the same time so it should only have to take over for one Primary at any given time.
November 18, 2011 at 8:35 am
Dev (11/18/2011)
Expensive:SAN requirement
Microsoft Cluster Server (MSCS) licences
Whereas mirroring requires 2 sets of storage (clustering is one), so unless using direct attached storage for the mirrors as opposed to SAN storage, it's twice the price.
No such product as Microsoft Cluster Server (well, not since NT 4). The OS licensing is the same - 2 machines, 2 OS licenses.
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
November 18, 2011 at 8:40 am
christopher.metzger (11/18/2011)
Whether it should be log shipping from VA to CA or if I can do another mirror so the SQL Server on the other end is more easily available. If I do log shipping then that means I will have to set a schedule for rollup and apply those logs so the DBs stay current and so if a DR event happens then I can get that system online quickly.
You can't do a second mirror, unless you're willing to wait for SQL 2012. Re log shipping, that automatically applies the logs to the secondary servers at whatever schedule you set. So if you're taking, shipping and restoring logs every 5 minutes, the worst you can lose is 5 min data.
As for bringing the log shipping secondary online, it's as simple as RESTORE DATABASE <db name> WITH RECOVERY or restoring one last log backup and specifying WITH RECOVERY.
From what you've said, log shipping is fine. A 4 hour RTO is incredibly generous. With a 15 min RPO, I'd recommend log backup and copy schedule at 10 minutes, 5 if you're paranoid.
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
November 18, 2011 at 8:53 am
GilaMonster (11/18/2011)
christopher.metzger (11/18/2011)
Whether it should be log shipping from VA to CA or if I can do another mirror so the SQL Server on the other end is more easily available. If I do log shipping then that means I will have to set a schedule for rollup and apply those logs so the DBs stay current and so if a DR event happens then I can get that system online quickly.You can't do a second mirror, unless you're willing to wait for SQL 2012. Re log shipping, that automatically applies the logs to the secondary servers at whatever schedule you set. So if you're taking, shipping and restoring logs every 5 minutes, the worst you can lose is 5 min data.
As for bringing the log shipping secondary online, it's as simple as RESTORE DATABASE <db name> WITH RECOVERY or restoring one last log backup and specifying WITH RECOVERY.
From what you've said, log shipping is fine. A 4 hour RTO is incredibly generous. With a 15 min RPO, I'd recommend log backup and copy schedule at 10 minutes, 5 if you're paranoid.
AH! So '12 will, supposedly, let me do it. See I thought '08 would so shows how much I know. This is excellent info - thanks!! And thank you for tolerating my ignorance - I know enough to be dangerous with SQL but once I can get this platform designed and built, and get through my CISSP in 2 weeks, I'll set out after the SQL cert. THANKS!!
November 18, 2011 at 8:59 am
And the RTO/RPO is what the contract specifies so that's the minimum. I think the 15 min RPO is good - I'm shooting for a <= 2 hr RTO if I can (and with the new Cloud architecture I think it will work out that way) possible <=20 mins for those that have warm VMs at the DR (since they will have separate IPs and separate addresses to point to).
It'll be pretty awesome when finished but Jesus is it a lot of work. Wish my magic wand wasn't in the shop. 😀
November 18, 2011 at 9:24 am
christopher.metzger (11/18/2011)
AH! So '12 will, supposedly, let me do it.
Yup. See http://msdn.microsoft.com/en-us/library/ff877884%28v=SQL.110%29.aspx
Each set of availability database is hosted by an availability replica. Two types of availability replicas exist: a single primary replica and one to four secondary replicas. The primary replica makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica. Each secondary replica maintains a set of secondary databases. Every secondary replica applies transaction log records to its own set of secondary databases and serves as a potential failover targets for the availability group.
So basically, 1 principal (primary replica) and up to 4 mirrors. Even better, some of the mirrors can be read-only for scale-out reporting.
See I thought '08 would so shows how much I know.
2008's mirroring was pretty much 2005's mirroring with a few enhancements.
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
November 19, 2011 at 6:47 am
GilaMonster (11/18/2011)
Dev (11/18/2011)
Expensive:SAN requirement
Microsoft Cluster Server (MSCS) licences
Whereas mirroring requires 2 sets of storage (clustering is one), so unless using direct attached storage for the mirrors as opposed to SAN storage, it's twice the price.
No such product as Microsoft Cluster Server (well, not since NT 4). The OS licensing is the same - 2 machines, 2 OS licenses.
Did I miss something here?
Before you install a SQL Server failover cluster, you must select the hardware and the operating system on which SQL Server will run. You must also configure Microsoft Cluster Service (MSCS), and review network, security, and considerations for other software that will run on your failover cluster.
Before Installing Failover Clustering
http://msdn.microsoft.com/en-us/library/ms189910.aspx
Configure Microsoft Cluster Server
Microsoft Cluster Server (MSCS) must be configured on at least one node of your server cluster. MSCS is only supported if it is installed on a hardware configuration that has been tested for compatibility with the MSCS software. You must also run SQL Server Datacenter, SQL Server Enterprise or SQL Server Standard in conjunction with MSCS. SQL Server Datacenter, and SQL Server Enterprise supports failover clusters with up to 8 nodes. SQL Server Standard supports two-node failover clusters.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply