Datasheet

The transaction log’s most important purpose is to serve as an exact point in time in case you need to
recover your database. Each data-modifying transaction is logged into the transaction log (although this
behavior can be minimized if you turn on certain features). If the database becomes corrupt for whatever
reason, you could take a transaction log backup and overlay it on top of your full backup, specifying
that you wish to recover to the point in time right before the corruption. Corruption is extremely rare
since SQL Server 7.0, but protecting against the remote chance of corruption is the DBA’s primary job.
A database can consist of multiple file groups that logically group one or multiple database files. Those
data files are written into in 8K data pages. You can specify how much free space you wish to be avail-
able in each data page with the fill factor of each index. (We go much more into this in Chapter 14.) In
SQL Server 2005, you have the ability to bring your database partially online if a single file is corrupt. In
this instance, the DBA can bring the remaining files online for reading and writing, and the user receives
an error if he or she tries to access the other parts of the database that are offline. (We cover that much
more in Chapter 18.)
The most that you can write into a single row is 8K. You are allowed to create a table larger in width than
8K only if there is a chance that it may not hold 8K of data, such as a table that has all varchar columns.
If you attempt to write more than 8K to the row, you will receive an error. Also, if you create a table that
writes more than 8K of data, you will receive an error.
SQL Native Client
The SQL Native Client is a data-access method that ships with SQL Server 2005 and is used by both OLE
DB and ODBC for accessing SQL Server. The SQL Native Client simplifies access to SQL Server by com-
bining the OLE DB and ODBC libraries into a single access method. The access type exposes some of the
new features in SQL Server:
❑ Database mirroring
❑ Multiple Active Recordsets (MARS)
❑ Snapshot isolation
❑ Query notification
❑ XML data type support
❑ User defined data types (UDTs)
❑ Encryption
❑ Password expiration
In some of these features, you can make the feature work in other data layers such as Microsoft Data
Access Components (MDAC), but it will take more work. MDAC still does exist, and you can still use it
if you don’t need some of the new functionality of SQL Server 2005. If you are developing a COM-based
application, you should use SQL Native Client, and if you are developing a managed code application
like in C#, you should consider using the .NET Framework Data Provider for SQL Server, which is very
robust and includes the SQL Server 2005 features as well.
5
SQL Server 2005 Architecture
04_055200 ch01.qxp 10/31/06 12:37 PM Page 5