Monday, July 23, 2012

Database Mirroring - Common Errors


Database Mirroring Errors

The following errors are the commonly occuring in 
database mirroring and hence 
I thought of collecting them and providing solution 
as well so that it might 
benefit others
Error:
Login Failure connecting to new principal server after failover
Error: 18456 Severity: 14 State: 16
The problem is that after performing failover the
 original mirror database becomes 
the new principal. We can connect successfully to 
the mirror using Windows login.
But we are unable to connect to the new principal database with 
Sql login as we get the below,
Cannot open database "" requested by the login. The login failed.


Solution:
This problem occurs because the SIDs (security identifiers) 
for the SQL Server logins
 on each server do not match. Although the names 
for the logins are the same, the 
login is resolved via the SID. This is not a problem 
with Windows/Domain user/group
 logins because the SIDs for these logins are created 
based on the domain SID for
 the user/group, and hence will be the same for the 
same given user/group.


1. You can either run the procedure sp_change_users_login
 to map the user
 and login (or )
2. At the time of creating logins in the mirror server, you
 will need to create 
the SQL Server logins on the mirror server not only with 
the same name, but
 also with the same SID as on the principal server.


Execute the below command in Mirror server to create the 
same login as in 
original principal server with the same SID as in the original 
principal server


Create Login "login name" With Password = "Password" , 
SID = "Sid of the same
 login from original principal server"


You can obtain the SID of the same login from original
 principal server using,


select name,sid from sys.sql_logins where name ='Login name'
Error:When you configure mirroring you might receive
 the below errror,
One or more of the server network addresses lacks a 

fully qualified domain name 
(FQDN). Specify the FQDN for each server, and click start 
mirroring again

Solution:

Assume that your principal server is A and mirror server is
 B and you have configured 
mirroring for Adventure Works database. The fully qualified 
computer name of each
 server can be found running the following from the
 command prompt:

IPCONFIG /ALL

Concatenate the "Host Name" and "Primary DNS Suffix". 
If you see something like:
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com
Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':
and you then have the partner name.


-- Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER =
 'TCP://A.corp.mycompany.com:5022';

-- Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER =

 'TCP://B.corp.mycompany.com:5022';

Pls replace your database name accordingly in the above command


Error:
Msg 1416, Level 16, State 31, Line 3. Database is not configured 
for database mirroring
Solution: 
You need to restore the Full backup from principal server 
using With NoRecovery 
option and also one transactional log backup from principal 
server using With
 NoRecovery 
option and then start configuring mirroring.