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
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.
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.
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....
is your testuser a SQ Login or a windows login?
answered Apr 12, 2010 at 10:24 AM