Manage Learn to apply best practices and optimize your operations.

Manageability and business analytics with SQL Server 2005

Although it's not scheduled to ship until early next year, attendees at the 2004 PASS Community Summit are eager to get closer glimpse at SQL Server 2005.

ORLANDO -- Though plenty of people have already caught a glimpse of SQL Server 2005, and some companies, Microsoft included, are even using Beta 2 in production environments, most enterprises won't be seeing the latest release of Microsoft's DBMS until it ships early next year. But attendees here at the 2004 PASS Community Summit are eager to learn about what's ahead.

Microsoft's third generation DBMS is a huge step towards Microsoft's vision of a "complete data platform," according to David Campbell, keynote speaker and general manager of the SQL Server relational engine at Microsoft. The volume of data is growing, the number of data types is expanding, and businesses are interested in learning more from their data sooner in its life cycle. And the way Campbell sees it, there should be no reason that these sorts of processes that used to take weeks to complete can't be done in a matter of seconds, directly in the database.

To that end, SQL Server 2005 includes a number of new features that make it easier to incorporate different types of data and develop and manage the entire system more efficiently. While maintaining, and enhancing, much of the GUI-based manageability that has made previous iterations of SQL Server so popular and easy to use, Microsoft is making a big move to appeal to enterprise users by embedding analysis and reporting features not typically included in a DBMS. Enterprises can build a robust data warehouse with all the pieces "included in the box," Campbell said.

Here are a few highlights of what you can expect in the final version of SQL Server 2005.

Focus on high availability, better backup and recovery

In the area of keeping SQL Server up and running, Microsoft has made a few notable updates. For one, online index operations allow an index to be built on a table without that table being locked. This is huge, according to Walter Guerrero of Computer Associates.

"If I want to create an index or alter an index, I can do it with everything online," Guerrero said. "Users will be able to access the table, and any concurrent changes will be placed into the table after the index creations is done."

James Luetkehoelter of Spyglass LLC, singled out database snapshot and database mirroring as two new features that greatly enhance the backup and recovery capabilities of SQL Server 2005. Database snapshot, essentially a read-only copy of a database, can be used to restore the original database back to a specific point in time. It can be used, for instance, as a safeguard when deploying new code. A DBA can take a snapshot of the database just before making any changes, and use it to go back in case anything goes wrong.

Database mirroring, a feature Oracle pros have long used, creates two physical copies of the same database. Described by Luetkehoelter as a "cost-effective alternative to clustering," database mirroring can be implemented on diverse hardware, but he notes that it can be complex to set up and isn't necessarily the best option to use over a WAN.

Integrated management tools

The familiar tools like Query Analyzer, Enterprise Manager, SQL Service Manager, Profiler, Index Tuning Wizard, and others are coming together in SQL Server 2005. Creating integrated, scalable tools to manage more systems and more types of systems was one of the key goals for SQL Server 2005, according to Richard Waymire, a group program manager in Microsoft's SQL server BI unit, who suggested that administrators might expect full integration even with third-party tools eventually. But for this round, Microsoft created a set of tools based on the "experiences" of configuration, management, authoring, performance tuning and operations.

Configuration tools, which will ship with MSDE, are built around SQL Computer Manager, which replaces Client Network Utility, Server Network Utility and SQL Service Manager.

SQL Server Management Studio "combines the object browser of Query Analyzer with the tree structure of Enterprise Manager," according to Waymire. It will include an enhanced help section that attempts to limit error numbers to a single possible scenario that would create it and includes a link to a specific Web page with resources and possible solutions.

Dmitry Sonkin and Slavik Krassovsky, software design engineers at Microsoft, emphasized the efficiencies DBAs can expect. Whereas now a DBA who typically deals with different problems at the same time and has to open many different instances of and windows of tools to handle different tasks, in SQL Server 2005, there's one tool, where all the menus are the same, the help section is the same, and the usability of the window interface is flexible. The goal of the tools, according to Krassovsky, is to increase productivity by making things like troubleshooting so much faster. "Management Studio will make life easier," Sonkin said.

All languages are supported in the new set of authoring tools, including the table designer and stored procedure designer. Templates will be available for common operations, and to the delight of many, Open Table will be restored in the beta 3 version.

For performance tuning and optimization tasks, Profiler maintains its current functionality and appearance but adds OLAP and DTS support, a new role for rights to run it, an XML showplan, deadlock visualization (without the need to use trace flag), and visual perfmon correlation. Database Tuning Advisor will replace the index tuning wizard and has new options, along with the ability to recognize partitions. Query hints are now broken down into groupings that make it easy to find the appropriate command. Replay, a tool not used by many people now, can be used to replay the profiler trace to simulate production loads and check coding before upgrading from SQL Server 2000 to 2005.

Finally, in operations, SQL Server Agent includes changes that should enhance performance and SQLCMD, the new command line interpreter, will replace osql and isql. SQLiMail will offer SMTP and cluster support, without the need for Outlook to be installed on the server.

Streamlined database application development

One of the biggest, and most controversial, changes in SQL Server 2005 is that the CLR is embedded inside the database engine, meaning T-SQL functions can be converted to CLR functions for significant performance gains, and applications can be run inside the engine.

A new tool called Service Broker also stands to alter the way applications are designed. Campbell described the new feature as an asynchronous programming environment that creates a "persistent dialogue" between the client application and the server.

Jeremy Kadlec, a consultant with Merrimack, NH-based Edgewood Solutions and a expert, singled out Service Broker as one that he thinks will have a big impact on application development, both for its technical innovation and the fact that it is integrated into SQL Server. Speaking of the queuing involved with a lot of different applications, Kadlec pointed out that most companies currently rely on third-party tools to alleviate the problem. But with Service Broker, Kadlec says, there's no additional cost associated with the functionality, and in the long run, it will be easier to maintain.

Additionally, semi-structured data, like user defined functions, stored procedures, triggers and XML files, will have "deep support" in 2005, and a new data type, varchar(max) will be available. Also, SQL Server 2005 can be accessed directly by Web services via HTTP and SOAP.

DTS is now SQL Server Integration Services

The utility formerly known as Data Transformation Services (DTS) has been revamped and renamed to SQL Server 2005 Integration Services. The name change isn't just a marketing ploy; the new services don't share a single line of code with the former DTS utility, according to Donald Farmer, a group program manager in Microsoft's SQL server BI unit. Described as "a quantum leap to the enterprise class," Integration Services include a wealth of new features like "full blown ETL" that scale up for large enterprises, but also maintain the ease of use that scale down to small businesses.

Most of the new features in Integration Services aim to ease, as the name implies, integration issues -- those stemming from scattered development and management interfaces and disparate data sources. With Integration Services, for example, developers can build projects in one Visual Studio process, and all development languages are supported. DBAs can manage SQL Server and Integration Services from one place. Non-traditional data, like XML or Web services files can be treated like relational or flat files.

As with DTS 2000, packages and tasks remain the units of execution, but Integrations Services provide some simple "out-of-the-box" tasks -- like FTP and e-mail -- that had to be done manually in DTS, and some major tasks -- like the data transformations task -- that didn't quite exist.

The data transformation task is what allows for source-independent data mining and cleansing -- before the data gets to the data warehouse. Whereas the traditional method is to load data first, then mine it, Integration Services make parallel processing possible, with data pushed to multiple destinations. And the wizard that drives the task makes it all pretty easy. Farmer demonstrated how he could create a slowly changing dimension, run an ETL process and debug it within five minutes. Missing values or errors, common problems in data warehousing implementations, were easily identified in the data flow, and actions to fix them could have been taken while the data was still loading.

For a customer looking for real-time information, these new features allow for much faster decision making, a capability appealing enough to Barnes and Noble that the national book retailer is taking potentially risky path of using SQL Server 2005 beta in production. Erik Veerman, a consultant for Atlanta-based Intellinet, is working on the high profile project, which involves sifting through an estimated 2-3 TB of inventory data to track every single item in stock to optimize local store sales. Veerman is impressed with SQL Server 2005's flexibility to handle complicated scenarios that needed hand-coded workarounds in SQL Server 2000 and the code management integration that not only sped up the development process, but also made working with his five member team easier.

Though the new features add complexity, Integration Services remains true to its ease-of-use roots, with a highly visual GUI that utilizes ample color coding and a lot of wizards that keep coding minimal. The services can be run on lightweight hardware -- Farmer demonstrated the ETL process on his laptop -- and will be available with the beta 3 version of SQL Server 2005.

Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.