Database Mirroring FAQs

Question:

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)

Answer:
This is a common error & everyone is know to this error. Database mirroring is officially supported from SQL Server 2005 SP1, hence in the RTM version database mirroring is disabled by default. You can use TRACE 1400 to enable in RTM version or else update your SQL Server with latest service pack.
Adding Trace Flag to Startup parameter

• Goto RUN --> Type sqlservermanager.msc
• Right click on SQL Server(instancename) service and click on properties
• Click on Advanced tab
• In the startup parameters enter this ;-T1400 and click on OK
• Restart SQLservices and then try configuring db mirroring
Or
• Update SQL Server to latest service pack.

Question:

When I configure mirroring I'm receiving 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

Answer:

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 = N'TCP://A.corp.mycompany.com:5022';
-- Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N'TCP://B.corp.mycompany.com:5022';

Question:

Why I'm getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring

Answer:

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.

Question:
IS it possible to configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

Answer:
Nope its not possible, both principal and mirror should have same edition. Refer the KB article http://msdn.microsoft.com/en-us/library/ms366349.aspx

Question:
Can I load balance my mirrored database (i.e can i use mirrored for SELECT query) like log shipping in standby mode?

Answer:
Yes offcourse its possible if your mirror server is running Enterprise edition. You can take snapshot of your mirrored database and then you can query against the snapshot to retrive data's.

Question:
Is it possible to take backup of mirrored database in mirror server?

Answer:
No, you won't be able to run BACKUP command against a mirrored database in mirror server.

Question:
Why I'm getting login failed error after failovering to mirrored database?

Answer:
This is because there might be mismatch in SID between the logins & user in database else the login doesn't exist in the mirror server.

Consider you have a login test in principal server and this test also exists in the principal database as a user. When you mirror the database, in the mirror server that database will be in restoring state and user test will have the same SID in the database on mirror server as in principal server. Once you have failovered to mirror server and mirror database is become principal, if you try to login with the test ID and then if you connect to the database you will be getting login failed because the test login in the mirror server will have different SID and the database user test will have different SID (i.e principal server login SID), hence to resolve this conflict you need to use sp_change_users_login procedure to map the original SID of the login to db users SID. If the login doesn't exist in the mirror server then you need to create a new login in the same name and map the SID back using the above procedure.

Consider the above situation happens in your production environment which will increase the downtime (eventhough mirroring is configured in High availability mode) since you need to map the logins back. Hence the best way to avoid this situation is that before configuring mirroring copy the logins (i.e the logins used for the application and have access to the mirror db) from the principal server to destination server with same SID. You can use Transfer Logins Task SSIS utility or else use How to transfer logins and passwords between instances of SQL Server

Question:
Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.

Answer:
No, thats not possible. You can create only one endpoint in a server for database mirroring you need to use this endpoint to configuring db mirroring for all the databases.

Question:
What is the recommended maximum number of mirror database I can configure for an instance.

Answer:
For 32-bit system MS recommends not more than 10 database to participate in database mirroring, refer link. There is actually no limit on this you can configure many databases, but adding more and more databases into mirroring will hurt the server performance and network IO, refer the link "How many databases can you *really* mirror per instance?"

Question:
I don't have a witness server, Incase if primary server goes down how can I failover the mirrored database.

Answer:
The best thing I can suggest you is to refer the link Failover in Database Mirroring to know the steps for manual failover.

Question:
Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping.

Answer:
No, thats not possible in Database mirroring, its one to one configuration.

Question:
How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec and I need to change this (this fills up eventlog too)

Answer:
You can use the below command to change the heartbeat time between principal and mirror. Execute the below script in principal server
Syntax
ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT <TimeOutInSeconds>
Example
ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30

Question: Is High performance mode supported in Standard Edition of SQL 2005?

Answer: Sql Server 2005 Standard edition supports only Full transaction safety level i.e it does not support High performance mode (Asynchronous).

Question: Where can I get database mirroring features supported by various editions of SQL Server 2005???
Answer : You can check the subtopic "Database Mirroring and SQL Server 2005 Editions" in the link "Database mirroring in SQL Server 2005"