DBMirroring Interview Questions

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';

Note: Replace the dbname before using the above script

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 articlehttp://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. You can refer the article How to fix orphaned SQL Server users.

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:
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

Note: Before using the script change the dbname.

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).

Advantages Database Mirroring:

• Database Mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimized data loss.
• It has automatic server failover and client failover mechanism.
• Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
• Because propagation can be done asynchronously, it requires less bandwidth than synchronous method (e.g. host-based replication, clustering) and is not limited by geographical distance with current technology.
• Database mirroring supports full-text catalogs.
• Does not require special hardware (such as shared storage, heart-beat connection) and clusterware, thus potentially has lower infrastructure cost

Disadvantages of Database Mirroring:

• Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
• Mirror server/database is not available for user operation.
• It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
• Automatic server failover may not be suitable for application using multiple databases.