SQL Server does not stop moving a mirrored database

SQL Server database mirroring is a software solution for database availability. Mirroring is deployed at each database level and only works under the full recovery model. Because of disk space problems, you need to move the mirrored database to a different location. We want to do this without stopping and destroying the mirror. Testing needs to be done in a different environment.

We have only a limited selection of files for database mirroring-enabled databases. The general method is as follows:

• Break the database mirroring session by using ALTER DATABASE or attach detach to move the online database files to a new location.

• Back up the database, restore the backup on the mirror server, and then rebuild the mirror.

Technically, this is possible, but it requires downtime and, especially for large databases, it takes a lot of extra time to move and recover.

A given downtime is always considered by the client and we have to find a solution that does not stop. The following steps explain how to move a database file without interrupting synchronization of database mirroring.

For mirrored instances:

1. Pause mirroring (optional) on the primary server.

2. Use the ALTER DATABASE statement on the mirror server to point to a new location.

3. Stop mirroring the SQL Server service.

4. Move the mirrored database file to a new location, and make sure that the permissions on the file are still there.

5. Start the mirrored SQL Server service.

6. Restore the mirror on the primary server database and verify that the mirror was successfully recovered.

For the primary instance:

1. Failover database to the mirror server so that the mirror server is now the primary server.

2. Pause mirroring (optional) on the new primary server.

3. Use the ALTER DATABASE statement on the new mirror server to point to a new location.

4. Stop the new mirrored SQL Server service.

5. Move the new mirrored database file to a new location, and make sure that the permissions on the file are still there.

6. Start the SQL Server service for the new mirror.

7. Restore the mirror on the primary server database and verify that the mirror was successfully recovered.

If you view the above schedule in detail, you can see that the application session is re-connected during a mirrored database failover. When the application load is running on the primary server, stop mirroring the SQL Server service, physically move the database files, and then start mirroring the SQL Server service. So there is no downtime.

However, make sure you have enough log space on the primary server because the mirroring state will be disconnected (not just a library, but all mirrored databases on the instance). When the mirroring state is fractured, log records are not sent from the primary server to the mirror server and will accumulate on the primary server. Once the mirror instance is started and the mirroring state becomes synchronous, the primary server will begin sending logging to the mirror server.

We can verify the success of the modification by checking the file location of all the mirrored databases with the following T-sql:

Select Db_name (dbid), name,filename from Sysaltfileswhere db_name (dbid) in (select Db_name (database_id) from Sys.database_mirroring where mirroring_state is isn't null) ORDER by 1

In general, database mirroring can be maintained without downtime when the database is moved. No action is required for the witness server, which remains online during the event. First, the program should be validated in the test environment and then implemented in the production environment. It is important to note that in asynchronous mirroring mode, it is also possible to refer to this practice only if the application outage is implemented and finally the main library is switched back.

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1904076

SQL Server does not stop moving a mirrored database