DB Mirroring VS. Clustered Failover Questions

Question

We are planning to update our two node, active/passive clustered system to SQL 2005 from SQL2K.  Would we be better off to use database mirroring rather than clustering with failover?  I believe mirroring takes 3 systems rather than two (one for each mirror and one for monitoring), but can the monitor be a rather low powered PC?

Our current cluster uses two 4-processor Dells and an external PowerVault RAID array with fiber channel connections.  Each server has two NICs, one for general network and one for heartbeat and a fiber channel card to connect to the external PowerVault. 

Here are my questions:

1 - If I understand it correctly, mirroring uses just separate servers without the need for the rather expensive and complicated shared fiber channel array that must go between them.  Do I still need a shared array or can I just use internal RAID arrays in each box?

2 - Are there any advantages to keeping our current system?  Our current system has worked well, but the failover takes a couple of minutes.  Is mirroring faster?

3 -  Is there still a virtual IP address and network name that everyone connects to? 

4 - What are the drawbacks?

5 -  Can I run non-mirrored DBs on the same servers or are they really locked together tightly?

6 - How much different in configuration can the two boxes be?  With the cluster,  I really need duplicates in every way.

7 - Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?

Thanks for any of your experiences on this.

Answer 1

Biggest difference is probably the level at which each provides its redundancy...mirroring provides protection/avail at the database level, but clustering provides protection at the server instance level. Another big difference is that in mirroring, the principal and mirror servers are separate SQL Server instances with distinct names, but a SQL instance on a cluster gets a single virtual server name and IP that remains the same no matter what node of the cluster is hosting the instance.

If you need protection at the server level (i.e. multiple databases are required for application functionality, etc.), clustering is probably a more appropriate choice...but if you only need to provide availability for one database at a time, mirroring has a number of advantages.

Unlike clustering, mirroring does't require proprietary hardware and does't have a potential failure point with shared storage...mirroring also can probably bring the standby database into service much faster than any other high availability technology, and works well with new capabilities in ADO.NET and SQL Native Access Client for client-side failover.

Note that you can't use database mirroring within a single cluster, but you can definately consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be aware that because a cluster failover is probably longer than the timeout value on mirroring, a High Availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state.

HTH

Answer 2

Thank you,  that is helpful.  I am wondering, if the mirroring is at the DB level, how does the application (in this case a three tier app with a thick-client VB app on the desktop communicating to a mid-tier portion on another server) know about the failure and manage to get connected to the mirror?  Is it transparent to the application, as is the case with a cluster, or must some code be written to test some indication of primary vs. mirror state?

It sounds like there is no provision for a virtual-IP as we would get with clustering.  Is that true?

Also, do we need to purchase Enterprise Edition of SQL 2005 for a two node, active/passive cluster arrangement, or will Standard Edition handle that type of setup?

Answer 3

Take a look at this article, it should answer all those questions and more:

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

 

Answer 4

Hi Chad,

Thanks for your post.

Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?

Regards,

Rakesh

Answer 5

I know you did not reply to me, but here is what we do.  You need a separate license for each box that takes part in mirroring.  They also need to be Enterprise versions if running  SQL 2000 and you want to use the MS mirroring wizard scheme.

 

If you use a separate box for the monitor, you will need a total of three servers.  If you use clustering, only one license is needed, since only one copy is running at any one time in an active/passive configuration.  In a sort of CYA approach, our company just buys one for each box, since there is always the potential for un-clustering and just running as separate servers.  As older servers are retired or downgraded from production, that can happen.  Your policies may differ.

Answer 6

Thank you Chad. Found your response very useful indeed.

Kind regards,

---

Mpumelelo