May 26, 2009 at 9:21 pm
First, if you haven't already spent your money on VMWare, may I suggest looking at MS Hyper-V? It is free with Windows Server 2008 and will reduce your costs. Second, if your SAN as the disk capacity, you may want to look at database mirroring. The failover will be faster, but it requires twice the disk space. A third virtual server would be needed to serve as a witness server between the other virtual servers.
May 26, 2009 at 11:18 pm
Firstly, I think that your information on clustering is not quite right. There is no loss of transactions that have been committed. Transactions that are in progress when a failure occurs are definitely lost - this is no different to the normal operation of a non-clustered server (e.g. if power is lost to the server). When a failure occurs, the disks used by the database server are "moved" to the standby node in for the server and SQL Server is start. The normal recovery process then run. This is where the delays are. First, the cluster needs to notice that the failure has ocurred (by default this takes 30 seconds but can be changed) and second, the instance of SQL Server needs to be started on another node. So depending on how long it takes for recovery to run in each database, the time required to return to normal service will vary. When a failover occurs, as far as your applications are concerned, all that happened is the server stopped and then started again since everything for the instance of SQL Server (right down to the IP address associated with it) move to the standby server.
Database mirroring can be quicker when a failure occurs because the mirrored database is on an instance of SQL Server which is running and is constantly applying (restoring) the transaction logs from the active database. From a SQL Server perspective, the failover time is likely to be a few seconds. From the perspective of your application, things are a little trickier than for clustering. All that happens is that the mirrored database is now on another server (instance of SQL Server actually). Your application needs to know about the other server. If the application is a current .Net application or is using SNAC, there are additional properties on the connection string that deal with the name of the mirror server. The application will see a broken connection, any active tranction will be lost and it can then immediately connect to the standby server.
If database mirroring is configured with a witness server (which needs to run any edition of SQL from Express upwards), the failover can be automatic (depending on the mirroring model you use)
Replication is not a good option for high availability because the transition to the replica is not automatic. A DBA would need to intervene to make sure that everything works smoothly (and that also applies when the failed server is restarted). With replication, there is definitely a risk that transactions may be lost because it is a store and forward architecture. The storing of the transactionsi (in the distribution database) may work, but there is the potential that they won't be forwarded to the replica.
As for Active/Active cluster, Active/Passive cluster - this is really up to you. Part of the decision is cost - MS does not charge for software on a standby server IF it is purely used for that purpose. Hence, the passive server in an Active/Passive cluster does not attract addition license fees. From a performance perspective, there are advantanges in using more than one node of the cluster since you, in theory, have a higher maximum capacity. However, you still need to make sure that each of the servers can handle the load without the other operating. When a failover occurs on an Active/Passive cluster, you only need to restart the service from the failed server - this may be quicker than restarting all (plus the database recovery time). You need to weigh this up when deciding how you wish to configure the cluster. If you are using a single database, then you really are committed to Active/PAssive.
I have no real information to add wrt virtual machines (VMWare or MS) Vs physical although I tend to prefer physical since there is no additional abstraction between the physical disks and SQL Server.
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply