At the moment I'd like to combine my departmental SQL Server into one big SQL Server 2008 instances spread across two servers (for redundancy reason).

How/what is the best way to approach this ? is it through clustering or Log shipping or VMware (hypervisor) High Availability features ?

Note: this DB server cannot go down as it is hosting the VCenter DB and also server monitoring database too.

the SQL Server is spread across several smaller servers 3-4 SQL Server 2000 and 2005 with the total of 70 DB inside those servers, what I'd like to do/build is to migrate them all into SQL Server 2008 in which I must make it redundant so that if one server failed for whatever reason, the other instance can just pick it up straight away.

so from several smaller SQL Servers, I want to consolidate them all into one more powerful SQL Server 2008 x64. but so far don't know which technology that is suitable for that or if it is possible to do in SQL Server 2008 Standard.

Any suggestion and comment would be greatly appreciated.


is it through clustering or Log shipping or VMware (hypervisor) High Availability features ?

No, no, no.

MIRRORING, 3 servers (one can be a small free one, just deciding which keeps working active).

Log shipping is slowe / delayed, clustering / vmware leaves one database as weak point that still can be corrupted.


TomTom has it correct, but let me expand on a few key points:

  1. Log shipping is great for DR, not great for up to date data. Your data is always old, say 15 minutes to 1 hour or even more old, depending on your settings. The re-connection is not seamless. You need to re-configure your application to point to the new system. Also, the 2nd database is not online, it's usually in recovery mode (to apply the logs), so you need to take it out of recovery mode in order to use it. This is why it's usually used for disaster recovery only.

  2. Clustering is not what you want when you're on a link with any latency at all. It provides a highly available SQL interface, but it does not make your data highly available, unless your SAN can do that for you. I would never try clustering between DC's.

  3. Mirroring. In this case, mirroring is your friend. Mirroring has two basic modes: Synchronous, or A-synchronous. Common to both modes: Each SQL server has its own local copy of the database, but only one SQL server can "own" it. In other words, even though you've got three SQL servers, only one of them is actually in use. The others are all in standby mode, waiting to be activated.

    In Synchronous mode, when a write transaction is received, it is executed by all of the SQL servers at the same time, and the next transaction is not processed until each SQL server has reported that it's committed the transaction. This ensures that every single SQL server has completely up-to-date data. This is great for high-speed, low-latency links, not so good for high-latency links as it will effectively slow down your database to the speed of the slowest partner.

    In asynchronous mode, SQL server does not wait for all the other SQL partners to commit the transaction. It just commits it locally and moves on. Each other SQL server then might be a few transactions behind, especially if the links between them are heavilly loaded. This means you get full-speed access to the active DB, but you run the risk of loosing a bit of data in the case of a failover. Also, this mode is only available on SQL Enterprise edition.

  4. VMWare HA. Well, this is a whole can of worms. It's good, but again you need to share the same SAN for it to work. Or are you talking about VMWare FT (fault tolerance)? If you are, I'd suggest forgetting about FT for an SQL server at the moment. FT is great in theory, and I'm even using it one one VM, but for most deployments the sacrifices you need to make are too great. Also, because the VM's are in lock-step with eachother, and bandwidth issues will murder performance.

To manage the ability to automagically fail-over, Clustering provides you with a virtual instance that you connect to and everything is done invisible to the user. For mirroring, you can achieve this easilly with the Network Load Balancing role running on :1433 so that when the SQL server goes off the network, the virtual instance switches over the other nodes. Alternatively, depending on your app support, you can specify alternate mirror servers in the SQL connection string.

You may be interested in the response I received to my question, SQL Server 2008 R2 100% availability.

  • Ah yes, many thanks for your response, so in this case SQL 2008 Cluster is the way to go since I only have standard edition ? – Senior Systems Engineer May 9 '11 at 6:41
  • And hw will you real time replicate the SAN between daa centers? It wont work. You are cheaper busing enterprise edition than triying SAN replication. – TomTom May 9 '11 at 7:09
  • 1
    I'd just like to point out that clustering vs. mirroring isn't an all-or-nothing situation. It's a very common scenario to replicate from one MSCS-clustered SQL Server instance to another at a DR site. This is very important when your high availability goals and your business continuity goals aren't necessarily the same. – jgoldschrafe May 9 '11 at 11:19
  • 1
    @Albert - you can still mirror in standard mode, but only in synchronous mode. I think that'll be your only option given what TomTom points out with replicating the SAN – Mark Henderson May 9 '11 at 21:28
  • many thanks for the suggestion Mark, here's what I'd like to implement. ESX1: VM1: Windows Server 2008 Standard + SQL Server 2008 Standard (RDM into the SAN) - Synchronous clustering mirror ESX2: VM2: Windows Server 2008 Standard + SQL Server 2008 Standard (RDM into the same SAN LUN as above) - Synchronous clustering mirror – Senior Systems Engineer May 10 '11 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.