Define Failover SQL Server

Question

Hi,

We are begining to test some BC solution's for our SQL Server DB's, we have decided that where possible we will use DB Mirroring and for all other less critical or where DB mirroring is not possible to use log shipping.

I have setup two test SQL Servers to test log shipping to be able to document procedures, and also to establish what needs to change in our client connections to allow failover to the secondary server.

We have a mix of applications that include ASP Classic, ASP.NET, and ODBC. I have come accross that ODBC (when using SQLNCLI) has the ability to use a mirror server, and with ASP.NET you can define a failover partner.

Can anyone provide information on how we can achieve failover support for our ASP Classic applications, and can anyone confirm wether the SQLNCLI and ASP.NET failover partner works with SQL log shipping?

I have done some testing in ASP.NET with adding failover partner to the connection string however the application keeps querying the principal server which makes me think I am missing something or this is not supported in log shipping.

My ASP.NET connection string is:

<add name="test-BC_originalConnectionString2" connectionString="Data Source=primary;Failover Partner=secondary;Initial Catalog=test-BC_SQL;User ID=me;Password=passw" providerName="System.Data.SqlClient" />

I would greatly appreciate any assistance anyone is able to provide me.

If there is any further inforamtion required please dont hesitate in letting me know.

Thanks,

Matt

Answer 1

From the lack of responses and also finding nothing further on the internet, I can only assume that log shipping doesnt support this sort of client-side failover configuration.

I have found lots of references to DNS (too slow to update all clients), and load-balancing appliances (too expensive for the project) but not much else.

We are going to introduce DB Mirroring for tier 1 services and look at other alternatives such as log shipping for lower tier services which have a lower return to operation level that will allow us to change the application connection strings.

Thanks,

Matt