Moving the master database file in an asynchronous mirror actual combat

Source: Internet
Author: User

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


A database is deployed as an asynchronous database mirror, the primary database is running out of disk space, and I have to move the primary database file 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. Manually fail over to the mirror library, and then move the new mirrored database file.

2. Move the master database file directly.


I personally think that if the business cannot tolerate long downtime, it is recommended to manually fail over to the mirror library, the business switch connection string. If, at this point, the new Mirror Library is on the same instance as the other online business database, to business first, it is recommended to redo the mirror and place the database file in the new location. After the image is completed, and then re-manually failed to transfer back, the business also point back.


As our online business happens to have a downtime maintenance window. During this time, I moved the master database directly. Next, let's try to move the master database files directly during a business outage.


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


2. 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 name of the WMS database and the current location of the files on the 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. (There are other business databases on the instance where the primary database is located, it is important to consider the impact on other businesses, we choose the second method of moving the mirrored database file)


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


6. OFFLINE the WMS database ONLINE to implement the changes. (Of course, it should be possible to restart the SQL Server service at this point, but if there are other business databases on the instance where the operation's main library is located, it will be affected by the recommendation or the database level operation)

Use Mastergoalter database WMS set Offlinealter database WMS Set Onlinego


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

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/M02/8E/78/wKiom1jBLBDypVD0AAAFCu2GSU8996.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/M02/8E/76/wKioL1jBLBHj2vorAAAzT7Wuj88821.png" height= "308"/>

The discovery status is normal.


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

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;padding-right:0px, "border=" 0 "alt=" clip_ image003 "src=" http://s3.51cto.com/wyfs02/M00/8E/76/wKioL1jBLBHxuDcaAAAFWatUBeU406.png "height="/>


This also verifies that the ALTER database moves the databases file by modifying the Master System database without being synchronized to the mirrored database.



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

Moving the master database file in an asynchronous mirror actual combat

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.