SQL Server Management Studio (SSMS) is an integrated application that provides access to most of the
What’s New in SSMS
SSMS is loaded with new features in SQL Server 2008. This tool was introduced in SQL Server 2005, and it brought quite a bit of change with it. There is also quite a bit of change with SQL Server 2008, but the basic look-and-feel of the application remains much the same as it was in SQL Server 2005. The standout features in the SQL Server 2008 SSMS include four new features geared toward the administrator and three new features geared toward the developer. Of course, all these features could be used by both administrators and developers.
The new administrator features in SSMS include a beefed-up Activity Monitor, a new Object Search option, a customizable Object Explorer Details window, and a new management tool named SQL Server Utility that was added
Finally, the new SQL Server Utility allows for the capture of resource information across multiple servers and provides one unified view for the display of this information. Enhancements that are focused on the developer include IntelliSense in the Query Editor, an integrated Transact-SQL (T-SQL) Debugger, and a Multiserver Query execution option.
The Integrated Environment
If you have been working with SQL Server for a long time, you may remember the SQL Enterprise Manager that came with SQL Server 6.5. In some respects, with SSMS, Microsoft has moved back to the paradigm that existed then. Like the SQL Server 6.5 Enterprise Manager, SSMS provides an integrated environment where developers and DBAs alike can perform the database tasks they need. Say goodbye to Query Analyzer, Analysis Manager, and a number of other disparate tools used in SQL Server 2000 and say hello to SSMS, which provides “one-stop shopping” for most of your database needs.
The tools available with SSMS can be broadly categorized into tools that are used for administering SQL Server and tools that are used for developing or authoring new SQL Server objects. As a matter of practice, developers use some of the administrative tools, and administrators use some of the development tools. SSMS comes with an expanded set of tools to help with SQL Server administrative tasks. It builds on the functionality that was available in SQL Server 2005 and adds some new tools and functionality to help ease the administrative burden.
Benefits of Registered Servers
With Registered Servers you can:
The servers listed above are all Database Engine servers. These server types are the conventional SQL Server instances, like those you could register in the SQL Server 2000 Enterprise Manager. You can also register several other types of servers. The icons across the top of the Registered Servers window indicate the types of servers that can be registered. In addition to Database Engine servers, you can also register servers for Analysis Services, Reporting Services, SQL Server Mobile, and Integration Services. The Registered Servers window gives you one consolidated location to register all the different types of servers available in SQL Server 2008. You simply click the icon associated with the appropriate server type, and the registered servers of that type are displayed in the Registered Servers tree.
The SQL Server 2008 Registered Servers window enables you to register servers that are running SQL Server 2005, SQL Server 2000, and SQL Server 7.0. You can manage all the features of SQL Server 2005 and SQL Server 2000 with SQL Server 2008 tools. You can also have both sets of tools
When a server is registered, you have several options available for managing the server. You can right-click the server in the Registered Servers window to start or stop the related server, open a new Object Explorer window for the server, connect to a new query window, or export the registered servers to an XML file so that they can be imported on another machine.
The import/export feature can be a real
Object Explorer provides a hierarchical user interface to view and manage the objects in each instance of SQL Server. The Object Explorer Details pane presents a tabular view of instance objects, and the capability to search for specific objects. The capabilities of Object Explorer vary slightly depending on the type of server, but generally include the development features for databases, and management features for all server types.
The Object Explorer window that existed in the SQL Server 2000 Query Analyzer was coordinated into SSMS in SQL Server 2005. SQL Server 2008 keeps on utilizing a coordinated Object Explorer that acts like SQL Server 2005.. The most huge element for those people dealing with countless articles is the ability to populate the Object Explorer tree
One often-overlooked Object Explorer feature is the
The graphs are easy to read, and a few areas of the report can be extended to give more detail. Shots at the base of a report are hubs that can be extended. For instance, the Disk Space Used by Data Files at the base of above picture can be extended to show insights about each of the information documents.
The Activity Monitor has seen some dramatic changes in SQL Server 2008. These changes build on the foundation established in SQL Server 2005 and help provide much more information related to the performance of your SQL Server instance. Before we get into the details of the Activity Monitor, let’s make sure you know where to find it. It is no longer found in the Management node of the Object Explorer. Instead, you right-click on the name of the server instance in the Object Explorer, and you see a selection for Activity Monitor.
Activity Monitor is a
When the Activity Monitor launches, you see a new display with four different graphs, as shown in the below picture. The graphs include % Processor Time (from SQL Server), Waiting Tasks, Database I/O and Batch Requests. These graphs give you a quick performance snapshot
The Processes Details window contains information similar to what was displayed in the SQL Server 2005 Activity Monitor. These details include information similar to what is returned with the sp_who system stored procedure. The server process ID (SPID) is listed in a column named Session ID, and the related information for each SPID is displayed in the remaining columns. If you right-click on a particular process, you can see the details of that process. You can then kill that process or launch the SQL Server Profiler to trace the activity for the process.
The Data File I/O window lists each database and its related database files. The amount of disk I/O experienced by each of the files is detailed in the columns of this display. You can isolate the database and files that are most heavily hit with read or write activity as well as the databases that may be suffering from poor I/O response with this screen. Finally, the Recent Expensive Queries window displays information similar to what you can obtain using catalog views. It provides statistics for all the databases on the instance and is a quick and easy way to find and tune expensive SQL statements. If you right-click on a row in the display and click Edit Query Text, you can see the entire SQL text associated with the query. You are able to click on one of the column headings such as CPU to sort the display according to the metric you feel defines cost. Best of all, you can rightclick on a row and choose Show Execution Plan, and you have the Query Plan ready for analysis
Log File Viewer
The Log File Viewer is another
One of the first things you notice when you launch the Log File Viewer is that a tree structure at the top-left corner of the screen shows the log files you are viewing. You can see that there are four different log types available: Database Mail, SQL Agent, SQL Server, and Windows NT. You can choose to display multiple log files within a given log type (for example, the current SQL Server log and Archive #1), or you can select logs from different sources. For example, you can display all the current log entries for SQL Server and the current log entry for the SQL Server Agent.
When multiple logs are selected, you can differentiate between the rows shown on the right side of the Log File Viewer by looking at the Log Source column and the Log Type column. The Log Source values match up with the names shown in the tree structure where the log was selected. The Log Type column shows the type of log, such as SQL Agent or SQL Server. Rows from the different log types are displayed together and sorted according to the date on which the row was created. The sort order cannot be changed.
Other noteworthy features in the Log File Viewer include the capability to filter and load a log from an external source. You can filter on dates, users, computers, the message text, and the source of the message. You can import log files from other machines into the view by using the Load Log facility. This facility works hand-in-hand with the Export option, which allows you to export the log to a file. These files can be easily shared so that others can review the files in their own Log File Viewer.
SQL Server Utility
The SQL Server Utility was added in SQL Server 2008 R2 and is geared toward multiserver management. It provides several new hooks in the SSMS environment that improve visibility and control across multiple SQL Server environments. Access to these new hooks is provided through a new Utility Explorer that can be displayed within your SSMS environment. This Utility Explorer has a tree-like structure similar to the Object Explorer, and it provides rich content related to the health and integrity of the SQL Server environments you have selected to manage using the SQL Server Utility. The below
The SQL Server Utility provides a holistic view of the health and utilization of the resources associated with managed instances of SQL Server and registered database applications. Here are the components and terms affiliated with it:
SSMS delivers an equally impressive number of features for database developers. Many of the features were available with SQL Server 2005, but SQL Server 2008 has added some new ones as well. T-SQL Debugging, IntelliSense in the Query Editor, and multiserver queries are a few of those new tools for developers found in SQL Server 2008. These new tools and the other essential developer tools from SSMS are discussed in the following sections.
The Query Editor
The Query Editor sits at the top of the list for development tools in SSMS. The Query Editor, as its name indicates, is the editing tool for writing queries in SSMS. It contains much of the functionality that was contained in SQL Server 2000’s Query Analyzer. The capability to write T-SQL queries, execute them, return results, generate execution plans, and use many of the other features you may be familiar with in Query Analyzer are also available with the Query Editor. One main difference with the Query Editor is that is has been integrated into the SSMS environment. In SQL Server 2000, the Query Analyzer was a separate application with its own independent interface. In SQL Server 2008, SSMS houses the query-editing capabilities along with all the administrative capabilities.
Clicking the New Query button, opening a file, and selecting the Script to File option from a list of database objects in the Object Explorer are just a few of the ways to launch the Query Editor. The below picture shows the query editor window with a sample SELECT statement from the AdventureWorks2008 database. The query editor window is displayed on the right side of the screen and the Object Explorer on the left side.
IntelliSense has finally made it to the SQL Server Query Editor. This much-anticipated tool was slated for SQL Server 2005, but it was pulled before making it to the marketplace. Fortunately, it made it to SQL Server 2008, and it was worth the wait. This is especially true for those developers who have been working with Visual Studio or other Microsoft development tools that have this feature. IntelliSense is a handy tool that helps you complete queries as you are typing them in the query editor window. Start typing and you will see. For example, type SELECT * FROM A in the query editor window, and a drop-down appears in the query editor window after you start typing the first letter after the FROM clause. The drop-down, in this case, contains the databases and tables from which you can select data. If you type in a stored procedure name to execute, a drop-down shows you the parameters that the stored procedure accepts. Type SYS. in the query editor window, and you see a drop-down of all the objects available in the SYS schema. This includes catalog views and the related columns that these views contain. If you type in a query that is incorrect, IntelliSense places a red squiggly line under the part of the query that is syntactically incorrect. The value of this tool will become more apparent as you use it. It can be confusing at times, but it will ultimately speed up your development time. It can also reduce the number of times you need to go to Books Online or some other help source and will make your development life easier.
Query Editor Types
The Query Editor in SQL Server 2008 enables you to develop different types of queries. You are not limited to database queries based on SQL. You can use the Query Editor to develop all types of SQL Server Scripts, including those for SQL Server Analysis Services (SSAS) and SQL Server Mobile Edition. The SSAS queries come in three different flavors: multidimensional expressions (MDX), data mining expressions (DMX), and XML for analysis (XMLA). Only one selection exists for creating SQL Server Mobile Edition scripts.
SQL Server 2008 is able to use the code editor without a database connection. When creating a new query, you can choose to connect to a database or select Cancel to leave the code pane disconnected. To connect to the database later, you can right-click in the code pane window and select the Connect option. You can also disconnect the Query Editor at any time or choose the Change Connection option to disconnect and connect to another database all at once. Along with disconnected editing are some changes to the Windows behavior that are worth noting.
The biggest changes relate to the behavior of query windows currently open at the time a file is opened for editing. With SQL Server 2000 Query Analyzer, the currently selected window would be populated with the contents of the file you were opening. Prior to this replacement, a prompt would be displayed asking whether you wanted to save your results. If the query window was empty, the contents would be replaced without the prompt for saving. With SQL Server 2008, a new query window is opened every time a new file is opened. The new window approach is faster but can lead to many more open windows in the document window. You need to be careful about the number of windows/connections you have open. Also, you need to be aware that the tabbed display shows only a limited number of windows. Additional connections can exist even if their tabs are not in the active portion of the document window.
Editing sqlcmd Scripts in SSMS
sqlcmd is a command-line utility introduced in SQL Server 2008. You can use it for ad hoc interactive execution of T-SQL statements and scripts. It is basically a replacement for the ISQL and OSQL commands used in versions prior to SQL Server 2005. (OSQL still works with SQL Server 2008, but ISQL has been discontinued.) You can write, edit, and execute sqlcmd scripts within the Query Editor environment. The Query Editor in SSMS treats sqlcmd scripts in much the same way as other scripts. The script is color-coded and can be parsed or executed. This is possible only if you place the Query Editor in SQLCMD mode, which you do by selecting Query, SQLCMD Mode or selecting the SQLCMD mode icon from the SSMS toolbar. A sample sqlcmd script in SSMS that can be used to back up a database. This example illustrates the power and diversity of a sqlcmd script that utilizes both T-SQL and sqlcmd statements. It uses environment variables set within the script. The script variables DBNAME and BACKUPPATH are defined at the top of the script with the SETVAR command. The BACKUP statement at the bottom of the script references these variables, using the convention $(variablename), which substitutes the value in the command.
SQL Server Management Studio includes the following general features:
Using the Query Designer in the Query Editor
A graphical query design tool is accessible from the query editor window where you write your queries. This is a great option that was missing in SQL Server 2000. With SQL Server 2000, you could access a graphical query designer by opening a table in Enterprise Manager and selecting Query, but this option was disconnected from the Query Analyzer environment, where the queries were authored. This tool was introduced in SQL Server 2005 and remains generally unchanged in SQL Server 2008. With SQL Server 2008, you can right-click in the query editor window and choose Design Query in Editor. A dialog box appears, allowing you to add tables to the graphical query designer surface. The selected tables are shown in a window that allows you to select the columns you want to retrieve. Selected columns appear in a SELECT statement displayed at the bottom of the Query Designer window. The below pic shows an example of the Query Designer window that contains two tables from the AdventureWorks2008 database. The two tables selected in this figure are related, as indicated by the line between them.
The T-SQL statements are generated automatically as you select various options on the Query Designer screen. If you select Sort Type, an ORDER BY clause is added. If you choose an alias for a column, it is reflected in the T-SQL. If tables are related, the appropriate joins are generated. When you click OK on the Query Designer window, the related T-SQL is automatically placed in the query editor window. You can edit the T-SQL as needed or use it as is. You can imagine the time savings you can achieve by using this tool.
Finally, you are able to debug T-SQL from within the SQL Server development environment. Yes, you could do this kind of thing using Visual Studio, but database developers should be able to debug in the environment where they generally develop their SQL statements—within SSMS. SQL Server 2008 provides this capability, and it works well. The trickiest part of debugging may be starting the debugger. It is not all that difficult but may be less than obvious for some. For example, let’s say you want to debug a stored procedure. To do this, you right-click on the stored procedure in the Object Explorer and select Script Stored Procedure As, Execute To, New Query Editor Window, and a script for executing the procedure is generated. If the stored procedure has parameters, you add the SQL to assign a value to those parameters to the script. Now you are ready to debug this script and the related stored procedure.
Another slick new option available with SQL Server 2008 is the capability to execute a script on multiple servers at once. Multiserver queries allow the contents of a single query editor window to be run against all the servers defined in a given registered server group. After the group is created and servers are registered in the group, you can right-click on the group and select the New Query option to create a query window that can be run against all the servers in the group.
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute offering SQL Server and T-SQL (SQL Server) Training, SQL Database Administration (SQL DBA) Training and Business Intelligence (MSBI / SQL BI) Training.
We have been working with numerous consultancies in USA & UK. We undertake training on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 versions. We make sure that all our sessions are very much interactive and well structured with doubts clarifications during and after each course.