2

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.

2
  • Are you trying to start SQL Mirroring between 2 servers, or with 2 servers and a mirror (high availability)? May 25, 2009 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, 2009 at 23:30

2 Answers 2

3

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.

0

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;

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .