SQL Server 2005 Administrative Architecture

In the preceding chapter I provided an insight into the various database and database server architectures on which SQL Server is built. You learned that SQL Server is able to dynamically acquire and free resources as needed. We discussed its ability to automatically manage and self-administer the procedure cache, the buffer cache, the data pages, and so on. You also learned how it processes queries, parses SQL statements, compiles procedure plans, and more. However, with large OLTP or data mining environments, you may still need to monitor the system, institute change control rules, and ensure that your deployment meets the service level requirements you or your customers have set for the system.

SQL Server thus provides a collection of services that allow you to schedule the automatic execution of repetitive tasks, such as backing up data, replication, and running reports. These services also include the SQL Server Agent, which, as mentioned earlier, provides you with a graphical or interactive environment you can use to configure schedules and tasks to run at regular intervals or upon events that occur in SQL Server and in the operating system. You can also program SQL Server to automatically respond to errors and exception conditions, and to send e-mail or pages to operators and on-duty administrators.

Microsoft has also published the administration APIs it used to build the graphical tools that ship with the Developer, Standard, and Enterprise Editions. These APIs support all the administrative services of the server. You can thus code your own applications for end users or include administrative routines in your data-dependent applications. The APIs and programming models have been encapsulated into a management framework, the programming of which is beyond the scope of this book.

Transact-SQL and Stored Procedures for DBAs

In the past chapters, I talked a little bit about Transaction-SQL, or T-SQL. If you are new to SQL Server administration, you should prepare yourself do get your hands dirty with some SQL programming. T-SQL is the language used for all commands sent to SQL Server; however, the language also supports statements that direct administrative functions and work orders in SQL Server. SQL supports several language subdivisions, the most important being Data Definition Language (DDL), which is sometimes called schema definition language, and Data Manipulation Language (DML).

DDL covers the commands that create, alter, and drop database objects, such as tables, indexes, and views. DML is the subdivision of the language that returns the values that are to be found in the tables at any given time and provides the directives for altering or deleting those values. Statements such as SELECT, INSERT, and UPDATE are typical DML directives. T-SQL has extended these subdivisions with directives that SQL Server will understand. A good example is the DENY statement. There are other subdivisions, but we can talk about those in the chapters in Part II and in Appendix.

Stored procedures are also extensively used to manage the internals of SQL Server. A good example is sp_compile, which forces the relational engine to recompile a query plan, or the sp_configure stored procedure, which is used to configure SQL Server (see Appendix).

Higher-level components or management objects, such as the SQL-DMO library we discussed earlier, typically call the T-SQL DDL commands and stored procedures. In other words, the methods used in the high-level objects do not provide any native manipulation or definition code but rather call across town to a DDL definition or some system-level stored procedure.