If you're responsible for managing a Windows environment that includes Microsoft SQL Server—but you're not a DBA—you might feel somewhat lost in a world of tables, indexes, stored procedures, queries, and database backup. In "SQL Server 101: Essential Concepts for Windows Administrators," June 2005, InstantDoc ID 46265, I helped you start to make sense of SQL Server by explaining its fundamental concepts. In this article, I continue your SQL Server orientation by discussing the tools you need to manage a SQL Server 2000 system, SQL Server security essentials, and database backup and recovery. With these two articles under your belt, you'll know enough about SQL Server to perform necessary administrative tasks on a SQL Server 2000 system.
SQL Server Management Tools
SQL Server 2000 provides three primary tools that you can use to manage the server: Server Manager, SQL Server Enterprise Manager, and SQL Query Analyzer. The Windows Server Manager icon is displayed in the system tray; you click the icon to start, stop, or pause the SQL Server service. A green arrow over the icon indicates that the SQL Server service is running. A red square over the icon means that the service is stopped. In addition to starting and stopping SQL Server, you also use Server Manager to start and stop the SQL Server Agent, the distributed transaction coordinator (DTC), the Microsoft Search service, and Analysis Services.
Enterprise Manager, which Figure 1 shows, is the primary GUI-based SQL Server management tool. You use Enterprise Manager to manage one or multiple SQL Server instances. You start Enterprise Manager by selecting Start, Programs, Microsoft SQL Server, Enterprise Manager. You can run it directly from the server system itself, but more typically you'd install the client tools that include Enterprise Manager on a management workstation and run it from there.
To use Enterprise Manager, you must first register the server that you want to use. To register a new SQL Server system, right-click the SQL Server Group node, then select New SQL Server Registration from the pop-up menu. Doing so starts the SQL Server Registration Wizard, 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 2000 Enterprise Manager to manage both SQL Server 2000 and SQL Server 7.0 systems, but not to manage systems running SQL Server 6.5 or earlier.
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.
Query Analyzer is both a management tool and a development tool. You start Query Analyzer by selecting Start, Programs, Microsoft SQL Server, Query Analyzer. As Figure 2 shows, you can use Query Analyzer to write and execute T-SQL statements. When Query Analyzer starts, it displays a blank input window in which you type T-SQL statements. You can use Query Analyzer 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 the green arrow icon 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 Analyzer 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. A T-SQL script also provides a reference point that you can use to check future versions for database-object changes.
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 2000 and SQL Server 7.0 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.
If you're using SQL Server authentication, you must create a SQL Server login either by running the sp_addlogin stored procedure or through Enterprise Manager. If you're using Enterprise Manager to create the login, navigate to the Server, Security, Logins node. Right-click Logins and select New Login from the pop-up menu. In the New Login dialog box, enter the login name, password, default language, and database.
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 Enterprise Manager. 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 Enterprise Manager, first expand the desired server node, then navigate to the database that you want the user to access. Expand the database node, 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. The T-SQL keywords that you use to set Grant or Deny permissions for each object are SELECT, INSERT, UPDATE, DELETE, EXEC, and DRI.
You can manage permissions either by using the T-SQL GRANT, DENY, or REVOKE statements or through Enterprise Manager. In Enterprise Manager, expand the Users node of the database you're interested in. Then in the details pane, right-click a particular database user, then select the Manage Permissions option from the pop-up menu. A matrix is displayed that lists all the database objects at the left side of the screen and all the statements to which those permissions apply at the right. To grant a permission, click once to display a green check mark in the appropriate place in the matrix. To deny a permission, click twice to display a red X in the matrix.
Create a Backup Strategy
Protecting data is job 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 2000 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. All 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.
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. Two good resources for more in-depth information about SQL Server are SQL Server Magazine (http://www.sqlmag.com) and the Microsoft SQL Server Web site (http://www.microsoft.com/sql). Also check out the sidebars "More SQL Server Tools," page 57, and "Importing and Exporting Data" for additional information about helpful SQL Server tools. Have fun diving in to SQL Server!
