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 ««123»»

Mirroring failover : SQL Server 2008 R2 Expand / Collapse
Author
Message
Posted Sunday, May 12, 2013 2:41 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
I have to ask again, please provide a step by step description of everything you have done, from setting up the mirror to testing the failover and what you have to do upon failover. Provide all the SQL you use (removing/replacing those things that are proprietary or sensitive).



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 #1451935
Posted Monday, May 13, 2013 11:04 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: Tuesday, September 16, 2014 8:43 AM
Points: 820, Visits: 1,175
Thank you very much Lynn for asking me to write the step by step script for you to replicate issue on your side.

While I was doing that I just realized that the default database for the Application user being as "Master".
So anytime the failover was happening the user was able to connect to Master but not the User DB.

I corrected it and it started working as expected.
Thanks you very much again.
Post #1452233
Posted Tuesday, May 14, 2013 4:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 2,059, Visits: 1,431
Neeraj Dwivedi (5/13/2013)
...While I was doing that I just realized that the default database for the Application user being as "Master".
So anytime the failover was happening the user was able to connect to Master but not the User DB.

I corrected it and it started working as expected.
Thanks you very much again.


Glad you've resolved the issue, but if the login and user was scripted from Production, I don't see how you could have got into this situation in the first place.

Regards
Liam
Post #1452484
Posted Tuesday, May 14, 2013 7:00 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: Tuesday, September 16, 2014 8:43 AM
Points: 820, Visits: 1,175
Please find attached the step by step script to reproduce the issue. What I realized that unless you explicitly specify DEFAULT_DATABASE=your Mirrored DB, it does not work.


  Post Attachments 
Set Up Mirroring Script.rtf (10 views, 103.13 KB)
Post #1452548
Posted Tuesday, May 14, 2013 7:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 2,059, Visits: 1,431
Thanks for providing the steps. If you had run step 3 on the Mirror, that would've prevented the issue I think - I will test it out when I get time.

Regards
Lempster
Post #1452561
Posted Tuesday, May 14, 2013 7:35 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: Tuesday, September 16, 2014 8:43 AM
Points: 820, Visits: 1,175
Step 3 is for principle. You can not run that on Mirror since it is in restoring mode.
Post #1452569
Posted Tuesday, May 14, 2013 7:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 2,059, Visits: 1,431
Sorry, I wasn't clear, I meant that you need to run Step 3 on the Mirror before you activate the mirroring session.

Actually, just thinking about this some more, when you manually failover, is the user present in the database, but as an orphaned user? If so, you can just run
sp_change_users_login @Action='update_one', @UserNamePattern='your_user', @LoginName='your_login'

Post #1452573
Posted Tuesday, May 14, 2013 7:53 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: Tuesday, September 16, 2014 8:43 AM
Points: 820, Visits: 1,175
I think I am missing something. When you restore DB in mirror, you do that with recovery switch enable. SO it will always be in restoring mode.
Post #1452590
Posted Tuesday, May 14, 2013 7:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 2,059, Visits: 1,431
Yes, see my edited post.
Post #1452592
Posted Tuesday, May 14, 2013 7:59 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: Tuesday, September 16, 2014 8:43 AM
Points: 820, Visits: 1,175
Yes. That was what I was doing. But I wanted the fail-over to be seamless. Without getting me involved at any time.
Post #1452597
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse