Upgrading Reporting Services. Reporting Services was originally released in January 2004 as a SQL Server 2000 add-on. The product is now natively integrated in SQL Server 2005 and introduces several enhancements, including the ability to specify multiple values for a parameter, enhanced printing support, interactive sorting in reporting, SharePoint integration, and programmability enhancements. Reporting Services 2005 also includes a new tool called Report Builder that lets business users create ad-hoc reports based on the report models that designers and developers create.
You can upgrade a default Reporting Services 2000 installation by using the SQL Server 2005 Setup, which takes care of upgrading published reports and snapshots. If you've customized the Reporting Services 2000 installation, you must install a new Reporting Services instance, migrate the instance data to the new installation, and deploy your reports on the new instance. Report definitions are upgraded when you open the reports in the designer.
SQL Server 2005 also includes a tool called Report Server Configuration Manager, which Figure 4 shows, that you can use to configure a local or remote report server instance. If you used the files-only installation option for your report server, you must use this tool to configure the server so that Reporting Services can use it. If you used the default configuration installation option, you can use the tool to verify or modify the settings that you specified during setup. This tool also lets you upgrade the report server database to the new format and restore the encryption key.
Before upgrading the Reporting Services installation, back up the report server database and configuration files. Also, use the rskeymgmt.exe utility to back up the symmetric key for the report server database.
Upgrading Tools and Utilities. Enterprise Manager, Query Analyzer, and the administration part of Analysis Manager are now integrated into SQL Server Management Studio. To add SQL Server 2000 Enterprise Manager registered servers into Management Studio, right-click the Database Engine node in the Registered Servers window, and select Previously Registered Servers. Note that database diagrams aren't upgraded during in-place upgrade or side-by-side migration. When you set up database diagramming in Management Studio and open the database diagrams, SQL Server 2005 automatically upgrades them.
Client Network Utility, Server Network Utility, and Service Manager tools are now integrated into SQL Server Configuration Manager. You'll use the Configuration Manager tool to enable or disable network protocols and manage aliases and services. In addition, SQL Server 2005 no longer ships DB-Library–based isql.exe. And Microsoft has deprecated ODBC-based osql.exe; you'll need to update your jobs and maintenance procedures to use the new OLE DB–based sqlcmd.exe. Microsoft has also deprecated MAPI-based SQL Mail and for side-by-side migration, it's disabled by default. If you use SQL Mail to send email messages, consider upgrading your messaging to SMTP-based Database Mail.
Additionally, SQL Server 2005 introduces a new .NET-based management API called SQL Server Management Objects (SMO) that supersedes SQL-DMO. In addition to providing maximum coverage of SQL Server 2005 features, the SMO API contains several other improvements, including a cached object model, delayed instantiation of objects for improved scalability and performance, enhanced scripting capabilities, and improved ease of use. If you have DMO-based scripts or applications, consider rewriting those to benefit from SMO.
Upgrading Other SQL Server Components. Unlike in previous releases, SQL Server 2005's Full-Text Search doesn't share the MSSearch service with other server products that use the MSSearch. Instead, one instance of the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service is installed for each SQL Server 2005 instance. After the upgrade, you must manually repopulate the catalogs. Note that in SQL Server 2005, Full-Text Search requires more disk space than earlier releases.
SQL Server 2005 Mobile Edition provides a command-line upgrade tool (upgrade .exe) in the %ProgramFiles%\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\[platform]\[processor] folder that you can use to upgrade SQL Server CE 2.0 databases to the SQL Server 2005 Mobile Edition.
Notification Services 2.0 instances aren't automatically upgraded to SQL Server 2005 Notification Services when you run Setup. After installing SQL Server 2005 and upgrading the database engine, you have to manually migrate the Notification Services 2.0 instance metadata by using Management Studio or the nscontrol command-prompt utility.
Post-Upgrade Checklist
A successful upgrade doesn't necessarily indicate the end of the upgrade process. Your upgrade plan must include the post-upgrade tasks you'll perform immediately after the upgrade and in the days and weeks following the upgrade. You also need to plan how you will take advantage of the power of SQL Server 2005 such features as T-SQL enhancements, Service Broker, snapshot isolation, and .NET integration. To be sure your post-upgrade plan is on the right track, you can use the following checklist.
- Run sp_updatestats against each upgraded database to update statistics information about the distribution of key values. This step ensures optimal query performance with upgraded databases. In addition to updating statistics, you should also run DBCC UPDATEUSAGE on all databases following upgrade. This corrects any page and row-count inaccuracies.
- Review the Upgrade Advisor report and perform the recommended post-upgrade tasks.
- For an in-place upgrade, verify the database compatibility level and set it to 90, if required. Several new features, such as Service Broker, require that the compatibility level be set to 90, but be aware that a 90 compatibility level might break some of your existing scripts; for instance, scripts that use *= LEFT OUTER JOIN syntax won't work at 90 compatibility level.
- Use the Surface Area Configuration tool or the sp_configure system stored procedure to determine whether features such as SQL Mail are disabled and to enable or disable such features.
- Verify high-availability and disaster-recovery planning tasks, SQL Server Agent jobs, and maintenance tasks. Reconfigure log shipping.
- Migrate DTS packages to SSIS to improve performance and use the new functionality benefits of SSIS.
- Build the Analysis Services cubes.
- Repopulate the full-text catalogs.
- If you've created any post-upgrade tests and scripts for validating and benchmarking the upgrade, run those tests.
- Plan for implementing new SQL Server 2005 features such as table partitioning, snapshot isolation, security enhancements, Service Broker, XML and .NET support, and Analysis Services enhancements. Upgrade your management and maintenance scripts to use the new catalog views and dynamic management objects.
Finally, you can bring the system online and monitor system activity to be sure the upgrade is working well.
Planning Pays Off
In addition to polishing many of the rough spots of earlier SQL Server versions and providing long-requested features, SQL Server 2005 introduces several enhancements in the areas of management, performance, scalability, high availability, programmability, and business intelligence (BI). If you're responsible for managing one or more solutions based on SQL Server 2000 or 7.0, the first step to reaping SQL Server 2005 benefits is to upgrade legacy SQL Server versions to SQL Server 2005. Although taking on a major upgrade might seem like a daunting task, Microsoft has invested a lot of time and resources in simplifying the SQL Server 2005 upgrade process. Tools such as Upgrade Advisor improve the overall upgrade experience, and careful planning and testing will help you identify and resolve any problems for a smooth upgrade.
End of Article
Prev. page
1
2
[3]
next page -->
