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

I was also searching for an answer to the original question about whether FailoverPartner is supported in non-mirrored environments. In our environment, there is a secondary database that is synchronized via replication.

To test, I setup the following connection string within an SSIS OLEDB source:

Data Source=PRIMARY;FailoverPartner=SECONDARY;Provider=SQLNCLI10.1;
                            Network=dbmssocn;Database=myDB;User ID=user;Password=password;

When I run, I see the following error

The connection attempted to fail over to a database which is not configured for database mirroring.

So it appears to be not possible unless the secondary server is actually in a mirror configuration.

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...