Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
SQL Server 2008 Management Tools
Last updated Mar 28, 2003.
Microsoft has made several changes in the tools area for each release of SQL Server for the past few years. One of the largest jumps was from SQL Server 2000 to 2005. They changed the main graphical management tools in two ways. The first was that they combined the object-navigation tool, called Enterprise Manager, and the graphical query-writing tool, called Query Analyzer into one tool, called SQL Server Management Studio, or SSMS. I'll spend the bulk of this overview there, and in subsequent tutorials I'll show you how to use other management tools such as the SQL Server Profiler, PowerShell and SQLCMD. I will briefly cover those tools here so that you're aware of them, but they deserve their own articles.
Another major change Microsoft made in the management tools is that they based the SSMS tool on Visual Studio, instead of the Microsoft Management Console, or MMC. They had to rewrite the tools to work in this new "shell," but it gave them certain advantages. Keep in mind that you don't have to install Visual Studio to get this new shell — Microsoft created a single installation that uses a customized version of the Visual Studio shell to house the new tools.
This change wasn't without its critics. Even though this new shell had great advantages, it was a change. And, in the midst of these changes, some features that existed in version 2000 were lost in the translation, such as the Transact-SQL Debugger and Object Search.
But as time went on, SSMS got new fixes and enhancements with each Service Pack. Reports were added, and then the ability to have custom reports of your own. The Activity Monitor had everything you needed to track down a pesky query, and the right-click menu added new actions for them. In SQL Server 2008, even more enhancements and fixes were added, as well as a return of the T-SQL Debugger and Object Search.
For new features such as Analysis Services and Reporting Services, a separate set of tools was created, called the Business Information Development Studio, or BIDS. This is also based on Visual Studio, even more closely related to the developer environment, creating a Project to hold the work and so on. I won't focus on that tool in this overview, since it deals more with designing solutions than managing systems.
In SQL Server 2008, Microsoft has kept the same structure for the management tools as in SQL Server 2005, and by and large that's a good thing. Imagine having to learn yet another set of management tools, to say nothing of the time it would have added to the development schedule for SQL Server 2008. But there have been significant changes and improvements to the tools. Let's focus on SSMS in this overview, with a brief stop at the Configuration Manager.
I'll start with the primary tool you need to configure your server to allow connections. In SQL Server 2000, configuration options are set using a combination of registry entries, Windows Control Panel Applets, SQL Server Enterprise Manager, and the sp_configure stored procedure in Query Analyzer. In SQL Server 2005, all of these settings are combined into just two tools: the Configuration Manager and the Surface Area Configuration tool (SAC). In SQL Server 2008, the Surface Area Configuration tool is gone (more on that later) and the Configuration Manager remains the same.
The Configuration Manager tool is a Microsoft Management Console (MMC) application that consolidates the settings involving the services and network information for SQL Server. From here you can stop and start the services for the database engine and other features such as the SQL Server Agent and Analysis Services. You can also enable or disable network protocols for SQL Server, and even change the ports the services listen on.
To change the service account or startup type for a SQL Server service, double-click the service name in the right-hand side of the panel. Clicking the Service and Advanced tabs within this panel allows you to configure startup options, locations and other information for each service.
To change the network settings for a SQL Server Instance or a SQL Server Client, expand the item and double-click the protocol name.
Each protocol has properties, settings and options based on what the protocol provides. Click on the various tabs within this panel to change a setting. I'll show you some other interesting uses for this tool in other tutorials.
SQL Server Management Studio
The primary interface for the Database Administrator is the SQL Server Management Studio (SSMS). As I mentioned earlier, this tool uses a Visual Studio-based interface which provides a set of development and management tools in one location with a full editing and properties screens, scripting support and other features. In SQL Server 2008, several new enhancements are added such as Object Search, an enhanced details panel, “Intellisense”, the Transact-SQL Debugger and more.
When you open SSMS, you're presented with a panel that allows you to select which Instance of SQL Server you want to connect to. You're also allowed to select the type of engine you want to connect to, such as the database engine or Analysis Services. Enter your SQL Server login credentials, or select Windows Authentication to use your current login. There are also options to set the protocol, encryption and so on. And in SQL Server 2008, there's yet another panel that allows you to enter additional connection parameters. That panel uses the same connection strings that you use in a .NET connection — more on those here: http://www.connectionstrings.com/?carrier=sqlserver.
There are many ways to configure the screen, so you may have panels open that aren't shown or you may have them arranged differently. If you want to put them back to the way they were originally, just pick "Reset Layout" from the "View" menu. If you do that now, you can follow along with the descriptions in this overview. After you finish reading this overview, you should experiment with the layouts by using the "View" menu item, displaying the screens you want, and using the small arrow icons on them to set them to "float", which allows you to put them all over your screen.
There are two primary parts to the default screen layout. The first is the Object Explorer pane, which is shown in the left side of the SSMS screen. This is the primary view you can use to work with objects. You can navigate the objects it shows by clicking once on the small "plus-sign" icon next to an object, or by double-clicking it. If you have a query window open (more on that in a moment), you can also click and drag an object onto it, and the text of the name of the object will be placed there for you.
Right-clicking any object brings up a context-sensitive menu for that object. As an example, you can click the plus-sign next to the "Databases" object and select a database. Right-click that database name and select "Properties" from the menu that appears to show the general properties that you can set or read.
There are several areas within the Properties panel that show or set information about the database. The information can be sorted or grouped, depending on the type of information. Any changes made in a panel can be saved to a set of Transact-SQL commands by clicking the Script button in the top part of the panel. A new query window is created to hold the commands. From there you can save the script or run it.
The next area is just to the right, and it contains a Query window, the Object Explorer Details pane or the Activity Monitor. As you add more views to SSMS, some of them will show up in this second area on the right. In this area you have several buttons that deal with the display content across the tab. The first navigates up through the list, the second refreshes the panel, the third synchronizes the display panel to the Object Browser list (more on that in a moment) and the fourth filters the list based on criteria you select. The last two buttons switch between a list display of the contents and various reports and dashboards that show the status of the object.
To show a new query, click the "New Query" button. To show the Activity Monitor, click the "Activity Monitor" icon in the icon bar (hover over the icons to see the text for that). We'll come back to those in a moment — but first let's go over the Object Explorer Details (OED) pane, since it has been enhanced in SQL Server 2008.
Object Explorer Details
To show OED, press F7. OED shows general information about the current object you have selected in Object Explorer (OE). As you navigate through OE, the contents of the OED area are replaced. You have a back, up and refresh button to control that navigation.
You can double-click items in OED to move down a node, or open its default action. Most of the time that involves opening the "Properties" panel, but that varies based on the object. You can also right-click an object in OED, to get a menu for the actions of that object, along with a "Synchronize" choice which will place you on that object in the Object Explorer pane if it is also showing.
In SQL Server 2008, you can choose the columns that you want to display in OED. Right-click in the header area and select the columns you want. You can sort these columns, left-click and drag them, and they will stay that way for your login until you make a change. An interesting feature is that you can select rows in the OED panel, and then press CTRL-C, and everything you selected, including the headers, will be placed in the clipboard in tab-delimited format. That means you can paste it easily into Microsoft Excel, Word or Outlook mails and the columns will be maintained.
There's another area just at the bottom of OED. Select an Object in OED, and then left-click and drag the bar separating the lower area in OED, and you'll see more properties of that object displayed. A small icon to the far right of a Property allows you to copy its value into the clipboard.
There's one more trick Object Explorer Details has up its sleeve. Just at the top of the OED area is the "Search" bar, where you can type in all or part of an object name to locate and display it in OED. If you type in the entire name, you get an exact match. To get a partial match, use the T-SQL wildcard characters (such as %). So "Buck" will get you all items named Buck, and "Buck%" will get you all items with Buck as the starting letters, and %Buck% will find all items with Buck anywhere in the name.
The Query Window
Your query window can change based on the options set on your system. Click the "Tools" and then "Options" item in the menu bar to change features such as line numbering, fonts and even the destination for the results of a query, such as "To grid" and "To File" or "To Text".
When you're working with a query, the tab name will display an asterisk next to the name of the file if it isn't saved. As you're editing a line, a thin yellow line appears next to the lines that have changed since the file was last changed. As you save the document you're working on, the lines are marked wtih a thin green bar at the left. As you make changes, the lines you change have a yellow bar next to them. It's a great way to keep track of your changes.
As you type your Transact-SQL statements, the system will automatically begin to fill things out for you. This is called "Intellisense," and you can turn it off or on in the "Options" area I mentioned earlier.
As you type your commands won't execute — you have to press F5 or click the "Execute" icon in the menu bar. The results are sent to the lower half of the screen, although you can set an option to have them go to their own panel, which is what I do. You can see the results in a grid, which is the default, or in plain text, which is useful for copying to other applications.
You can save what you've typed to a file, or open a file to display in this area using the "File" menu. You can also drag a file from Windows onto a Query Window, and the system will read the file and place the text there.
We'll use the Query Window throughout this site.
The Transact-SQL Debugger
In the top icon bar you’ll see a green triangle icon that will take your query text and start it in the Transact-SQL Debugger. This is a tool that walks through your code one line at a time, allowing you to see the results of the statements. You can also put a “break point” so that the code stops in a certain place, and more. Try entering a system stored procedure (such as sp_adduser ‘Buck’) to see how this works.
The Activity Monitor
The Activity Monitor, which you can get to from the icon bar, shows four graphs of system activity, along with bands of information that show even more information about the activity on your system. When you expand those bands, you can right-click a running query to kill it, send the output to the SQL Profiler tool, or even open the query in a new Query Window.
There are several panels you can show, all selectable from the View menu bar item. Two of the most important are the Registered Servers panel and the Object Browser panel.
On my screens I have other panels hidden, with only their titles shown vertically. Your screen may have these panels displayed. If you would like them to hide automatically when you work with another area of the screen, click the push-pin icon in the upper right hand corner of that panel. The panel will then "slide out" when you work with it, and "slide in" when you click any other area of the screen. This is useful if you don't have a lot of screen real-estate to work with.
In the Registered Servers panel you can set the server names and credentials you'll use to make connections to the servers. In the top portion of the Registered Servers panel you'll see several icons that represent the type of servers and engines you can connect to.
Once you click an icon, you can right-click the title of that item (for example you can select Database Engine) and select “New” and then “Server Registration...” to provide the name and security credentials to connect to a new server. You can remove a server in a similar fashion. Having all of the various types of servers consolidated to one location makes it simple to keep track of what you have to administer.
In SQL Server 2008, there's another kind of Registered Server. The Central Management Server is a place where you can define a server name, just like you would normally, but in this case others can also register the same server name in this node. When they do, whatever you register underneath it automatically shows up for everyone.
And yet one more enhancement — you can click not only a single server and get a Query Window, but you can click on a group name and run a query. All the servers will run the query and return the results to the main query results window.
There's a lot more to this versatile environment that I'll show throughout this site.
PowerShell for SQL Server
Within SSMS, you can right-click almost any object in the Object Explorer and get a PowerShell window. I've covered PowerShell elsewhere on this site, but this is different. The PowerShell environment in SQL Server 2008 is aware of SQL Server, and it drops you right in the path of the object you selected. From there you can use standard PowerShell commands, including DIR and CD to move through the database. I'll cover this great new feature in great depth in another tutorial.
The SQLCMD tool is an operating system command-line based query tool. With it you can connect to a server and run a command and receive the output on the current command window. You can also run a script from a file, send the results to a file, and even use variables with it. To see all the options, drop to a command-line in the operating system where the tool is installed and type SQLCMD /?.
The SQL Server Profiler tool shows activity on a server. It's a kind of "SQL Sniffer" in that it can read the information going to and from a SQL Server Database Engine or Analysis Services server. It functions similarly to the tool by the same name in SQL Server 2000, although in this version you can include Windows Performance Monitor objects and counters so that you can observer platform information along with the SQL Server activity to correlate the two. Using this feature you can determine what T-SQL statement is running when the processor or memory load is high.
Database Tuning Advisor
In SQL Server 2000, you could monitor activity for index performance issues with a tool called the Index Tuning Wizard. In SQL Server 2005 and 2008, this capability is enhanced with the Database Tuning Advisor. Available as a tool of its own or from within SQL Server Management Studio, this tool can tune not only indexes but the physical layout as well.
The process is to start the tool while a set of operations are run on the server. This process is normally done on a testing server, but one similar in size and capability to the production server. Once the activity completes, the Database Tuning Advisor will make suggestions on everything from table arrangements to filegroup and index layouts, based on settings you choose.
I'll explain how to use the Database Tuning Advisor along with SQL Server Profiler to tune and optimize your SQL Server instances in another tutorial.
InformIT Articles and Sample Chapters
If you’re using the Compact Edition of SQL Server, here is a free article on the tools for it.
Books and eBooks
The starting point for a full tutorial on SSMS is here.