Full text

If you're new to managing Microsoft SQL Server 2005 you might feel somewhat lost in a world of tables, indexes, stored procedures, queries, and database backup. In this Essential Guide, I'll show you the tools you need to manage a SQL Server 2005 system, SQL Server security essentials, and database backup and recovery. With this article under your belt, you'll know enough about SQL Server to perform necessary administrative tasks on a SQL Server 2005 system.

SQL Server Management Tools

SQL Server 2005 provides four primary tools that you can use to manage the server: SQL Server Management Studio, Query Editor, SQL Server Configuration Manager, and SQL Server Surface Area Configuration.

SQL Server Management Studio, which Figure 1 shows, is the primary GUI-based SQL Server management tool. You use SQL Server Management Studio to manage one or multiple SQL Server instances. You start SQL Server Management Studio by selecting Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio. You can run it directly from the server system itself, but more typically you'd install the client tools that include SQL Server Management Studio on a management workstation and run it from there.

To use the SQL Server Management Studio, you must first register the server that you want to manage. To register a new SQL Server system, right-click the topmost SQL Server Group node, then select Register from the context menu. Doing so opens the Register Server dialog, which prompts you for the SQL Server name and authentication information, then connects you to an instance of the server. You can use SQL Server 2005 Enterprise Manager to manage both SQL Server 2005, SQL Server 2000 systems, and SQL Server 7 systems but not to manage systems running SQL Server 6.5 or earlier.

Figure 1 SQL Server Management Studio

After you've registered a SQL Server system, you can view a list of all the database objects on that server, as Figure 1 shows. Right-clicking each object typically displays a pop-up menu that contains a list of relevant actions for each object. For instance, right-clicking a Tables object displays a pop-up menu that lets you create a new table, open the table designer, or retrieve rows from the table.

Figure 2 Query Editor

Query Editor is both a management tool and a development tool. You start Query Editor by first opening SQL Server Management Studio and then clicking New Query. As Figure 2 shows, you can use Query Editor to write and execute T-SQL statements. When Query Editor starts, it displays a blank input window in which you type T-SQL statements. You can use Query Editor to build database objects by executing T-SQL Data Definition Language (DDL) statements, or you can use it to retrieve and update data by executing T-SQL Data Manipulation Language (DML) statements. After entering the T-SQL statements, you can run them either by pressing F5 or by clicking Execute on the toolbar. If the statement is a query, you'll see the results displayed in the bottom half of the screen. One important point to remember when you're running Query Editor is that the actions are taking place on the SQL Server system itself, not on the system on which you're running the tools.

As I mentioned earlier, you don't necessarily need to know T-SQL to maintain a SQL Server installation, but familiarity with T-SQL can be a great help. For instance, to create database objects or configure other objects such as a linked server (i.e., a link to a remote database server), it's a good idea to use T-SQL scripts that you can create and execute in Query Analyzer. A T-SQL script gives you a handy reference for the definition of all the database objects, and you can easily rerun the script to recreate the database and its object on other systems. AT-SQL script also provides a reference point that you can use to check future versions for database-object changes. If you're not familiar with T-SQL there's a graphical query designer that you can use to jump start your T-SQL skills. You can start the graphical Query Designer by right clicking in the Query Editor and selecting Design Query in Editor.

Firgure 3 SQL Server Configuration Manager

The SQL Server Configuration Manager and the SQL Server Surface Area Configuration are both started from the Start, All Programs, Microsoft SQL Server 2005, Configuration Tools menu. There is some degree of overlap between these two tools but the bottom line is that the SQL Server Configuration Manager is used to manage server level settings and to start and stop services while the primary use if the SQL Server Surface Area Configuration Tool is to manage server level security settings. In addition to starting and stopping SQL Server, you also use SQL Server Configuration Manager to start and stop the following SQL Server services: Integration Services, FullText Search, Analysis Services, Reporting Services, Browser and Agent. Figure 3 shows the SQL Server Configuration tools.

Figure 4 New Login Dialog

SQL Server security

SQL Server security comprises three basic components: logins, adding database users, and granting permissions. Each component has a different role in SQL Server security. A login is required for a user to connect to the SQL Server system. If you're using Integrated security, that login is the user's Windows username. Otherwise, the administrator must manually add a login to SQL Server. The login connects the user to the server but not to a database. To enable a user to connect to a database, you must first create a database user account for that person and add it to the database. An administrator must create a set of valid database users for each database. Likewise, accessing the database doesn't mean that a user can access the objects in that database. To allow the user access those objects, the administrator must grant the user permissions to the specific database objects. In other words, the login connects you to the server, the database user account connects you to the database, and granting permissions lets you access objects in the database. SQL Server 2005 roles, which are similar to Windows groups, simplify management by letting you group similar users together.

Setting up logins is the first step in connecting your users to the server. If you're using Windows authentication, you don't need to do anything to add logins. When a user attempts to connect to the database, SQL Server authenticates the user to a Windows domain controller (DC) before SQL Server will let the user access the server. However, you need to grant the user permission to access the server by running the sp_grantlogin stored procedure. You can also use Windows groups to grant groups of users permission to log in to SQL Server. To grant all members of a group access to SQL Server, you'd run the sp_grantlogin stored procedure and specify the group name.

Figure 5 Setting database user object permissions

If you're using SQL Server authentication, you must create a SQL Server login either by running the sp_addlogin stored procedure or through SQL Server Management Studio. If you're using SQL Server Management Studio to create the login, navigate to the Server, security, Logins node. Right-click Logins and select New Login from the context menu. In the New Login dialog box, enter the login name, password, default language, and database. Figure 4 shows an example of the new Login dialog.

Setting up a server login lets the user connect to the server but doesn't let the user access the database. To allow a server login to access a given database, you must create a database user, which you can do either through a T-SQL statement or by using the SQL Server Management Studio. To add a new database user by using T-SQL, you use the sp_adduser stored procedure. First, run the use database command to set the correct database context. Then run the sp_adduser stored procedure, specifying an existing login name as the first parameter. To add a database user through SQL Server Management Studio, first expand the desired server node, then navigate to the database that you want the user to access. Expand the database node, expand the security node then right-click the Users node, and select the New User option from the pop-up menu.

After you've added the database user, you can then grant that user permission to access different database objects (e.g., tables, views). SQL Server supports three basic types of permissions: Grant, Deny, and Revoke. As the names suggest, Grant lets a user access an object, and Deny prohibits the object's use. Deny permission takes precedence over Grant. The Revoke permission essentially undoes whatever permission is currently in effect. In other words, it revokes a previously granted or denied permission. For each database object, you can grant or deny permissions to apply to various actions. You can manage permissions either by using the T-SQL GRANT, DENY, or REVOKE statements or through SQL Server Management Studio. The T-SQL keywords that you use to set Grant or Deny permissions for each object are Alter, Control, Delete, Insert, References, Select, Take Ownership, Update, and View Definition. To set database object permissions using SQL Server Management Studio, expand the database node, expand the security node, expand the Users node and right click the desired user and select Properties from the context menu. This will display the Database User properties dialog shown in Figure 5.

The database securables are listed in the top half of the window while the effective permissions are listed in the bottom half of the window. To grant permission check the Grant column. To deny permission check the Deny column.

Create a Backup Strategy

Protecting data is job number 1 for DBAs, and the most important step in protecting that data is to create backups. To suitably protect and back up your system's data, you must understand SQL Server's three recovery models and your database-backup options. In basic terms, the recovery model sets the balance between logging overhead and being able to completely recover data. SQL Server 2005 provides three recovery models: Simple, Full, and Bulk-Logged.

* The Simple recovery model offers the lowest logging overhead but can't recover any data past the end of the last backup. All data modifications, made since the last backup are considered expendable and, in the case of a restore, must be redone.

* The Full recovery model considers all data to be critical and therefore recoverable to the point of failure. AU data modifications are logged. By default, SQL Server uses the Full recovery model.

* The Bulk-Logged recovery model lies midway between the other two models. In this model, the vast majority of typical database transactions are logged and fully recoverable, but bulk operations such as bulk copy and SELECT INTO aren't logged and must be redone. The Bulk-logged model logs all other transactions and can recover to the end of the last database or log backup.

Table 1 Sample Database Backup Schedule

You can back up SQL Server database data to disk, tape, or other media. Performing disk backups is the fastest mechanism for backing up and restoring data. However, when you back up to disk, you should protect against drive failure by directing backups to a separate drive and, ideally, a separate controller from your database data. SQL Server supports three basic types of database backup: full, differential, and log. A full backup creates a full copy of the database. A differential backup copies only the database pages modified after the last full database backup. A log backup copies only the transaction log. You can also perform a partial database backup by backing up only file groups, but that technique is beyond the scope of this article.

How you choose recovery models and a backup strategy involves many considerations that are specific to your business. Some of the primary questions you must answer are

* What's your availability requirement?

* How much downtime is acceptable?

* What's the financial cost of downtime?

* Are some databases more critical than others?

* How frequently does data change?

* Can data be recreated?

If you don't already have a basic backup plan in place, the sample backup schedule in Table 1 can give you some ideas for creating one. The basic idea behind this sample plan is that the full database backup gives you a known point from which to begin the restore process. Frequent differential backups minimize the number of transaction-log backups that you need to apply to bring your restore process up to the last current transaction. In the sample backup plan, the maximum number of transaction-log backups that you might need to apply is eight. Your basic restore strategy is to restore the last full database backup followed by the last differential backup. Then you apply all the transaction-log backups since the last differential backup. Naturally, you'll probably need to adjust the frequency of each backup type to fit your organization's requirements. Using this example, you perform the backup to disk and institute a separate process for archiving the data.

You're Ready to Go

I've given you information that can help you get started administering a new SQL Server installation or get a better handle on administering the SQL Server systems that you already have. Of course, there's plenty more to know about managing SQL Server. One good resource for more in-depth information about SQL Server is the Microsoft SQL Server Web site (http://www.microsoft.com/sql). Also check out the sidebar "Importing and Exporting Data" for additional information about importing and exporting data to SQL Server.