Controlling Databases with SQL Server 2005 Database Engine

Scripting Database Objects

SQL Server 2005 has two levels of scripting functionality that assist you in automatically transforming a SQL Server task or action to a TSQL script. The scripting functionality is a great way to automate redundant administration responsibilities or settings. Moreover, you don’t have to be a TSQL scripting expert to create solid scripts.

You can generate a script from within a majority of the SQL Server dialog boxes or pages. For example, if you make changes to the SQL Server Processor Properties page, such as enabling the options Boost SQL Server Priority or User Windows Fibers, you can click the Script button at the top of the screen to convert these changes to a script. In addition, this script can be fired on other SQL Servers to make the configuration automatically consistent across similar SQL Servers.

When you click the Script button, the options available are Script Action to New Query Window, Script Action to File, Script Action to Clipboard, and Script Action to Job.

Another alternative to creating scripts is right-clicking a specific folder within Object Explorer and selecting Script As or right-clicking a database, selecting Tasks, and then selecting Generate Script to invoke the Script Wizard. Some of these tasks include scripting database schemas, jobs, tables, stored procedures, and just about any object within SQL Server Management Studio. Additional scripting statements include Create, Alter, Drop, Select, Insert, and Delete.

Backing Up and Restoring the Database

Creating a backup and recovery strategy is probably the most important task you have on your plate. When you’re creating backups, it is imperative that you understand the recovery models associated with each database such as Full, Simple, and Bulk-Logged and understand the impact of each model on the transaction log and the recovery process. In addition, it is a best practice to back up the user databases, but to restore a full SQL Server environment, the system database should be included the backup strategy.

For more information on recovery models and backing up and restoring a SQL Server environment, see Chapter 17. That chapter focuses on backing up all components of SQL Server, such as the Database Engine, Analysis Services, Reporting Services, and Information Services.

Transferring SQL Server Data

There are many different ways to transfer data or databases from within SQL Server Management Studio. There are tasks associated with importing and exporting data and copying and/or moving a full database with the Copy Database Wizard. To use the transferring tasks, right-click a database, select Tasks, and then select Import Data, Export Data, or Copy Database.

Each of these ways to move data is discussed in its entirety in Chapter 11, “Creating Packages and Transferring Data.”

Taking a SQL Server Database Offline

As a database administrator, you may sometimes need to take a database offline. When the database is offline, users, applications, and administrators do not have access to the database until it has been brought back online.

Perform the following steps to take a database offline and then bring it back online:

1. Right-click on a desired database such as AdventureWorks, select Tasks, and then select Take Offline.
   
2. In the Task Database Offline screen, verify that the status represents that the database has been successfully taken offline and then select Close.

Within Object Explorer, a red arrow pointing downward is displayed on the Database folder, indicating that the database is offline. To bring the database back online, repeat the preceding steps but select Online instead.

In addition, you can use the following TSQL syntax to change the state of a database from Online, Offline, or Emergency:

  ALTER DATABASE database_name 
  <db_state_option> ::=
      { ONLINE | OFFLINE | EMERGENCY }

Note

When the database option is configured to an Emergency state, the database is considered to be in single-user mode; the database is marked as read-only. This mode is meant for addressing crisis situations.

Shrinking a Database

The Shrink Database task reduces the physical database and log files to a specific size. This operation removes excess space in the database based on a percentage value being entered. In addition, you can enter thresholds in megabytes, indicating the amount of shrinkage that needs to take place when the database reaches a certain size and the amount of free space that must remain after the excess space is removed. Free space can be retained in the database or released back to the operating system.

The following TSQL syntax shrinks the AdventureWorks database, returns freed space to the operating system, and allows for 15% of free space to remain after the shrink:

USE [AdventureWorks]
GO
DBCC SHRINKDATABASE(N’AdventureWorks’, 15, TRUNCATEONLY)
GO

Tip

It is best practice not to select the option to shrink the database. First, when shrinking the database, SQL Server moves pages toward the beginning of the file, allowing the end of the files to be shrunk. This process can increase the transaction log size because all moves are logged. Second, if the database is heavily used and there are many inserts, the database files will have to grow again. SQL 2005 addresses slow autogrowth with instant file initialization; therefore, the growth process is not as slow as it was in the past. However, sometimes autogrow does not catch up with the space requirements, causing performance degradation. Finally, constant shrinking and growing of the database lead to excessive fragmentation. If you need to shrink the database size, you should do it manually when the server is not being heavily utilized.

Alternatively, you can shrink a database by right-clicking a database and selecting Tasks, Shrink, and Database or File.

Renaming a Database

The following steps illustrate how to change the name of a database by using SQL Server Management Studio:

1. In Object Explorer, right-click the name of the database and select Rename.
   
2. Type in the new name for the database and press Enter.

Administering the SQL Server Agent

The SQL Server Agent is a Microsoft Windows Service that executes scheduled tasks configured as SQL Server jobs. Ultimately, in SQL Server 2005, any task can be transformed into a job; therefore, the task can be scheduled to reduce the amount of time wasted on manual database administration. The SQL Server Agent can be managed from within SQL Server Management Studio.

Note

The SQL Server Agent service must be running to execute jobs and tasks. This is the first level of defense when you’re troubleshooting why agent jobs are not firing.

Administering the SQL Server Agent Properties

Before utilizing the SQL Server Agent, you should first verify and configure the Agent properties to ensure that everything is copacetic.

The SQL Server Agent Properties dialog box has six pages of configuration settings, described in the following sections.

The General Page

The SQL Server Agent page maintains configurable settings such as Auto Restart SQL Server if It Stops Unexpectedly and Auto Restart SQL Server Agent if It Stops Unexpectedly.

From a best practice perspective, both the restart settings should be enabled on mission-critical databases. This prevents downtime in the event of a server outage because the service will restart if failure is inevitable.

You can change the error log path if preferred and configure a send receipt via the Net send command. In addition, you can include execution trace messages to provide meticulous information on SQL Server Agent operations.

The Advanced Page

The Advanced page controls the behavior of SQL Server Event Forwarding and Idle CPU conditions. It is possible to forward unhandled events, all events, or events based on predefined severity levels selected in the drop-down list to a different server. The target server must be specified in the server drop-down list. The differences between unhandled and handled events are that unhandled events forward only events that no alert responds to, whereas handled events forward both the event and the alert. The final section is tailored toward SQL Server Agent and CPU settings. These settings define the conditions when jobs will run based on values such as Average CPU Usage Falls Below in Percentage and And Remains Below This Level for In Seconds.

Note

In enterprise production environments, a SQL Server instance should have enough processing power that these CPU conditions settings are not required.

The Alert System Page

The Alert System page includes all the SQL Server settings for sending messages from agent alerts. The mail session settings are based on the prerequisite task of configuring SQL Server Database Mail. These topics are discussed in Chapter 21, “Monitoring SQL Server 2005” (online).

The Job System Page

The Job System page controls the SQL Server Agent shutdown settings. You can enter a numeric value based on a time increment that governs how long a job can run before automatically being shut down. It is also possible to specify a nonadministrator Job Step Proxy Account to control the security context of the agent; however, this option is available only when you’re managing earlier SQL Server Agent versions.

The Connections Page

The Connections Page should be used to configure a SQL Server alias for the SQL Server Agent. An alias is required only if a connection to the Database Engine will be made without using the default network transport or an alternate named pipe.

The History Page

You should use the final page, History, for configuring the limit size of a job history log setting. The options include setting maximum job history log size in rows and maximum job history rows per job.

Administering SQL Server Agent Jobs

The first subfolder located under the SQL Server Agent is the Job folder. Here, you create new jobs, manage schedules, manage job categories, and view the history of a job.

Follow these steps to create a new job:

1. In Object Explorer, first connect to the Database Engine, expand the desired server, and then expand the SQL Server Agent folder.
   
2. Right-click the Jobs folder and select New Job. 
    
3. On the General page in the New Job dialog box, enter a name, owner, category, and description for the new job. 
    
4. Ensure that the Enabled check box is set to True, as illustrated in Figure 1.16. 
    
5. Click New on the Steps page. When the New Job Steps page is invoked, type a name for the step and enter the type of job this will be. The options range from Transact-SQL, which is the most common, to other items such as stored procedures, Integrations Services packages, and replication. For this example, select TSQL Type and enter the following TSQL syntax in the command window:
   
   BACKUP DATABASE [AdventureWorks] TO DISK =
   N’C:\Program Files\Microsoft SQL Server \MSSQL.1\MSSQL\Backup\AdventureWorks.bak’
   WITH NOFORMAT, NOINIT,
   NAME = N’AdventureWorks-Full Database Backup’,
   SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO 
   
6. From within the General page, parse the command to verify that the syntax is operational and click the Advanced page.
   
   
   Figure 1.16.  Specifying the Create New Job Details on the New Job dialog box.
   
7. The Advanced page includes a set of superior configuration settings. For example, you can specify actions on successful completion of this job, retry attempts including intervals, and what to do if the job fails. This page also includes Output File, Log to Table, History, and the potential to run the job under a different security context. Click OK to continue.  
   
8. Within the New Job dialog box, you can use the Schedules page to view and organize schedules for the job. Here, you can create a new schedule or select one from an existing schedule. 
   
9. Click OK to finalize the creation of the job.

Enabling or Disabling a SQL Server Agent Job

Each SQL Server Agent job can be either enabled or disabled by right-clicking the job and selecting either Enable or Disable.

Viewing SQL Server Agent Job History

From a management perspective, you need to understand whether a SQL Server Agent job was fired properly, completed successfully, or just outright failed. The Job History tool, which is a subcomponent of the Log File Viewer, provides thorough diagnostics and status of job history. Perform the following steps to review job history for a SQL Server Agent job from within SQL Server Management Studio:

1. In Object Explorer, first expand the SQL Server Agent and then the Jobs folder.
   
2. Right-click a desired job and select View Job History. 
    
3. In the Log File Viewer, review the log file summary for any job from within the center pane. 
    
4. Choose from additional options such as loading saved logs, exporting logs, creating a filter, parsing through logs with the search feature, and deleting logs.

Administering SQL Server Alerts and Operators

The SQL Server Alerts and Operators folders are used for monitoring the SQL Server infrastructure by creating alerts and then sending out notifications to operators. For more information on creating alerts and operators, review Chapter 21.

Administering SQL Server Proxies

The Proxies Folder found within the SQL Server Agent enables you to view or modify the properties of the SQL Server Agent Proxy account. You enter a proxy name and credentials and select the subsystem the proxy account has access to.

Administering SQL Server Error Logs

The final folder in the SQL Server is Error Logs. You can configure the Error Logs folder by right-clicking the folder and selecting Configure. The configuration options include modifying the error log file location, reducing the amount of disk space utilized by enabling the option Write OEM Error Log, and changing the Agent Log Level settings. These settings include enabling Error, Warnings, and/or Information.

Perform the following steps to view SQL Server Agent Error Logs:

1. In Object Explorer, first expand the SQL Server Agent and then the Error Logs folder.
   
2. When all the error logs are listed under the Error Logs folder, double-click any of the error logs to view them.

Summary

The Database Engine is the core component within SQL Server; it provides a key service for storing, processing, and securing data. SQL Server 2005 Service Pack 2 introduces many new features that improve your success at administering and managing this core component. In addition, reading this chapter will help you to fully understand how to manage and administer the SQL Server instance server properties, Database Engine folders, database properties, and SQL Server Agent.

Best Practices

Following is a summary of some of the best practices from the chapter:

• Leverage the scripting utility within SQL Server Management Studio to transform administration tasks into TSQL code.
  
• Unless there is a specific need to do otherwise, it is a best practice to allow SQL Server to dynamically manage the minimum and maximum amount of memory allocated to SQL Server. However, if multiple applications are running on SQL Server, it is recommended to specify minimum and maximum values for SQL Server memory. Therefore, the application cannot starve SQL Server by depriving it of memory. 
   
• The preferred authentication mode is Windows Authentication over SQL Server Authentication because it provides a more robust authorization mechanism. 
   
• Configuring SQL auditing is recommended to capture both failed and successful logins. 
   
• Do not set the database to automatically shrink on a regular basis because this leads to performance degradation and excessive fragmentation over time. 
   
• The first Database Engine administration task after a successful SQL installation should involve tuning and configuring the server properties.  

• Configure the recovery model for each database accordingly and implement a backup and restore strategy. This should also include the system databases. 
   
• Database files, transaction log files, and operating system files should be located on separate volumes for performance and availability. 
   
• When multiple database files and transaction log files exist, organize them through the use of filegroups. 
   
• Create basic reports in Management Studio to better understand the SQL Server environment. 
   
• Automate administration tasks by using SQL Server 2005 Agent jobs.