Maintaining SQL Server 2000 Databases

Microsoft SQL Server 2000 provides two tools, Enterprise Manager and Query Analyzer, that make basic database maintenance a snap. Both tools have graphical user interfaces that make working with databases much simpler.

To use Enterprise Manager on a computer with SQL Server 2000 installed, on the Start menu, click Programs, click Microsoft SQL Server, and then click Enterprise Manager. Once Enterprise Manager appears, you can access the database you want to maintain by opening the Console Root folder and then expanding the Microsoft SQL Servers group, the SQL Server group containing your database server, and the database server, as shown in Figure 12-1.

Figure 12-1: The SQL Server Enterprise Manager.

To connect to a SQL Server on a different computer, right-click the SQL Server group to which you want to add the SQL Server connection, click New SQL Server Registration, and then provide a server name, authentication details, and any other requested information in the dialog box shown in Figure 12-2.

Figure 12-2: Connecting to SQL Server on a different computer.

To use Query Analyzer on a computer with SQL Server 2000 installed, on the Start menu, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer. You then need to provide the information requested in the Connect To SQL Server dialog box, shown in Figure 12-3. (To connect to the SQL Server installed on the same computer, type (local) in the SQL Server list.)

Figure 12-3: The Connect To SQL Server dialog box in Query Analyzer.

The following sections describe how to use Enterprise Manager and Query Analyzer to perform three important SQL Server 2000 database maintenance tasks:

• Documenting your databases.

• Backing up your databases.

• Using the SQL Server 2000 Maintenance Plan Wizard to automate common database maintenance tasks.

Documenting and Re-Creating SQL Server Databases

After you create a database, you should document the database in the event some type of unanticipated data failure requires you to re-create the structure of your database. To document a database, open Enterprise Manager, right-click the database you want to document, point to All Tasks, and then click Generate SQL Script. To thoroughly document a database, on the General tab of the Generate SQL Scripts dialog box, click the Show All button and then select the Script All Objects option. On the Formatting tab, select all the options except Only Script 7.0 Compatible Features. Also select all the check boxes on the Options tab. Clicking OK creates a text file that can be used to re-create the structure of the database from scratch.

Caution

A SQL script created with the Generate SQL Scripts dialog box re-creates only the database’s structure, not the database’s data.

You use Query Analyzer to re-create a database’s structure from scratch. After you connect to the server on which you want to re-create the database’s structure, click Open on the File menu in Query Analyzer, click the SQL script file for the database, and then click Open. In the final step, click Execute on the Query menu.

Caution

Using a SQL script file to re-create a database’s structure on a SQL Server computer that includes a database with the same name will delete the existing database. Do not practice re-creating a database’s structure on a computer that has an identically named database or you will lose the existing database.

Backing Up and Restoring SQL Server Databases

Backing up your databases is the most important database maintenance task. Losing data and having no way to recover it, needless to say, can be disastrous in terms of lost time, lost money, and missed business opportunities.

Although you can use the Database Maintenance Plan Wizard (described in the next section) to automate database backups, you should immediately set up automated backup tasks for your databases until you complete a formal database maintenance plan with the Database Maintenance Plan Wizard. To set up an automated backup task for a database, right-click the database icon in Enterprise Manager, point to All Tasks, and then click Backup Database. Provide the backup settings in the SQL Server Backup dialog box and then click OK. To restore a database from a backup, right-click the server’s Database folder in Enterprise Manager, point to All Tasks, click Restore Database, and then provide the restore settings in the Restore Database dialog box.

For information about the settings in the SQL Server Backup or the Restore Database dialog box, click the General or the Options tab in the dialog box you want information about and then click the Help button.

Using the Database Maintenance Plan Wizard

The most convenient way to maintain SQL Server 2000 databases is to use the SQL Server 2000 Database Maintenance Plan Wizard. This wizard can regularly perform the following maintenance tasks:

• Reorganize databases to allow new data to be added more quickly.

• Compress databases to provide more database storage space.

• Update indexes to allow data to be located more quickly.

• Check databases to be sure they are not damaged.

• Back up databases and transaction logs to ensure that data can be restored to a specific point in time in the event of data loss.

• Duplicate transaction logs to another SQL Server to enable a standby server in case the first server is unavailable or busy. (This task is available only with SQL Server 2000 Enterprise Edition.)

To run the Database Maintenance Plan Wizard, once you have opened your database server, click the plus sign to open the Management folder, right- click the Database Maintenance Plans icon, and then click New Maintenance Plan. Complete the wizard’s pages to create the maintenance plan. The following sections describe some of the terminology used in the Database Maintenance Plan Wizard. For help on any of the wizard’s pages, click the Help button on that page.

System Databases Versus User Databases

System databases refer to databases that SQL Server uses internally to maintain data integrity across the server. System databases include information such as server login accounts, database templates, system alerts, and automated tasks. Some of the system databases you see in Enterprise Manager include the master, model, and msdb databases.

User databases refer to the databases in which you or your organization enter data for storage and analysis as part of conducting business. These databases generally include all databases except system databases. The best option to select on the Database Maintenance Plan Wizard’s Select Databases page is All Databases, as it creates a maintenance plan for all the databases on the server.

Indexes

As the name implies, indexes are used by SQL Server to look up information when you need to find data. Indexes are stored on index pages. A certain amount of free space is desirable on index pages so that new index entries can be added. Creating lots of index pages with a small number of index entries on each page slows down search requests. The Database Maintenance Plan Wizard’s Update Data Optimization Information page provides good default settings for optimizing database indexes. Selecting all the options on this page and using their default values provides good overall index optimization.

Backups and Transaction Logs

Backups are used to store copies of data in case the original data is lost or corrupted. Backups can be stored on storage media such as tape or in a folder on a drive connected to the SQL Server computer.

Transaction logs keep a record of data added, deleted, and changed in a database. When a database fails, SQL Server can use transaction logs to restore data as it was at a specific point in time. Without transaction logs, restoring a database to the state it was when the database failed can take much longer, and you might need to enter some of the data again.

When you create a database, you have the opportunity to create accompanying transaction logs. You should always create transaction logs at the same time you create a database, and you should always back up all your databases and transaction logs.

Securing SQL Server Databases

You should secure your SQL Server databases to prevent unauthorized access. You can also track SQL Server activity if you suspect you have a security issue with your SQL Server databases.

To secure the databases on a computer running SQL Server, depending on the type of security you want to allow, use Enterprise Manager to connect to the SQL Server and then do the following:

• Open the server’s Security folder, and then add login credentials for one or more databases on the server. You can also add linked servers or remote servers to handle the distribution of data requests on your system.

• Open the server’s Databases folder, expand a specific database icon, and add a user or security role to the individual database.

  Note

  For more information about SQL Server security, see SQL Server Books Online included with SQL Server.

You can use SQL Server Profiler to track SQL Server activities such as the success or failure of a login attempt or whether attempts to access database objects were successful. To use Profiler, point to Programs on the Start menu, point to Microsoft SQL Server, and then click Profiler. For help setting up event tracking in Profiler, click Contents And Index on the Help menu in Profiler.