Can I use Failover Partner in my SQL connection string without mirroring?

Question

I admit I'm a bit of a cheapskate, but I don't want to spring for the three servers needed to get automatic failover for SQL Server via Mirroring, especially since one of the three would do nothing other than be a witness server.

If I set up normal SQL replication (snapshot, say) from primary server to backup server, can I just set my connection string in my ASP.NET app to include 'Failover Partner=backup'? It seems like ADO.NET on the client should just try the primary, and then failover to the backup if the primary wasn't available.

Or does that only work with mirroring?

(I like the idea of mirroring, but if you don't want to pay for the witness server, you have to manually switch the backup server to be the primary.)

Thanks!

Answer 1

Gregor, if I remember right, your witness server can be SQL Express running on very cheap hardware (desktop class PC even if you need). As far as I know, there is no way to do automatic failover without using either clustering or mirroring with a witness.

See http://msdn.microsoft.com/en-us/library/ms189590.aspx and http://msdn.microsoft.com/en-us/library/ms175191.aspx for more info.

Snip taken from the 2nd url: "We strongly recommend that the witness reside on a separate computer from the partners. Database mirroring partners are supported only by SQL Server 2005 Standard and later versions and by SQL Server 2005 Enterprise Edition and later versions. Witnesses, in contrast, are also supported by SQL Server 2005 Workgroup and later versions and by SQL Server 2005 Express Edition and later versions. A witness can run on any reliable computer system that supports any of these editions of SQL Server. However, we recommend that every server instance that is used as a witness correspond to the minimum configuration that is required for the SQL Server Standard version that you are running."

Answer 2

Under the terms of Microsoft's licensing for SQL Server 2005/2008, you only need one license (per CPU) for your primary SQL server.

You don't have to buy an additional SQL server license for the mirror because the mirror server only communicates with the primary.

In the event of a failover, you have 30 days to failover back to the primary, otherwise you would then need to purchase additional licenses.

Here is the link to the licensing details, refer to Passive Servers / Failover Support on page 2.

Microsoft's Licensing Overview...