In enhancing existing management tools and designing new ones for SQL Server 2005—formerly code-named Yukon—Microsoft's SQL Server Tools team followed two guiding principles: "no secrets" and integration. Euan Garden, product unit manager for SQL Server Tools for SQL Server 2005, tells SQL Server Magazine how his team relied on these principles to build database-management functions that are more transparent, more robust, and easier to use.
What are the top new management tools in SQL Server 2005?
We're introducing several new and exciting tools in SQL Server 2005 that will build on what we provided customers in SQL Server 2000. Microsoft's goal is to continue to enhance database-management functions to make them more robust and easier to use.
SQL Server Management Studio, previously called SQL Server Workbench, is perhaps the most visible new tool in SQL Server 2005. Management Studio combines the functionality of Enterprise Manager, Query Analyzer, and Analysis Manager into one consolidated tool. Management Studio also provides integrated management of Reporting Services, Notification Services, XML, SQL Server 2005 Mobile Edition (formerly code-named Laguna), and multiple versions of SQL Server from the same interface for increased DBA productivity, flexibility, and manageability.
Besides integrating the range of functions and technologies I just listed, we've also tried to address as many customer requests as possible within the new Management Studio environment. We received a lot of feedback about Enterprise Manager, which is a key tool for most of our customers today. And we wanted to make it even more productive. Today in Enterprise Manager, people primarily interact with the UI's dialogs, which pop up and block the rest of the UI, making it difficult to do multiple tasks at the same time—especially when Enterprise Manager is performing a long-running operation such as backup and restore. In addition, we found that many users are running SQL Server Profiler with Enterprise Manager to find out what statements Enterprise Manager is sending to the server so they can run the statements later in batch. To address these issues, we've introduced a new type of dialog that's resizable and non-blocking. The new dialogs also let customers script and schedule their actions, which we believe makes the UI much more transparent. We've also streamlined the SQL calls that request information from the server and replaced Current Activity with a new Activity Monitor. The new Activity Monitor performs more efficient queries, automatically refreshes, and supports sorting and filtering.
Another new tool, the Database Tuning Advisor (DTA), replaces the Index Tuning Wizard (ITW), providing a richer, more scalable experience that leverages the core concepts we introduced in the ITW in SQL Server 7.0. To improve productivity, we've separated the UI from the tuning engine. With this new tool, DBAs can leave a tuning session, close the UI, and come back to it later or have multiple sessions tuning at the same time, each session potentially running against different servers. DTA can also recommend a partitioning strategy.
SQL Computer Manager is a new configuration tool that extends Computer Manager and supports disconnected configurations for all the server products, including SQL Server, Analysis Server, and Reporting Server. Good examples of where disconnected configurations could be useful are in setting service accounts (where changing the password no longer requires a reboot) and in creating and editing aliases. You can also perform these tasks through our Management APIs, which provide transparency and control.
In addition, we received a lot of feedback about how to enhance SQL Server 2000 SQLMail. Listening to customer requests, we felt the right solution was not to try to take the SQLMail architecture forward but instead to start leveraging new technologies such as managed code and SQL Server Service Broker to create SQLiMail in SQL Server 2005. We discovered that customers wanted the following: no requirement to install Microsoft Outlook on their servers, added support for the SMTP protocol, and a simpler way to configure the mail solution. SQLMail still exists in SQL Server 2005, but SQLiMail replaces most of its functionality. The new product works very differently from SQLMail because it's not dependent on Messaging API (MAPI) or Outlook and it works in a range of server environments, such as clustered and 64-bit implementations. SQLiMail also works with a wide range of SMTP servers in addition to Microsoft Exchange. SQLiMail actually goes into a Service Broker queue and uses an external .exe file to send mail from outside the server process.
The last new component I can share is SQLCMD, the new command-line tool that replaces osql and, to a lesser degree, isql. In trying to better understand how we could help make DBAs more productive in the operations and command-line environment, we interviewed and watched lots of them as they did their work, primarily using osql, batch files, and script. With SQLCMD, I think we've created a simple yet effective tool that DBAs will love.
What enhancements have you made to existing tools?
For Profiler, we've added support for profiling and replaying Analysis Services, for profiling Data Transformation Services (DTS), for Performance Monitor correlation, for deadlock visualization, for Showplan extraction, and much more. For SQL Agent, we've done a lot of work on performance and security, and we've added features to support Windows Management Interface (WMI)-based alerts and new subsystems such as DTS and Analysis Services.
Is there a design philosophy that permeates the new and improved management tools?
Our guiding principles for SQL Server 2005 tools are "no secrets" and integration. "No secrets" means that everything we do in the UI should be exposed to users and independent software vendors (ISVs) through an API and that DBAs should be able to work out what we've done and be able to reproduce it more easily. For integration, we built SQL Server Management Studio to aggregate the management of three different components—SQL Server, Analysis Services, and Reporting Services—and several other components. In addition, SQL Computer Manager, Profiler, and Replay work with more than just SQL Server.
Can SQL Server DBAs and developers keep Enterprise Manager and Query Analyzer on their desktops and use them as they're getting used to the new SQL Server 2005 tools? And what is Microsoft doing in the toolset for backward compatibility?
We want to make this an easy transition for customers. So if you install SQL Server 2005 alongside SQL Server 2000, existing versions of Enterprise Manager and Query Analyzer will continue to service SQL Server 2000 and 7.0. However, if you upgrade to SQL Server 2005, we'll replace the tools. Management Studio also works against non-SQL Server 2005 servers, as does Profiler and other tools.
Will SQL Server 2005 or a future release provide built-in tools for data loading and stress testing?
The tools I use to perform these tasks are DTS or SQLCMD for data loading and Replay for stress testing (this is the same tool we use internally). The new capabilities in SQLCMD make it a powerful tool for batch work, including data loading. And in Replay, you can rerun traces from custom managed-code applications because we've exposed the Replay engine through a series of APIs that let you perform the same actions without having to use the Replay GUI.
Do SQL Server 2005's management tools let you isolate resource-utilization problems, such as excessive I/O, CPU, and memory utilization, and who's causing these problems? Currently, customers have to use undocumented features to isolate such problems—for example, using DBCC SQLPERF with WAITSTATS, IOSTATS, and UMSSTATS options. What is Microsoft doing to help customers tune and analyze SQL Server more effectively?
No matter who or what is causing the resource-utilization problems, the first step to solving them is making sure that the data is available. With the next release of SQL Server, we've focused on providing more information within the servers. For the relational server, you can see this data through a new technology called Dynamic Management Views. These views provide a snapshot of internal structures and states so that you can query, archive, and compare such information. We'll expose this information through a series of reports, but we're also working with tools vendors to help them better expose and leverage this information. Exposing this information will make the server far more transparent and will help customers diagnose many of the issues you list.
Does Microsoft plan on adding a SQL Server "health" application that can evaluate and suggest tuning enhancements and configuration changes?
Today, customers can use the SQL Server 2000 Best Practices Analyzer (BPA) to scan their systems and ensure they're up-to-date. BPA is in beta testing now, but already, we've received positive feedback about its ability to help customers make their systems more productive. In addition, we work closely with the Microsoft Operations Manager (MOM) team and, last year, released a major update to the SQL Server Management Pack that's part of MOM. MOM 2005 will feature significant enhancements, including the explicit changes we engineered to support SQL Server. One example is that you'll be able to run diagnostic queries from inside the MOM Operations Console.
A key change that will make the next release of MOM more effective for SQL Server professionals is that it will include fewer SQL rules by default. This might seem like a weird enhancement, but bear with me for a moment. One challenge in an operations environment is prioritizing problems. Because of this, when a warning goes off, it has to be real. In MOM-speak, this means making sure that when we raise an alert, it's valid and critical to the environment. We've also worked on adding SQL Server-specific tasks to the environment.
What have you done in SQL Server 2005 to make it easier for DBAs to manage multiple servers? And realistically, how many servers could one DBA manage for simple administration tasks?
In SQL Server 2005, you have five key tools for managing multiple servers. Some of these tools existed in SQL Server 2000 and have been enhanced; others are new in SQL Server 2005:
- SQL Agent (master and target server—MSX/TSX)—Allows jobs created on one server to run on any number of other servers.
- SQLCMD—We added the ability to connect to multiple servers through a single script.
- Maintenance plans—This great new technology simplifies the creation of complex workflow-based maintenance plans. We've leveraged the rich control flow from DTS to provide the execution engine for maintenance. We generate DTS packages that you can edit and run. All these packages do is generate T-SQL, so you also have improved transparency for maintenance.
- Scriptable dialogs—These new UI elements in Management Studio let you use the UI as a T-SQL generation tool. You can then use SQL Agent to schedule and distribute these T-SQL scripts.
- MOM—As discussed earlier, you can run diagnostic queries and perform more SQL Server-specific tasks.
We know of DBAs managing more than 250 servers by using MSX/TSX today, and our goal is to raise that number in SQL Server 2005.
How are the concepts of authoring and management different from a tools perspective? Several white papers about the new toolset use these terms.
Authoring is writing. Just as this interview is written in English, scripts are written in a variety of languages, including T-SQL, MDX, XML for Analysis (XML/A), XML, XQuery, and so on. We want to provide a rich, consistent, integrated environment for authoring that includes core functionality such as version control, Intellisense, projects, templates, and a range of other features across all languages. Management, in contrast, encompasses the core tasks that DBAs perform, such as backup and restore, user management, and so on.
Both terms emerged from an exercise we did to identify tasks and roles for SQL Server administration before starting work on SQL Server 2005. When we did the task-analysis exercise for SQL Server, we found groups of tasks that fall under the DBA role, and we grouped these tasks into six experiences. To this day, we think about the tools within the context of these six experiences:
- Management—includes core tasks such as backup, restore, and security
- Authoring—writing scripts
- Operations—"lights out" scalability of management; includes SQL Agent, MOM, and SQLCMD
- Performance Tuning and Optimization—includes tools such as DTA, Profiler, and Replay
- Configuration—primarily handled by SQL Computer Manager
- APIs—used to write custom scripts that mirror the functionality provided by the tools I mentioned earlier, including WMI, SMO, and the new trace and replay objects
In a smaller organization, one person may have the experience and ability to perform all these tasks. However, in a larger organization, the tendency is for these tasks and experiences to be spread out across people and even across broader teams.
SQL Server 2005 introduces a new sample database called Adventure Works. What benefits does this new sample database bring SQL Server DBAs and developers?
The Adventure Works sample database, which we've worked on for a couple of years, replaces the Northwind, Pubs, and Foodmart sample databases. Although we'll still supply the scripts for some of these older databases, they won't be installed. When we looked at the range of features in the next release of SQL Server—such as schemas, partitions, SQL Server Service Broker, and the XML data type—we realized we wouldn't be able to build many samples using Northwind and Pubs because their data models aren't rich enough to support our scenarios. Instead, we made an effort to build samples that show integrated solutions ranging from e-business, to financials, to manufacturing, to business intelligence (BI). We feel that Adventure Works greatly enhances our ability to demonstrate new functionality through samples, Books Online (BOL), Microsoft Developer Network (MSDN), and other resources.
SQL Server 2005 introduces a dedicated administration connection. What is this connection, and what's it good for?
Think about this problem: You have a runaway query inside SQL Server that's using all the CPU and eating up memory. To diagnose the problem and potentially kill the query, you need to connect and find out what's going on. But because the CPU and memory are maxed out, you can't access the server. In SQL Server 2005, you can connect through SQLCMD by using the Dedicated Admin Connection (DAC) because we reserve a small amount of resources at startup time to let this connection take priority. DAC will be available for troubleshooting only and won't be available for normal queries.
What benefits does SQL Server 2005's dynamic Help bring us? Do the tools provide other functionality to make it easier for people to find information?
The new Help system, which we're sharing with Visual Studio, is a leap forward. Dynamic Help lets you see the Help within the editor rather than having to separately search for the Help item in another window. The new Help search engine also lets you search within BOL and the community for answers.
What three features of SQL Server 2005's new management tools are you most proud of?
I'm proud of many of the features and requests we've incorporated from customers. Our customers feel that we made significant advances with SQL Server 7.0 and 2000, and I'm confident that they'll be just as pleased with the new level of management tools in SQL Server 2005. If I have to get specific, I think I'm most proud of the new dialogs in SQL Server Management Studio, SQLCMD, and SQLiMail. I'm convinced that DBAs will love these tools. However, if you catch me on a different day, when I'm spending time with different aspects of the tools, I could easily give you three different favorites.
Is SQL Server Management Studio really Visual Studio (VS) in sheep's clothing? Will DBAs need to learn VS to be proficient with SQL Server administration, and will they need to license VS?
SQL Server Management Studio uses some pieces of VS, but it isn't VS. And DBAs won't need to install, learn, or license VS to be able to administer SQL Server.
What role does WMI play in the SQL Server 2005 tools world, and how does SQL Server Management Objects (SMO) map to WMI?
We added a WMI provider on top of SQL-DMO in SQL Server 2000. For SQL Server 2005, customers said they preferred the strongly typed, rich object model in SQL-DMO, so we're moving to a series of new management object models in managed code. These models, called Management Objects, come in three specializations: SQL Server Management Objects (SMO), Replication Management Objects (RMO), and Analysis Management Objects (AMO).
We've also added a new WMI provider for configuration. Customers who want to change service-account passwords, client-configuration properties, or other disconnected configuration information can do so through WMI. However, we've also wrapped this functionality in SMO to accommodate customers who want to use only one object model.
Why are you providing a single environment—SQL Server Management Studio—for all query types such as T-SQL and MDX?
We're providing a unified environment for T-SQL and MDX for a couple of reasons. The first is that we want to provide a consistent authoring experience. The second is that a lot of customers work in both languages. For example, if you want to build a data warehouse, you'll end up writing T-SQL scripts and MDX and maybe XML/A. We want to provide a single experience for that customer.
Does SQL Server 2005 provide better tools for debugging and managing source code?
Yes, the next release of SQL Server features a new debugger resulting from our collaboration with the Visual Studio team. And the authoring experience has source-control integration.
