September 30, 2008 at 9:17 am
I'm a senior database developer / development DBA, and I wanted some advice for our production system, on the available options for dealing with failover in SQL Server 2005. We have what I'd consider medium sized databases, a few gig each, but a separate database for each of our clients, in a software as a service type of application environment.
What experience I have with clustering was in SQL Server 2000 at another company, and my role as a developer there was mostly isolated from the setup and configuration of the cluster. Unfortunately I don't have any 2005 clustering experience nor any experience with mirroring. From what I've read though, it seems that clustering is a more expensive option because it requires approved specialized hardware, and mirroring can just use commodity hardware, and mirroring with a witness can have very fast failover. Our production DBA seems sold on clustering though and has kept me out of the conversations with the infrastructure team. What advantages would there be to clustering over mirroring, or is clustering more of a legacy solution that Microsoft kept in because there are so many clusters out there from SQL Server 2000 and earlier that they still needed to support going forward in their software?
Also, from what I've read in Books Online, I got the impression that clustering in SQL Server is only for failover, but our production DBA seems to think that he can setup load balancing. I thought this could only be done by either using replication, or trying a "data dependent routing" scheme to split the data across multiple instances (this was tried before I was here at this company with near-disasterous performing results). Scalable Shared Databases or Snapshots are not very good options for us because of explicit requirements of real time reporting when data changes. There won't be huge numbers of transactions, but more likely spurts of small data changes throughout the day and evening from each client.
September 30, 2008 at 9:21 am
Clustering is for redundancy of the entire server, mirroring is for redundancy of individual databases. They both have their place and neither is a replacement for the other.
Neither is a scale-out/load balancing scenario. In a cluster, the database is on shared disks and only one node can own those disks and hence run the database at a time. In mirroring the mirror DB is no accessible unless you use a database snapshot, and even then it's read-only
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
October 1, 2008 at 6:48 am
OK, thanks Gail for confirming that clustering does not have load balancing capability.
Does anyone have insight on what advantages if any clustering would have over mirroring? It still seems to me that clustering is at a disadvantage, because it uses shared disk so there is still one potential point of failure, and would be more expensive because of specialty hardware requirements.
October 1, 2008 at 8:45 am
It's not a matter of an advantage/disadvantage, it's another concept.
Clustering is a failover system for your server (not for your storage), while mirroring is a failover for your complete environment. Mirroring can be setup in different stages, including an automatic switch the the mirrored database server, see BOL.
There are also some limitations about the physical distance between the 2 servers (in both cases) , make sure you know these before making a decision.
For clustering, in most cases one server is active and the other (passive) is doing nothing. One diskgroup is defined and connected to the active node. But you can take advantage of the passive node by adding an additional diskgroup and connect this to the passive node. You cannot share the same disk (same data), but at least you can use the passive node.
Say you have:
* server 1, shared diskgroup a, application fgh, server 2 is the failover for server 1
* server 2, shared diskgroup b, application xyz, server 1 is the failover for server 2
Only if a failover occurs, the remaining server will get both diskgroups and a higher load (but how often will that happen?)
Wilfred
The best things in life are the simple things
October 1, 2008 at 8:55 am
Clustering's good when you need redundancy for the entire server, not just a single databases.
Mirroring happens at the database level, so if the app depends on resources outside of the DB, you have to ensure that the resources are available on both machines. If there are 2 DBs that are both needed and cross DB queries run, then you have to ensure that they're both mirrored and that they both fail over at the same time
If you have other services than SQL (SSAS for eg), mirroring won't help there, but if they're cluster aware, they can fail over with the cluster nodes.
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
October 1, 2008 at 8:58 am
Wilfred van Dijk (10/1/2008)
Clustering is a failover system for your server (not for your storage), while mirroring is a failover for your complete environment.
Mirroring is a failover for a single database. While multiple databases on 1 server can be mirrored to a second server, they will fail over individually.
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
October 1, 2008 at 9:09 am
You're right, my specification was not accurate enough.
Wilfred
The best things in life are the simple things
October 2, 2008 at 5:59 am
Gail touched on this, but it is a very important point - with DB Mirroring, the application must support mirroring. Most commonly, by using an "SQL Native Client" odbc driver.
With Clustering, the application does not need to know anything about the configuration. Even if the Cluster fails-over, the application is not aware that anything happened.
Andy
October 2, 2008 at 6:05 am
Are you sure? We are running Axapta 3 on a 2K5 Cluster. If failover happens, all connections are broken. So AFAIK your application also needs to be cluster aware.
Wilfred
The best things in life are the simple things
October 2, 2008 at 6:27 am
Depends on where the applications are located. But you are essentially correct - during a failover, the SQL Server stops on one node and starts on a new node. This will close all existing connections, and roll forward/backward all open transactions.
Some applications can be coded to be "cluster aware" and thus handle this situation. Other applications will just reconnect without a fuss.
If an application expects the database to always be available, and has little or no error handling which runs when the database fails to respond for a few seconds, then that application will probably throw a nasty error.
Andy
October 2, 2008 at 7:55 am
AndyD (10/2/2008)
With DB Mirroring, the application must support mirroring. Most commonly, by using an "SQL Native Client" odbc driver.
Indeed. It's a point I forget to mention.
With mirroring it's the client app's responsibility to know where the mirror server is and to connect to that when the principle is down. If the apps are using SNAC (SQL Native Access Client), that's fine. SNAC 'knows' when it connects to ask the server if there's a mirror and to cache that in case it's needed. Or the connection string itself can have the failover server added to it.
For apps that aren't using SNAC, the finding of the mirror and switching of connections to the mirror has to be coded in to the app. If it's not and the principle fails, the app will not be able to use teh mirror. It doesn't know there is one, let alone where to find it.
Are you sure? We are running Axapta 3 on a 2K5 Cluster. If failover happens, all connections are broken. So AFAIK your application also needs to be cluster aware.
The connections will be broken. The server has, after all, failed. However all they have to do is reconnect to the same servername/IP once the failover's complete and they can carry on working as normal. That's not the case with 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
October 2, 2008 at 8:48 am
Thanks Gail, nicely summarised.
October 3, 2008 at 1:30 am
With Mirroring, assuming either SNAC or the Failover Partner setting in the database connection string are used, the application will keep trying the Data Source, then the Failover Partner, Data Source again etc. until either, one of them responds, or the Connection Timeout is reached. If neither has responded and the timeout is reached (I believe the default for an ADO.Net connection string is 15 seconds) the application will throw an error.
October 3, 2008 at 1:58 am
With clustering the servers are physically located near each other in order to share the disks. It will protect against failure of one of the nodes in the cluster but not a failure of the disks or loss of site. So if you use a SAN for example and the SAN goes down or the server room has a fire the cluster won't help. Clustering protects against node failure at a single site and applies to the database instance. The application that connects to the database will be disconnected but can reconnect to the same database and the same server name.
With mirroring the servers can be physically located near each other but they can also be at different sites. If they are at different sites this will protect against failure of the server and/or loss of disk at the primary site. You have to set mirroring up for each individual database and there is a limit on how many can be handled by the hardware/network. The application will have to reconnect to a new server name
October 3, 2008 at 2:02 am
With a SAN cluster nodes can be, and often are, located at different sites. The key thing is a 500ms failover timeout on the heartbeat link. With mirroring there is no timeout. The log on the Principal will keep growing until it is able to connect to the mirror.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply