DB Mirroring copies all the objects of a database from the main server to the mirror server (the mirrored or replicated server).
In any case, this doesn’t copy the logins from the main server to the mirror one, which means that it can’t connect to the mirror server database after the failover.

To solve this problem we need to know the SID of the login on the main server.
Once we know the SID of the login, we can create a new one on the mirror server using this SID.

To know the SID of the main server login we must run the following statement on the main server:

SELECT sid  FROM sys.server_principals where name = ‘<LoginName>’

To create the login on the mirror server using the SID of the main server, we can run the following sentence on the mirror server:

CREATE LOGIN <LoginName> WITH PASSWORD = ‘<Paswword>’ sid = <sid for same login on principal server>

Remember that the <LoginName>, the <Password> and the <SID of the login on the main server> must be exactly the same ones on both servers: main and mirror.

Finally, the application’s connection string must be changed including the “failover partner” parameter:

Data Source=myServerAddress;
Failover Partner=myMirrorServerAddress;

Initial Catalog=myDataBase;
User Id=sqluser;
Password=sqlpassword;

TAGS: DB Mirroring, Failover, how-to, Login, Microsoft QL Server, Mirror server, SID

speech-bubble-13-icon Created with Sketch.
Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*