Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
samengr
Starting Member
1 Post |
 Posted - 2010-04-11 : 07:38:43
|
| Hi All,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 serversqlserver2: mirror serverlogin to DB1: testuseruser of DB1: testuser linked to testuser loginI created a "mirroring" user on both machines and sqlserver1 and sqlserver2 are started with this mirroring user likeSQLSERVER1 running Automatic .\mirroring SQLSERVER1 Agent running Automatic .\mirroringSQLSERVER2 running Automatic .\mirroring SQLSERVER2 Agent running Automatic .\mirroringThe 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"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. 7. 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). 8. When I recreate the login and user for DB1 on sqlserver2 then I can connect to this DB 9. 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 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-05-23 : 12:20:02
|
| Database Mirroring is at database level not Server Level. You will map the logins considering the fact that logins exists in both servers. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-18 : 06:25:03
|
| you synchronize the users in the database to the logins after the mirror database goes online--------------------keeping it simple... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-18 : 08:18:56
|
| Better bet is to create the logins on the mirror server with the same SID as on the principal. Then when you fail over the users are mapped and there's nothing that you need to do.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
SQL 2008 mirrroing issue … any help ??