August 7, 2014 at 12:32 pm
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?
Regards,
AG
August 16, 2014 at 1:50 am
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 http://msdn.microsoft.com/en-us/library/ms175484.aspx
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?
August 18, 2014 at 3:45 am
Is your issue with Replication or Database Mirroring?
qh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply