January 15, 2009 at 7:16 am
Hello,
I have been asked to look at high availability solutions for SQL SERVER. The requirements for the solution are the following:
1) Power outages (The servers will be kept in two different physical locations) or hardware failures do not affect the availability of the databases.
2) The ability to install patches and windows updates to the servers whilst still maintaining database connectivity.
3) No data loss as a result of switch over if a failure occurs.
4) No Manual intervention to keep the database connectivity if one of the servers falls over.
5) Must be built into SQL Server no third party apps
My understanding from research is that this rules out log shipping as the failover cannot be manually automated and there is the possibility of data loss.
My understanding is that clustering will fulfil all of these requirements, is this correct?
Also my understanding that is that clustering on SQL server is likely to decrease performance rather than increase it due to the overhead of clustering is this correct? Are there any performance issues likely to result from having one server in one location and another node in another location?
What about Mirroring I understand that the fall over process can be automated, is this correct? But is there loss of connectivity if you apply updates? Is there any data loss likely if a server switch has to occur? Are there any performance issues likely to result from having one server and the mirror in another location?
Sorry folks, for all the questions but the more I research it the more questions I have!!!
January 15, 2009 at 7:22 am
I assume that your Data center has Generator that will kick in Automatically. Keeping the Servers in two physical location reduces your options.
Keep in mind that Clustering means both the servers will use a shared disk space. I am not sure how you will manage that with the Servers being in different physical locations.
-Roy
January 15, 2009 at 7:30 am
You actually cannot meet these requirements, sorry. You can only come close.
Mirroring is your only available option. Clustering requires a shared disk array. This would give you a single point of failure so your power outage problem could stop you if the disk array location lost power and had no backup power system.
Mirroring is the only built-in solution that will give you a full copy of your database on entirely separate hardware as well as automatic failure (of sorts).
It covers #1, #2, and #5.
#3 - no data loss. Well, you could lose data in an active transaction. That is data, so "no loss" is really not going to happen with any solution. Mirroring is very close to no loss.
#4 - well, if you have programmed your application correctly, and it is using the appropriate SQL client, and the connection string includes the primary and mirror server the failover can be handled automatically. You still get a disconnect when there is a failover, so your application needs to know to reconnect if there has been a disconnect. Many applications have not been programmed correctly to handle this situation, so you may or may not be ok.
Now, if you application does not already handle disconnects correctly and you have to program to handle #4, you could modify the program to handle disconnects and re-connect to a secondary log-shipped database if the primary server is unavailable, but mirroring is probably easier to deal with and will have less potential data loss.
January 15, 2009 at 7:56 am
If I could persuade them to drop the requirement to house the servers in different locations and rely on the inhouse generator, would clustering fufill the other points?
January 15, 2009 at 8:01 am
You can look at Marathon clustering. Seems very efficient. But not sure of the performance. Set up is not easy. You get both Hardware and Software redundancy. Also it does not even use Shared Disk Space. It is set up between two Physical machines with Local Disks. This means you dont have a Single point of failure. It does fail over with out any issue.
-Roy
January 15, 2009 at 8:27 am
jabba (1/15/2009)
If I could persuade them to drop the requirement to house the servers in different locations and rely on the inhouse generator, would clustering fufill the other points?
Ummm... no.... If it's absolutely critical to the future well being of a company, I think the servers should actually be several hundred miles apart (different regions, if possible) to be able to deal with catastrophies like hurricane Katrina, 9/11, and other catastrophies that effect entire buildings and, sometimes, entire regions.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2009 at 8:42 am
jabba (1/15/2009)
If I could persuade them to drop the requirement to house the servers in different locations and rely on the inhouse generator, would clustering fufill the other points?
the nodes of clustered servers can be physically separate, the limiting factor is getting a response from the keepalive heartbeat in time. Rule of thumb is about 100 miles but others have reportedly had theirs further apart.
However clustering does not fulfill #1 (single disk location) and #2
Mirroring is your best bet for these requirements as long as you use SNAC and app can handle reconnection (as others have stated)
Of course you can mix and match (cluster plus logship to another server, or cluster and mirror)
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply