Technical: Microsoft – SQL Database – Moving database files within Database Mirroring Environment (v 2008-R2)

Technical: Microsoft – SQL Database – Moving database files within Database Mirroring Environment (v 2008-R2)

So I have this database and the file are quite big. One of the paths towards optimization is obviously I/O. Microsoft Windows 2008/R2 supports a maximum of 255 Queue Depth. The Queue Depth is the maximum number of outstanding I/O “commands”.

So if you run Performance Monitor and see high Disk Queue Lengths, do the following:

  • Check your HBA and make sure that you have adjusted HBA to match the highest supported by your HBA
  • Work with your storage Vendors to bring in new LUNS; obviously doing so will give you one more 255 Queue Depth to burn through; as the 255 is for each LUN.  Obviously this number is LUNS, physical drives, and not logical drives; that is re-partitioning a singe LUN physical does not help

So if you bring in new LUNs, here are the steps to moving over Microsoft SQL Server datafiles to use the new LUN.

Summary

  • On principal node, suspend all database backup jobs
  • On principal node, turn off database mirroring
  • On principal node, offline the database
  • On principal node, document your current database file structure
  • On principal node, change database file structure mapping
  • On principal node, use file OS Copy tools to copy database files
  • On principal node, online database
  • On passive node, change database file structure mapping
  • On passive node, use file OS Copy tools to copy database files
  • On passive node, restart MS SQL Server and auxiliary services
  • On active node, re-issue database mirroring commands
  • On active and passive nodes, review database file mappings
  • On active node, resume database backup jobs

On Principal Node, Suspend all Database backup jobs

To be able to “resume” mirroring without having to take full database & log backups on Principal and restoring same on mirror, please, please suspend all database backup jobs & procedures (on the principal node)

 

On Principal Node, Turn off Database Mirroring

Database Mirroring is configured at the database level.  As we will be moving database files that can potentially be in use, let us suspend the database mirroring.

Syntax:
   alter database <database-name>
      set partner off;

Sample:
   alter database [lab] 
      set partner off;

 

On Principal Node, Offline Database

Take the database offline

Syntax:
   use [master];
   alter database <database-name>
       set offline
         with rollback immediate;

Sample:

   use [master];

     alter database [lab]
        set offline
         with rollback immediate;

 

On Principal Node, Document your current Database file structure

Document your current database file structure



Syntax:

        use [master];
	select 
		  db_name(tblDatabaseFile.database_id) as databaseName
		, tblDatabaseFile.name
		, tblDatabaseFile.physical_name
	from   sys.master_files tblDatabaseFile
	where  tblDatabaseFile.[database_id] = db_id()
	;

Sample:

        use [master];
	select 
		  db_name(tblDatabaseFile.database_id) as databaseName
		, tblDatabaseFile.name
		, tblDatabaseFile.physical_name
	from   sys.master_files tblDatabaseFile
	where  tblDatabaseFile.[database_id] = db_id('Lab')
	;

On Principal Node, Change Database Internal file mapping

Via Transact SQL, modify the filenames, in our case, the folders that our database files point to.



alter database [Lab]
modify file
(
     NAME = LabExt__2013_01
   , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_01.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_02
   , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_02.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_03
  , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_03.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_04
  , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_04.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_05
  , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_05.ndf'
)

On Principal Node, Copy the datafiles

Copy the database files from the original location to the new location.

Note that this should be done outside of the SQL and on the OS itself. There are quite a few tools that currently allow one to copy huge database files in parallel chunks.

To reduce the size of this post, we will not cover the aforementioned tools at this time.

On Principal Node, Online Database

Take the database online

Syntax:
   use [master];
   alter database [database-name]
       set online;

Sample:
   use [master];
   alter database [lab] 
      set online;

On Passive Node, Change Database Internal file mapping

Note that this a repetition of what you did on the active node.  Database file mappings are stored in the master database and so the changes you made on the active node are not transported to the passive node; since database mirroring suspension occurred beforehand.



alter database [Lab]
modify file
(
     NAME = LabExt__2013_01
   , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_01.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_02
   , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_02.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_03
  , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_03.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_04
  , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_04.ndf'
)

alter database [Lab]
modify file
(
    NAME = LabExt__2013_05
  , FILENAME = 'D:\Microsoft\SQLServer\Datafiles\Lab\LabExt__2013_05.ndf'
)

On Passive Node, Attempt to move physical database files

On the Active Node, we were able to move the database files from the old location to the new one.  Before doing so, we took the database offline.

Unfortunately, when we attempted same on the passive node, we received an error message.

The command issued is:



  alter database [Lab] set offline;

The error message is:


Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.

Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

 

So how to get to those files. Once again “Googled of help”. Upon doing so saw a few links that were not altogether helpful.

But, here comes an helpful blog entry.

Truth be told, I am not altogether neutral about help. There are particular bloggers I have a strong bias towards. That list includes Amit Banerjee.

Unfortunately, life is a reward based system.

So Amit Banerjee’s entry states:

Moving those large files for secondary databases
http://troubleshootingsql.com/2012/09/13/moving-those-large-files-for-secondary-databases/

 

Amit thinks it is quite hard to move database files on the secondary host of a “Log-Shipping” configuration unless one restarts the secondary database SQL Server Instance.

So I have to take this loss on the secondary host.

Here are the steps we took:

  • Suspended mirroring session on other databases that are involved in Database Mirroring with the partner node
  • On the passive node, shutdown MS SQL Server Auxiliary services (SQL Server Agent)
  • On the passive node, shutdown MS SQL Server

On Passive Node, Copy the datafiles

On the passive node, copy the datafiles

On Passive Node, Restart MS SQL Server

  • On the passive node, start MS SQL Server
  • On the passive node, restarted MS SQL Server Auxiliary services (SQL Server Agent)
  • Resumed suspended mirroring sessions (while leaving the one involving the database we are working on disengaged)

On Principal Node, Turn Database Mirroring (back on)


   Syntax:
        ALTER DATABASE [database-name]
           SET PARTNER = 'TCP://<partner-host-name>:<partner-port-number>'

    Example:
        ALTER DATABASE [LAB]
           SET PARTNER = 'TCP://LAB-PARTNER:5022'

On Principal & Partner Node, review your “new” database file structure

Document your current database file structure



Syntax:

        use [master];
	select 
		  db_name(tblDatabaseFile.database_id) as databaseName
		, tblDatabaseFile.name
		, tblDatabaseFile.physical_name
	from   sys.master_files tblDatabaseFile
	where  tblDatabaseFile.[database_id] = db_id()
	;

Syntax:

        use [master];
	select 
		  db_name(tblDatabaseFile.database_id) as databaseName
		, tblDatabaseFile.name
		, tblDatabaseFile.physical_name
	from   sys.master_files tblDatabaseFile
	where  tblDatabaseFile.[database_id] = db_id('Lab')
	;

On Principal Node, Resume all Database backup jobs

On active node, resume all database backup jobs

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s