Click Next, and the Select Databases to Migrate screen appears; this screen is fairly self explanatory. Make your selections and then click Next. The Validating Databases screen appears. At this point, the wizard performs the migration and reports on its progress, noting any issues along the way. When the wizard is done, click Next, and the Completing the Wizard screen
According to Microsoft, the Analysis Services Migration Wizard is unable to migrate three OLAP constructs: linked cubes, drill-through options, and remote partitions. You need to manually re-create these constructs.
When your migration is complete, you need to remember to reprocess your cubes; otherwise, you are unable to query the new database. In addition, the migrated database doesn’t yet exploit the features of SSAS’s Unified Dimensional Model (UDM) in your existing
Upgrading Reporting Services
SQL Server 2008
You can run the Upgrade Advisor tool on the Report Server computer to determine any issues that might prevent a successful upgrade. Known upgrade issues currently include the following:
Performing an In-Place Upgrade of Reporting Services
If you’ve run the Upgrade Advisor and it doesn’t report any issues that would prevent a successful upgrade (or you’ve addressed any issues it raises), you can perform an in-place upgrade of any instance of SQL Server 2000 Reporting Services SP2 or SQL Server 2005 Reporting Services.
Before upgrading Reporting Services, you should first back up the following:
Before running the upgrade, you first need to stop IIS and the Report Services Windows service on each machine on which you will be running the in-place upgrade. (For a Web farm [now known as a scale-out implementation] the upgrade must be run on every node.)
Then run the Installation Center and select your existing instance for upgrade at the appropriate screen. The Installation Center upgrades the instance in-place, including all its components and any published reports and snapshots. Upgrading Reporting Services also requires updates to your Report Server databases.
Because the Report Server database schema can change with each new release of Reporting Services, it is required that the database version match the version of the Report Server instance you are using. In most cases, a Report Server database can be upgraded automatically with no specific action on your part. The following list identifies all the conditions under which a Report Server database is upgraded:
The Reporting Services Configuration tool no longer provides a separate Upgrade button or upgrade script. Those features are obsolete in SQL Server 2008 due to the automatic upgrade feature of the Report Server service.
After the database schema is updated, you cannot roll back the upgrade to an earlier version. Always back up the Report Server database in case you need to re-create a previous installation. SQL Server 2008 introduces changes to the Report Definition Language (RDL), the report object model, and the rendering object model that affect reports created in earlier versions of the software. When you upgrade a Reporting Services installation from a prior version to a SQL Server 2008 Reporting Services installation, existing reports and snapshots that have been uploaded to a Report Server are automatically upgraded to the new schema the first time they are processed. If a report cannot be automatically upgraded, the report is processed using the backward-compatibility mode. Also, if you open an .rdl file in Report Designer that was created for the SQL Server 2000 or SQL Server 2005 namespace, Report Designer automatically upgrades the report to the current namespace. After you save the report, you cannot open it in earlier versions of Report Designer.
If you are unable to perform an in-place upgrade of your existing installation for any reason, your other option is to install a new instance of SQL Server 2008 Reporting Services and then migrate your Report Server database and configuration files to the new instance.
Migrating to Reporting Services 2008 The migration process for Reporting Services includes a combination of manual and automated steps. The following tasks are required to perform a Reporting Services migration:
When you are backing up the Report Server configuration files, the files to back up include
During the install of your new instance of Reporting Services, when you reach the Reporting Services screen, you need to be sure to select the Install but Do Not Configure option. After moving your Report Server databases, launch the new Reporting Services Configuration tool and select the Report Server database that you’ve moved from the previous installation to automatically upgrade it. Then restore your backed-up encryption key. Just as with an in-place upgrade, to upgrade the reports themselves, all you need to do is open them in the Report Designer, which automatically converts them to the new Report Definition Language format.
After you successfully migrate your Report Server to a SQL Server 2008 Reporting Services instance, you might want to perform the following steps to remove programs and files that are no longer necessary:
Upgrading SSIS Packages
When you upgrade an instance of SQL Server 2005 to SQL Server 2008, your existing SQL Server 2005 Integration Services packages are not automatically upgraded to the package format that SQL Server 2008 Integration Services uses. You have to manually upgrade your SQL Server 2005 packages. There are multiple methods to upgrade SQL Server 2005 packages. Some of the methods are only temporary. For others, the upgrade is permanent. The table below shows lists each of the upgrade methods and whether the upgrade is temporary or permanent.
At the next screen, you can specify a log file for the process. You click Next again and then click Finish to complete the migration. As with all the other wizards provided with SQL Server 2008, the Package Migration Wizard reports progress and any issues on a per-package basis, offering an exportable report at the end.
After migration is complete, the original DTS package is still available on the SQL Server 7 or 2000 instance, in unmodified form. You can import packages into SQL Server in SSMS by connecting to SSIS in the Object Explorer and then navigating to the Stored Packages node and then the MSDB node. If you selected a file system folder as the destination, right-click the File System node and then select Import Package to display the migrated packages.
Now that you’ve taken in a great deal of information to help your organization transition to SQL Server 2008, it’s time to put that knowledge to work by actively taking the plunge. If you need even more documentation, you can look to the many other chapters in this book and even more resources on the Web that can assist you. Of course, there’s an abundance of content on Microsoft’s website (after all, it’s in Microsoft’s interests that customers upgrade to SQL Server 2008), including webcasts, TechNet, and online learning courses available to MSDN subscribers. When your new environment is ready to go, you can move on to Chapter 10, “Client Installation and Configuration,” to learn how to get your clients up and running with your new installation of SQL Server 2008.
Migrating DTS Packages
SSIS is a complete rewrite of the DTS runtime, and this is why your DTS packages are not automatically migrated to SQL Server 2008 when running an in-place upgrade. You essentially have two options for how to handle your existing DTS packages:
Full DTS support in SQL Server 2008 consists of multiple components. The first component is the Client Tools Backward Compatibility option. During an installation or upgrade, on the Feature Selection page, select Integration Services and choose to install the Client Tools Backward Compatibility option. This option installs the Execute DTS 2000 Package task for SSIS.
The next component you need to install is DTS runtime support. To install runtime support for Data Transformation Services packages, go to the Microsoft Download Center and locate the Microsoft SQL Server 2008 Feature Pack page. From there, download the Microsoft SQL Server 2005 Backward Compatibility Components (this component has not been updated for SQL Server 2008). If you also want to use the SQL Server 2008 tools to open and view DTS packages, you have to download and install the design-time support as well. This support can also be found in the Microsoft Download Center on the Feature Pack for Microsoft SQL Server 2005 page.
After you install the DTS runtime support, your DTS packages can run as before. You can run your DTS packages one of the following ways:
If you also installed the design-time support, you are able to continue to edit and manage your DTS packages. You can manage your DTS packages from SQL Server Management Studio under the Data Transformation Services node, which is available in the Management/Legacy folder.
Here, you can open existing DTS packages stored on the file system or in the msdb database, or add additional packages to the server by clicking the Import button. Although DTS packages can be modified and renamed, you cannot create new DTS packages within SSMS. The DTS runtime support is intended to be used only on a temporary basis until you have the opportunity to migrate your DTS packages to SSIS. To migrate your DTS packages to SSIS, you can use the DTS Package Migration Wizard.
To run the DTS Package Migration Wizard, you first need to make sure that the SSIS service is in the running state. In SSMS, open the Object Explorer and navigate to the Legacy node, under Management. Then right-click the Data Transformation Services (DTS) node and select the Package Migration Wizard option to migrate one or more packages (those stored on a server or as files) to SSIS. The Package Migration Wizard is available only in the Developer, Standard, and Enterprise Editions of SQL Server 2008.
When you run the Package Migration Wizard, you first need to select the source and destination servers (the source must be a SQL Server 7 or 2000 instance, and the destination must be a 2008 instance with SSIS running) on the Choose Source Location and Choose Destination Location screens. Then click Next to reach the List Packages screen as seen the pic below, where you check the check boxes for the packages you want to bring over. The name for each imported package is listed in the Destination Package column, and you can click there to edit it.
The SQL Server 2008 Upgrade Matrix
No software upgrade section would be complete without an illustrative table showing the versions and editions of SQL Server for which the upgrade methods described thus far are supported. They are presented in the below picture.
As you see in
Upgrading Other SQL Server Components
Now that you’ve seen how to migrate databases, jobs, logins, custom error messages, and full-text catalogs, let’s discuss how you can migrate the rest of your SQL Server objects. First, let’s look at Analysis Services.
Upgrading Analysis Services
The following sections highlight some important considerations you should be aware of when upgrading Analysis Services.
Upgrading from SQL Server 2005 Analysis Services
You can upgrade an existing instance of SQL Server 2005 Analysis Services to SQL Server 2008 Analysis Services or
Users running in a 64-bit environment must upgrade Analysis Services before upgrading the SQL Server Database Engine. You can, of course, run setup more than once, so in this situation
Upgrading from SQL Server 2000 Analysis Services
Because of changes to the underlying architecture of Analysis Services between SQL Server 2000 and SQL Server 2008, you cannot perform an in-place upgrade. You have to migrate your SQL Server 2000 AS databases to SQL Server 2008. The first task is to install a new named instance of SQL Server 2008 Analysis Services (SSAS) by using the SQL Server 2008 Installation Center program. When this process is complete, you can use the Analysis Services Migration Wizard to import your SQL Server 2000 Analysis
This wizard re-creates your existing OLAP structures on the new instance, without altering the original source material. If you remove the prior instance of SQL Server 2000 Analysis Services after you have migrated its databases, you can use the Analysis Services Instance Rename tool to make the named instance of SQL Server 2008 Analysis Services the default instance on the server. To launch the Analysis Services Migration Wizard, open the Object Browser and connect to Analysis Services. Then navigate to the top-level Analysis Services node to find the wizard. You can also simply select Start, Run and
Click Next on the Welcome page, and the Specify Source and Destination screen appears (see picture below). You need to enter the name of your SQL Server 2000 Analysis Services server as the source. Then you have two options:
The SSIS Package Upgrade Wizard is the recommended approach for upgrading your SQL Server 2005 SSIS packages. Because you can configure the wizard to back up your original packages, you can continue to use the original packages if you experience upgrade difficulties. You can run the SSIS Package Upgrade Wizard from SQL Server Management Studio, from SQL Server Installation Center, or at the command prompt. To run the wizard from SQL Server Management Studio, connect to Integration Services, expand the Stored Packages node, right-click the File System or MSDB node, and then click Upgrade Packages. To run the wizard from SQL Server Installation Center, click Tools and then click Upgrade Integration Services packages. At the command prompt, run the SSISUpgrade.exe file from the C:\Program Files\Microsoft SQL Server\100\DTS\Binn folder.
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute offering SQL Server and T-SQL (SQL Server) Training, SQL Database Administration (SQL DBA) Training and Business Intelligence (MSBI / SQL BI) Training.
We have been working with numerous consultancies in USA & UK. We undertake training on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 versions. We make sure that all our sessions are very much interactive and well structured with doubts clarifications during and after each course.
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE