SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring failover : SQL Server 2008 R2


Mirroring failover : SQL Server 2008 R2

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40000 Visits: 38564
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).

Cool
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)
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 1336
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.
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3227 Visits: 1657
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
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 1336
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.
Attachments
Set Up Mirroring Script.rtf (63 views, 103.00 KB)
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3227 Visits: 1657
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
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 1336
Step 3 is for principle. You can not run that on Mirror since it is in restoring mode.
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3227 Visits: 1657
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'


Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 1336
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.
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3227 Visits: 1657
Yes, see my edited post.
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 1336
Yes. That was what I was doing. But I wanted the fail-over to be seamless. Without getting me involved at any time.
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