Moving a mirrored database file in an asynchronous mirror actual combat

In asynchronous database mirroring, move the mirrored database file in combat

A database is deployed as an asynchronous database mirror and there is not enough disk space on the mirrored database to move the mirrored database files to the new disk.

In asynchronous database mirroring, the application connects to the primary database access. We are here to choose the right approach depending on your online environment:

1. Redo the image directly and place the data file in a new location.

2. Move the mirrored database files directly.

All operations on a database file are moved to a mirrored db instance, and you can move the mirrored database file directly if there are no other online primary databases on the mirrored DB instance that have no impact on the online business.

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

2. Use ALTER DATABASE on the Mirror library to point to a new location.

A) Determine the logical file name of the Dxlisting database and the current location of the files on the 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 mirrored 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 resume the image recovery and verify that the image was recovered successfully.

There is also a way to simply restart the SQL Server service for the instance where the mirror database is located without pausing the service for a period of time to move the data file, which I am actually working on online.

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

2. Use ALTER DATABASE on the Mirror library to point to a new location.

A) Determine the logical file name of the Dxlisting database and the current location of the files on the 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 for the instance where the mirror database resides.

4. Move the mirrored 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 mirroring state.

On the main library:

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image001" src= "http://s3.51cto.com/wyfs02/M01/8E/76/wKiom1jBChqRlNW7AAAFyPPxzMo011.png" height= "/>"

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image002" src= "Http://s3.51cto.com/wyfs02/M00/8E/76/wKiom1jBChvwJU9ZAAAy9CnExLk644.png" height= "295"/>

On the Mirror library:

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image003" src= "http://s3.51cto.com/wyfs02/M00/8E/74/wKioL1jBChvCEtGIAAAQr_vGVTU932.png" height= "/>"

You can see that the mirror library is not in a normal state. 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 A, state 2, line 1

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

5. Make the dxlisting database OFFLINE ONLINE to implement the changes. (Of course, it should be possible to restart the SQL Server service at this point, 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 A, State 1, line 1

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

MSG 5069, Level A, State 1, line 1

ALTER DATABASE statement failed.

At this point, let's look at the database state again.

On the Mirror library:

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image004" src= "Http://s3.51cto.com/wyfs02/M02/8E/74/wKioL1jBChuhGzB_AAAQ7dAjurQ866.png" height= "102"/>

On the main library:

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image005" src= "http://s3.51cto.com/wyfs02/M01/8E/74/wKioL1jBChyhUSX-AAAFYfgsERo220.png" height= "/>"

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image006" src= "Http://s3.51cto.com/wyfs02/M02/8E/76/wKiom1jBCh2AOMDgAAAze6wYXBA833.png" height= "311"/>

The discovery status is normal. The previous error can be ignored.

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

650) this.width=650; "title=" clip_image007 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image007 "src=" http://s3.51cto.com/wyfs02/M02/8E/74/wKioL1jBCh3QGsxKAAAFltZybuo400.png "height="/>

Reference:

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

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

Moving a mirrored database file in an asynchronous mirror actual combat