You perform most database administration work through SQL Server Management Studio. Use SQL Server Management Studio to carry out many common database administration tasks, including:
This section examines each of these tasks.
SQL Server organizes information using a top-down hierarchy that starts with server groups at the highest level and then moves down to servers, and then to databases, and then to objects. Therefore, you must work your way down to the database level to view the databases installed on a particular server instance. If you have registered a server instance and have connected to it previously, you can view its databases by completing the following steps:
In SQL Server Management Studio, use Registered Servers view to select a type of server, such as Database Engine. If you need to expand a server group to see the servers listed in the group, click the plus sign (+) next to the name of the group.
In Registered Servers view, select a server by double-clicking its name in the list. This connects you to the server in Object Explorer view.
Note
If the SQL Server service is stopped, you must restart it before accessing the server. Additionally, if you have not authenticated the server connection, you may need to provide a SQL login account and password. You may also need to reestablish a connection with the server. In either case, enter any necessary information and then click OK/Yes to continue.
In Object Explorer view, click the plus sign (+) next to the server’s Databases folder to see a list of the databases available on the server.
Right-click the database you want to work with, and then select Properties. This displays the Database Properties dialog box shown in Figure 7-1.
The Database Properties dialog box includes several different properties pages:
General. Provides general database information, such as status, owner, date created, size, and space available. Also details the last backup date and collation setting.
Files. Provides details on the data and log files associated with the database. If the database has been configured for full-text search, the Use Full-Text Indexing check box is selected. However, catalog files associated with the database are not listed.
Filegroups. Lists the filegroups associated with the database and allows you to add or remove filegroups.
Options. Provides boxes for viewing and managing standard database options and settings.
Permissions. Lists users or roles that have specific permissions allowed or denied in the database and allows you to set database permissions for users or roles.
Extended Properties. Provides boxes for viewing and managing extended database properties.
Mirroring. Provides boxes for viewing and managing database mirroring settings.
Transaction Log Shipping. Details the current log shipping configuration (if any) and allows you to manage log shipping.
You can also use Transact-SQL (SQL) to examine database information. Transact-SQL is an enhanced version of the standard structured query language that SQL Server uses. In SQL Server Management Studio, access the Query view. You can do this by right-clicking the name of a server to which you have already connected in Object Explorer view, and then selecting New Query. Alternately, click New Query on the main toolbar, select Database Engine Query, and then establish a connection to the database engine on a specific server.
Once you have accessed the Query view, use the following command to view database information:
sp_helpdb <dbname>
gowhere dbname is the name of the database you want to examine.
When you view database information in this way, you get an overview of the database as well as a listing of current data and log files. Table 7-1 summarizes the information available when you view database properties using T-SQL. This data is returned in two different result sets; you will need to scroll down in the Results pane to see the additional result set.
Table 7-1. Database Properties Viewable Using T-SQL
Column Name | Description |
|---|---|
compatibility_level | The current compatibility level of the database. The level 90 indicates SQL Server 2005 compatibility. |
created | The date the database was created. |
db_size | The total size of the database including all data and log files. |
dbid | The unique identifier for the database on the current server. |
filegroup | The filegroup associated with the database file. Filegroups allow you to group sets of database files together. |
fileid | The unique identifier for the file in the current database. |
filename | The full file name and path. |
growth | The number of megabytes or percent by which the file grows. |
maxsize | The maximum file size. Unlimited means there is no limit. |
name | The name of the database or file (without a file extension). |
owner | The database owner. |
size | The current size of a file. |
status | The database status. |
usage | The way the file is used, such as data only or log only. |
A new SQL Server installation includes the system databases listed in Table 7-2. System databases are critical to the proper operation of SQL Server, and an important aspect of the administration process is backing up and maintaining these databases. Sample databases can also be installed, but they are meant only to provide examples and do not need regular maintenance.
Table 7-2. Summary of System Databases
Database Name | Database Type | Description |
|---|---|---|
master | System | Maintains information on all databases installed on the server. This database is modified anytime you create databases, manage accounts, or change configuration settings. Back up the master database regularly. |
model | System | Provides a template for all new databases. If you want new databases to have certain properties or permissions, put these changes in the model database, and then all new databases will inherit the changes. |
tempdb | System | Provides a temporary workspace for processing queries and handling other tasks. This database is recreated each time SQL Server is started, and it is based on the model database. |
msdb | System | Used by the SQL Server Agent service when performing handling alerts, notifications, and scheduled tasks. You can access all the information in this database using SQL Server Management Studio options. |
distribution | System/Replication | Used by Replication Services when you configure a server as a publisher, distributor, or both. This database is created when you configure replication, and it is not created automatically with a new installation. |
Real World
You can install the sample databases during or after setup. To install sample databases during setup, select Workstation Components | Books Online And Development Tools on the Components To Install page, and then click Advanced, expand Books Online And Samples, expand Databases, expand Samples, and then select the sample databases and samples to install. To install sample databases after setup, use Add Or Remove Programs. In the Add Or Remove Programs dialog box, select Microsoft SQL Server 2005 and click Change. Setup will then inspect your system. On the Change Or Remove Instance page, select Workstation Components as the existing component to change and click Change Installed Components. From Feature Selection, expand the Books Online And Samples node, expand Databases, expand Samples, and then select the sample databases or samples to be installed. To install and attach the sample database, select Attach Sample Databases during the setup process.
The main elements of a SQL Server database are referred to as objects. The objects you can associate with a database are:
Constraints
Defaults
Indexes
Keys
Stored procedures
Extended stored procedures
Tables
Triggers
User-defined data types
User-defined functions
Views
You can also associate users, roles, rules, and full-text catalogs with databases.
To examine objects within a database, complete the following steps:
In SQL Server Management Studio, use Registered Servers view to select a type of server, such as Database Engine. If you need to expand a server group to see the servers available in the group, click the plus sign (+) next to the name of a group.
In Registered Servers view, select a server by double-clicking its name in the list. This connects you to the server in Object Explorer view.
In Object Explorer view, work your way down to the database level. Expand the Databases folder, and then expand the entry for a specific database to see a list of nodes for database objects, including:
Database Diagrams. Contains database diagrams, which are visual diagrams of a database and the information it contains. Use Database Designer to create and manage diagrams.
Tables. Contains system and user tables. System tables are used for many purposes including database mail, database maintenance plans, replication, backup and restore, and log shipping. System tables should not be modified directly.
Views. Contains system and user views. Standard views combine data from one or more tables to make it easier to work with the data. Indexed views have unique clusters to improve query performance. Partitioned views join horizontally partitioned data from tables on one or more servers.
Synonyms. Contains synonyms, which are alternate names for schema scoped objects. Applications can use synonyms to refer to objects in the database abstractly. You could then change the underlying name of the object without having to modify the application programming.
Programmability. Contains nodes used to represent most programmable object types and subtypes, including stored procedures, functions, triggers, assemblies, data types, rules, and defaults.
Service Broker. Contains Service Broker–related objects, including message types, contracts, queues, services, routes, and remote service bindings.
Storage. Contains storage-related objects, including full-text catalogs, partition schemes, and partition functions.
Security. Contains security-related objects, including users, roles, schemas, and keys.
Note
Database objects are covered in detail in chapters in Part III. For example, you will find more information on tables, indexes, and views in Chapter 9.