A SQL Better Than the Original

SQL Server 2005 will raise the bar in terms of integration and standardization.

• By Kathy McKinney
• September 01, 2005

Depending on which edition you're talking about (there are several—see "The Many Faces of SQL"), SQL2005 will also include business intelligence (BI) tools, built-in reporting support, improved performance and reliability features, and greatly improved functionality for an XML and HTTP standards world. Those extras come at no charge, by the way—a fact that's sure to get your customers' eyes to light up.

There are myriad other improvements and enhancements across the board in SQL2005:

• Manageability improvements include SQL Server Management Studio and SQL Management Objects
• Availability improvements include Database Mirroring; expanded Failover Clustering that also includes Analysis Services, Notification Services and SQL Server Replication; Database Snapshot; Dedicated Administrator Connection; Online Index operations and Restore
• Scalability improvements include Table and Index partitioning and Snapshot Isolation
• Security improvements include Kerberos authentication for log-in policies and native encryption, and all client/server communications encrypted by default The new utilities in SQL2005 will please the database administrator, architect and developer alike, so you'll have something to tell all your customers.

Looking Better
The first thing most SQL veterans will notice is the change in the interface and tools. Gone are the standard Enterprise Manager, Query Analyzer, Profiler and Import & Export Data selections. Instead, these features are built into the SQL Server Management Studio—a new, integrated management console. This is the SQL administrator's primary tool.

Based on Microsoft's Visual Studio Framework, this integrated management console gives your admin customers an interface to control not only the SQL Server engine, but Integration Services (a data transformation services replacement), Analysis Services, Reporting Services and Notification Services. From a developer's perspective, the learning curve is shortened because the SQL Server Management Studio and the Business Intelligence Development Studio use the Visual Studio architecture. Developers can now program in any .NET language, such as Visual Basic.NET or C+, directly in SQL2005.

There is now a native Web service component to SQL2005 as well. Your customers will be able to get at the database from their Web browser without needing IIS running on the SQL Server computer. New SQL commands help administrators easily set up these HTTP endpoints and configure the stored procedures, permissions and so on.

SQLCMD has replaced ISQL (Interactive SQL) and OSQL (Interactive SQL using ODBC), the command line interface utilities to SQL Server. SQLCMD uses OLE-DB instead of ODBC, and can also use environmental variables. For those of us who are script aficionados, the new SQLCMD will come as a welcome addition.

There is also a special SQLCMD connection called the Dedicated Administrator Connection (DAC). This is available in the event that SQL Server doesn't respond to other connections. It lets administrators troubleshoot and correct problems, where previously they would have to restart the service and/or do a full system reboot to free the SQL Server process.

SQL2005's new Data Definition Language (DDL) allows full triggers, with rollback transaction support placed on DDL statements—not just data modification commands. This means, for example, that a Create Table statement can fire off a trigger. Creating tables is often permitted only by the server administrator and usually in a very controlled environment. Implementing a DDL trigger can check the user and deny the Create Table operation. DDL triggers are implemented within a database or server scope, depending on the command.

Disaster Recovery
SQL2005 introduces numerous options for disaster recovery, most notably database snapshots and full backups. Database snapshots are read-only variants of a source database that customers can use to restore data back to the original database. If any data is improperly updated or deleted, customers can simply copy it from the snapshot to the source database.

SQL2005 also introduces mirrored backup files to provide data availability in the event of a single backup media failure. The backups must be of the same media type, such as disk or tape. Customers can make restoration files from either of the files in the backup mirror.

Reliability
SQL2005's database mirroring supports data reliability. Database mirroring is a form of continuous transaction replication with an optional "witness server" that can detect breaks in the mirror and automatically switch to the mirrored database as the primary. The beauty of this fault-tolerance feature is that customers don't need any special hardware; plus there are no geographic constraints as with clustering technology.

There are a number of other replication enhancements in SQL2005. The interface is streamlined, there's a new Replication Monitor, and it has performance improvements, including the ability to use an Update command instead of Delete/Insert commands for certain record modifications.

Performance
Performance enhancements in SQL2005 include advanced index management and partitioning, enhanced concurrency options and a new Database Tuning Advisor that replaces the previous Index Tuning Wizard. For performance testing, there's a new option to disable an existing index. This lets developers test a query both with and without an index. The index still exists and developers can re-enable it at any time.

When creating and rebuilding indexes, developers can now allow page locks or row-level locks (instead of the previous index lock required). Thus, queries won't be blocked during an index build. For large databases that use table partitioning, developers can now partition associated indexes, which will speed up queries.

The performance impact of these features, in conjunction with the general engine enhancements, have generated TPC-H benchmark results that bettered SQL Server 2000 by 162 percent and Oracle by 38 percent—all at a cost that is 20 percent less per query. HP has also posted a TPC-C result on SQL2005 that exceeds the one million transaction-per-minute benchmark, the highest ever recorded in a Windows environment. That's good news to pass along to prospective customers.

Integration
Microsoft continues to push the envelope with regard to integrating its products, lowering total cost of ownership (TCO), increasing business acumen and decreasing development time—all within a secure, reliable and open environment. SQL2005 is no exception.

Bundling Reporting Services, Integration Services, Analysis Services, Notification Services, Replication Services and an integrated management and development toolset will help customers of all sizes leverage their data assets, increase productivity, reduce IT complexity and realize a lower TCO. Because of SQL2005's standards-driven architecture, integration with Microsoft Operations Manager, Systems Management Server, Windows Update Services, Web services and other applications is much more straightforward.

True to Microsoft's direction to provide integrated, enterprise BI products, it will also release Visual Studio 2005 and Biztalk Server 2006 the week of Nov. 7.

Competitive Landscape
SQL2005's strongest competitor is clearly Oracle. Some features in SQL2005 will give Oracle a run for its money though, including:

• Online index reorganization and table partitioning
• Dynamic performance views
• Dedicated Administrator Connection
• Password complexity requirements for SQL Server log-ins
• Database mirroring with automatic failover

The mechanism used to embed code in the SQL and Oracle databases is similar on the surface, but architecturally different. With SQL2005, Common Language Runtime (CLR) and .NET integration into the database engine lets developers call code directly from Transact-SQL, while Java is called indirectly from an Oracle database. The SQL2005 Visual Studio Manager provides a single interface with which to develop, debug and use .NET stored procedures, triggers and packages.

Perhaps one of the most important features of SQL2005—that has no corresponding functionality in Oracle, by the way—is an integrated tool for data extraction, transformation and loading. This and the Integration Services in SQL2005 simplify the process of importing and massaging external data for subsequent use in database tables.

Many customers will want to rewrite their Crystal Reports with the inclusive Report Service and Report Generator. Then they'll be able to schedule or request reports ad-hoc, and hard-code and compile them to provide a complete application that can run outside of the Report Service process.

(Back to top)

Marketing and Sales
Microsoft is gearing up to help its partners understand, promote and sell SQL2005 through a series of training programs, road shows, toolkits and conversion utilities. To make it easier for partners, as well as the general public, to learn about the new product prior to production, Microsoft has launched its first Community Technology Preview (CTP) program.

This program lies between the beta evaluation process and production release. Anyone can download the current CTP version to install and test without the restrictions of a standard beta program.

The CTP program is a big boost for partners who need to thoroughly understand the product prior to production release so they can be subject matter experts for their clients. Since there is no formal feedback requirement involved in the CTP program, partners don't have to use their limited resources for product testing and communicating results back to Microsoft.

As further example of its commitment to educate the general user community, Microsoft offers a number of free online courses. For the fall launch of SQL2005, Microsoft also is planning a 15-city "Get Ready for SQL Server 2005 Road Show," a series of one-day immersion sessions into the administration, development and BI features of the new release. During the road show sessions, experts will present a full day of SQL2005 technical content and best practices for migrating to SQL2005. Representatives from third-party tool vendors and from Microsoft itself will show attendees how to use SQL's new capabilities to improve their database-computing environment.

A Fitting Sequel
SQL2005 is much more than a major product upgrade because there are so many powerful and integrated products and services bundled at no additional cost. This will be a compelling driver for introducing small to midsize businesses (SMBs) to SQL2005.

No longer will they have to get one vendor's database, a reporting product from another vendor, a data warehouse solution from yet another and a fourth product for integration components. It's all there in one package. Single-vendor, single-source software drives down costs and increases productivity, which makes this the ideal time to heavily promote SQL2005 to the SMB community.

Enterprise organizations will also be looking seriously at SQL Server 2005 to support their large databases, eliminate third-party products and associated costs, speed up development and lower overall costs. SQL2005 is an enterprise database ready for use in the most demanding mission-critical line of business applications. Its feature set, disaster recovery capability, reliability, performance and competitive edge, combined with an impressive TCO, will likely shift the way enterprise database architects design databases for years to come.