Lesson 2:Planning a SQL Server Database

3 4

When planning a SQL Server database, the design must take into consideration a number of factors, including database files and filegroups, transaction logs, the SQL Server installation and its operating environment, and security. This lesson discusses each of these considerations.

---

After this lesson, you will be able to:

• Describe many of the factors that you should take into consideration when planning a SQL Server database.

Estimated lesson time: 25 minutes

---

Files and Filegroups

To map a database, SQL Server 2000 uses a set of operating system files. All data and objects in the database, such as tables, stored procedures, triggers, and views, are stored within the following types of operating system files:

• Primary.  This file contains the startup information for the database and is used to store data. Every database has one primary data file.
• Secondary.  These files hold all of the data that does not fit into the primary data file. If the primary file can hold all of the data in the database, databases do not need to have secondary data files. Some databases might be large enough to need multiple secondary data files or to use secondary files on separate disk drives to spread data across multiple disks or to improve database performance.
• Transaction Log.  These files hold the log information used to recover the database. There must be at least one log file for each database.

A simple database can be created with one primary file that contains all data and objects and a log file that contains the transaction log information. Alternatively, a more complex database can be created with one primary file and five secondary files. The data and objects within the database spread across all six files, and four additional log files contain the transaction log information.

Filegroups group files together for administrative and data allocation/placement purposes. For example, three files (Data1.ndf, Data2.ndf, and Data3.ndf) can be created on three disk drives and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks, thereby improving performance. The same performance improvement can be accomplished with a single file created on a redundant array of independent disks (RAID) stripe set. Files and filegroups, however, help to easily add new files to new disks. Additionally, if your database exceeds the maximum size for a single Windows NT file, you can use secondary data files to grow your database further.

Rules for Designing Files and Filegroups

When designing files and filegroups, you should adhere to the following rules:

• A file or filegroup cannot be used by more than one database. For example, the files sales.mdf and sales.ndf, which contain data and objects from the sales database, cannot be used by any other database.
• A file can be a member of only one filegroup.
• Data and transaction log information cannot be part of the same file or filegroup.
• Transaction log files are never part of a filegroup.

Default Filegroups

A database comprises a primary filegroup and any user-defined filegroups. The filegroup that contains the primary file is the primary filegroup. When a database is created, the primary filegroup contains the primary data file and any other files that are not put into another filegroup. All system tables are allocated in the primary filegroup. If the primary filegroup runs out of space, no new catalog information can be added to the system tables. The primary filegroup is filled only if autogrow is turned off or if all of the disks that are holding the files in the primary filegroup run out of space. If this situation happens, either turn autogrow back on or move other files off the disks to free more space.

User-defined filegroups are any filegroups that are specifically created by the user when he or she is first creating or later altering the database. If a user-defined filegroup fills up, only the users tables specifically allocated to that filegroup would be affected.

At any time, exactly one filegroup is designated as the default filegroup. When objects are created in the database without specifying to which filegroup they belong, they are assigned to the default filegroup. The default filegroup must be large enough to hold any objects not allocated to a user-defined filegroup. Initially, the primary filegroup is the default filegroup.

The default filegroup can be changed by using the ALTER DATABASE statement. When you change the default filegroup, any objects that do not have a filegroup specified when they are created are allocated to the data files in the new default filegroup. Allocation for the system objects and tables, however, remains within the primary filegroup, not in the new default filegroup.

Changing the default filegroup prevents user objects that are not specifically created on a user-defined filegroup from competing with the system objects and tables for data space.

Recommendations

When implementing a database, you should try to adhere to the following guidelines for using files and filegroups:

• Most databases will work well with a single data file and a single transaction log file.
• If you use multiple files, create a second filegroup for the additional files and make that filegroup the default filegroup. This way, the primary file will contain only system tables and objects.
• To maximize performance, you can create files or filegroups on as many different available local physical disks as possible and place objects that compete heavily for space in different filegroups.
• Use filegroups to enable the placement of objects on specific physical disks.
• Place different tables used in the same join queries in different filegroups. This procedure will improve performance due to parallel disk input/output (I/O) searching for joined data.
• Place heavily accessed tables and the non-clustered indexes belonging to those tables on different filegroups. This procedure will improve performance due to parallel I/O if the files are located on different physical disks.
• Do not place the transaction log file(s) on the same physical disk with the other files and filegroups.

Transaction Logs

A database in SQL Server 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.

SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database as well as the transactions that performed the modifications. The transaction log records the start of each transaction and records the changes to the data. This log has enough information to undo the modifications (if necessary later) made during each transaction. For some large operations, such as CREATE INDEX, the transaction log instead records the fact that the operation took place. The log grows continuously as logged operations occur in the database.

The transaction log records the allocation and deallocation of pages and the commit or rollback of each transaction. This feature enables SQL Server to either apply (roll forward) or back out (roll back) each transaction in the following ways:

• A transaction is rolled forward when you apply a transaction log. SQL Server copies the after image of every modification to the database or reruns statements such as CREATE INDEX. These actions are applied in the same sequence in which they originally occurred. At the end of this process, the database is in the same state that it was in at the time the transaction log was backed up.
• A transaction is rolled back when you back out an incomplete transaction. SQL Server copies the before images of all modifications to the database since the BEGIN TRANSACTION. If it encounters transaction log records indicating that a CREATE INDEX was performed, it performs operations that logically reverse the statement. These before images and CREATE INDEX reversals are applied in the reverse of their original sequence.

At a checkpoint, SQL Server ensures that all transaction log records and database pages that were modified are written to disk. During each database's recovery process, which occurs when SQL Server is restarted, a transaction needs to be rolled forward only if it is not known whether all the transaction's data modifications were actually written from the SQL Server buffer cache to disk. Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling forward transactions. Because all pages modified before the checkpoint are guaranteed to be on disk, there is no need to roll forward anything done before the checkpoint.

Transaction log backups enable you to recover the database to a specific point in time (for example, prior to entering unwanted data) or to the point of failure. Transaction log backups should be a consideration in your media-recovery strategy.

Environment

Generally, the larger the database, the greater the hardware requirements. Database design should always take into consideration processor speeds, memory, and hard disk space and configuration. There are other determining factors, however: the number of concurrent users/sessions, transaction throughput, and the types of operations within the database. For example, a database containing infrequently updated school library data would generally have lower hardware requirements than a one-terabyte (TB) data warehouse containing frequently analyzed sales, product, and customer information for a large corporation. Aside from the disk storage requirements, more memory and faster processors would be needed for the data warehouse to cache more of the data in memory and to quickly process queries referencing large amounts of data.

Estimating the Size of a Database

When designing a database, you might need to estimate how big the database will be when it is filled with data. Estimating the size of the database can help you determine the hardware configuration that you will need to meet the following requirements:

• Achieving the performance required by your applications
• Ensuring the appropriate physical amount of disk space to store the data and indexes

Estimating the size of a database can also lead you to determine whether the database design needs refining. For example, you might determine that the estimated size of the database is too large to implement in your organization and that more normalization is required. Conversely, the estimated size might be smaller than expected, requiring you to denormalize the database to improve query performance.

To estimate the size of a database, estimate the size of each table individually and then add the values that you obtain. The size of a table depends on whether the table has indexes and, if so, what type of indexes. For more information about estimating the sizes of various types of tables, refer to SQL Server Books Online.

Physical Database Design

The I/O subsystem (storage engine) is a key component of any relational database. A successful database implementation usually requires careful planning in the early stages of your project. The storage engine of a relational database requires much of this planning, which includes the following:

• What type of disk hardware to use
• How to place your data onto the disks
• Which index design to use to improve query performance when accessing data
• How to set all configuration parameters appropriately for the database to perform well

SQL Server Installation

Although the installation of SQL Server is beyond the scope of this training kit, you should always take into consideration the following issues before performing an installation:

• Be sure that the computer meets the system requirements for SQL Server 2000.
• Back up your current installation of Microsoft SQL Server if you are installing SQL Server 2000 on the same computer.
• If you are installing a failover cluster, disable NetBIOS on all private network cards before running SQL Server Setup.
• Review all SQL Server installation options and be prepared to make the appropriate selections when running Setup.
• If you are using an operating system that has regional settings other than English (United States), or if you are customizing character set or sort order settings, review topics on collation settings.

Before running SQL Server 2000 Setup, create one or more domain user accounts if you are installing SQL Server 2000 on a computer running Windows NT or Windows 2000 and want SQL Server 2000 to communicate with other clients and servers.

You should log on to the operating system under a user account that has local administrative permissions; otherwise, you should assign the appropriate permissions to the domain user account. Be sure to shut down all services dependent on SQL Server (including any service that uses ODBC, such as Internet Information Services, or IIS). In addition, shut down Windows NT Event Viewer and registry viewers (Regedit.exe or Regedt32.exe).

Security

A database must have a solid security system to control the activities that can be performed and to determine which information can be viewed and modified. A solid security system ensures the protection of data, regardless of how users gain access to the database.

Planning Security

A security plan identifies which users can see which data and perform which activities in the database. You should take the following steps to develop a security plan:

• List all of the items and activities in the database that must be controlled through security.
• Identify the individuals and groups in the company.
• Cross-reference the two lists to identify which users can see which sets of data and perform which activities in the database.

Security Levels

A user passes through two stages of security when working in SQL Server: authentication and authorization (permissions validation). The authentication stage identifies the user who is using a login account and verifies only the capability to connect to an instance of SQL Server. If authentication is successful, the user connects to an instance of SQL Server. The user then needs permissions to access databases on the server, which is done by granting access to an account in each database (mapped to the user login). The permissions validation stage controls the activities that the user is allowed to perform in the SQL Server database.

Authentication Modes

SQL Server can operate in one of two security (authentication) modes: Windows Authentication and Mixed mode. Windows Authentication mode enables a user to connect through a Windows NT 4.0 or Windows 2000 user account. Mixed mode (Windows Authentication and SQL Server Authentication) enables users to connect to an instance of SQL Server by using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows NT 4.0 or Windows 2000 user account can make use of trusted connections in either Windows Authentication mode or Mixed mode.

Lesson Summary

When planning a SQL Server database, you must take into consideration database files and filegroups, transaction logs, the SQL Server installation and its operating environment, and security. All data and objects in the database are stored within primary, secondary, and transaction log files. Filegroups group files together for administrative and data allocation/placement purposes. The filegroup that contains the primary file is the primary filegroup. A database in SQL Server 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database. SQL Server uses the transaction log of each database to recover transactions. Database design should always take into consideration processor speeds, memory, and hard disk space and configuration. There are other determining factors, however: the number of concurrent users/sessions, transaction throughput, and the types of operations within the database. When designing a database, you might need to estimate how big the database will be when it is filled with data. When installing SQL Server, you should take into consideration several issues. A database must have a solid security system to control the activities that can be performed and to determine which information can be viewed and modified. A security plan identifies which users can see which data and perform which activities in the database.