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

Practice of moving mirror database files in asynchronous database mirroring

A database is deployed as asynchronous database mirroring, and the disk space of the mirrored database is insufficient. I have to move the mirrored 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:

Redo the mirror directly and put the data file in a new location.

Move the mirror database file directly.

All the operations of moving the database file are for the mirror database instance. If there is no other online master database on the mirror database instance, which will not affect the online business, you can move the mirror database file directly.

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

two。 Use Alter Database on the mirror library to point to a new location.

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

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

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

USE master;GOALTER DATABASE DXListingMODIFY FILE (NAME = DXListing, FILENAME ='E:\ SQL-DATA\ DXListing.mdf'); GOALTER DATABASE DXListingMODIFY FILE (NAME = DXListing_log, FILENAME ='E:\ SQL-DATA\ DXListing.ldf'); GO

3. Stop the SQL Server service for the instance where the mirror database resides.

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

5. Start the SQL Server service for the instance where the mirror database resides.

6. Click "RESUME" on the main library to restore the image, and verify that the image is restored successfully.

In another way, you only need to restart the SQL Server service of the instance where the mirror database resides, instead of pausing the service for a period of time to move data files. I actually operate in this way online.

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

two。 Use Alter Database on the mirror library to point to a new location.

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

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

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

USE master;GOALTER DATABASE DXListingMODIFY FILE (NAME = DXListing, FILENAME ='E:\ SQL-DATA\ DXListing.mdf'); GOALTER DATABASE DXListingMODIFY FILE (NAME = DXListing_log, FILENAME ='E:\ SQL-DATA\ DXListing.ldf'); GO

3. Restart the SQL Server service of the instance where the mirror database resides.

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

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

On the main library:

On the mirror library:

As you can see, the state of the mirror library is abnormal. At this point, I do the following on the mirror library:

USE masterGOALTER DATABASE DXListing SET PARTNER RESUMEGO

The error is as follows:

Msg 945, Level 14, State 2, Line 1

Database 'DXListing' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

5. Re-ONLINE the DXListing database OFFLINE to implement the changes. (of course, it should be possible to restart the SQL Server service at this time, but it is recommended to operate at the database level.)

USE masterGOALTER DATABASE DXListing SET OFFLINEALTER DATABASE DXListing SET ONLINEGO

The error is as follows:

Msg 954, Level 14, State 1, Line 1

The database "DXListing" cannot be opened. It is acting as a mirror database.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

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

On the mirror library:

On the main library:

It is found that the state is normal. Previous mistakes can be ignored.

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

Reference:

Https://msdn.microsoft.com/zh-cn/library/bb522469.aspx

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.