We've had seven years to get used to SQL Server's management tools, Query Analyzer and Enterprise Manager. Although...
these tools have their flaws, we know our way around them. For many long-time SQL Server DBAs, firing up Query Analyzer is like coming home after a long day. Some might call these tools aged, but to me, they're just well broken in -- a couple of comfortable companions on the database management road.
Alas, this wistful reflection will be my last; I'm ditching those old standbys for the new toolset. Sleek and redesigned for the twenty-first century, SQL Server Management Studio (SSMS) represents the best of Query Analyzer and Enterprise Manager rolled into one all-powerful tool. Add to that improvement many productivity features from one of the best development environments on the market -- Visual Studio -- and the old tools suddenly seem as though they've aged like vinegar, not wine.
Let's take a look at some of the features that will make this tool the envy of every non-SQL Server 2005 DBA.
|TABLE OF CONTENTS
Visual Studio features
Object Explorer filtering
XML execution plans
Improved templates user interface
Summary pane reports
|Disconnected editing||Return to Table of Contents|
Picture this: Your company is sending you on an important business trip to a remote office somewhere in the wilds of Los Angeles. On the plane, after waiting the requisite 10 minutes after takeoff, you hastily boot up your notebook to finish up the queries you're planning to deliver.
Not so fast! You only have the SQL Server 2000 client tools loaded on your notebook. As soon as you launch Query Analyzer you're given a login prompt -- no login means no editing because you're not connected to the server. So, you spend the rest of the flight hacking away in Notepad, muttering about the lack of syntax highlighting.
SSMS does away with the connectivity requirement. Every feature of the editor works whether you're connected to or disconnected from a server. It's finally possible to work how you want, when and where you want, without carrying around a server on your notebook.
|Visual Studio features||Return to Table of Contents|
SSMS is built to look and feel like Visual Studio. This means, among other things, the addition of a common Properties window, Explorer-style dialogs to manage hierarchical items, and Visual Studio-like dockable windows. Developers will be instantly at home in this environment, and while DBAs may require a bit more ramp-up time, they too have many great new features to exploit.
One of the most important new features is the ability to create solutions. A solution (alternatively called a project) is a group of script files that can be organized together as a single unit. Once a solution is opened, the SSMS interface allows users to open scripts included in the project by simply double-clicking file names in Solution Explorer. And when a script or a group of scripts has been edited, all changes can be saved at once. This means no more confusion about which scripts are dependent upon one another.
Caption: Creating solutions.
Tabbed editing is an especially great user interface feature borrowed from Visual Studio. DBAs are known to be multitaskers; many brag about the number of scripts they were editing at once. (50? 100? Keep going…) Unfortunately, the pull-down menu in Query Analyzer that is used to "manage" windows didn't cater to multitaskers. SSMS steps up to the plate with tabbed windows. Flipping from window to window is now as simple as clicking on the appropriate tab.
Caption: Navigating tabbed editing.
Bookmarking is another extraordinarily useful feature for DBAs who just can't decide which script to edit first. This feature lets you assign markers to lines of open files, so you can quickly find your way back later. Working on a particularly large script? Want to remember the location of a key clause that you know you'll need to fix after tweaking a different area? Bookmark it. You'll be able to instantly return to it by selecting the line from the SSMS Bookmark management window.
Caption: Bookmarking assigns markers to text.
Another SSMS feature that will be instantly familiar to those who've used Visual Studio is also one of the features most often requested for the SQL Server client tools: source control integration. Simply open the Options dialog and navigate to the Source Control pane to select a plug-in. As in Visual Studio, source control integration works best when used in conjunction with solutions. A solution can be associated with a source control repository, then parts of it can be selectively checked out for editing -- all without leaving the SSMS user interface.
|Object Explorer filtering||Return to Table of Contents|
Working with databases that have a large number of user objects can be difficult, especially when it means scrolling through huge lists to find a specific object or group of objects. A familiar feature from Query Analyzer, which carried over to SSMS, is Object Explorer (previously called Object Browser). It is a dockable window that lets users navigate database objects based on their type. Tables, views, stored procedures and other types of objects each have their own folder. In SSMS, Object Explorer gives you the ability to filter within those folders, to further cut down the work required to find specific objects. A user can click the Filter icon to filter results by schema, name or creation date.
Caption: Object Explorer lets you filter within folders.
Once a filter has been selected, the list is reduced in size. For instance, the screenshot below shows the result of filtering for views that contain the string dm_ (the Dynamic Management Views.)
Caption: Filtering for views.
|XML execution plans||Return to Table of Contents|
Reading execution plans is a key part of the performance-tuning process, and Query Analyzer's graphical query plan feature took this to a new level. Much easier to read and understand than text-based execution plans, graphical plans meant that you could very quickly figure out what problems might be present in a query.
SSMS goes one step further, enabling you to save graphical query plans as XML files. A query plan saved this way can be reopened by any SQL Server Management Studio user -- including one who doesn't have access to the database or the query.
Doing some long-term performance evaluation? Now you can archive your graphical query plans so they can be evaluated as the database changes. Need help tuning an especially problematic query? E-mail the XML plan to a colleague, who can analyze the plan without seeing your data.
To use this save feature, simply right click on a graphical plan and click Save Execution Plan As. The plan will save with the extension ".sqlplan." When you want to view the plan, simply reopen it in SSMS and it will be displayed in its full graphical glory.
|Improved templates user interface||Return to Table of Contents|
Query Analyzer, while not especially feature-rich, does have one element that almost no one I know uses: templates. This feature initially seemed to have so much promise -- just think, the ability to define common query parts that can be brought up on command and dynamically substituted with a simple key combination (CTRL-SHIFT-M). But the templates feature just isn't user friendly enough in Query Analyzer. Adding new templates is a hassle. It requires users to save scripts with special file extensions into a specific Query Analyzer folder.
In SSMS, the user interface does the heavy lifting. Want to add a new template or organize templates into folders? Simply right click and add away. Hopefully, the new ease of use will mean that this feature finally gets the attention it deserves. Templates are a great way to organize frequently used queries.
Caption: Enjoy easy-to-use templates.
|Scripting options||Return to Table of Contents|
Here's a simple feature that requires almost no explanation: the Script icon. Found on almost every dialog box in SSMS, this icon lets you save any user-interface-driven action as a script, which you can then use for future reference or later deployment to other servers. Just click the Script icon when you're ready to save what you've done.
Caption: The Script icon
|Summary pane reports||Return to Table of Contents|
A popular destination for DBAs within Enterprise Manager is the Taskpad view, which lets you see a quick summary of the current state of your database, in a nice graphical format.
Microsoft has eliminated the Taskpad from SSMS, but in its place is a much more powerful feature, the Summary pane. This pane shows a graphical representation of whatever is selected in the Object Explorer, much like the main Enterprise Manager pane. But added to the Summary pane is an integrated-reporting feature. Whenever you've selected either a server or a database in Object Explorer, the Report icon will come to life, allowing you to select from a series of pre-built reports that give you a look at the state of your system. It's like the Taskpad, only a lot more in-depth. You can even print the reports for posterity -- or to show management what a good job you're doing. You can't beat that.
Caption: The Summary pane lets you print in-depth reports.
|Conclusion||Return to Table of Contents|
SQL Server Management Studio takes the most important parts of the SQL Server client tools family, joins them and then extends them as a new tool with great potential. Integrated developer productivity and DBA analysis tools mean quicker project completion and easier access to the data you need to make sure servers are running at their optimum levels. And next time you see some poor sap stuck on a plane editing offline in Notepad, you can smile, knowing that you'll never suffer that fate again.
About the author: Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.