Blog | Dec 12, 2013
Move Mirrored Databases with NO DOWNTIME
Database mirroring is primarily a software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with those databases that use the full recovery model. We recently received a request to move files of Mirrored databases to a different location. The catch here is that the job had to be done without any downtime or breaking the database mirroring. Something tricky if not attempted or tested in a different environment.
With database mirroring enabled for the database we are left with limited options for file movement. The most common approach that is followed is explained below:
• Break the database mirroring session, move the online database files to new location either by using Alter database or through Attach Detach.
• Take backup and restore the backup on mirror server and then re-establish mirroring.
Technically, this will work, but it will require Downtime and moreover Restore would take good amount of time especially in case of a big database.
Given that downtime is something that a client is always concerned about, we had to find a solution with zero downtime. The steps below will explain the route we took to move database files with zero downtime without disturbing the database Mirroring.
For Mirror Instance:
1. Suspend mirroring on Principal Server (optional).
2. Issue Alter database statement on the mirrored server to point to the new location.
3. Stop mirrored SQL Server services.
4. Move database files of the mirrored database to the new location and make sure that permissions on the files are preserved.
5. Start mirrored SQL Server services.
6. Resume mirroring on Principal server databases and Verify mirroring is successfully resumed.
For Principal Instance:
1. Fail over databases to Mirror Server so that Mirror server can now act as Principal server.
2. Suspend mirroring on new Principal server (optional).
3. Issue Alter database statement on the new Mirror server to point databases to the new location.
4. Stop new mirrored SQL Server services.
5. Move database files of the new mirrored database to the new location and make sure permissions on the files are preserved.
6. Start new mirrored SQL Server services.
7. Resume mirroring on Principal server databases and verify mirroring is successfully resumed.
If we take a detailed look at the above plan we can figure out that Application sessions will be re-established during failover of the mirrored database.
Stopping the mirror SQL Server services, physically moving the database files and then starting the Mirror SQL Server Services can be done while the Application workload is running on the Principal server. So NO downtime is required!
However, you do want to make sure that there is enough log space on the principal, because the mirroring state will be disconnected (not just for one database, but for all the mirrored databases on the instance). When the mirroring state is disconnected the log records will not be sent from the principal to mirror and will be accumulated on the principal server. Once the mirror instance is started, the mirroring state will change to synchronizing, and the principal server will start sending the log records to the mirror server.
We can check the file location of all the mirrored databases by using the below TSQL to verify if changes are getting reflected:
Select DB_NAME(dbid),name,filename from sysaltfiles where DB_NAME(dbid) in (Select DB_NAME(database_id) from sys.database_mirroring where mirroring_state is not null) order by 1
It can be concluded that it is a possibility to preserve database mirroring without any downtime while moving the databases. No action was required on the witness server which remained online all the time during the activity. As a good practice we tested this plan first on development server before moving to production. It is also important to mention that in case of asynchronous mirroring failing over the database may take time, so be prepared to pay the price for High Performance mode!