Can I use Failover Partner in my SQL connection string without mirroring?
failover, mirroring, replication, sql-server
Solution
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.
Problem
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!