SQL-Articles

Introduction:

I could see most of them are asking known errors of database mirroring in many forums and we are repeatedly providing the same solution. Hence I thought of consolidating all the know errors and its solutions for database mirroring in this post as FAQ. Probably I must pickup all the know errors, If I have missed anything you can very well post in FORUMS section.

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 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. Refer the article Database Snapshot.

—————————————————

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

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

—————————————————

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“ 

—————————————————

General, Troubleshoot Database Mirroring

Introduction

In most of the forums I’m seeing a thread how to move databases from one server to another server. Seeing those things I’ve written this article to simple the task. Here in this article I’m going to provide you some steps to move all the databases (including system db) from one server to another server (hope no error should happen).

Steps to Move the database

Step1: Before detaching the db you need to script default database for all the logins, because if the user db is default db for logins then after detaching the db the login will point NULL and hence when the user logs in he will be in trouble. You can use the script “Script Default Database” to script all the default databases for the logins. Execute the script and save the output.

Step2: Once the above step completed you can detach all the user databases except system db’s (master, msdb, tempdb & model) in the source server. You can use the script “Detach User Databases” to detach all the user databases in the source server.

Step3: Stop SQL Service in the source server and copy all physical files (mdf, ldf & ndf) of the dbs to the destination server

Step4: Once the above step completed successfully, you need to first attach the system databases databases from the new path (i.e the path where you have copied the physical files). You can make use the articles “Moving System Databases - SQL Server 2005” or “Moving system databases — SQL Server 2000” to attach the system databases from new path.

NOTE: For moving system dbs you need to have same SQL edition, version and build in both source and destination servers. If you are not planning to move system db’s just skip this step.

Step5: Attach all the databases in the destination server from the new path.
Step6: Once the above steps completed successfully, start sql services, check errorlog for any errors.
Step7: Once you find everything perfect execute the script (saved in step1) to map all the logins back to its default databases and start using the applications with the respective FIDs.

Discussion

All your healthy discussion in this topic are most welcomed @ FORUMS section.

General Administration, SQL Server

I got a message from Microsoft today that I was awarded as MVP. I am now proud to say that I’m part of few Microsoft Most Valuable Professional’s (MVP) :).
I want to thank everyone who participated in my nomination as a Microsoft MVP in SQL Server. I am very thankful and gracious to have been awarded such a prestigious honor especially regarding a product that I have thoroughly enjoyed working with. I will continue to strive to bring information forward so that everyone may benefit.

You can view my profile @ VidhyaSagar’s MVP Profile

In this fortunate moment I would like to thank Mr.SugeshKumar who inspired me to get this valuable award!

Personal Personal