SQL Server 101: Essential Concepts for Windows Administrators

Often in small-to-midsized businesses (SMBs) that don't have a dedicated DBA, the job of managing Microsoft SQL Server systems falls to the Windows administrator. Administering SQL Server can be intimidating when you're not familiar with it. If you're a SQL Server newbie and don't know where to start to learn about the product, look no further. This two-part series will give you the essential knowledge that you'll need to effectively manage a SQL Server system for a department or small business. In this article, I provide some crucial getting-started information and explain SQL Server's basic components. In part 2, which will be published in an upcoming issue of Windows IT Pro, I'll discuss some indispensable tools for managing SQL Server and give you pointers for creating effective SQL Server security and database-backup strategies.

First Things First
The first step in setting up a SQL Server system is sizing memory adequately. Database systems thrive on RAM, and SQL Server is no exception. Your absolute minimum level should be 512MB for a departmental system. Larger systems will need substantially more RAM. Given the price of RAM today, you can easily add 1GB to 2GB of RAM to your SQL Server system for little additional cost. Investing in extra RAM now can head off performance problems, which can cost plenty in terms of troubleshooting time and end-user productivity.

Microsoft has made SQL Server 2000 easy to install and run, but in some ways, installation is a bit too easy. For instance, at installation time, if you accept the default settings, you'll end up with a system that performs less than optimally. By default, the SQL Server installation creates the database data files and log files on the same drive. However, your SQL Server system will perform much better if you place those files on different drives. The first thing you should do, then, after you install SQL Server is to put your data and log files on different drives. Of course, to do this, you must make sure your SQL Server system has enough drives. At a minimum, you need three drives: one for the OS, one for the data files, and one for the log files. Figure 1 shows a typical drive configuration for a small-to-midsized SQL Server installation. In this sample eight-drive system, the OS, data files, and log files are all on separate drives, and the OS and log files use mirroring to provide data redundancy. The data files use RAID 5 (data striping) to provide more efficient storage. For maximum data protection, you could instead use RAID 1 (mirroring) for your data drives. However, this solution costs significantly more than RAID 5 because mirroring requires twice as much disk space as you'll actually use so that the drives can be mirrored. SCSI drives outperform IDE drives, and as you might expect, faster drives tend to perform better.

For better recoverability, you should consider mirroring the log files. A popular configuration for SQL Server installations is to use RAID 1 for disk volumes that contain log files and RAID 5 for disk volumes that contain data files. In addition, you might want to throw in an extra drive on which you can perform traces or other diagnostic operations. Finally, for performance and security reasons, it's best to install SQL Server on a member server, not on a domain controller (DC).

Your next important consideration in setting up a SQL Server system is the type of authentication that you want to use. SQL Server supports two authentication types: Windows authentication and SQL Server (aka mixed-mode) authentication. In Windows authentication, SQL Server checks incoming login credentials against Windows user accounts. For SQL Server authentication, you must create and maintain a separate set of logins within SQL Server. Although each type has its advantages and disadvantages, you're usually better off using Windows authentication if you can. Windows authentication lets you maintain only one set of passwords, and applications that connect to the SQL Server system don't need to pass the login information as part of the connection string. Windows maintains all the login credentials. Finally, whatever you do, be sure to give the sa account a strong password. Many SQL Server exploits are designed expressly to take advantage of an sa account that has no password. Don't leave the sa password blank, and don't use easily guessed values such as sa or password.

SQL Server Essentials
You'll probably find it easier to manage SQL Server 2000 when you understand how the product works. SQL Server comes with four system databases—master, model, msdb, and tempdb—and two user databases. The master database is probably the most important system database. It contains tables that describe all other databases on the system and also contains login and security information. The model database is a template for all new databases. Any new database that's created on the server inherits all the settings in the model database. The msdb database is used by SQL Server Agent to store job-scheduling information and also maintains backup and replication information. The tempdb database stores temporary work tables. The objects in tempdb persist only while the user who created them is logged on.

The two sample user databases are Pubs and Northwind. The Pubs database is a small author-publication database containing sample information about a set of authors, their books, and their publishers. The Northwind database is similar to the sample Northwind database that ships with Microsoft Access. It contains sample order and sales information for a fictitious company called Northwind Traders. Although Northwind isn't large compared with the size of a typical SQL Server database, it's larger than the tiny Pubs database.

If you're tasked only with managing a SQL Server system and not with creating new databases, you probably won't need to get into the details of creating database objects. Typically, your IT department or application vendor will create those. However, a basic understanding of the core database objects—databases, tables, indexes, views, stored procedures, and triggers—can help you manage and troubleshoot problems.

Databases. Databases contain the information that applications use. SQL Server databases contain collections of tables, views, indexes, and stored procedures. Each application is typically designed so that it connects to its own database. A single SQL Server system can support multiple databases up to as many as 32,767 databases per server. SQL Server databases can grow quite large: The maximum size of a database is 1,048,516TB. Every database must consist of at least two files: a data file and a log file. The data file contains the table, row, and column information that's stored in the database. The log file contains all the transaction operations (INSERT, UPDATE, and DELETE) that users or applications execute against the database. As I mentioned earlier, to ensure optimum performance, you should never place the data and log files on the same drive on production systems. Likewise, you should never place data and log files on compressed or encrypted drives.

During database creation, SQL Server uses a copy of the model database as a template for the new database. You can specify a maximum growth amount for the database either in megabytes or as a percentage of its size. However, for most installations, you're better off specifying the auto-grow option, which lets the database grow automatically as necessary. If you're familiar with your application, you'll want to anticipate the database's growth and size appropriately so that SQL Server avoids using auto-grow during production periods, which would cause a temporary performance hit.

Tables. The table is the basic unit of storage for all relational databases. Tables contain a set of related information. For instance, each row in a customer table would contain all the related information for a given customer. Typically, such information includes the customer's ID number, name, address, and contact information. Each separate piece of information (e.g., customer number) is contained in a column, and each column is defined to hold only a given data type. For example, the first column in the customer table might be named CustomerID and be defined as int, meaning that it can store only integers. The second column, CustomerName, might be defined as varchar(40), meaning that it can store as many as 40 characters of text data.

Indexes. The primary reason for using indexes is to improve query performance. Indexes are built on selected columns in a table. SQL Server uses two types of indexes: clustered and nonclustered. A clustered index determines the order of data in the table. When a clustered index is created on a table, SQL Server arranges the rows in the base table to match the order defined in the clustered index. Each table can have only one clustered index. Nonclustered indexes don't reorder the data in the base table; they simply provide an alternative optimized access path to the data.

You can delete or add indexes without affecting the basic database design. Although an index's primary purpose is to improve data-retrieval speed, too many indexes can have the opposite effect. When an index is built, SQL Server must maintain that index (in other words, keep it up-to-date). As data is added to the tables in a database, the SQL Server engine must not only add the data to the table but also update the existing indexes with the new data. As you might imagine, the more indexes you've created, the longer this update process can take. Too many indexes can adversely affect your system's performance. One common technique that can improve batch processing is to write one script that deletes the affected indexes before a batch procedure begins, then write another script that recreates the indexes when the procedure is finished. SQL Server 2000 provides an Index Tuning Wizard that analyzes queries and suggests indexes that would improve query performance.

Views. A view is like a virtual table or a stored query. The data that's accessible through a view isn't stored in a discrete database object. Instead, you create a view by using a SQL SELECT statement. When accessing a view, the user sees the results of that SELECT statement. Views are typically used to restrict users to a subset of the rows or columns in one or more tables. You can also use views to join multiple tables, thereby making them appear to be one table.

Stored procedures and triggers. Stored procedures, which you create by using compiled T-SQL code, are the backbone of most database applications. Triggers are a special type of stored procedure that can be attached to a table. Because stored procedures are compiled, they provide better performance than dynamic SQL (i.e., SQL code that a program generates before the SQL code is executed). When dynamic SQL is executed on the server, the database engine first parses the statement to ensure that the syntax is valid. Then the server constructs a data-access plan. Stored procedures let SQL Server perform this work at the time the stored procedure is created instead of at runtime, which gives stored procedures a performance advantage over dynamic SQL at execution time. SQL Server is very smart about how it caches queries and will even cache ad hoc dynamic SQL for a period of time, but there's no guarantee that the dynamic SQL will still be cached the next time it's used.

Like stored procedures, you use T-SQL to create triggers. However, unlike stored procedures, which can be freely executed by any user who has permission to those stored procedures, triggers are executed only by the database itself. You attach triggers to a table by using the CREATE TRIGGER statement, and they're executed only when an INSERT, UPDATE, or DELETE action is performed on the table. DBAs commonly use triggers to help enforce Declarative Referential Integrity (DRI). You can also cascade DELETE operations from the referenced table to associated rows in the referencing table. Trigger code fires only after SQL Server checks constraints, including DRI.

Take a Break
It's time to sit back and absorb what you've learned about SQL Server 2000. By now, you should understand the basics of sizing RAM and determining disk configuration on a SQL Server system. You should also be familiar with SQL Server's fundamental components. Stay tuned for part 2 in this series, where I'll reveal more SQL Server insights to help make your job as a part-time DBA a lot easier.