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

Practice of moving master database files in asynchronous database mirroring

A database is deployed as asynchronous database mirroring, and there is not enough disk space for the main database. I have to move the main database file to the new disk.

In asynchronous database mirroring, the application connects to the primary database access. We have to consider which approach is appropriate according to your online environment:

1. Manually fail over to the mirror library, and then move the new mirror database file.

two。 Move the master database file directly.

In my opinion, if the business cannot tolerate long downtime, it is recommended to manually fail over to the mirror database and switch the connection string. If at this time, there are other online business databases on the instance where the new mirror library is located, it is recommended to redo the mirror and put the database files in a new location to put the business first. Wait for the image to be built, and then manually failover back, and the business will also point back.

Because our online business happens to have a downtime window. In the meantime, I move the main database directly. Next, let's first try to move the main database file directly during a business downtime.

1. Make a full backup on the primary database to avoid problems.

two。 Click "Pause" on the main library to pause the mirroring session.

3. Use Alter Database on the main library to point to a new location.

A) determine the logical file names of the WMS database and their current location on disk.

SELECT name, physical_nameFROM sys.master_filesWHERE database_id = DB_ID ('WMS'); GO

B) use ALTER DATABASE to change the location of each file.

USE master;GOALTER DATABASE WMSMODIFY FILE (NAME = FLUXWMS_DEMO, FILENAME ='H:\ SQL-LOG\ WMS.mdf'); GO

4. Restart the SQL Server service for the instance where the primary database resides. (here, as there are other business databases on the instance of the main database, be sure to consider the impact on other businesses. We choose the second method of moving mirror database files.)

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

6. Re-ONLINE the WMS database OFFLINE to implement the changes. (of course, it should be possible to restart the SQL Server service at this time, but if there are other business databases on the instance where the main database is located, it is recommended to operate at the database level.)

USE masterGOALTER DATABASE WMS SET OFFLINEALTER DATABASE WMS SET ONLINEGO

At this point, let's take a look at the database status.

On the main library:

It is found that the state is normal.

7. Click "RESUME" on the main library to resume the mirroring session. Verify that the mirror state is synchronized.

This also verifies that the operation of the Alter Database mobile database file modifies the master system database and will not be synchronized to the mirror database.

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.