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

User redirection in DB Mirroring Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 11:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 6:33 AM
Points: 11, Visits: 37
I have configured DB Mirroring with automatic fail over.My users are connected to Server_A .My Partner is Server_B and witness is Server_C.As we know that whenever a fail over occurs Server_B will take the roll from Server_A and it becomes Online.But my point is how the users are redirected to Server_B.
Is it the DBA who changes the connection string or the Application Developer or the Database Developer.How ? What happens behind the screen.Please explain this with details..
Post #1487642
Posted Friday, August 23, 2013 2:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 610, Visits: 571
'Out of the box' its a manual process, if you have server names in the connect string for your app pointing at serverA then it will need to be manually changed to ServerB (My vote is App support should be responsible for this, but it will vary between companies).

One (of many i'm sure) way to potentially make this simplier could be to use a DNS alias for the application connect string, so when you fail over, change the DNS entry to point at ServerB...although would still be a manual process, its only a single change to make.

Rod
Post #1487677
Posted Friday, August 23, 2013 2:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 858, Visits: 2,503
usually, the app developer will put a "failover partner" in the connection string for the application. When the primary goes offline, e.g. the server blows up, and the application cannot connect to the database, it will try the server specified by the failover partner.

I'd personally only use DNS redirection when you don't have automatic failover setup.
Post #1487689
Posted Friday, August 23, 2013 2:44 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 610, Visits: 571
Very good point, The failover partner makes much more sense when witnesses are being used. We tend not to have auto failover due to our network 'setup'.

Rod
Post #1487694
Posted Friday, August 23, 2013 2:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 20,861, Visits: 32,887
skpani (8/22/2013)
I have configured DB Mirroring with automatic fail over.My users are connected to Server_A .My Partner is Server_B and witness is Server_C.As we know that whenever a fail over occurs Server_B will take the roll from Server_A and it becomes Online.But my point is how the users are redirected to Server_B.
Is it the DBA who changes the connection string or the Application Developer or the Database Developer.How ? What happens behind the screen.Please explain this with details..


There should be no need to modify the connection string due to a failover from principal to mirror. If the connection string is setup to include both the principal and failover servers, the application itself will manage the switch as needed with no outside intervention required.

The only time the connection string would need to modified would be if the connection string contained only the server name for the principal server and no partner server was included and it was the first time a client was attempting to connect and the named server was unavailable.

Read more here:

http://msdn.microsoft.com/en-us/library/ms366348(v=SQL.100).aspx



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1487695
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse