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.