Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding a new article to existing publication(That subscribers have not been generated based on...


Adding a new article to existing publication(That subscribers have not been generated based on snapshot) in SQL SERVER 2008 R2

Author
Message
Aspet Golestanian Namagerdi
Aspet Golestanian Namagerdi
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 829
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
SoHelpMeCodd
SoHelpMeCodd
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 709
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?
quackhandle1975
quackhandle1975
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2758 Visits: 1227
Is your issue with Replication or Database Mirroring?

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search