Microsoft has converted the trunc. log on chkpt. and Select Into/Bulk Copy options to the recovery model setting for easier usability. The goal of this option change is to ensure that DBAs fully understand what setting the recovery model option means to a disaster-recovery strategy. SQL Server 2000 gives you three recovery models for your database: simple, full, and bulk_logged.
The simple recovery model is the easiest to administer but is the least flexible disaster-recovery strategy. Choosing a simple recovery model is the equivalent of setting trunc. log on chkpt. to true. With this recovery model, you can perform only full and differential backups: Because your transaction log is constantly being truncated, transaction log backups aren't available. Generally, for a production system that contains mission-critical data, you wouldn't use this recovery model because it doesn't let you recover to the point of failure. The best "recover to" time you can provide with this recovery model is the time of the last known good full and differential backups. In this model, you'd have to restore to the last known good full backup, then restore the last differential backup over it. (The differential backup applies just the changes to the database that have occurred since the last full backup.)
The full recovery model sets both the trunc. log on chkpt. option and the Select Into/Bulk Copy option to false. This configuration gives full point-in-time recovery because all backup options are available. This model is ideal for protecting a production environment that contains mission-critical data, but it leads to a higher cost in equipment and administration because large transaction logs can build up quickly if you have an active database system. Because Select Into/Bulk Copy is set to false in this model, SQL Server logs all events, including bulk loads.
The final recovery model is bulk_logged, which sets trunc. log on chkpt. to false and Select Into/Bulk Copy to true. SQL Server doesn't log all bulk operations into the transaction log in this model. Instead, it minimally logs bulk operations and logs the extents where the data changed. When you perform a transaction log backup, SQL Server backs up not only the transaction log, but also the extents where the bulk operations occurred. This model results in a smaller transaction log than the full recovery model, but a much larger transaction log backup if bulk operations occurred. However, using full, differential, and transaction log backups still gives you the point-in-time recoverability if no bulk operations were performed.
You can change the recovery mode to simple by selecting Simple from the Model drop-down list box on the database Options tab. Microsoft also extended the ALTER DATABASE command to let you set database properties. For example, you can use the following T-SQL syntax to set the recovery model to full:
ALTER DATABASE Northwind
SET RECOVERY FULL
Some of the coolest new behind-the-scenes setting changes in SQL Server 2000 are the various options you have for placing a database in single-user mode. DBAs often place a database in single-user mode to fix corruption or other data problems. While a database is in this mode, no other user can access the data, so you can correct data problems before users can use the bad data. With SQL Server 7.0, you have to ensure that all users are disconnected before you can place a database in single-user mode. In a high-speed OLTP database, such as an e-commerce system, this process is extremely difficult because as fast as you can disconnect a user with the KILL statement, another user connects. SQL Server 2000 improves this process dramatically, enabling you to give users a specific amount of time to complete their transactions before you disconnect them automatically. You can also disconnect all connections from a database without any delay.
Microsoft expanded the ALTER DATABASE command to support the commands necessary to place your database in single-user mode. One method of placing a database in single-user mode is to check the Restrict Access check box, then select Single user on the database Options tab. Alternatively, you could use the ALTER DATABASE command:
ALTER DATABASE Northwind
SET SINGLE_USER
After you execute the command, SQL Server waits for all connections to the database to complete their transactions. Any users who try to connect while the database is in this state receive the error message that Figure 2 shows and are directed to their default database (typically the Master database). This error message means that the database is frozen until all users have disconnected. If the target server or the person issuing the command doesn't have the query timeout setting set, the client could wait indefinitely for the query to finish until all active connections are disconnected. In Query Analyzer, you can specify a query timeout setting in seconds under the Connections tab on the Options screen (select Tools, Options). Only the user who issued the ALTER DATABASE command can remain connected.
You can also disconnect any users who have open connections to the server by using the ROLLBACK IMMEDIATE command. You can't use Enterprise Manager with this command; you must use Query Analyzer to execute the following query:
ALTER DATABASE Northwind
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
After you execute this command, SQL Server drops all connections and rolls back their transactions immediately. All users performing a transaction will receive a connection error and will be unable to reconnect to the database.
You can optionally specify a certain amount of time that SQL Server will give users to complete their transactions before it disconnects them. You use the ROLLBACK AFTER keywords as the following query shows:
ALTER DATABASE Northwind
SET SINGLE_USER
WITH ROLLBACK AFTER 20 SECONDS
SQL Server will wait 20 seconds after you issue this command, then disconnect any connections and roll back their transactions. New users aren't allowed to connect during this process, which is a database-level version of pausing the server. Any new users trying to connect to the database during the 20 seconds will receive the error message that Figure 2 shows. If no users are connected at the time you execute the query, the database will go into single-user mode immediately.
The final setting new to Enterprise Manager's database Options tab is the compatibility level, which Figure 1 shows. To set the option, select the appropriate level in the Compatibility Level drop-down box. SQL Server 2000 is represented as 80, 7.0 as 70, and 6.5 as 65. The compatibility-level option determines how some database query actions will perform. Because SQL Server's relational engine has evolved, some query results might vary from release to release. To preserve backward compatibility for queries, Microsoft implemented compatibility levels at the database level in SQL Server 7.0. For example, if you ran the query
SELECT DATALENGTH('')
you would get two different results depending on whether you set your database's compatibility level for SQL Server 2000, 7.0, or 6.5. A SQL Server 2000 or 7.0 database would return a result of 0 because SQL Server 2000 and 7.0 treat empty strings as truly empty. In SQL Server 6.5 compatibility mode, a database would return a result of 1 because SQL Server 6.5 interprets an empty string as a single space. This compatibility-level setting exists in SQL Server 7.0 but is accessible only through the stored procedure sp_dbcmptlevel.
In SQL Server 2000, Microsoft made several substantial new server and database configuration changes. Don't take these and other SQL Server settings lightly: Even the smallest tweak could have a serious positive or negative performance impact. As you tweak settings in SQL Server, use a server-monitoring tool such as Performance Monitor to make sure your server experiences no performance erosion after the adjustments.
End of Article
Prev. page
1
[2]
next page -->
