ToolKit
LANGUAGES: N/A
ASP.NET VERSIONS: 1.0 | 1.1 | 2.0
New SQL Server Tools
Several Must-have Tools for Database Development
By Ken McNamee
SQL Server 2005 has been released, and with it come a number of new tools to help you manage and develop databases. The Cadillac of these tools is SQL Server Management Studio, which finally combines the GUI ease of use of Enterprise Manager with the development power of Query Analyzer. Like most other Microsoft developer tools these days, Management Studio also comes in an Express flavor, which makes it a great option when you simply need to install a database tool and don t need the database.
If you prefer to use command line tools for automated database scripting, or just because you re one of those crazy people who prefer command line tools, then SQL Server 2005 includes a new tool called SQLCMD, which is akin to the old ISQL. In addition, you can download another tool from Microsoft called the SQL Server Express Utility, which is similar to SQLCMD but provides some shortcuts for doing common tasks (such as attaching, detaching, or shrinking databases).
Finally, SQL Server 2005 includes an updated version of SQL Profiler, as well as a new tool called the Database Engine Tuning Advisor, which can analyze the performance characteristics of queries and database schemas and provide some recommendations for making them better.
Management Studio
Without a doubt, SQL Server Management Studio is my new favorite tool. As powerful as Visual Studio is for Windows/Web development, Management Studio is nearly as powerful for database development. In fact, as you can see in Figure 1, Management Studio is built on top of Visual Studio 2005 s IDE shell, so it has the same highly productive interface with dockable/tabbed windows, source control integration, extensibility enhancements, and a very configurable environment.
Figure 1: SQL Server Management
Studio is built on top of the Visual Studio 2005 IDE shell and combines the
ease of use of Enterprise Manager with the development power of Query Analyzer.
Essentially, Management Studio is a marriage between Enterprise Manager and Query Analyzer. You can use it to easily point and click your way through creating databases and tables, managing security logins, or setting up database maintenance plans. At the same time, you can also create queries and stored procedures and view execution plans to help you optimize them. Creating queries is made easier by the query designer dialog box, which allows you to lay the foundation of the query by choosing the tables and columns you want to include before tweaking and executing them in the editor window.
The one feature that makes Management Studio incredibly useful for me is its ability to connect to version 7.0 and 2000 versions of SQL Server in addition to just 2005. Within my server environment I have many different versions of SQL Server installed I have everything from MSDE 2000 to SQL Server 2000 to SQL Server 2005 Express to SQL Server 2005 Enterprise. Although the ability to manage all these databases from one tool feels like such a luxury right now, I m sure that I ll get used to it pretty quickly.
Although the full version of Management Studio only comes included with the commercial versions of SQL Server 2005, Microsoft has also released an Express version, which you can download from http://www.microsoft.com/downloads/details.aspx?familyid=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E. Management Studio Express is missing some of the features of its commercial brother, such as source control integration, mobile database and Analysis Services support, and SQL Profiler and the Tuning Advisor. While not as full-featured, the Express version is still a very powerful development tool with all the query editing and database management options as the non-Express version. In fact, I use Management Studio Express for all my database development now. That s right I no longer need to crack open Enterprise Manager or Query Analyzer, no matter which database I m connecting to.
Database Engine Tuning Advisor
Management Studio, the commercial version, includes a tool called the Database Engine Tuning Advisor, which allows you to improve your database schema. Although not a substitute for a professional DBA, the Tuning Advisor can give you some pretty good recommendations on problem areas in your database and how to improve performance through the smart use of indexes and database partitions.
As you can see in Figure 2, the Tuning Advisor is a GUI tool that provides reports on the performance of the database schema. You choose a query or set of tables and the Tuning Advisor will apply a workload to the database for the amount of time you specify and then report on how well the test went mostly how well your choice of indexes performed.
Figure 2: The Database Engine Tuning
Advisor can report on the performance of real or hypothetical database schemas
so that you can make informed decisions on the best use of indexes in your
development.
In addition to testing the current database schema, you can also define a hypothetical database schema in an XML file and have the Tuning Advisor run the test against that. These what-if scenarios can go a long way toward helping you decide on the best index modifications without having to actually implement them first.
Command Line Tools
While I always prefer to use a GUI tool if I can, command line tools do have their place. In particular, they are very useful for executing automated scripts. I use one script to return my database that I m developing against a baseline state so that I can do a bunch of testing without worrying about polluting the database for future tests. With SQL Server 2000, the command line tool I use is ISQL, but SQL Server 2005 includes a similar tool, more appropriately named SQLCMD, which has many of the same options for connecting to databases and executing SQL statements.
Another new tool is the SQL Server Express Utility, which allows you to not only execute SQL statements but also provides shortcut options for listing, creating, attaching, detaching, and shrinking databases, either locally or remotely. You can also upgrade a database file from an older SQL Server version to the version that is currently running. The SQL Server Express Utility even has an option that pops up a dialog box to allow you to enter in a query, load it from a file, or save it to a file. You can execute the query within this dialog box and see the results in an output window. It s not Management Studio, but it is a little more user-friendly than the command line. You can download this tool from http://www.microsoft.com/downloads/details.aspx?familyid=FA87E828-173F-472E-A85C-27ED01CF6B02.
Conclusion
I m pretty confident that just about everyone will soon be using Management Studio, if only the Express version. There s really almost no reason to use anything else anymore. Nuff said.
Ken McNamee is a Senior Software Developer with Vertigo Software, Inc., a leading provider of software development and consulting services on the Microsoft platform. Prior to this, he led a team of developers in re-architecting the Home Shopping Network s e-commerce site, http://www.HSN.com, to 100% ASP.NET with C#. Readers can contact him at [email protected].
