SQL Server 2005 Upgrade Handbook

By Douglas McDowell, Erik Veerman, and Michael Otey

On This Page

Reap the Benefits of SQL Server 2005
Enhanced Enterprise Data Management
Developer Productivity
Advanced Business Intelligence
Competitive Features
Rapid ROI: Consolidate on SQL Server 2005
Customer Scenarios
Preparing for a Smooth Upgrade
The Upgrade Mechanism
The Upgrade Process
Component Upgrade Considerations
Operating System and Hardware Upgrade Considerations
Additional SQL Server 2005 Resources
About the Authors

Reap the Benefits of SQL Server 2005

If your organization depends on SQL Server today, prepare for SQL Server 2005—an upgrade that delivers immediate results for existing applications and continues to deliver results as you enhance existing applications and develop new applications to fully exploit the new capabilities. Today’s business environment demands a comprehensive data-management platform that delivers business results with efficiency. SQL Server 2005 provides a comprehensive data-management platform, integrating the development and management of relational data as well as extraction, transformation, and loading (ETL), OLAP, and reporting with the security, performance, and availability to meet the needs of the most demanding enterprise applications.

You can intelligently share data across databases, devices, and applications from multiple vendors by adopting SQL Server 2005’s standardized data platform. This strategy delivers value by letting you make IT investments based on open standards and widely available developer and administration skill sets and tools.

The new release also lets you control costs without sacrificing enterprise-level performance, reliability, or security. Through a comprehensive enterprise data-management feature set, commodity hardware support, and highly productive integrated development and management environments for developers and DBAs, SQL Server 2005 provides the industry’s lowest upfront-implementation and ongoing maintenance costs. The family of SQL Server 2005 editions—Express, Workgroup, Standard, and Enterprise—includes everything you need in one product, with no incremental fees for comprehensive, out-of-the-box data integration, management, analysis, and reporting functionality. Let’s look at how the new release delivers value in the key areas of enterprise data management, developer productivity, and business intelligence (BI).

Enhanced Enterprise Data Management

SQL Server 2005 is ready for the enterprise, offering exceptional data availability and manageability, hardened security, and the ability to scale from handheld mobile devices to the most demanding OLTP systems and multi-terabyte data warehouses. The new release makes DBAs more productive by automating routine tasks and letting administrators focus on higher value activities. SQL Server 2005 also provides an extensible framework for proactive health and performance monitoring. Here’s a look at the new release’s enterprise data-management features

Enterprise Performance

SQL Server 2005 boasts impressive performance for every component, from the relational engine to BI. The relational engine handles demanding OLTP workloads and multi-terabyte data warehouses. SQL Server 2005 Integration Services (SSIS) moves millions of rows per second while performing in-memory transformations with delivery to multiple destinations. Analysis Services offers unified access to information with sub-second query times, advanced caching, and data mining with predictions for very large data sets. Notification Services supports hundreds of thousands of subscription-based users with numerous business rules for event polling. Reporting Services has deployment models to scale up or scale out, coupled with advanced caching and snapshot strategies to support high user concurrency. Bulk Load has always been a fast way to insert data into SQL Server, and in SQL Server 2005, you’ll find even faster performance for bulk-loading in and out of the SQL Server process using Bulk Copy Program (bcp.exe) and BULK INSERT (T-SQL). The new release also features better performance and more options for the OPENROWSET() function, which you can also use as a scalable method for loading XML documents.

High Availability

For mission-critical applications, SQL Server 2005 delivers 24 x 7 data availability, providing end users consistent access to information. Failover clustering and database mirroring technologies let you deliver highly reliable, available applications to your employees, customers, and partners with minimal downtime. Features such as online indexing, piecemeal backup and restore, partitioning, dynamic configuration, and support for hot memory swapping minimize or eliminate downtime and give users uninterrupted access to enterprise data—even during disaster recovery operations. SSIS offers distributed deployment and restartability, promoting resilient (ETL) processes. The integration of Notification Services and Reporting Services with Microsoft IIS brings network load balancing (NLB) for maintaining uptime. And SQL Server 2005 provides more—and more affordable—disaster recovery options than previous releases, with peer-to-peer replication, database mirroring, log shipping, and the Analysis Services Server-Sync functionality allowing multiple servers to support primary servers. And for guaranteed direct access to SQL Server for vital database recovery operations, SQL Server 2005 adds a dedicated administrator connection.

Manageability

SQL Server 2005 gives you a single, unified management tool—SQL Server Management Studio—that lets you manage the entire SQL Server platform from one interface. This integration boosts DBA productivity across all the SQL Server implementations in your enterprise. The release also lets you more easily identify, troubleshoot, and resolve performance problems. SQL Server Profiler benefits from improved trace capabilities that encompass all core products in SQL Server, including SQL Server Database Services, Analysis Services, and Integration Services. With SQL Server’s new extensible XML-based definition, you can capture and effectively analyze more details, use aggregated views, and perform correlations with Windows Event Logs and a newly expanded set of performance counters. Microsoft has added graphical deadlock and Showplan enhancements along with more comprehensive access to crucial metadata through catalog views for database objects and Dynamic Management Views (DMVs) for ongoing server activity, dynamically changing state, and diagnostic information. You can also automate all repetitive or common SQL Server administrative tasks—such as programmatically retrieving configuration settings, creating new databases, applying T-SQL scripts, creating SQL Server Agent jobs, and scheduling backups—by using SQL Management Objects (SMO) and the Profiler API. SMO also enables ISVs and partners to build on top of the management framework and provides much better scalability and performance than SQL Distributed Management Objects (SQL-DMO). SQL Server 2005 continues to support applications written in SQL-DMO with DMO9. You can also use SQL Server Agent to build a proactive performance monitoring solution and speed problem isolation and identification. The new Database Tuning Advisor (DTA), which replaces the Index Tuning Wizard (ITW), can help you resolve performance issues. Furthermore, new security contexts let you grant users who don’t have system administrator (sa) access the ability to create database traces and use DTA for database tuning.

Security

Microsoft set the standard for security with its Trustworthy Computing initiative, which ensures a safe and reliable computing experience. SQL Server 2005 delivers on this from the design of the product through its final deployment: secure by design, secure by default, and secure in deployment. By default, SQL Server 2005 delivers maximum security with a minimal surface area. A new dedicated security-configuration interface called the Surface Area Configuration (SAC) consolidates access to configurable services and settings and gives you brief configuration descriptions to help you make informed decisions. The new release introduces fine-grain administration rights, which let you grant levels of development and administrative rights decoupled from the levels of data access rights in each platform subcomponent. SQL Server 2005 also gives you more control over grantable permissions, password policy enforcement, and increased levels of data encryption for storage and transmission. A secure development environment also lets developers more easily sign, verify, and manage code—including Common Language Runtime (CLR) assemblies that run in the database—and all managed code uses Code Access Security (CAS) to prevent assemblies from performing certain operations to protect the OS or database server from compromise.

Developer Productivity

SQL Server 2005’s many new development features and a comprehensive enterprise tool set empowers developers—whether in small or large project teams—to rapidly deliver robust enterprise database applications. Here’s how SQL Server 2005 helps your development team reduce time to market and collaborate to produce better solutions.

Time to Market

Deep integration between Visual Studio 2005 and SQL Server 2005 fosters rapid development and testing cycles. The developer interface increases efficiency, with wizards, accelerators, and step-by-step documentation letting you develop often complex processes in a fraction of the time normally required. With SQL Server 2005, developers can use one development framework for relational, XML, and OLAP applications integrated with Visual Studio 2005 for faster and more efficient development and debugging. Developers can take advantage of the architectural switch from batch-level recompilations to statement-level recompilations, which requires less coding to prevent undesired recompilations. The introduction of large value types—varchar(max), varbinary(max), and nvarchar(max)—reduces the complexity of database programming, decreasing the special requirements for blob data types. SQL Server 2005 also adds Query Notifications, which enable an application to request a notification from SQL Server when the results of a query change. This functionality lets developers design more efficient applications that aren’t unnecessarily polling the underlying database for changes and using caching or disconnected recordsets when the database hasn’t been updated. You can also gain further development efficiencies by using new data-access-layer features, including the support for Multiple Active Result Sets (MARS), which allow applications to have more than one active default result set per connection.

Team Collaboration

Larger groups of SQL Server developers can now work together interactively on complex or small projects by using the mature Visual Studio 2005 deployment model. SQL Server 2005’s integration with Visual Studio Team System and other source-control platforms promotes developer consistency and accountability and enables better configuration-management processes. Support for mature configuration-management practices and procedures reduces rework and miscommunication.

Interoperability

Through support for Web services and the .NET Framework, SQL Server 2005 supports interoperability with multiple platforms, applications, and programming languages. Support for existing and emerging open standards such as HTTP, XML, SOAP, XQuery, and XSD facilitates communications across your extended enterprise systems. Native XML support in SQL Server 2005 runs deep: You’ll find XML storage in a dedicated XML data type that has its own index type, strong query capabilities via XQuery and XPath, and the ability to create XML code from relational data by using the XML Schema Definition (XSD) language. FOR XML PATH, a new relational query output format, allows nested FOR XML queries, which greatly simplifies queries for which you might currently be using the FOR XML EXPLICT option. Microsoft has even added an XML output format as an alternative to the conventional Showplan for query-plan evaluation.

Developers can build more secure, reliable, and scalable applications using the new SQL Server 2005 Service Broker technology. Service Broker provides queuing and reliable messaging between SQL Server instances, supporting scalable applications that benefit from a robust asynchronous programming model. Your applications can also embed new reporting functionality when you use the Visual Studio 2005 Report Controls for Reporting Services. These controls let you deploy reports whether or not you have a Reporting Services report server available for report processing and rendering. With native support for the .NET Framework CLR, SQL Server 2005 and Visual Studio 2005 converge to let developers write stored procedures, triggers, user-defined functions (UDFs), user-defined types (UDTs), and user-defined aggregates in managed code. And because managed code compiles to native code before execution, you can gain significant performance increases in some scenarios.

Advanced Business Intelligence

SQL Server 2005 provides the components and tools you need to implement a comprehensive, end-to-end BI solution—whatever your analysis and reporting needs are. And implementing the release’s entire integrated BI feature set can magnify the benefits you’ll achieve with a SQL Server 2005 upgrade. How can the new and improved BI features help you deliver solutions that give your users and business decision makers access to the information they need?

Single Version of the Truth

SQL Server 2005 provides a holistic view of your business for all reporting and analysis, truly integrating reporting with OLAP. This new paradigm, called the Unified Dimensional Model (UDM), handles complex data modeling scenarios, combining the benefits of relational and traditional OLAP so that you get a balance between data latency and query performance. UDM also helps you with localization issues and other unique reporting and analysis circumstances that historically required multiple products. The new BI Development Studio, integrated with Visual Studio, encompasses all BI development and extends developer productivity benefits into all corners of SQL Server development. In addition, SSIS’s enterprise ETL features, including high-performance data-movement and advanced data-cleansing capabilities, lets you more easily create and maintain a centralized data store that represents your entire business.

Timely Business Insights

By letting your organization quickly and easily integrate and analyze business data from multiple heterogeneous data sources, SQL Server’s BI capabilities can help your business gain crucial insight into its markets and compete more effectively. In SQL Server 2005, the reduction of data latency that SSIS provides, SQL Server Query Notifications and real-time OLAP capabilities within the UDM, timely data mining predictions, and compelling presentation options (including Reporting Services, Microsoft Office, and Microsoft SharePoint Server) give developers multiple ways to build solutions that users can rely on for rapid and critical business insight.

Advanced Analytics and Reporting

Through rich reporting, advanced analytics, data mining, and familiar tools such as Microsoft Office, SQL Server 2005 lets you give users the power to build their own views of business information. With the introduction of Report Builder, the Reporting Services ad hoc reporting tool, end users can intuitively navigate data sources to build and share reports that let them drill down from summary data into details. Report Builder provides a data-modeling tool that you as a developer can use in a one-time process to create an intuitive semantic model for your users. With this semantic model, users can then use a light-weight browser-deployed .NET Smart Client interface for ongoing ad hoc report generation. SQL Server 2005 also provides rich analytics that you can use to build applications that integrate data-mining models into daily business operations. The new release adds six new data mining algorithms.

Competitive Features

A SQL Server 2005 upgrade also presents a great opportunity to migrate applications to SQL Server. New features such as snapshot-related connection contexts, Read Committed Snapshot Isolation, and Snapshot Isolation let many Oracle-hosted applications behave on SQL Server 2005 just as they do on Oracle. Impressive enhancements to the T-SQL query language accelerate SQL Server’s competitive lead. T-SQL now includes constructs such as EXCEPT and INTERSECT, PIVOT and UNPIVOT, RANK, and TOP N Sort in addition to common table expressions (CTEs), which enable advanced recursive queries. And the release adds new error-handling capabilities with TRY...CATCH statements. You can also use Data Definition Language (DDL) triggers, a special kind of trigger that fires in response to DDL statements, to perform administrative tasks in the database, such as auditing and regulating database operations.

The new SQL Server release features a simplified licensing model (per processor or per server with client access licenses) for every feature a specific software edition offers. This enables SQL Server 2005 to play a central role in an organization where you can extend the value of an initial licensing investment by using additional features of the comprehensive platform at no incremental cost. For example, you can expand a SQL Server 2005 data-storage platform upgrade to also offer management, replication, analysis, and reporting functionality.

In addition, SQL Server 2005 is an excellent solution for small and medium businesses. Microsoft has responded to the needs of smaller businesses with the introduction of two entry-level licensing options: the freely distributable SQL Server 2005 Express and the cost-effective SQL Server 2005 Workgroup Edition. Designed to scale from the largest enterprise down to the smallest business, SQL Server 2005 provides the same performance, security, reliability, and business value to all customers.

Prepare to realize the benefits of upgrading to SQL Server 2005, which delivers an enterprise data-management platform with advanced BI functionality and impressive developer and administrator productivity features. Start digging into SQL Server 2005’s advantages today and begin your upgrade planning so that your DBAs, developers, and entire organization can reap the benefits this new release brings.

Rapid ROI: Consolidate on SQL Server 2005

Some in the IT industry continue to recommend a “best of breed” approach, which means identifying and using the very best software component for each task at hand. But what does this approach mean from a data-management perspective? Following this strategy, which seems wise at first blush, you would use the very best relational database management system (RDBMS), the very best data replication software, the very best extraction, transformation, and loading tool (ETL), the very best multidimensional (OLAP) database engine, the very best full-text index and search engine, the very best alerting and notification engine, the very best data mining suite, and the very best enterprise reporting suite.

But all these independent, best-of-breed software components can produce a bloated, complex, non-integrated data-management platform, creating a fragmented portfolio of tools instead of a seamless, integrated platform. Each isolated tool demands its own server, its own client development and administration tools, its own licensing and maintenance agreement, its own specially skilled developers, its own deployment model, its own security model and auditing structure, and its own operational team. Add these costs together, and the “platform” cost can be staggering.

If you haven’t taken inventory of your data-management software recently, take a moment to answer the questions in Figure A. Then, consider the benefits of consolidating on the comprehensive, integrated data-management and analysis platform that SQL Server 2005 provides.

Figure 1

Each SQL Server 2005 component competes aggressively in its respective software category but represents an amplified value proposition when you deploy it as part of an integrated platform. SQL Server 2005 provides enterprise-class tools that offer excellent value independently and unequaled value in a holistic deployment strategy.

Consolidating on SQL Server 2005 lets you concentrate and conserve your resources instead of diverting them into a variety of different products, licensing requirements, and development and administration tools and skills. The SQL Server 2005 platform is focused on lowering total cost of ownership (TCO) through resource concentration. TCO for IT assets goes beyond software expenses to include the “hard” costs associated with the people who develop and administer the software, the dedicated hardware the software requires, and the accessibility of information assets by end users, as well as the “soft” costs of inefficiencies and ineffective solutions.

Best-of-breed data-management strategies require specialists or specialized knowledge to get the most out of each product. Each tool or suite also has its own development, administration, and end-user tools and interfaces, so each task a developer, administrator, or end user needs to complete requires stepping out of one interface and into another. But as anyone in IT today knows, personnel allocations aren’t increasing. Your IT department has to meet growing business needs with fewer developers and administrators—and less time. And as you try to do more with less, SQL Server is your best ally. Self-tuning functionality, rapid deployment features, product interoperability, and a strong support community (including helpful product and online documentation, newsgroups, and supporting software utilities) prove SQL Server is the upgrade and migration path of choice—especially when your business infrastructure depends on limited personnel and time.

SQL Server 2005 also offers powerful tools that deliver productivity improvements for developers, administrators, and end users. Through its comprehensive tool sets for IT professionals and end users, the new SQL Server 2005 release reduces the complexity of creating, deploying, managing, and using enterprise applications. In addition, embedded business intelligence (BI) tools for reporting and data analysis integrate with familiar tools such as Microsoft Office, letting users at all levels of your organization make business decisions based on realtime data. And the release’s scalability and performance advancements, such as partitioning, extended indexing, and 64-bit support, enable you to build and deploy applications that can meet any business demands you have.

You can also see consolidation’s benefits in the hardware realm. Today’s software landscape has created a “dedicated server” mindset when mixing products from different vendors for production use. Each product in the environment must have its own server because we don’t know how the performance, security, or interoperability of one product will affect the other. This setup grows more complex as vendors introduce new builds, patches, and service packs of their products and we try to mitigate against the unknown risks of applying the fixes and upgrades. Although implementing dedicated servers gives you peace of mind, it can exponentially expand hardware and OS costs as well as the administrative burden associated with each server. However, using the integrated SQL Server 2005 platform to deploy comprehensive application architectures, requires fewer servers than a best-of-breed approach. And because of SQL Server 2005 integration and interoperability on both 32-bit and 64-bit platforms, performance and monitoring in a consolidated environment becomes more manageable and resources are better directed.

Instead of a best-of-breed data-management approach, consider a viable option that lets your organization consolidate business needs on a single platform that provides stronger IT and end-user value, lower licensing costs, fewer servers, and a standardized skill set for a standardized tool set. Concentrate your personnel resources, tools, and hardware by using SQL Server 2005.

Customer Scenarios

Barnes & Noble: SQL Server Integration Services Handles Load

“Moving to SQL Server 2005 Integration Services for our enterprise ETL needs was the right decision,” says Lou Ann Leary, vice president of applications for Barnes & Noble. Barnes & Noble’s multi-terabyte data warehouse tracks detailed product sales and inventory across its stores and online commerce. SQL Server powers the solution on every tier: SQL Server 2005 Integration Services, the release’s new extraction, transformation, and loading (ETL) tool, processes and loads the data, processing 15 million to 20 million records daily and 200 million records each week.

Leary adds that Barnes & Noble also increased developer productivity and performance with SQL Server 2005. “We were able to immediately capitalize on the team development features and rapid learning curve,” she notes. “But more impressive were the performance benefits as we went to production. The process handles millions of transactions each day in short order, keeping our analysts up-to-date with information on the status and trends of our business.”

Another high-value target area for Barnes & Noble is data mining. “We are working to implement the data mining features of SQL Server 2005 in order to predict and therefore reduce out-of-stock scenarios at our stores,“ Leary adds.

Recall Corporation’s Answer: Full-Text Search Enhancements

“The performance increases in the SQL Server 2005 Full-Text Search capabilities saved us from an application re-architecture,” says Brian Beard, vice president and chief information officer of Recall Corporation. “We had simply outgrown the Full-Text capabilities of SQL Server 2000.”

Recall depends on a global work-order entry application that heavily uses SQL Server’s Full-Text Search features. With internationally distributed deployments of the application in three data centers, Recall has more than 260 million rows indexed for full-text search. Dave Hudson, Recall’s manager of application development, talks about the out-of-the-box performance increases the company saw after implementing SQL Server 2005. “We first tested with Beta 2 and were pleased to see our rebuild time for the Full-Text indexes drop from 14 hours down to 90 minutes on 2.5 million rows of data,” he says. “When we successfully deployed the latest beta on production data and saw that indexing time was truly linear—indexing 150 million rows in only 16 hours—we knew the 2005 upgrade was the only answer we needed.”

“The upgrade to SQL Server 2005 from 2000 was a smooth transition,” adds Alan Wren, Recall’s DBA, who upgraded the company’s clustered deployment to SQL Server 2005. “And the wealth of new tools will enable us to maintain our databases more efficiently and with fewer resources.”

Jason Willard, who leads Recall’s business intelligence (BI) efforts, has been another internal champion for SQL Server 2005. “We are deploying 2005 for every tier of our enterprise BI platform: ETL, relational data warehouse, OLAP, data mining, and enterprise reporting,” he says. Willard has had strong support from Scott Johnson, director of IS, who orchestrated the deployment of the Full-Text Search upgrade and is currently planning a consolidated 64-bit BI infrastructure.

Preparing for a Smooth Upgrade

Each SQL Server 2005 component has a unique architecture and life cycle—the two primary areas that will affect your upgrade path. Some SQL Server 2005 components build on a solid foundation to augment, optimize, and stabilize existing functionality. Microsoft has performed extensive, under-the-hood rework of other features to give SQL Server the horsepower and programmability features that your applications need. The new release also provides complete component overhauls and additions to meet a new generation of data-management needs.

These changes, taken in bulk, might seem overwhelming at times. But even with all the enhancements in SQL Server 2005, the process of moving your solutions to take advantage of the new release’s functionality is very manageable. In a short time, you’ll be in a stable position, planning how to leverage the new features.

Preparing for a SQL Server 2005 upgrade involves understanding some basic principles that will help you make appropriate decisions and ensure your success. Let’s begin our look at upgrade planning by exploring the overall upgrade path for each SQL Server 2005 component and seeing how the new SQL Server 2005 Upgrade Advisor tool can help you identify areas you need to pay special attention to. Then, we’ll drill into specific upgrade considerations for each SQL Server 2005 component, from the database engine through Integration Services and Analysis Services, and ending with Reporting Services. As with any upgrade, the keys to success are appropriate planning and testing for the needs of your specific environment.

The Upgrade Mechanism

For all components, SQL Server 2005 provides an upgrade from SQL Server 2000 or 7.0. Note that Microsoft distinguishes between a SQL Server 2005 upgrade and a migration.

An upgrade is an automated process in which the upgrade tool, called Setup, moves an old instance of SQL Server to a new instance while maintaining the data and metadata of the old instance. At the end of the upgrade, the old instance is no longer available and the new instance has the same name as the old instance. Migration is a manual process in which the DBA installs a new instance of SQL Server and copies the metadata and data from an old instance of SQL Server to the new instance. Migration provides access to two instances of the system, letting you verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance and the old instance is manually removed.

Although the database engine brings with it many new features, you can easily upgrade databases on SQL Server 2000 or 7.0 to SQL Server 2005 by using the Setup wizard or by performing a database restore or attach/re-attach. Moving from Data Transformation Services (DTS) to SQL Server 2005 Integration Services (SSIS), however, requires a migration, assisted by an out-of-the-box migration tool to help you move data processing to the new architecture. Table 1 summarizes the upgrade path for each SQL Server 2005 component.

SQL Server Component

Upgrade/Migration Path

Database Engine

Upgrade Tool: Setup
Migration Method: Side-by-side installation, then database backup/restore, detach/attach

Analysis Services

Upgrade Tool: Setup
Migration Tool: Migration Wizard migrates objects, requires optimization and client provider upgrades

Integration Services

Upgrade Tool: None
Migration Tool: DTS Migration Wizard
Migration Method: Migration Wizard converts 50-70 percent of tasks, requires some manual migration; runtime DTS DLLs available in SSIS; package re-architecture is recommended

Reporting Services

Upgrade Tool: Setup
Migration Method: Side-by-side installation and deployment of reports on new instance

Notification Services

Upgrade Tool: None
Migration Tool: Upgrade of Notification Services instances during install

Table 1: Upgrade path for each SQL Server Component

Using the compiled knowledge from the product team, internal lab testing, and extensive SQL Server 2005 early adopter experience, Microsoft has developed an essential tool for your upgrade preparation called the Upgrade Advisor. Figure 1 shows the Welcome screen for the Upgrade Advisor, which analyzes the configuration of your existing database server, services, and applications and provides reports that identify changes within the SQL Server 2005 product that will impact your upgrade. These changes include security enhancements, closer adherence to the SQL standard, and architectural changes. The Upgrade Advisor also provides links to documentation that describe these changes and necessary steps to complete the upgrade process. The Upgrade Advisor will help you manage the changes between releases, improve your upgrade planning, and minimize any surprises after you’ve completed your upgrade. Whether you’re running Analysis Services for BI, DTS for data processing, Notification Services for alerting, Reporting Services for your enterprise reporting, or a combination of components, the Upgrade Advisor has you covered.

Figure 1

The Upgrade Advisor, built on a rules-based engine, is easy to install and run, even against remote servers. Here’s how it works. When you execute the tool, a simple wizard prompts you to select components on a local or remote server, as Figure 2 shows. Based on your selection, the wizard prompts you to identify details about each component. For the database engine, you can pick all the databases on the server or select each one separately. The Upgrade Advisor analyzes all stored procedures and embedded T-SQL programs. Even better, you can point to a SQL trace file that will analyze the T-SQL running against your databases (an important feature if you have applications that embed SQL logic). You can analyze DTS packages that might be stored in files or embedded in the SQL instance you choose; you can also select your Notification Services instance at this time.

Figure 2

After the tool completes its analysis, you can view a list of issues in the Upgrade Advisor Report Viewer, as Figure 3 shows. The Report Viewer provides a summary of issues, noting whether you should handle the corrections before or after the upgrade. The Report Viewer lets you view the details of any modifications you need to make to your server, which objects (e.g., scripts, stored procedures) you need to modify, and details about when to make the changes. The Report Viewer also helps you manage the modification tasks, letting you check off completed tasks, sort tasks, and create Microsoft Excel spreadsheets of the report details to distribute among members of your project team.

Figure 3

In addition, the Upgrade Advisor lets you drill down into the report details, opening a Microsoft Help file that explains how to address specific issues and workarounds. After you view the details of a specific issue, you can browse to other rules included in the Help file and see additional areas the tool evaluates during its analysis.

A pre-release version of the SQL Server 2005 Upgrade Advisor will soon be available. This version is intended to perform preliminary upgrade analysis and to provide valuable feedback to the Upgrade Advisor product team. In this release, the wizard and report viewer are fully functional, but the tool will process only a limited set of upgrade rules. Because the analysis reports will have limited information, Microsoft has included a zip file with some sample reports to review. To use these sample reports, unpack them into your Upgrade Advisor Reports directory.

Note that it’s important to review the included readme file before installing the Upgrade Advisor; the file contains crucial information about the required prerequisite software and a description of the tool’s included rules, known issues, and so on. (You can address questions and general feedback to the Upgrade Advisor team at sqluafb@microsoft.com.)

The Upgrade Process

Before we look at the upgrade considerations for each major SQL Server 2005 component, let’s review a general road map that can help your upgrade team from planning to production. You can break the upgrade process into four phases: planning and research, testing and process validation, the production upgrade, and post-upgrade considerations.

Planning and Research

The upgrade process begins now. Your developers, DBAs, and application architects have plenty of motivation to start the educational and review process. Their training, experience, and research will drive much of the planning process. Because they intimately know your applications’ profiles, they’ll be able to provide valuable insight into the upgrade details.

The planning phase should move from identifying the databases targeted for the upgrade to determining the changes and processes the upgrade will require. The Upgrade Advisor will help your team determine where to focus its efforts and what to expect. A major decision in the preliminary work is to decide whether to perform an in-place upgrade or a side-by-side migration. You’ll base this decision on a combination of factors, including the platform upgrade path available, enhancements you want to implement during the upgrade, your application architecture, and hardware requirements.

Generally, you should conduct the following planning activities:

  • Learn about SQL Server 2005 upgrade tools. Understand the platform’s highlights, drill into the functionality, and test the upgrade and migration tools.

  • Assess your application features. Evaluate and determine which applications, servers, and databases will benefit most from the upgrade.

  • Select your upgrade path. Use the Upgrade Advisor to help you decide which upgrade path, in-place upgrade or side-by-side migration, will work best for your environment.

  • Identify the prerequisites for the upgrade process. Work with your team to research compatibility and functionality changes you’ll need to make to ensure a successful upgrade and to take advantage of the release’s new and enhanced features. The Upgrade Advisor will provide valuable help.

  • Set specific planning and research milestones. Determine your upgrade path and steps, set up an initial test plan, and make sure you have a risk mitigation and recovery plan in place.

Testing and Process Validation

Nothing can replace testing. Even if you plan to upgrade only the SQL Server database engine without changing your application, testing will help identify any backward-compatibility problems and behavioral changes from previous SQL Server releases that the Upgrade Advisor didn't detect. Furthermore, testing will help validate data and organize the upgrade process. This phase entails establishing a test environment and composing validation scripts and application functions to confirm a successful upgrade.

Your final plan should include a backup of your SQL Server 2000 or 7.0 databases and a tested recovery strategy. Also be sure to identify all application references (such as connection strings, package references, and reports) to the upgraded SQL Server components. Here is where an in-place upgrade has advantages: When you upgrade an earlier SQL Server release in-place through the install upgrade process, all existing application connections remain the same because the server and server instance do not change.

Consider these tasks in the testing and validation phase:

  • Prepare your test environment. Side-by-side migrations require a separate test SQL Server 2005 installation. In-place upgrades require a test machine running SQL Server 2000 or 7.0 and target database copies. Hardware comparable to your production setup will allow production volume testing.

  • Set a pre-upgrade baseline. This baseline will help you evaluate your system post-upgrade and determine any behavioral changes, letting you simulate a typical workload after your upgrade. The baseline will also help you confirm functionality and document performance improvements or changes. To set up the baseline, you can use familiar tools such as SQL Server Profiler, application load testing tools, Performance Monitor counters, and Showplan statistics.

  • Develop a test plan. Set up a generalized testing script or test procedures for the following areas: data validation, data processing, stress and workload, client/server performance, and application functionality.

  • Develop a recovery plan. Develop upgrade rollback procedures in case of an upgrade interruption. The recovery plan should include running a DBCC consistency check on the pre-upgrade databases before backup as well as a full restore of the database to validate the backup reliability. After the upgrade, you should also perform a consistency check and a backup with validation. Make sure you test your rollback procedures.

  • Create application-modification procedures. Your test environment should include the full application tier so that you can confirm application changes work as expected. These application-modification procedures should include a catalog of effected users. Such procedures also allow complete documentation of your application changes so that they can be applied successfully during the production cutover.

  • Perform an upgrade test run. A final test run of the upgrade will confirm that the process and procedures work as you expect. You can use the Upgrade Advisor in this step. Run the tool after you apply your pre-upgrade changes to validate that you’ve addressed all the problem areas the tool identified earlier.

The Production Upgrade

The Upgrade Advisor and Setup wizard should help you confidently through the planning and testing steps, positioning you for a successful production upgrade. You can use some of the testing steps you developed for pre-upgrade use (such as record counts and validation scripts) in validating the upgrade upon completion. You'll generally follow the subsequent steps for your production upgrade, depending on the SQL Server component you’re upgrading:

  1. Back up your systems (applications and databases). Perform a consistency check if applicable, back up database and related systems, then validate the backup.

  2. Perform pre-upgrade tasks. Notify your users, then disable user interface components, pausing all data processing, data entry, and data changes. Make the necessary pre-upgrade changes you identified in testing. Re-execute the Upgrade Advisor to validate the pre-upgrade state, and perform an optional secondary backup of your systems before the upgrade.

  3. Perform primary SQL Server back-end platform upgrade tasks. Run SQL Server 2005 for side-by-side migration. Install the .NET Framework and SQL Native Client. In the Setup wizard, specify the same instance as your legacy installation. Then, specify the same components as your legacy instance (e.g., Database Services, Analysis Services, Reporting Services). Once setup is complete, perform tasks for special upgrade considerations (e.g., repopulation of full-text indexes, special handling of clusters and log shipping). Next, make any post-upgrade platform changes, such as scripts or tasks required to support the back-end functionality on the new SQL Server 2005 platform. And last, run platform data and functionality validation testing scripts to confirm the success of your SQL Server 2005 upgrade.

  4. Make primary application changes. Make application functionality changes to support new back-end structures, and make any required database reference changes in application connection strings and other connection references. Test application functionality, including data processing, front-end and report usage, and other application components based on the test procedures you created in the planning phase.

  5. Perform post-upgrade steps. For the database engine, the upgrade automatically sets the compatibility mode to 8.0; but you might want to set the compatibility mode to 9.0 to take advantage of the release's new features. For side-by-side migrations, stop the former platform services (or set the database to read-only) to prevent unknown data changes. For the relational data, run DBCC consistency checks to validate the data. Back up SQL Server 2005 structures and data with backup validation, and back up application systems and files. Then, re-enable processing and the application user interface, notifying your users that the upgrade is complete.

Post-Upgrade Considerations

Following your successful upgrade, you need to address just a few areas to finalize the process and prepare for short-term changes that will let you leverage the new SQL Server 2005 features and functionality. First, you should perform some DBA tasks involving high availability, disaster-recovery planning, and recurring maintenance. These tasks include re-evaluating weekly and monthly procedures for the new platform and testing disaster recovery. If you perform a side-by-side installation, certain SQL Server 2005 features are switched off by default to reduce the surface area. You can switch on the features by using sp_configure or the Surface Area Configuration (SAC) utility.

Another valuable phase in the post-migration process is identifying new SQL Server 2005 features that your applications can leverage by asking the following questions:

  • Will table partitioning give your database expanded performance and table management?

  • Will the XML features solidify your .NET application architecture, or will the new database mirroring feature give you the high availability that your applications demand?

  • For Analysis Services, can you use the many-to-many (M:N) relationships that your BI requirements specify, or will your users require the Reporting Services ad hoc query tool, Report Builder?

  • Are your data processing needs strapped by bottlenecks and volume, and do they need the performance benefits that SSIS provides?

  • Would you like to integrate notifications into your line of business applications and leverage the ability of Notification Services to natively communicate with phones, pagers, PDAs, and email?

  • Does your development team need a better way to develop, test, and deploy applications by using the new Business Intelligence Studio and its ability to integrate with source control?

These features are only a few of the compelling new functions that SQL Server 2005 offers for increasing your database systems’ productivity, reliability, and capability.

Component Upgrade Considerations

Given the breadth of the SQL Server 2005 platform, writing an upgrade guide that applies across the board is no easy task. So, let’s take a closer look at the upgrade considerations for the major SQL Server 2005 components. (For a comprehensive and detailed discussion of upgrade considerations, see SQL Server 2005 Books Online and the white papers, Webcasts, and other resources listed in “SQL Server 2005 Resources,”.

Upgrading to the SQL Server 2005 Database Engine

The database engine upgrade is the easiest upgrade and will result in immediate return on investment in the areas of management, performance, and high availability. Again, the two main options for the database engine upgrade are side-by-side migration (in which you install the SQL Server 2005 engine as a secondary instance on the same server as your SQL Server 2000 or 7.0 server or on a completely separate server) and an in-place upgrade (in which you upgrade an instance of SQL Server 2000 or 7.0 through the install process and databases and other objects are upgraded “in place”).

With a side-by-side migration, the most common upgrade path is a simple database detach and re-attach on the SQL Server 2005 instance or a database backup and restore from the older version to the new version. If you keep an up-to-date version of your metadata scripts, you can also create the objects on the SQL Server 2005 server and use BCP to export and import your data. The other option is an in-place upgrade, in which you upgrade and adapt the databases, settings, and extended features to the SQL Server 2005 engine during the install process. When you run the setup process on a server that has a SQL Server 2000 or 7.0 instance, you’ll see an option to upgrade the selected instance to SQL Server 2005.

Note that for the database engine upgrade, all your existing Microsoft Data Access Components (MDAC) and ADO.NET applications will continue to function as when they were running against SQL Server 2000 or 7.0. In fact, SQL Server 2005 doesn't come with a newer release of MDAC. But new to the platform is the SQL Native Client, combining an updated SQL ODBC driver and SQL OLEDB provider with network libraries in a single DLL. The SQL Native Client lets you leverage SQL Server 2005's new client-access features, such as Multiple Active Result Sets (MARS), the XML data type, and user-defined types (UDTs). SQL Server comes with tight integration with the .NET Framework 2.0, which includes the latest ADO.NET version.

The in-place server upgrade will be the easiest. Although this approach requires a more thorough fallback plan and testing, you reap the rewards through seamless connectivity. By performing an in-place upgrade, logins and users remain in-sync, database connections remain the same for applications, and SQL Agent jobs and other functionality is concurrently upgraded during the installation. Note that several features, such as log shipping, replication, and cluster environments, have special upgrade considerations.

As noted earlier, for the database engine, the upgrade sets the compatibility mode to 8.0. You might benefit from leaving this setting at 8.0 under certain circumstances, such as for T-SQL references that are no longer supported in SQL Server 2005. I’ve run across some query hints that require rewriting, for example, as well as some legacy ANSI join syntax that’s being phased out (the *= left outer join syntax in particular). The analysis phase of your upgrade will uncover situations where using a lower compatibility setting might apply. However, I recommend that you fix any syntax that requires a lower compatibility level than 9.0 (SQL Server 2005) during the upgrade process. By reworking the syntax, your developers will have immediate access to all the new programming enhancements and features in the release. One quick trick to isolating these type issues and other syntax that might cause upgrade trouble is to script out the objects and procedures from the earlier platform version and attempt to run the scripts within SQL Server 2005. A simple attach or restore might suppress these issues. Also remember that some SQL logic can be embedded in your application. For data validation, running DBCC checkdb on your attached or restored database will confirm the integrity of the migrated data.

One caution: You probably have developers who are adept at leveraging system objects to make their lives easier. Microsoft has always told us to use Information_Schema views to get various metadata instead of querying the system tables directly because Microsoft can’t guarantee that the underlying object structure will persist in new platforms. With the new release, Microsoft has changed SQL Server’s underlying object structure. Also note that SQL Server 2005 catalog views and Dynamic Management Views (DMVs) have restricted permissions. PUBLIC users no longer have permissions to view catalog views, and users with GUEST/PUBLIC permissions can't select from DMVs.

Migrating to SQL Server 2005 Integration Services

There’s a reason that Microsoft didn’t use the name of Integration Services’ predecessor, Data Transformation Services (DTS), for its new SQL Server 2005 extraction, transformation, and loading (ETL) component. SQL Server Integration Services (SSIS) was a complete code rewrite—Microsoft didn’t bring one line of code over from DTS. With industry demands for faster performance and hardware consolidation to handle ever-increasing data complexity and volume, DTS wasn’t positioned as the long-term solution. So while DTS and SSIS are both ETL tools, architecturally they diverge greatly. Because of this, migration will require some redesign and solution changes so that you can leverage the compelling new SSIS features.

Moving from DTS to SSIS is a migration, involving wizard-driven output along with some manual redesign to complete the process. Some DTS tasks have a straightforward upgrade path to SSIS and are accommodated by the wizard. You might be able to use the wizard to upgrade other tasks depending on their use and design, but some tasks might be more difficult to upgrade or not upgradeable. Here’s what you can expect in upgrading your DTS packages:

  • Simple data pumps. Data pumps created by the DTS Wizard or that are simple copy column transformations will be upgradeable most of the time.

  • Workflow packages. If your packages are mainly workflow based, with tasks such as the Execute SQL task, they will have a fairly straightforward upgrade path and should also be handled by the wizard in most cases.

  • Data pumps with transformations. If your developers used the DTS Designer to build packages that use data pumps with transformations, the wizard will encapsulate the old data pump task. The newly migrated package will invoke the old DTS object model at runtime. Before you upgrade your SQL Server 2005 installation to the next version of SQL Server, you'll have to replace this encapsulated functionality with the new SSIS constructs. Also in this category are Data Driven Query Tasks and Parallel Data Pump Tasks.

  • Self-modifying packages. You’ll need to redesign DTS packages that leverage the DTS API to manipulate DTS objects and properties. For example, a common scenario is a looping construct in DTS, designed with scripts that use the API to modify wait states on other package steps. You can redesign this construct by using the built-in For Loop and For-Each Loop Containers in SSIS.

  • Scripts tasks. A migrated ActiveX Script Task will run unless it tries to access the DTS runtime package objects. Variable access should use the new SSIS variables without redesign. But in general, you should still reevaluate script tasks against the new SSIS functionality and possibly rewrite them using new tasks.

  • OLAP processing tasks. As with Data Transformation Tasks, the wizard will encapsulate these tasks, and they will run in SSIS. After the target server has been upgraded, consider replacing encapsulated functionality with the new SSIS functionality.

  • Custom tasks. The wizard will encapsulate these custom tasks, and in many cases, they will run in SSIS. However, the best approach is to make new SSIS custom tasks or transformations.

  • Dynamic property tasks. You’ll need to redesign these tasks in SSIS, using Expressions and Configurations to replace the Dynamic Property functionality.

Note that you'll have time to incrementally migrate packages over to SSIS. When you install SQL Server 2005, you have the option to install the runtime files required for DTS packages to execute on SQL Server 2005—without SQL Server 2000 needing to be installed. This makes the side-by-side migration a compelling story, especially in an environment where the DTS packages contain many tasks that require manual migration. SSIS also contains an Execute DTS package object when the runtime files or SQL Server 2000 has been installed on the SSIS server. A side-by-side implementation of SQL Server 2005 SSIS and SQL Server 2000 DTS will give you the greatest flexibility as you approach package migration.

Upgrading Analysis Services

Dimensions, partitions, storage modes, aggregates, and measures—the strengths of Analysis Services 2000—have been preserved in the new release. However, SQL Server 2005 also brings many notable enhancements. The Unified Dimensional Model (UDM), for example, now goes beyond traditional OLAP sources to allow expanded relational and aggregate data in a unified view. Dimensions are another area with valuable changes. A shift from a hierarchy-based model to an attribute-based model, with related optimizations on the storage and aggregation side, allows Analysis Services 2005 to fully scale for enterprise performance and volume.

From an upgrade perspective, Microsoft provides direct in-place upgrade from Analysis Services 2000 to Analysis Services 2005—preserving cubes, partitions, dimension hierarchies, measures, calculations, and sets. Because Analysis Services objects are built on top of a Data Source View (DSV) referencing database engines, it’s important to create the DSV on the base tables that the Analysis Services 2000 objects are built on rather than on views referencing underlying tables. The Migration Wizard does a first-rate job of generating DSVs that are complete with relationships and attributes from source tables. This will allow developers to add attributes to the cube even though they were not present in Analysis Services 2000.

Note that the Migration Wizard won’t optimize the Analysis Services objects; it simply moves the objects in place to the new Analysis Services server. The goal of the wizard is to migrate the cube structures and architecture objects so that client applications relying on the Analysis Services 2000 structures won’t break after you’ve migrated the cube to Analysis Services 2005. Thus, the migrated cube design might not take advantage of SQL Server 2005 enhancements (you can take advantage of those features later). However, your cubes will have the immediate performance and scalability benefits of the new Analysis Services architecture. When the Migration Wizard finishes its processes, you can then reprocess your cube and test your data and reports.

For Analysis Services 2005, the biggest upgrade considerations revolve around the client access methods and structure impact to reports. Analysis Services 2005 takes advantage of the new Web service protocol for OLAP, XML for Analysis (XML/A), that Microsoft helped write. (Support for XML/A first came to Analysis Services 2000 as a Web release, letting an Analysis Services 2000 server listen and respond to XML/A requests.) With native support for XML/A, you need to update existing client components of OLEDB for OLAP (Pivot Table Services—PTS) to access SQL Server 2005. That means users will need the latest version of PTS that comes with SQL Server 2005. The new driver will be installed side-by-side with the earlier PTS version, letting users access both SQL Server 2005 and SQL Server 2000 Analysis Services.

The second client-access consideration is the OLAP structure and related MDX compatibility after the upgrade. MDX isn’t gracious to members and structures that have changed. Although the Upgrade Wizard does a good job at recreating the OLAP structure, with the dimension architecture change from hierarchy based to attribute based, you might find small anomalies, structural and data, that appear after the upgrade. Therefore, report and data testing is more critical here than on the database engine side. You might need to recreate some reports and underlying MDX for the structures in Analysis Services 2005.

Upgrading Reporting Services

Because Microsoft released Reporting Services 2000 for the first time early in 2004, you won’t see major architectural changes in the Reporting Services 2005 platform. However, you will see several new features such as multi-select parameters, built-in MDX support, and dynamic report generation. Microsoft provides a direct, in-place upgrade path for moving from Reporting Services 2000 to Reporting Services 2005. Furthermore, Reporting Services 2005 will run RDL report definitions created in Reporting Services 2000 without requiring you to upgrade the definition. However, when a developer opens a report in the BI Development Studio, the developer will be prompted to convert the RDL to the new Reporting Services 2005 standards.

Ensuring Success

Managing the upgrade to SQL Server 2005 will require plenty of planning and testing. But with appropriate forethought and preparation—and use of the SQL Server 2005 Upgrade Advisor and Setup wizard—you’ll be able to avoid surprises and identify the areas where you need to concentrate your efforts. With a smooth upgrade under your belt, you’ll be ready to fully leverage the power and functionality that SQL Server 2005 provides.

Operating System and Hardware Upgrade Considerations

The basic operating system (OS) and hardware requirements for SQL Server 2005 are much the same as they are for SQL Server 2000. SQL Server 2005 runs on Microsoft Windows Server 2003, Windows XP, and Windows 2000 Server, with a recommended hardware configuration of a 1 gigahertz (GHz) processor and 1 gigabyte (GB) or more of RAM. Table 2 summarizes hardware requirements for each SQL Server 2005 edition (except for the Developer and Evaluation editions). Also note that you need Microsoft IIS 5.0 or higher to run SQL Server 2005 Reporting Services.

SQL Server Edition

Max. Procs

RAM Requirements

32-bit Support

x64 Support

Itanium Support

Express

1

Minimum: 128 MB
Recommended: 512 MB or more
Maximum: 1 GB

Yes

As 32-bit application

No

Workgroup

2

Minimum: 512 MB
Recommended: 1 GB or more
Maximum: 3 GB

Yes

As 32-bit application

No

Standard

4

Minimum: 512 MB
Recommended: 1 GB or more
Maximum: 64 GB (32-bit) or 512 GB (64-bit)

Yes

Yes

Yes

Enterprise

32 (32-bit) or 128 (64-bit)

Minimum: 512 MB
Recommended: 1 GB or more
Maximum: 64 GB (32-bit) or 512 GB (64-bit)

Yes

Yes

Yes

Table 2: Hardware requirements for SQL Server 2005 editions

Although SQL Server 2005 will run on the same hardware configurations that support SQL Server 2000, advances in both hardware and OS technology give you new processing capabilities at lower prices than ever before, making it tougher to decide on the correct hardware for SQL Server 2005. New processor advancements from AMD and Intel, with their support of the x64 platform, along with Microsoft’s new native 64-bit Windows Server 2003 x64 Edition makes the 64-bit platform an important consideration for both new SQL Server 2005 implementations as well as upgrades from existing SQL Server systems. The key to determining the correct platform is matching your expected workload and budget to the available options.

The 64-bit Intel Itanium Platform

For workloads that need the highest levels of scalability, Intel Itanium 2-based systems are the clear platform choice. The Itanium 2 holds all the top TPC-C benchmark scores for Windows Server. Itanium 2 systems support a much higher level of addressable memory than the x64-based platform and provide greater SMP capabilities. Although Itanium 2-based systems have the highest cost of acquisition, they also offer the most scale-up capabilities. This level of system typically provides redundant hardware for fault tolerance and hardware-based system partitioning.

The x64 Platform

Clearly, not every organization needs the highest levels of scalability. Far more systems fall into the 2-way to 4-way server zone, which is exactly the segment today’s x64 systems address. A primary reason for moving to the x64 platform might be if your current workload is memory constrained and you’ve reached the 32-bit Windows 2GB barrier. Although you could move to Address Windowing Extensions (AWE), which lets you address more than 3GB of memory, AWE uses Non Paged Memory and doesn’t deliver the same level of performance as using system memory on an x64 system. Other workloads that can benefit from the 64-bit capabilities of the x64 platform include business intelligence (BI) solutions and server consolidation scenarios.

The move from 32-bit SQL Server to 64-bit SQL Server is seamless. Both editions of SQL Server use the same on-disk structures; you simply need to detach the databases for the 32-bit system, then attach them to the 64-bit system. You don’t need to change any client applications.

The 32-bit Platform

The 32-bit platform remains the price-performance choice. If your current SQL Server 2000 system has available headroom, you can leverage the investment your business has made in its 32-bit servers by continuing to run SQL Server 2005 on them. Although 32-bit systems might not have the same level of scalability as 64-bit platforms, the raw performance of 32-bit systems has increased and will continue to improve with enhancements in raw CPU speed and the availability of new dual-core systems in 2005.

Additional SQL Server 2005 Resources

Microsoft SQL Server Home Page
This comprehensive window into everything SQL Server contains links to Webcasts, technical documents, and customer implementation studies that can help you get ready to upgrade to SQL Server 2005. And watch for information about the new SQL Server 2005 Upgrade Advisor download. See https://www.microsoft.com/sql.

SQL Server TechCenter on TechNet
The best resource for SQL Server database administrators is the SQL Server TechCenter, which focuses on technical information for DBAs and IT Professionals. See https://www.microsoft.com/technet/prodtechnol/sql/default.mspx.

SQL Server Developer Center on MSDN
An excellent resource for SQL Server database developers is the SQL Server Developer Center, which focuses on data-centered development with SQL Server and features interviews with prominent SQL Server team members. See https://msdn2.microsoft.com/sqlserver/default.aspx.

SQL Server 2005 Edition Feature Comparison
One of the most important SQL Server 2005 references is the feature comparison chart for the different editions of SQL Server 2005. SQL Server 2005 has new Express and Workgroup editions; plus you’ll want to see which editions of the product support various new features. See https://www.microsoft.com/sql/prodinfo/features/features-at-a-glance.mspx.

SQL Server 2005 Express
You can get first-hand experience using SQL Server 2005 in your own environment by downloading SQL Server 2005 Express for free. In addition to being a great developer’s database, SQL Server Express includes many new SQL Server 2005 features, such as CLR integration, new T-SQL commands, and the new XML and varbinary(max) data types. See https://msdn2.microsoft.com/sqlserver/aa336346.aspx.

Virtual Hands-On Labs
You can get first-hand experience using some of the new features in SQL Server 2005 by going through the 11 different SQL Server 2005 Hands-On Labs. These labs cover topics such as SQL-CLR, T-SQL, and Reporting Services. This site also contains links to technical papers and blogs. See https://msdn2.microsoft.com/sqlserver/bb895904.aspx.

Webcasts
Get ready for SQL Server 2005 with a 10-part TechNet educational Webcast series that covers everything from SQL Server 2005 tools, security enhancements, and high availability features to replication and scalability. See https://www.microsoft.com/events/series/technetsqlserver2005.mspx.

SQL Server Integration Services
SQL Server 2005 introduces Integration Services (SSIS), the replacement for Data Transformation Services (DTS). Learning about SSIS will be a top priority for administrators, and this valuable site includes links to blogs from business intelligence (BI) developers and details some SSIS best practices learned from real-world projects. See https://msdn2.microsoft.com/library/bb469733.aspx.

SQLJunkies
If you want to learn more about the development features of SQL Server 2005, check out the blogs at SQLJunkies.com. In addition to technical articles, the SQLJunkies site has an extensive list of blogs, including some from Microsoft SQL Server 2005 developers and program managers. See http://www.sqljunkies.com.

SQL Server Magazine
SQL Server Magazine’s Web site, SQLMag.com, has an extensive collection of technical articles and columns by renowned SQL Server authors that can help you learn the ins and outs of SQL Server 2005. Itzik Ben-Gan provides a guide to the release’s new T-SQL features; Microsoft Senior Product Manager Matt Nunn writes a Preparing for SQL Server 2005 column; and other industry experts step you through developing CLR database objects, getting started with SSIS, and more. See http://www.sqlmag.com.   

About the Authors

Douglas McDowell (douglas@solidqualitylearning.com) is director of operations for business intelligence at Solid Quality Learning. He is a mentor, solution architect, project manager, and a founder of Atlanta.mdf, an Atlanta SQL Server users’ group. He is an MCSE, an MCDBA, an MCT, and winner of Microsoft’s Worldwide Business Intelligence Solution of the Year.

Erik Veerman (erik@solidqualitylearning.com) is an associate mentor for Solid Quality Learning and has designed dozens of SQL Server-based business intelligence solutions across a broad business spectrum. As an expert in OLAP design, ETL processing, and dimensional modeling, Erik is a frequent presenter for his local PASS chapter and speaks at the national PASS and SQL Server Magazine Connections conferences.

Michael Otey (mikeo@teca.com) is senior technical editor for SQL Server Magazine and president of TECA, a software-development and consulting company in Portland, Oregon. A frequent industry speaker, Mike is the author of Microsoft SQL Server 2005 New Features (Osborne/McGraw-Hill).