Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Microsoft SQL Server Administration

Last updated Mar 28, 2003.

If you’re like me, you need to keep the server on track to make a living, or maybe you’ve had a server thrust upon you because the database administrator left. Perhaps you’re a developer who has to keep her own server running for a project. In any case, you’re probably interested in two things: that the server keeps running, and keeps running fast. That's what this section of the InformIT SQL Server Reference Guide is all about.

We also provide an easy way to find out when any part of the guide is updated. If you’re using an RSS reader, you can subscribe to these notifications here. We also have a blog you can subscribe to for even more up-to-date information.

The sections in this Guide are designed to allow quick access to what you need. The tutorials and overviews can be read in just a few minutes, and many contain useful scripts and hands-on guides to examples you can follow.

One of the first functions that the database administrator is responsible for is configuring the software and server. While just about anyone can insert the SQL Server CD and click Next, Next, Finish, there are decisions to be made even before the software is installed. It’s important to determine the server’s use to properly size and configure the hardware. The decisions on drive layouts, number of servers, replication schemes, and so forth are important bits of information to have in hand prior to performing the installation.

Once the server is installed, managing space on the server becomes an issue. SQL Server has the ability to automatically grow (and shrink) any database, but taking a "set it and forget it" approach isn’t always best. Several strategies are available for monitoring and notification of the space used on the server.

Performing regular backups is the primary responsibility of the DBA. Even if you don’t know the other parts of the DBA’s job, having consistent backups is crucial. If these backups are not available, all the SQL skill in the world won’t help you in a server-meltdown crisis. Fortunately, Enterprise Manager has a rich set of tools and wizards to help you with this task, and will even automate it for you. Keep in mind, however, that an easy wizard is no substitute for understanding the choices it makes.

Once you’re comfortable that you have backups for that impending crisis, the next bit of knowledge to gain is how to perform recovery steps. Once again, SQL Server 2000’s Enterprise Manager and SQL Server 2005's (and later) SQL Server Management Studio has all the tools you’ll need to recover the database to the "last known good" state.

As time goes on, you’ll need to learn to manage the server environment as it changes. Keeping logs on service pack installations and security accounts is always a good practice.

As I mentioned earlier, the DBA’s job is first to keep the server running, and second to keep it running fast. This involves actively monitoring and optimizing performance on each database. SQL Server provides many tools, and vendors supply more, to aid you in this task.

Not to be overlooked in managing the server is maintaining security. The server must be secured against physical and logical attack (with service packs and firewalls), and the data must be made available only to those who are authorized to retrieve it. To complicate matters a bit, not only must the server to accessible to the users, but also the databases and even the objects within the databases must be authorized. Drilling down even further, each object (such as a table), has multiple types of access that can be granted or denied to a particular user or group. For instance, a particular user will need access to the server (a logon), access to the database (a login), and access to view but not change a table (object permission).

Both graphical and command-based options are available for security tasks, and another way of implementing security involves the use of programmatic access, where the user has no rights inside SQL Server at all.

There is a lot more information on SQL Server Administration in this part of the guide, so make sure you check back often or subscribe to the updates.

InformIT Articles and Sample Chapters

You can get a sample chapter from my book on SQL Server 2005 Administration here.

Books and eBooks

I've also written a few books in my day, and I have one for learning more about administering SQL Server. The book is here, the e-book is here.

Online Resources

There's a great portal for SQL Server Administration here.

Click Next to continue reading about Microsoft SQL Server Administration. Next topic: DBA Survival Guide — 10 Minutes to SQL Server.