This chapter is from the book
This chapter is from the book
This chapter is from the book
SQL Server Database Engine Management Tasks
The following sections cover additional tasks associated with managing the SQL Server Database Engine.
Changing SQL Server Configuration Settings
Presently, most of the configuration settings can be changed from within SQL Server Management Studio. These settings can also be changed using the SP_CONFIGURE TSQL command. The syntax to change configuration settings is
SP_CONFIGURE ['configuration name'], [configuration setting value] GO RECONFIGURE WITH OVERRIDE GO
The configuration name represents the name of the setting to be changed, and the configuration setting value is the new value to be changed. Before you can change settings, however, you must use the SP_CONFIGURE command. You must enable advanced settings by first executing the following script:
SP_CONFIGURE 'show advanced options', 1 GO RECONFIGURE GO
For a full list of configuration options, see SQL Server 2005 Books Online.
Managing Database Engine Informational Reports
To succeed in today's competitive IT industry, you must be armed with information pertaining to SQL Server 2005. SQL Server 2005 introduces a tremendous number of canned reports that can be opened directly from within SQL Server Management Studio. These reports provide information that allows you to maximize efficiency when conducting administration and management duties.
You can open these canned reports by right-clicking a SQL Server instance in Management Studio and selecting Reports and then Standard Reports. The standard reports include
- Server Dashboard
- Configuration Changes History
- Schema Changes History
- Scheduler Health
- Memory Consumption
- Activity - All Blocking Transactions
- Activity - All Cursors
- Activity - Tip Sessions
- Activity - Dormant Sessions
- Activity - Top Connections
- Top Transactions by Age
- Top Transactions by Blocked Transactions Count
- Top Transactions by Locks Count
- Performance - Batch Execution Statistics
- Performance - Object Execution Statistics
- Performance - Top Queries by Average CPU Time
- Performance - Top Queries by Average IP
- Performance - Top Queries by Total CPU Time
- Performance - Top Queries by Total IP
- Server Broker Statistics
- Transaction Log Shipping Status
The standard report titled Server Dashboard, displayed in Figure 1.14, is a great overall report that provides an overview of a SQL Server instance, including activity and configuration settings.
)
Figure 1.14 Viewing the standard Server Dashboard SQL Server canned report.
Detaching and Attaching Databases
Another common task you must conduct is attaching and detaching databases.
Detaching a Database
When a database is detached, it is completely removed from a SQL Server instance; however, the files are still left intact and reside on the file system for later use. Before a database can be detached, all user connections must be terminated; otherwise, this process fails. The detach tool includes the options to automatically drop connections, update statistics, and keep full text catalogs.
To drop the sample AdventureWorks database, follow these steps:
- In Object Explorer, first connect to the Database Engine, expand the desired server, and then expand the Database folder.
- Select the AdventureWorks database, right-click on the database, select Tasks, and then select Detach.
- In the Detach Database dialog box, enable the following options, as displayed in Figure 1.15: Drop Connections, Update Statistics, and Keep Full Text Catalogs. Click OK.
Figure 1.15 Specifying detach settings on the Detach Database dialog box.
)
Attaching a Database
Here's a common usage scenario for attaching databases: Say you need to move the database from a source to a target SQL Server. When a database is attached, the state of the database is exactly the same as when it was detached.
The following steps illustrate how to attach a database with SQL Server Management Studio:
- In Object Explorer, first connect to the Database Engine, expand the desired server, and then select the Database folder.
- Right-click the Database folder and select Attach.
- In the Attach Databases dialog box, click the Add button to add the database to be attached.
- In the Locate the Database Files dialog box, specify the path to the *.mdf file and click OK.
- Optionally, change the name or owner of the database.
- Click OK to attach the database.
Alternatively, you can use the following TSQL syntax to attach the AdventureWorks database:
USE [master] GO CREATE DATABASE [AdventureWorks] ON ( FILENAME = N'D:\AdventureWorks_Data.mdf' ), ( FILENAME = N'D:\AdventureWorks_Log.ldf' ) FOR ATTACH GO if exists (select name from master.sys.databases sd where name = N'AdventureWorks' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [AdventureWorks].dbo.sp_changedbowner @loginame= N'COMPANYABC\SQL.Service', @map=false GO
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:
- Right-click on a desired database such as AdventureWorks, select Tasks, and then select Take Offline.
- 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 }
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
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:
- In Object Explorer, right-click the name of the database and select Rename.
- Type in the new name for the database and press Enter.