Chapter 8: Data Storage Design and SQL Server

This chapter will explore the most common storage technologies available to you. It will also present some benefits and concerns about different storage designs so that you can more easily choose what suits your solution. We will finish the chapter with a presentation of SQL Server-examining its architecture and how it fits into your storage policy.

Filegroups

A simple database might only hold a primary data file and a log file. A more complex and larger database, on the other hand, often consists of secondary files and more log files as well.

By building filegroups, you can create your secondary data files in these groups for data placement purposes. When you create your database, you specify in which filegroup you want to place your database objects. This way, you can have control of where you place your objects, since you also know on which disk the filegroups are placed. You can even create a table or other object across multiple filegroups to increase performance. (This obviously is true only when the filegroups are created on separate disks.)

To better understand this, imagine a database that has two tables, Table_A and Table_B. The database also has two RAID disk arrays, as you can see in Figure 8-11. Table_A is constantly accessed sequentially with read-only requests, whereas Table_B is accessed only occasionally with write requests (perhaps only once every minute or so).



 Figure 8-11. Two tables are separated on two different filegroups and then placed on different disk arrays.

In this scenario, it would be great for performance to place a filegroup on each disk array and then place each table in separate filegroups. This way you separate the heavily accessed table from the less accessed one, which improves performance.

If one of the disk arrays had more disks than the other, you should also place the read-only table on that array, because doing so also improves read performance.

If some of your disks are too heavily accessed despite this, you could create only one filegroup and place it on both disk arrays. You then place both tables on this filegroup, which spreads I/O on both arrays. Table data will be written to both disk arrays, because the filegroup spans the two.

Since the best way to optimize disk I/O is to distribute your data on as many disks as possible, you should consider this during the physical design phase (as you will see later in the section "Physical Design"). By using filegroups, we can even further distribute I/O evenly, because when the data is written to a table on a filegroup that spans many disks, the data is spread in proportion across all the files in the filegroup. You can use three kinds of filegroups:

• Primary filegroups
   
• User-defined filegroups
   
• Default filegroups

The primary filegroup contains the primary data file. It also contains all the files not specifically put into another filegroup. All system tables are always placed on the primary filegroup. The primary data file must also always be placed in the primary filegroup.

User-defined filegroups are the filegroups you create yourself.

The default filegroup is wherell tables and indexes are placed if not specifically put in another filegroup. The primary filegroup is the default filegroup, if you do not specify otherwise. You can, if you want, switch the default filegroup with the ALTER DATABASE command in Transact-SQL.

Another benefit of using filegroups is that you can perform backups on separate filegroups independently of each other. You can also restore them separately. This can speed up backup and restore operations considerably.

Automatic File Growth

A cool feature of SQL Server is that you do not have to expand the files manually when they run out of space. SQL Server handles this by itself, if you specify this when creating the file (see Figure 8-12).



 Figure 8-12. Specifying automatic file growth and maximum file size in Enterprise Manager

TIP If you use this option, you should probably also remember to specify the maximum size of the file.You do not want to run out of space on a disk, which could happen if you forget to set this option.

As you can see, you need to do some planning before you set these options, but with a little careful thinking, it will prove to be a great help for you.

Lock Management

If you have a database accessed by many users or applications, you need an option to prevent the same data from being modified by two or more processes at the same time. By using locks, you can make sure a process has a dependency on a resource, and as a result have access rights to it. This means other processes cannot modify the resource until the first process releases the lock. Locking ensures that multiple users can read and write to the same database without getting inconsistent data and without overwriting each other's modifications by accident. All locks are managed on a per-SQL Server connection basis.

TIP If you want to learn more about SQL Server and the way it handles locks, please see the document "Understanding Locking in SQL Server" at the following URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_7xde.asp.

Different levels of locks can be used with SQL Server. They vary in granularity from the finest, which is the Row Identifier (RID) lock, to the coarsest, which is the database lock. Table 8-2 describes the locks from the coarsest to the finest. The finer the lock, the more concurrency among users you have.

Table 8-2. Lock Levels in SQL Server



Table 8-3. The Six Modes of SQL Server Locks