Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a peculiar scenario where my mirrored database can't be accessed on one server but it can on the other. I've tried specifying a specific SQL login to get it working but when the database is failed over the other server SQL no longer is able to use those credentials. I also tried using integrated security to use domain administrator account which I figured would be consistent between both servers. No luck. So what can I do to remedy this problem and is there a way to have the one server instance pick up the security accounts from the other. Both servers are in a trusted domain.

share|improve this question
    
Are you trying to start SQL Mirroring between 2 servers, or with 2 servers and a mirror (high availability)? –  Sander Rijken May 25 '09 at 22:14
    
High availability. they alreayd work in SQL I just can't log into the DB on the failed over server with the same credentials. –  Middletone May 25 '09 at 23:30

2 Answers 2

up vote 3 down vote accepted

SQL Server Database Mirroring is a database level high-availability mechanism. Anything that affects databases other than the one you're mirroring (ex. logins stored in Master) need to be manually propogated over to the mirror after the failover happens, or (more ideally) on a regular basis during scheduled maintenance.

If the logins are copied over and you're still running into issues, you're probably looking at a SID mismatch. Have a look here for more information and a resolution.

share|improve this answer

During any for of mirroring, only the principal is available, the mirror is offline.

In high availability mode there's also a witness, which is needed for auto failover. There need to be 2 nodes that agree on who the master is, to avoid both nodes become master.

You can use a connectstring like this to automatically connect to the active of the 2 servers:

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial
Catalog=myDataBase;Integrated Security=True;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.