Simplest solution for high availability of SQL server 2008?

Question

I have a bunch of SQL servers which I periodically performs maintainance on (Windows Update patches etc.). Now I want to the database online 24/7 and need to implement one of the high-availability solutions for SQL server. The solutions needs to be cheap and simple to use.

I have no problems tweaking the connection strings for the clients of the database, so currently I'm looking into database mirroring with manual failovers when taking down a partner instance for patching etc.

Is this the best thing to do or are there other options which doesn't involve setting up a failover cluster?

The servers are virtualized with a fully redundant storage solution.

Any tips are appreciated, thanks in advance!

Answer 1

The four high availability solutions I'm aware of are:

• Failover cluster
• Log shipping
• Mirroring
• Replication

Log shipping is probably not 24/7, so that leaves three. Serverfault is definitely a better place to ask about their relative merits.

Answer 2

Mirroring with a PARTNER-server would probably be the cheapest solution (you can skip the PARTNER-server if you plan to switch manually).

Failover requires shared disks (NAS) aswell as cluster-capable Windows-licenses (very expensive).

I'm not sure about replication, or how it differs from mirroring, but my research I did gave the conclusion that mirroring was the one for me. However I don't mind some downtime when doing upgrades, I just keep mirrored instances of the database in case of severe hardware failure.

It might be that replication is for a complete instance of an SQL-server, whilst mirroring is done per database. In my case, I have 2 production servers, that both replicates it's databases to a third, backup-server for disaster-recovery. I think that wouldn't have been possible with replication.

Answer 3

For auto failover I would choose mirroring. You can build a 2nd database connection string into your app and whenever the preferred isnt available it will default to the backup - therefore giving your app 24/7. This has its downsides though, once 'flipped' to the mirror you have to either accept that this is the way it is until another maintenance job requires the mirror to shift back again or you have to manually swap the mirror over.

In order for this to be truly 24/7 you will need to enable auto rather than manual, maybe you will need a witness server to make the decision... There are lots of factors to include in the choice - are you working with servers on different sites, clustering, multiple web/app servers ... ?

As previous answers have suggested, http://serverfault.com/search?q=sql+mirroring will have people who have made just this choice, ready to help you in much more detail

A big benefit of mirroring is that providing the mirror server has no other activity it is license free, the live server license transfers over if the mirror takes over. Full details on SQL licensing pages at microsoft.com