MS SQL Server 2005 Management Studio Part 1
October 6, 2006
Included with SQL 2005 is the new Management Studio graphical interface. This tool combines many of the features that were previously found in the SQL Sever 2000 Enterprise Manager and Query Analyzer. Those features are now combined into one interface along with a high number of enhancements. This article will introduce many of the features and options of the new Management Studio so your project can get up and running rapidly. Next month, we will touch on the Business Intelligence Development Studio, another new GUI included with SQL 2005.
When starting up the Management Studio, several panels or windows will appear. Below are pictured the Registered Servers, Object Explorer, and Query panels.
Windows and panels can be turned on or off by selecting them on the View menu located on the top tool bar.
Registered Servers Panel
Database server connections can be saved and organized in the Registered Servers panel. Once a server is registered, it can be managed and displayed in the Object Explorer panel. Below are the steps to create a new registration;
1. Click the Database Engine icon. (The yellow cylinder under the words Registered Servers)
2. Right click Database Engine inside the Registered Servers panel.
3. Select New, Server Registration.
4. Enter the server and connection information.
The other option listed under New was to create a Sever Group. Groups allow a collection of servers to be organized inside a common folder.
Right clicking on the server once it is registered allows you to Start and Stop the server. In addition, its Configuration Manager can be invoked.
Double clicking the new registration will connect the Management Studio to the database server and present it in the Object Explorer panel.
The Object Explorer can be used to graphically display almost every object associated with a Sever, such as databases, procedures, views, security, and jobs.
Drilling down into the Databases folder will display all the tables, views, procedures (located under the programmability folder), and security associated with the database. The Security folder contains logins and roles. The Server Objects folder holds Endpoints, Linked Servers, and Triggers. Replication holds publications. The Management folder contains Maintenance plans, Database Mail, Full Text, and the Distributed Transaction Coordinator. Lastly, there is a folder for Notification Services.
To create a new database from the Object Explorer, right click Databases and select New Database, then provide a database name.
The SQL 2000 tool Query Analyzer has now been integrated into the Sever Manager. To create a new query, follow the steps below;
1. Start in the Object Explorer.
2. Expand the Databases folder.
3. Right Click a database name and select New Query.
This will bring a blank query window into the Query panel. There should also be SQL Editor tool bar located along the top border as shown below.
If the tool bar isnt visible, right click on an empty space in the top menu bar; a sub menu of available tool bars should appear. Select SQL Editor.
Rather than construct queries by hand in the Query panel, they can be designed graphically in a tool called Query Editor. The tool is very similar to designing queries in Microsoft Access and is pictured below.
This tool existed in SQL 2000, but was limited to only SELECT queries. UPDATES and DELETES were not supported. In 2005, they are now supported along with INSERTS. To invoke the Query Designer, right click anywhere in a blank query window and select Design Query in Editor. By default, the designer is set to SELECT. To change to some other type, such as INSERT, right click anywhere in the designer, then select Change Type, as shown below.
The Solution Explorer is new in SQL 2005. This panel allows for a collection of TSQL scripts to be organized into a Visual Studio style project and solution and is displayed below. If the panel isnt visible, enable it by selecting View from the top menu, then Solution Explorer.
Besides the organization benefit, solutions can be added to Microsoft Source Safe. Source Safe is a separate application used for versioning control. If you are already using Visual Studio with Source Safe, youll find the same concepts you currently use, like check in check out, apply. To initially setup source control, go to the top menu and select Tools, Options, then Source Control.
Maintenance Plans are packages that can be executed or scheduled to do house keeping tasks such as backups, re-indexing, and history cleanup. Plans are found in the Object Explorer, inside the Management Folder, under Maintenance Plans. The SQL 2000 Maintenance Job Wizard is included in SQL 2005, but has been expanded to include additional options. To create a plan, right click Maintenance Plans and select either the Wizard or to manually create one. If you receive an Agents XP error, run the following script;
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE GO
Selecting the wizard will being up an easy to follow, Click Next, graphical interface for creating Maintenance Plans. However, the manual option is almost as easy. After selecting the option to create a manual plan, a new window of tasks that can be dragged and dropped is presented. These are placed on a form one at a time then connected to each other as shown below.
The Activity Monitor gives a quick view of database locks and processes. The Monitor is found in the Object Explorer, under Management. There are three views. The first shows total system processes, the second displays Locks by Process, and the third shows Locks by Objects. All three can be filtered when searching for something specific.
Many useful tools have been added to the new SQL 2005 Management Studio. This article introduced only a few of the more common panels. Next month, well continue with Diagrams for Foreign Key mapping, and the Debugging of stored procedures.