posted on Monday, June 06, 2005 10:18 AM
by
davidboschmans
Using the SQL Server 2005 Upgrade Advisor
My last three weeks have been completely filled with the SQL Server 2005 Early Adopter Program that Microsoft Belux is running. The program targets partners and customers who are already building applications and solutions using SQL Server 2005. The program started with some intensive training sessions on SQL Server's Data Platform, Business Intelligence and Development functionality followed by some compatibility labs and a day of training focusing on extending current applications or solutions.
Last week we completed the compatibility labs track. These labs offer the partners and customers the opportunity to test their existing SQL Server database applications for compatibility with both the 32-bit and 64-bit editions of SQL Server 2005 using the testing tools and methodologies developed by the SQL Server Release Services Team. Following steps were taken to test the SQL Server 2005 compatibility:
- Using a SQL Server playback - which silumates all application to database interaction - the participants could gain insight into the coding changes that may be required to make their application function properly on SQL Server 2005.
- By analyzing the success rate and performance of the participants playbacks executing against SQL Server 2000, and comparing those statistics with playback runs against both 32-bit and 64-bit editions of SQL Server 2005, the participants were able to determine whether there are any significant behavioral differences in the functionality and performance of their application when running against SQL Server 2005.
- For the first labs Michelle Alexander & Jennifer Perret, both members of the SQL Server Product Team, took the lead. Thanks to their deep knowledge and expertise of SQL Server the participants had the opportunity to impact the quality of SQL Server 2005 by exposing the SQL Server team to real-world application interaction with SQL Server 2005 that may not have been encountered before.
- By learning playback testing procedures, participants learned valuable methodologies for application testing that they may be able to implement in their own quality assurance environments back at the office.
One of the tools used to check the compatibility of the existing applications is the SQL Server 2005 Upgrade Advisor [UA]. As SQL Server 2005 has introduced new features and has changed existing features to help improve performance, security and manageability this may affect your existing applications. The UA helps you plan for upgrades from SQL Server 7.0 or SQL Server 2000. This new tool that will be part of SQL Server 2005 - included from the June CTP on - helps identifying areas where you need to pay special attention to. It gives you the ability to check following SQL components: database server, anaysis services, notification services, reporting services and data transformation services.
The UA analyzes the configuration of installed SQL Server components (built on a rules-based engine - cfr. FxCop), and then generates a report that identifies issues that you must address to ensure a successful upgrade. Through the report viewer you can access the excellent UA documentation which gives an overview on how to resolve the upgrade issues. The following categories of issues are documented:
- Analysis Services Upgrade Issues
- Data Transformation Services Upgrade Issues
- Database Engine Upgrade Issues
- Full-Text Search Upgrade Issues
- Notification Services Upgrade Issues
- Replication Upgrade Issues
- Reporting Services Upgrade Issues
- SQL Server Agent Upgrade Issues
During our labs the UA-tool really showed its value. Almost all customers detected (critical, severe, warning) issues that needed to be resolved before their app would run successfully on SQL 2005. But even without using UA for all types of SQL components it gives you great information on the component the issue is applicable. And it also provides you a description of the corrective action to take to resolve the issue. This way all SQL 2005 people - DBA's, BI-people and developers - can learn a lot on how to benefit the most from the new SQL Server 2005 features. Consider this as a great place to start learning the new SQL features. This UA documentation will be integrated later on in the SQL Server 2005 Books Online.
I learned a lot on the new features of SQL Server 2005 solving customers issues during the labs. If you want to learn more on the SQL Server 2005 Upgrade Advisor and you're attending TechEd 2005 US this week make sure to attend the session DBA312 - Upgrade and Migration: Planning Considerations for SQL Server 2005 on June 10, 9:00 AM - 10:15 AM. The session will be delivered by Michael Raheem and Michelle Alexander.
The new features of SQL Server 2005 are well worth the upgrade effort, but how do you upgrade your existing systems painlessly? This session drills into the necessary upgrade planning: preparing to upgrade, choosing the best upgrade strategy, and identifying incompatibility issues. Learn tips and techniques on how to reduce your upgrade down time and how to best utilize the upgrade tools. This session has live demos of the Upgrade Advisor tool and in-place upgrade features of SQL Server 2005.