Shulou(Shulou.com)06/01 Report--

SQL Server database mirroring is a software solution for database availability. Mirroring is deployed at each database level and can only work in the full recovery model. Due to disk space problems, you need to move the mirror database to a different location. We want to complete this task without downtime or destroying the image. Tests need to be done in different environments.

We have only limited options for file movement in databases with database mirroring enabled. The general methods are as follows:

Break the database mirroring session and move the online database files to a new location by using Alter database or Attach Detach.

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

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

Given downtime is always considered by the client, we have to find a non-downtime solution. The following steps explain how to move database files without interrupting synchronized database mirroring without downtime.

For mirror instances:

Optionally, pause mirroring on the primary server.

Use the Alter database statement on the mirror server to point to a new location.

Stop the mirroring SQL Server service.

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

Start the mirror SQL Server service.

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

For the primary instance:

Fail over the database to the mirror server, so that the mirror server is now the primary server.

Optionally, pause mirroring on the new primary server.

Use the Alter database statement on the new mirror server to point to a new location.

Stop the SQL Server service for the new mirror.

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

Start the SQL Server service for the new image.

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

If you look at the above plan in detail, you can see that the application session is reconnected during a mirror 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 the mirroring SQL Server service. So there is no downtime.

However, you need to make sure that there is enough log space on the primary server, because the mirrored state will be disconnected (not just one library, but all mirrored databases on the instance). When the mirror state is disconnected, log records are not sent from the primary server to the mirror server and will accumulate on the primary server. Once the mirror instance starts and the mirror state becomes synchronized, the primary server will start sending log records to the mirror server.

We can verify that the modification is successful by checking the file locations of all mirror 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 not null) order by 1

In general, database mirroring can be kept without downtime when the database is moved. No action is required for the witness and remains online during the activity. First of all, this scheme should be verified in the test environment and then implemented in the production environment. It is very important to note that in asynchronous mirroring mode, we can also refer to this practice, but it needs to be implemented in the case of application downtime.

The location of the data or log files of the mirror database will be moved in the following application scenarios:

Insufficient disk space

On the database server, there is not enough disk space for the mirrored database, so you can migrate MDF or LDF files to a new disk.

Accelerate the mirror database to catch up with the main database

After the master database recovers from the downtime, it becomes the mirror database of the new master database. In order to accelerate the mirror database to catch up with the master database, you can move the data and log files to a better performance disk, such as SSD.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.