Export (0) Print
Expand All

How to Use Attached Data Disk to Store Database Files

Updated: February 28, 2014

For instances of SQL Server running in an Azure virtual machine, using attached data disks to store business, data, log, and backup files is generally recommended. As noted in the Performance Best Practices for SQL Server in Azure Virtual Machines article, store databases larger than 10 GB on attached data disks for better performance.

Depending on the image you use for provisioning an instance of SQL Server in an Azure virtual machine, SQL Server and the databases could be installed on the C: drive. Relocating the database files to the attached data disks and changing the default location for the database files requires moving database files and changing the server property settings.

The following are common scenarios that require moving database and sometimes reconfiguring the default database file location settings.

 

Scenario

Required Action

You provision an Azure virtual machine by using an image from the Azure Gallery that includes SQL Server. SQL Server is preinstalled on the C: drive and all the system database files are stored on the C: drive. This scenario is also similar to using a custom image that has SQL Server installed on the C: drive.

You want to move all the database files, including system databases, to an attached data disk. You also want to configure SQL Server default settings, so all new databases are installed on the attached data disk.

Move database files

Configure database default location

SQL Server running in an Azure virtual machine has a user database installed on the C: drive. The database size is now larger than 10 GB. You want to move the database files from the C: drive to an attached data disk based on the guidelines provided in thePerformance Best Practices for SQL Server in Azure Virtual Machines topic.

Move database files

You want to install a specific database on an attached data disk due to the size or other requirements.

Select the attached data disk for the database file location

You want to restore a database and relocate it to an attached data disk.

Restore to the attached data disk

The following sections describe the tasks and tools you can use to move database files, configure SQL Server settings to install databases on an attached data disk, select an attached data disk for a new database, and restore a database to an attached data disk.

Moving Database Files

In this example, F: drive refers to an attached data disk, initialized, and labeled with the drive letter F. For information on how to attach disks to an Azure virtual machine, see How to Attach a Data Disk to a Virtual Machine.

To move the database files from the operating system disk to the F: drive, use the following steps:

  1. Create a folder on F: drive for the instance of SQL Server and name it with the instance ID. This is the root directory for all the database files. For example, if you are moving files from a default instance of SQL Server, create a folder named MSSQL11.MSSQLSERVER. If you are moving database files in multiple instances, create a folder for each instance.

  2. Get a list of all the data and log files by using the following query:

    SELECT      db.name AS [DatabaseName]
    ,f.name AS [LogicalFileName]
    ,f.physical_name AS [PhysicalFileName]
    FROM        sys.databases db, sys.master_files f
    WHERE       db.database_id = f.database_id
    AND         f.type_desc IN (N'ROWS', N'LOG')
    ORDER BY    db.name
    
    
  3. Update the paths for all data files and transaction log files to point to the F: drive. You can use the ALTER DATABASE statement to make the updates for all databases except the master system database.

    The new paths should match the old directory paths. For example, if the old path is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\ MSSQL\DATA\master.mdf, the new path should be F:\ MSSQL11.MSSQLServer\MSSQL\DATA\master.mdf. Create the necessary subfolders in the root directory.

    1. Updating Master Database File Location: From SQL Server Configuration Manager, click SQL Server Services. Select the SQL Server instance, and right click to select Properties. Select the Startup Parameters tab, and update the values for –d and -l parameters.

    2. Updating File Location for all other databases: To change the file location, use the ALTER DATABASE statement. Run the following statement for each data and log file you are moving.

      ALTER DATABASE <DatabaseName> MODIFY FILE (NAME='<LogicalFileName>', FILENAME='<Path/OSFileName>')
      
      
      
      For more information, see Moving Database Files.

  4. Stop the SQL Server Service.

  5. For all the databases with updated paths, copy the files to the new location on the F: drive. Configure the file system permissions for Database Engine access. The following are two ways to configure the File System permissions:

    1. Using Windows Explorer, set the permissions for the DATA directory and all the files in the DATA directory. For more information, see Configure File System Permissions for Database Engine Access.

    2. Use icacls utility to set the permissions. The following sample commands copy the ACL from the old DATA directory and restore it to the new directory and files.

      The first command copies the ACL for the DATA directory and saves it to a file name ACLFile_Dir. The second command copies ACL for all the files in the DATA directory to a file name ACLFile_Files.

      icacls "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA" /save ACLFile_Dir 
      icacls "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\*" /save ACLFile_Files
      
      The following two commands restore the ACL copied in the previous step and apply it to the new directory and files.

      icacls "F:\mssql11.mssqlserver\mssql" /restore AClFile_Dir 
      icacls "F:\mssql11.mssqlserver\mssql\data" /restore ACLFile_Files
      
      For more information on the icacls utility, see Icacls.

  6. Restart the SQL Server service. The databases should now be pointing to the new location.

Configuring Database Default Locations

To change the default installation location of database files, change the database default location entries under Server properties. Database created after this change use the new location to store the files. For more information, see Server Properties - Database Setting Page

Select Attached Data Disk As the Location for a New Database.

When creating a database, use the CREATE DATABASE statement and specify the new location on the attached data disk with the FILENAME argument. For more information, see Create Database (Transact-SQL).

Restore a Database Backup to an Attached Data Disk

To relocate the data and log files when restoring a database backup to an attached data disk, use WITH MOVE argument to specify the new location. For more information, see Restore (Transact-SQL).

Community Additions

ADD
Show:
© 2014 Microsoft