SQL 2008 mirrroing issue .... ??

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.

full back of DB1 (Full mode)
Trn log backup of DB1 (Full mode)
Created the login details on the qslserver2 (mirror) server.
Restore the DB1 and Trn Log backup with no recovery mode. When I do this DB1 on mirror server shows DB1 Restoring mode.
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.
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.

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).
When I recreate the login and user for DB1 on sqlserver2 then I can connect to this DB
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.

asked Apr 09 '10 at 01:29 PM in Default

Sohail
11 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

is your testuser a SQ Login or a windows login?

answered Apr 12 '10 at 10:24 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Given that the servers are not in the same domain, that suggests you are using SQL logins. Have you checked that the logins on both servers have the same SID? If not, the mirror will contain an orphaned user.

Run sp_change_users_login 'Report' against the mirror (after failover of course) and it will list the login if this is your problem.

The solution is to drop and recreate the login on one of the servers so that they both have the same SID. Google for sp_help_revlogin. That will generated the TSQL for you.

answered Apr 26 '10 at 04:09 PM

Richard Fryar
1

Just noticed, your connection string is wrong. Instead of specifying integrated security, you need to specify the user name and password. That's assuming your initial statement about not being in a domain is correct.

Apr 26 '10 at 04:13 PM Richard Fryar

(comments are locked)