In this article we will cover:

• Configuring a data file and log file on multiple physical disks
• Using files and filegroups
• Moving an existing large table to a separate physical disk
• Moving non-clustered indexes to a separate physical disk
• Configuring the tempdb database on separate physical disk

Configuring data file and log file on multiple physical disks

If you know the exact difference between the ways in which data files and log files of a database are accessed, you can understand why you should place data files and log files on separate physical disks for better performance.

The data file of a database, which is normally a file with a .mdf or .ndf extension, is used to store the actual data in the database. The data is stored in pages that are 8 KB in size. When particular data is queried by the user, SQL Server reads the required data pages from the disk into memory containing the requested data from the data file. In case SQL Server needs to make any modifcation in the existing data, it reads the required data pages into the buffer cache, updates those cached data pages in memory, writes modifications to the log file, when the transaction is committed, and then writes the updated data pages back to the disk, when the checkpoint operation is performed. SQL Server performs configurable checkpoint operations at regular intervals. In-memory modified data pages are called dirty pages. When a checkpoint is performed, it permanently writes these dirty pages on disk.

The log file is used to record any change that is made to the database. It's intended for recovery of the database in case of disaster or failure. Because a log file is intended to record the changes, it is not designed to be read randomly, as compared to a data file. Rather, it is designed to be written and accessed in a sequential manner.

SQL Server is designed to handle and process multiple I/O requests simultaneously, if we have enough hardware resources. Even if SQL Server is capable of handling simultaneous I/O requests in parallel, it may face the issue of disk contention while reading large amounts of data from data files and writing large a number of transaction logs to log files in parallel with two different requests if data files and log files reside on the same physical disk. However, if data file and log file are located on separate physical disks, SQL Server gracefully handles and processes such requests in parallel.

When simultaneous requests for reading data and writing transaction logs are commonly expected in the OLTP database environment, placing data files and log files on separate physical drives greatly improves the performance of the database.

Let's suppose that you are a DBA and, in your organization, you maintain and administer a production database called AdventureWorks2012 database. The database was created/ installed by an inexperienced team and has been residing in the default location for SQL Server. You are required to separate the data files and log files for this database and place them on different physical disks to achieve maximum I/O performance. How would you perform this task?

The goal of this recipe is to teach you how to separate the data files and log files for an existing database to improve the I/O response time and database performance.

Getting ready

This recipe refers to the following physical disk volumes:

• E drive—to store the data file
• L drive—to store the log file

In this article, wherever it is said "separate disk volume" or "separate drive", consider it a separate physical drive and not logical partitioned drive.

The following are the prerequisites for completing this recipe:

• An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.
• Sample AdventureWorks2012 database on the instance of SQL server. For more details on how to install the AdventureWorks2012 database, please refer to the Preface of this book
• E drive should be available on your machine.
• L drive should be available on your machine.

How to do it...

The following are the steps you need to perform for this recipe:

1. Start SQL Server Management Studio and connect to SQL Server.
2. In the query window, type and execute the following script to verify the existing path for data files and log files for the AdventureWorks2012 database:

--Switch the current database 
--context to AdventureWorks2012 
USE AdventureWorks2012
GO
--Examine the current 
--location of the database.
SELECT physical_name
FROM sys.database_files
GO 

3. Assuming that the AdventureWorks2012 database resides in its default location, depending upon your SQL Server installation path, you may see a result in the output of the previous query, similar to the one given here:

4. Now, execute the following query to bring the database offline:

USE master
GO
--Bring database offline
ALTER DATABASE AdventureWorks2012 
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO 

5. Once the database is offline, you can detach it without any problem. Right-click on AdventureWorks2012Object ExplorerTasks and then Detach…, as shown in following screenshot:

6. This step brings up the Detach Database dialog box, as shown in following screenshot. Press the OK button on this dialog box. This will detach the AdventureWorks2012 database from the SQL Server instance and it will no longer appear in Object Explorer:

7. Create the following two directories to place data files (.mdf files) and log files (.ldf files), respectively, for the AdventureWorks2012 database, on different physical disks:
   • E:SQL_Data
   • L:SQL_Log
8. Now, using Windows Explorer, move the AdventureWorks2012_data.mdf and AdventureWorks2012_log.ldf database files manually from their original location to their respective new directories. The following paths should be the respective destinations:
   • E:SQL_DataAdventureWorks2012_Data.mdf
   • L:SQL_Log AdventureWorks2012_Log.ldf
9. After the data and log files are copied to their new locations, we will attach them and bring our AdventureWorks2012 database back online. To do this, in Object Explorer, right-click on the Databases node and select Attach….
10. You will see the following Attach Databases dialog box. In this dialog box, click on the Add…> button:

11. The previous step opens the Locate Database Files dialog box. In this dialog box, locate the .mdf data file E:SQL_DataAdventureWorks2012_Data.mdf and click on the OK button, as shown in following screenshot:

12. After locating the .mdf data file, the Attach Databases dialog box should look similar to the following screenshot. Note that the log file (.ldf file) could not be located at this stage and there is a Not Found message against AdventureWorks2012_log.ldf, under the AdventureWorks2012 database details: section. This happens because we have moved the log file to our new location, L:SQL_Log, and SQL Server tries to find it in its default location:

13. To locate the log file, click on the … button in the Current File Path column for the AdventureWorks2012_log.ldf log file. This will bring up the Locate Database Files dialog box. Locate the file L:SQL_LogAdventureWorks2012_log.ldf and click on the OK button. Refer to the following screenshot:

14. To verify the new location of the AdventureWorks2012 database, run the following query in SSMS:

--Switch the current database 
--context to AdventureWorks2012
USE AdventureWorks2012
GO
--Verify the new location of 
--the database.
SELECT 
  physical_name
  ,name
FROM sys.database_files
GO 

15. In the query result, examine the new locations of the data files and log files for the AdventureWorks2012 database; see the following screenshot:

How it works...

In this recipe, we first queried the sys.database_files system catalog view to verify the current location of the AdventureWorks2012 database. Because we wanted to move the .mdf and .ldf files to new locations, we had to bring the database offline.

We brought the database offline with the ALTER DATABASE command. Note that, in the ALTER DATABASE command, we included the ROLLBACK IMMEDIATE option. This rolls back the transactions that are not completed, and current connections to AdventureWorks2012 database are closed. After bringing the database offline, we detached the AdventureWorks2012 database from the instance of SQL server.

You cannot move a database file to a new location if the database is online. If a database is to be moved, it must not be in use by SQL Server. In order to move a database, you can either stop the SQL Server service or bring the database offline. Bringing the database offline is a preferable option because stopping SQL Server service stops the functioning of the whole SQL Server instance. Alternatively, you can also select the checkbox Drop Connections in the Detach Database dialog box, which does not require bringing a database offline.

We then created two new directories—E:SQL_Data and L:SQL_Log—to place the data and log files for AdventureWorks2012 and moved AdventureWorks2012_Data.mdf and AdventureWorks2012_Log.ldf over there. We then attached the AdventureWorks2012 database by attaching the .mdf and .ldf files from their new locations. Finally, we verifed the new location of the database by querying sys.database_files.

You can script your Attach Database and Detach Database actions by clicking on the Script button in the wizard. This allows you to save and re-use the script for future purposes.