Category Archives: Mirroring

How to move data files of the mirror database

Posted on by

The ideal scenario for database mirroring would be to have the principal and secondary server instances be exact clones of each other from a hardware and disk space standpoint. This would ensure that the performance on the mirror instance is the same as the principal instance. But due to multiple constraints, this is not always possible. I recently had a question on how to move a secondary data file to a different physical location on the mirror instance. The catch here is that the physical data file locations on the principal and mirror instances are not the same. Now this can be tricky if not attempted or tested in your environment. The Microsoft SQLCAT(Twitter|Blog) team has a blog post on how to move the transaction log of a mirrored database to a secondary location. We shall use the same logic for the data file as well.

Steps

  1. If the server on which the data file relocation has to be done is not the mirror server currently, then failover the mirrored database so that the server on which the OS File has to be physically moved is the current Mirror.
  2. Change to High Performance mode if running under High Safety.
  3. Execute the ALTER DATABASE statement to move the data file to the new location on the mirror instance.
  4. Pause Mirroring to prevent any automatic failover while OS File Copy is happening.
  5. Stop the Mirror instance.
  6. Copy the file across to the new location (OS File Copy).
  7. Start the Mirror Instance.
  8. Resume mirroring and switch back to High Safety if the mirroring was running under High Safety. The mirroring should continue without any issues. Failback to original server if needed.

If the data file locations are the same on the principal and mirror and the data files need to be moved to different locations on both the server instances, then this is much easier. You would need to fire the ALTER DATABASE command on the principal server instance and then move the data files to the new location after failing over the database. The new data file location would take effect only after the server instance is restarted.

CU10 is a must for all instances where mirroring is being used

Posted on by

If you happen to use Database Mirroring on SQL Server 2005, then Cumulative Update 10 for SQL Server 2005 Service Pack 3 is a must for your instances. There are 2 database mirroring related fixes in this cumulative package. I would suggest that all SQL Server 2005 instances that have database mirroring enabled need to have this update installed ASAP.

982933 (http://support.microsoft.com/kb/982933/ )
Error message when you shrink data files on principal in a Database Mirroring for two SQL Server 2005 servers

983480 (http://support.microsoft.com/kb/983480/ )
FIX: Various issues occur when a database is involved in a database mirroring session and in a transactional replication if trace flag 1448 is enabled

983500 (http://support.microsoft.com/kb/983500/ )
FIX: The role switch is delayed when a mirroring automatic failover occurs in SQL Server 2005

How to generate an Automatic Failover for a Mirrored Database

Posted on by

I have heard this question multiple times:

How do I generate a condition which will cause an Automatic Failover of a SQL Server Mirrored database?

First of all, you would need to setup mirroring under Full Safety mode with a Witness instance.

The only condition that triggers an automatic database failover would be if the Mirror and Witness instance are unable to connect to the Principal server instance.

Figure 1

Figure: Synchronized database mirroring session

As per the TechNet article, Automatic Failover would be under the following condition:

When safety is FULL, if neither the mirror nor the witness can see the principal, the mirror server can form a quorum with the witness and change its role to become a new principal server, assuming a the mirroring session was SYNCHRONIZED when the principal went away.

So, the condition above is self explanatory.

How can this be done?

1. If your Witness and Mirror are on different boxes or on the same box and the Principal instance is on a different box. Then:

  • Stop the Database Mirroring endpoint using the following command on the Principal instance: ALTER ENDPOINT <mirroring endpoint name> STATE = STOPPED. Note: This would put the database mirroring into a disconnected state. To bring database mirroring out of disconnected state, you need to start the endpoint using the following command: ALTER ENDPOINT <mirroring endpoint name> STATE = STARTED
  • Plug out the network cable for the Principal box provided the Principal and (Mirror & Witness) instances are on two different boxes.
  • Block all TCP traffic on the port that the server has established the Mirroring session.
  • Pause the Principal Instance service (Have not tried this one yet but theoretically it should work!)

2. Attach a Visual Studio Debugger or Windows Debugger to the Principal Server instance. Do not hit GO to continue the execution of the process being debugged.

THE ABOVE METHODS SHOULD ONLY BE TRIED ON A TEST ENVIRONMENT. THESE ARE NOT SOMETHING THAT SHOULD BE ATTEMTPED ON A PRODUCTION BOX.

This is just an informational article for simulating automatic database failover for testing any alert jobs or any other business logic that you need to kick off during an automatic failover.

What messages would be present in the SQL Server Errorlogs?

When the Automatic Failover is initiated, it would have the following messages printed in the SQL Server Errorlog of the Mirror instance:

2010-04-19 22:08:03.590 spid15s      Error: 1479, Severity: 16, State: 1.
2010-04-19 22:08:03.590 spid15s      The mirroring connection to “TCP://<FQDN>:5022” has timed out for database “dbMirror” after 10 seconds without a response.  Check the service and network connections.
2010-04-19 22:08:03.590 spid15s      Database mirroring is inactive for database ‘dbMirror’. This is an informational message only. No user action is required.
2010-04-19 22:08:03.590 spid15s      The mirrored database “dbMirror” is changing roles from “MIRROR” to “PRINCIPAL” due to Auto Failover.
2010-04-19 22:08:03.720 spid15s      Recovery is writing a checkpoint in database ‘dbMirror’ (15). This is an informational message only. No user action is required.

The Witness instance would have the following messages in the Errorlog:

2010-04-19 22:08:03.360 spid25s      Error: 1479, Severity: 16, State: 1.
2010-04-19 22:08:03.360 spid25s      The mirroring connection to “TCP://<FQDN>:5022” has timed out for database “dbMirror” after 10 seconds without a response.  Check the service and network connections.

On the Principal server, the system event logs might report that the SQL Server service is unresponsive based on the Service Timeout value configured on the machine (Default is 30 seconds):

Log Name:      System
Source:        Service Control Manager
Date:          4/19/2010 10:04:02 PM
Event ID:      7011
Task Category: None
Level:         Error
Keywords:      Classic
Description:
A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.

After the Principal instance comes back online, the following messages would be printed in it’s Errorlog:

2010-04-19 22:28:19.190 spid26s      Error: 1474, Severity: 16, State: 1.
2010-04-19 22:28:19.190 spid26s      Database mirroring connection error 4 ‘An error occurred while receiving data: ’64(The specified network name is no longer available.)’.’ for ‘TCP://<FQDN>:5023’.
2010-04-19 22:28:19.200 spid27s      Error: 1474, Severity: 16, State: 1.
2010-04-19 22:28:19.200 spid27s      Database mirroring connection error 4 ‘An error occurred while receiving data: ’64(The specified network name is no longer available.)’.’ for ‘TCP://<FQDN>:5024’.
2010-04-19 22:28:19.360 spid20s      Database mirroring is inactive for database ‘dbMirror’. This is an informational message only. No user action is required.
2010-04-19 22:28:19.490 spid35s      Starting up database ‘dbMirror’.
2010-04-19 22:28:19.530 spid20s      The mirrored database “dbMirror” is changing roles from “PRINCIPAL” to “MIRROR” due to Role Syncronization.
2010-04-19 22:28:19.590 spid35s      Bypassing recovery for database ‘dbMirror’ because it is marked as a mirror database, which cannot be recovered. This is an informational message only. No user action is required.
2010-04-19 22:28:19.810 spid20s      Database mirroring is active with database ‘dbMirror’ as the mirror copy. This is an informational message only. No user action is required.
2010-04-19 22:28:45.440 spid20s      Database mirroring is inactive for database ‘dbMirror’. This is an informational message only. No user action is required.

If you notice above, you will see that even though the Principal was not available during the failover, it assumed the role of the Mirror after it came back online. This Role Synchronization is what keeps the Principal and the Mirror database status updated to prevent Split Brain scenarios.

References:

SQL Server Best Practices Article

Database Mirroring Best Practices and Performance

Database Mirroring with SQL Authentication

Posted on by

It’s quite fascinating that a database can be mirrored across two sites and a front-end application will not have a clue about a failure due to a failover partner string specified in the connection string of the application.

Database mirroring was a new feature introduced in SQL Server 2005 which has seen a sizeable amount of implementation over the years. What people have not noticed is how the application connects to the database on two completely different servers w.r.t. authentication. Most organizations use Windows Authentication and after a database failover, users are able to connect seamlessly. When SQL Authentication is used to connect to the mirrored database after a failover and this login only has logon rights on the mirrored database, then it becomes a different ballgame altogether.

SQL Server stores the information of a login using a SID value. The SID value for Windows Authentications (Domain Accounts) are the same throughout the environment hence a problem never arises here when the database is failed. But for SQL Login. Rule of thumb tells us that the login used to login to a mirrored database should exist on both instances which hosts the principal and mirror databases. So, what happens when you use a SQL Authentication after a failover?? Since, SQL Server relies on SIDs to map a database user to a SQL login. You can find the information under sys.database_users and sys.syslogins catalog views. Since, after a failover the SIDs for a SQL Authentication is bound to have a mismatch (as SQL Authentication SIDs are machine specific and not something derived from the AD as in the case of Domain Accounts), you will start getting login failed or login timeout period expired for SQL Authentication.

To resolve such an issue make sure that the SID of the SQL Authenticated user is the same on both the principal and mirror server instances.

So, if you have run into an issue of having orphaned logins after failing over your mirrored database, then you can follow the steps below to resolve the issue:

1. Use the sp_help_revlogin script to script out the SQL Login

2. Drop the user on the mirrored instance and recreated it using the script obtained above

3. You would have to failover and re-map the SQL login to the database user under user mappings because if you have a database user and drop the SQL login associated with it , then the database user becomes orphaned.

This is documented both in the whitepaper on Database Mirroring on Technet as well as Books Online which talks about resolving orphaned users issue after a database failover.

Related Article:

http://blogs.msdn.com/sqlserverfaq/archive/2009/04/13/orphaned-users-with-database-mirroring-and-log-shipping.aspx