Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Adding a new article to existing publication(That subscribers have not been generated based on snapshot) in SQL SERVER 2008 R2 Expand / Collapse
Posted Thursday, August 7, 2014 12:32 PM


Group: General Forum Members
Last Login: Friday, February 13, 2015 5:52 PM
Points: 185, Visits: 748
Hi Everyone,

We have mirroring databases for our disaster recovery. Due to that all of our connection strings in our SSIS packages have 'Failover partner' set to mirror side. By mistake in one of config files the Data source was poiting to Mirror database and Failover partner was pointing to Principle one.

This Weekend mirrored server was shut down for maintance purpose and all the jobs that were using that config file started to fail. I was thinking by having the failover partner setup in connection string all the jobs should continue to work, but this did not happen. Would you please somebody explain why this happened?


Post #1600817
Posted Saturday, August 16, 2014 1:50 AM


Group: General Forum Members
Last Login: Saturday, April 11, 2015 5:26 AM
Points: 24, Visits: 198
I was thinking by having the failover partner setup in connection string all the jobs should continue to work, but this did not happen.

Mirroring protects the server and the database from failing, not the client. How the client behaves depends upon the client (even when the failover partner is configured correctly). The errors a client raises should be diagnostic.

From BOL
If the specified server instance is available and is the current principal server instance, the connection attempt typically succeeds.
If that attempt does not work, the data access provider tries the failover partner name, if available. If either partner name correctly identifies the current principal server, the data access provider normally succeeds in opening the initial connection. On completing this connection, the data access provider downloads the server instance name of the current mirror server. This name is stored in the cache as the failover partner name, overwriting the client-supplied failover partner name, if any. Thereafter, the .NET Framework Data Provider for SQL Server does not update the failover partner name. In contrast, SQL Server Native Client updates the cache whenever a subsequent connection or connection reset returns a different partner name.

What data provider(s) is/are the SSIS packages using? Could the "Thereafter" sentence explain the errors I imagine the client was raising? What complete & verbatim errors were being raised?
Post #1604020
Posted Monday, August 18, 2014 3:45 AM



Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 2,555, Visits: 1,141
Is your issue with Replication or Database Mirroring?


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1604328
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse