DataCenter failover solution using SQL 2011

Last Post 05 Jun 2011 07:54 PM by e90fleet. 9 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
e90fleet
New Member
New Member

--
23 May 2011 08:39 AM

Hi all, if you have an option to create the best solid DataCenter failover solution using the latest technology's including in SQL 2011 what would that be?

The main point here is to reduce the business downtime while the databases and web servers are involved with OS patching,
hardware maintenance, database maintenance, etc.

Fast application failover between Data Centers during planned and unplanned downtime is very important.

Will Multi-Subnet Failover Clustering work in this case along with replication? ( what kind of replication? )


How about the AlwaysOn feature?

I know it's a little early for Denali but still...

Thanks

rm
New Member
New Member

--
23 May 2011 09:52 AM
Possible to go with db mirroring? Apps can repoint to new server (if set connection string properly) when you switch site. AlwaysOn goes beyond that.
e90fleet
New Member
New Member

--
23 May 2011 10:49 AM
Thanks RM, I think it's possible to go with db mirroring. Currently are looking for different options and trying to figure what will be the best solution. Like pros/cons between replication and AlwaysOn
rm
New Member
New Member

--
23 May 2011 11:43 AM
Apps can't auto switch site easily with replication unless you add such logical in apps.
e90fleet
New Member
New Member

--
02 Jun 2011 08:40 AM
Until Denali is out and AlwaysOn is available we need to implement some kind of DR solution using the current available options.

We are thinking about Active/Passive data centers where if datacenter1 is down the second data center will automatically pickup the load.

Do you think mirroring will be the best option in this case or should we try peer-to-peer or other type of replication?

Another thing is that the main databases used by the website are currently subscribe data to 2 subscribers using transactional replication and those replicated databases are used by internal applications and has to be up to date.

So i am wondering how i can accomplish this? Can a database be involved in a mirroring and replication at the same time?
Also, can that replication database (subscriber) also be used in mirroring to mirror data to the second data center?

Thanks
gunneyk
New Member
New Member

--
02 Jun 2011 04:07 PM
Take a look at this web site http://sqlcat.com/whitepapers/default.aspx for lots of good white papers including some on HA & DR. There is a new one on Replication and Mirroring that may be of particular interest. But in your case I think you need to define your goals a bit more. You say DR but most of what you talked about is actually HA and there is a dfference. The ultimate solution will depend on what your SLA is and how much time and or money you are willing to spend to achieve that. But until you lay out exactly what you need to accomplish you can talk options all you want but you could just be going down the wrong path if they don't meet the goals.
e90fleet
New Member
New Member

--
02 Jun 2011 04:40 PM
Thank you for the link, i wil take a look at the articles.
e90fleet
New Member
New Member

--
03 Jun 2011 04:52 PM
This is basically what i am thinking to do for now:

1) Failover Clustering for High Availability with Database Mirroring for Disaster Recovery. The second datacenter will have duplicate clusters waiting for a fail over.
2) But i am not sure how to set up a reporting sub-system that doesn't hit the live dbs all the time - e.g. some kind of     replication (only read access is required on the reporting sub-system but it has to be up to date all the time)

What do you think about this solution?
gunneyk
New Member
New Member

--
04 Jun 2011 01:49 PM
A couple comments. First havig a duplicate cluster in the DR site is usually not woth the extra cost for most companies. The idea is you would only be running at the DR site in the event of a disaster in the first place and usually it is understood not to be as robust or even fast as the primary site. Mirroring to the DR site is OK but one thing to think of is human error. If you have someone for intstance run a DELETE and forget the WHERE clause this action will also be imediately applied to the DR site. Where as if you used something like Log SHipping you could restore on a delayed basis and possably catch something like that before it is applied. Not saying this is a better solution but something to think of. Also with reporting your only choice today to report off an up to date copy is replication which has its own set of requirements and characteristics. If you wait until Denaili you can however report off of a mirrored database. Again more to think about. The solution all boils down to actual requirements and how much time and money you want to spend to achieve it. Can't say that enough.
e90fleet
New Member
New Member

--
05 Jun 2011 07:54 PM
Andrew, very good points, thank you. I would like to wait until Denali is out, but management want to have some kind of temporary solution in place for now and planning to upgrade to Denali next summer. This is my first project on this level which i never done it before so i don't know how much money is enough.


Acceptable Use Policy
---