SQL Server Database Engine

The SQL Server 2005 database engine is the service that provides support for relational databases. It is a highly reliable, available, and scalable database server. As shown in Figure 2-2, its primary role in BI solutions is to be the long-term authoritative data store for enterprise data from which Analysis Services will build OLAP databases and cubes.

Usually a single server machine runs a single instance of the database engine, but each server can be configured to run more than one instance of the database engine at the same time if necessary. Each instance has its own completely separate set of databases and tables. It is usually better to have a single instance on a machine because the overall performance of the server will be improved and the administration simplified, but sometimes it is useful to be able to run multiple isolated environments on the same large server, such as when you need to run different versions or service pack levels to support existing applications. SQL Server 2005 supports up to 50 instances on a single server.

Management

SQL Server 2005 greatly simplifies the job of managing a database because it is largely self-tuning. Whether you need to manage a few or hundreds of SQL Servers, tools are included that will reduce the cost of implementing and maintaining SQL Server instances. Most management operations can be performed with the database online.

For backup and index maintenance operations, a wizard will create and deploy scheduled maintenance plans. It requires only a few minutes to implement a maintenance plan for a SQL Server instance. Backups of the data or the log are performed online, so there is no need to take the database down to create a backup. Most index reorganizations or rebuilds are also performed with the database online.

To help track down resource-intensive queries or operations, SQL Profiler can monitor all statements or a subset of the statements sent to SQL Server, as shown in Figure 2-3. You can see the statement, who issued it, how long it ran, and many other metrics and attributes. You can view the list directly or record it to a table or file. You can capture the text of a query and have the Database Engine Tuning Advisor provide suggestions for creating new indexes to help the query perform better.

Figure 2-3. Analyzing query performance with SQL Server Profiler

SQL Server is extensively instrumented to provide information about how it is using resources. To monitor the resource consumption or internal metrics of SQL Server's operation, you can run the System Monitor (previously known as Perfmon). You can use this information to determine or predict requirements for additional resources.

If you are managing more than a few SQL Servers, a separate product called Microsoft Operations Manager (MOM) can collect events and errors raised by each instance and filter out the important information for forwarding to the system operators. With the MOM interface, you can see at a glance the state of any of the servers. Predictive logic in MOM can alert you to potential issues before they become critical issues.

Scheduled Execution of Processes

SQL Server Agent is a Windows service that can schedule and execute jobs. A job is a sequence of one or more steps. A step can invoke an operating system command, SQL script, Analysis Services query or command, Integration Services package, an ActiveX script, or a replication management command. The job can be executed on demand or scheduled to recur on a regular basis. The sequence of job steps can be conditional on the results of a prior job step. Notifications can be sent via e-mail, pager, or net send command on the success or failure of the job.

Security

SQL Server supports authentication of connections using either Windows authentication alone or Windows and SQL Server authentication. Windows authentication uses your Windows credentials to identify you; SQL authentication uses a login name and password that a SQL Server administrator creates for you. This login is valid only for connecting to a single SQL Server instance.

SQL Server 2005 authentication has been enhanced over that of SQL Server 2000 to provide security rules such as password expiration and strong password requirements. Access to a database is not possible without an authenticated connection. You can use Windows groups or individual accounts to assign database permissions. Using groups makes administration easier because the database administrator (DBA) no longer needs to administer individual users, just a smaller number of groups. Windows authentication is preferred because it provides a single logon for users, it is more secure, and you don't have to maintain two sets of accounts.

Database permissions can be very granular. You can set read, write, update, delete, or deny permissions at the database level, table level, or column level. In a medical application, you could deny read permission on personal identification data to most users, while allowing reads on a Diagnosis column. This would allow statistical analysis of disease data, without letting a user associate this information with any patient. The ability to invoke any management or database operation, such as backup or database creation, can be granted, or denied, to any user or group. As with all rights and permissions in SQL Server, deny overrides any granting of rights or permissions.

Transmissions "over the wire" can be encrypted to protect data being sent between the client and server. You can also encrypt stored data down to the column level, using certificates, symmetric keys, or asymmetric keys.

Availability

SQL Server provides a wide range of options to ensure availability under almost any circumstances. Whether you need server redundancy simply to be able to perform server maintenance, or geographically remote synchronized databases for disaster recovery, you will find the features and tools to support these requirements. Clustering provides automatic failover to one or more alternative servers connected to the same storage area network (SAN). Database mirroring is a cost-effective solution that provides complete synchronization over the network between databases and fast automatic failover to a completely separate set of servers and storage. Replication offers the ability to synchronize a subset of the database with another active server. All the options maintain transactional integrity.

Scalability

You can run SQL Server on either 32-bit or 64-bit Windows platforms, but the file structures are exactly the same, so you can freely move databases from 32-bit to 64-bit and back again. 64-bit architecture gives you the advantages of a much larger and more efficient memory space, and more processors per server. The reason memory space is important is to support larger data and query caches. You can use up to 32TB of RAM on a 64-bit platform.

You can also add more processors to improve performance or handle a larger workload. Eight-way servers are commonplace now and are appropriate for many situations. For larger data warehouses, you can scale up SQL Server to 128 processors on a single server.

Multi-terabyte data warehouses are supported with good design and infrastructure in most contexts. The maximum single database size is 1,048,516 terabytes. We will avoid becoming famous for stating that this "should be enough for anyone." However, it is likely enough for the next few years, for most uses.

Support for Very Large Databases

Partitioned tables and distributed partitioned views are two features of the database engine that enhance support for very large databases. A partitioned table appears as a single table to a query, but the rows in the table are physically divided across a number of filegroups in the same database. A distributed partitioned view is similar in concept, but the tables are distributed across several SQL Servers and presented to the user through a view. If the SQL Servers are multiple instances on a single server, this is called simply a partitioned view. These features offer improvements in performance through parallel queries and through manageability and maintenance (because you can treat each partition independently in many respects).