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.
Prev. page
1
2
[3]
4
next page 
