locked
SQL 2008 mirrroing issue ( DB users arent working) … ?? RRS feed

  • Question

  • Hiya,

    I am using MS SQL 2008 on MS SQL 2003 servers. I am using principal and mirror setup with out witness server.

    sqlserver1 and sqlserver2 arent part of domain. They are on the same vlan and can communicate to each other without having any firewall rules.

    sqlserver1: principal server
    sqlserver2: mirror server
    login to DB1: testuser
    user of DB1: testuser linked to testuser login

    I created a "mirroring" user on both machines and sqlserver1 and sqlserver2 are started with this mirroring user like

    SQLSERVER1 running Automatic .\mirroring SQLSERVER1 Agent running Automatic .\mirroring

    SQLSERVER2 running Automatic .\mirroring SQLSERVER2 Agent running Automatic .\mirroring

    The setup was quite straight forward.

    1. full back of DB1 (Full mode)
    2. Trn log backup of DB1 (Full mode)
    3. Created the login details on the qslserver2 (mirror) server.
    4. Restore the DB1 and Trn Log backup with no recovery mode. When I do this DB1 on mirror server shows DB1 Restoring mode.
    5. At this time I cant check the user (testuser) of the DB is linked to testuser login or not as the DB is in restoring mode on mirror server.
    6. On principal server set it up mirroring and it shows every thing fine. I can see the status: "Synchronized: the databases are fully synchronized"
    7. At this point I have tested through front end application I am able to connect to DB1 and can do every thing as testuser is a db_owner.
    8. Now when I do fail-over manually (means mirrored server become principal and the principal become mirror now) I am unable to connect to sqlserver2 (old mirror/new principal server)
    9. When I recreate the login and user for DB1 on sqlserver2 then I can connect to this DB
    10. The problem is when I do failover again sqlserver1 loss its db user connectivity.

    I dont know what I am doing wrong? May be missing any point?

    I am using doing automatic failover by ADO.Net String " Connection string should look like: Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True; "

    The problem I am facing is "When I do fail over manually on principal server for testing The user of the DB on mirrored server doesnt maintain its connectivity to the login of that DB. When i recreate user under mirrored DB it works fine (I can only do it when mirror server becomes principal server otherwise It doesnt allow me to do any thing as it shows in restoring mode ... but when I do failover again the other creat problem"

    Can anyone help me to sort out this problem? How users should be linked to both logins?

    Any response will be highly appreciated....

    Many thanks.

    Sam

    Sunday, April 11, 2010 11:51 AM

Answers

  • Hi Sam,

    This is a an issue which usually occurs whenever u do a manual failover for mirroring that all the logins are not transferred to the mirrored server, in case u not using automatic failover. This issue does'nt occur when the failover occurrs on its on i.e automatic failover. To fix this issue you can do a workaround wherein u create a script of the neccessary logins from principal server and run them on mirrored server after failover. There are basically orphan users in the mirrored server which u need to fix.

    http://msdn.microsoft.com/en-us/library/ms175475.aspx


    Apps Dev
    • Proposed as answer by Rojit Monday, April 19, 2010 10:08 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, April 20, 2010 12:46 AM
    Monday, April 12, 2010 1:21 PM
  • Apps Dev is correct.. This happens because you have created the login (which is serve scope) manually in two servers so that the SID will be different. Mirroring will copy the user (database scope) to the mirror server where it will point to the SID in principal server, where as in mirror it will be different, so create the ID in secondary server with the same SID.

    In the article below check the last section

    http://sql-articles.com/articles/high-availability/how-can-i-bring-mirror-database-online-after-principal-server-is-down-


    Vidhya Sagar. Mark as Answer if it helps!
    Friday, April 16, 2010 4:04 PM

All replies


  • Hiya,

    I am using MS SQL 2008 on MS SQL 2003 servers. I am using principal and mirror setup with out witness server.

    sqlserver1 and sqlserver2 arent part of domain. They are on the same vlan and can communicate to each other without having any firewall rules.

    sqlserver1: principal server
    sqlserver2: mirror server
    login to DB1: testuser
    user of DB1: testuser linked to testuser login

    I created a "mirroring" user on both machines and sqlserver1 and sqlserver2 are started with this mirroring user like

    SQLSERVER1 running Automatic .\mirroring SQLSERVER1 Agent running Automatic .\mirroring

    SQLSERVER2 running Automatic .\mirroring SQLSERVER2 Agent running Automatic .\mirroring

    The setup was quite straight forward.

    1. full back of DB1 (Full mode)
    2. Trn log backup of DB1 (Full mode)
    3. Created the login details on the qslserver2 (mirror) server.
    4. Restore the DB1 and Trn Log backup with no recovery mode. When I do this DB1 on mirror server shows DB1 Restoring mode.
    5. At this time I cant check the user (testuser) of the DB is linked to testuser login or not as the DB is in restoring mode on mirror server.
    6. On principal server set it up mirroring and it shows every thing fine. I can see the status: "Synchronized: the databases are fully synchronized"
    7. At this point I have tested through front end application I am able to connect to DB1 and can do every thing as testuser is a db_owner.
    8. Now when I do fail-over manually (means mirrored server become principal and the principal become mirror now) I am unable to connect to sqlserver2 (old mirror/new principal server)
    9. When I recreate the login and user for DB1 on sqlserver2 then I can connect to this DB
    10. The problem is when I do failover again sqlserver1 loss its db user connectivity.

    I dont know what I am doing wrong? May be missing any point?

    I am using doing automatic failover by ADO.Net String " Connection string should look like: Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True; "

    The problem I am facing is "When I do fail over manually on principal server for testing The user of the DB on mirrored server doesnt maintain its connectivity to the login of that DB. When i recreate user under mirrored DB it works fine (I can only do it when mirror server becomes principal server otherwise It doesnt allow me to do any thing as it shows in restoring mode ... but when I do failover again the other creat problem"

    Can anyone help me to sort out this problem? How users should be linked to both logins?

    Any response will be highly appreciated....

    Many thanks.

    Sam

    • Merged by Alex Feng (SQL) Tuesday, April 13, 2010 8:15 AM duplicated thread
    Sunday, April 11, 2010 11:45 AM
  • Your database principal testuser is no longer associated with the sql login testuser.

    On the principal server:

    select sid from sys.server_principals where name = 'testuser':

    then on the mirror server:

    create login testuser with password = 'xxx', sid = 'the sid goes here'

    Now the user and login will be associated even after a failover to the mirror.

    Monday, April 12, 2010 9:45 AM
  • Hi Sam,

    This is a an issue which usually occurs whenever u do a manual failover for mirroring that all the logins are not transferred to the mirrored server, in case u not using automatic failover. This issue does'nt occur when the failover occurrs on its on i.e automatic failover. To fix this issue you can do a workaround wherein u create a script of the neccessary logins from principal server and run them on mirrored server after failover. There are basically orphan users in the mirrored server which u need to fix.

    http://msdn.microsoft.com/en-us/library/ms175475.aspx


    Apps Dev
    • Proposed as answer by Rojit Monday, April 19, 2010 10:08 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, April 20, 2010 12:46 AM
    Monday, April 12, 2010 1:21 PM
  • 1. All changes to principle is getting transfer to mirror. 

    2. If not . check the endpoint whether STATE is STARTED else start it.

    Friday, April 16, 2010 1:24 PM
  • Apps Dev is correct.. This happens because you have created the login (which is serve scope) manually in two servers so that the SID will be different. Mirroring will copy the user (database scope) to the mirror server where it will point to the SID in principal server, where as in mirror it will be different, so create the ID in secondary server with the same SID.

    In the article below check the last section

    http://sql-articles.com/articles/high-availability/how-can-i-bring-mirror-database-online-after-principal-server-is-down-


    Vidhya Sagar. Mark as Answer if it helps!
    Friday, April 16, 2010 4:04 PM