Datasheet

files can also be added to the Primary filegroup. More filegroups can also be defined upon initial cre-
ation of the database, or added after the database is created. Chapter 4 describes the storage architecture
of files in greater detail, and Chapter 5 explains the advantage of filegroups. For now, it is sufficient to
know that all of the data objects in a database (such as tables, views, indexes, and stored procedures) are
stored within the data files. Data files can be logically grouped to improve performance and allow for
more flexible maintenance (see Figure 1-1).
Figure 1-1: Data files and filegroups
Log Files
Upon initial creation of a database, one transaction log must be defined. The transaction log is used to
record all modifications to the database to guarantee transactional consistency and recoverability.
Although it is often advantageous to create multiple data files and multiple filegroups, it is very rarely
necessary to create more than one log file. This is because of how SQL Server accesses the files. Data files
can be accessed in parallel, enabling SQL Server to read and write to multiple files and filegroups simul-
taneously. Log files, on the other hand, are not accessed in this manner. Log files are serialized to main-
tain transactional consistency. Each transaction is recorded serially in the log in the sequence it was
executed. A second log file will not be accessed until the first log file is completely filled. You can find a
complete description of the transaction log and how it is accessed in Chapter 4.
SQL Server Security
Chapter 6 provides a thorough discussion of SQL Server 2005 security features. However, to select the
proper authentication model during installation, it is important to have a basic understanding of how
SQL Server controls user access.
SQL Server 2005 can be configured to work in either the Windows Authentication Mode or the SQL
Server and Windows Authentication Mode, which is also frequently called Mixed Mode.
Windows Authentication Mode
In Windows Authentication Mode only logins for valid Windows users are allowed to connect to SQL
Server. In this authentication mode, SQL Server “trusts” the Windows, Windows Domain, or Active
Directory security subsystem to have validated the account credentials. No SQL Server accounts are
allowed to connect. They can be created, but they cannot be used for login access.
MyDB
MyDB_Log.ldf
MyDB_Data2.ndf
MyDB_Data.mdf
Primary FileGroup
MyDB_Data4.ndf
MyDB_Data3.ndf
UserData FileGroup
18
Chapter 1
04_047046 ch01.qxp 10/18/06 12:18 AM Page 18