Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 21, 2005

Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005 - Page 2

By William Pearson

Hands-On Procedure

Let's get some hands-on practice migrating an Analysis Services 2000 database. We noted earlier that the Migration Wizard is a convenient tool for this purpose, particularly in light of the fact that the Wizard leaves the original Analysis Services 2000 databases intact as Analysis Services 2000 databases. The Migration Wizard actually works with a copy of the original databases in recreating them on the desired instance of Analysis Services 2005.

The Migration Wizard allows us to select the Analysis Services 2000 databases that we wish to migrate, and then provides us with a couple of options for getting them to Analysis Services 2005: We can move the databases (actually copies of the databases that are created for this purpose, as we mentioned earlier) directly to Analysis Services 2005. A second option exists to have the Wizard generate an XMLA script for execution at a later time that will accomplish the same ultimate end.

We can start the Migration Wizard in several ways. We will work with the Wizard within the SQL Server Management Studio, taking the following steps:


Preparation: Access SQL Server Management Studio

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.

Click for larger image

Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name / instance, if appropriate) into the Server name selector.

6.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears, with the appropriate input for our local environments, similar to that depicted in Illustration 2.

Illustration 2: The Connect to Server Dialog, with Representative Settings

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  Within the Object Explorer (the leftmost pane of the Studio, by default) right-click the server in which we are working,

9.  Select Migrate Database... from the context menu that appears, as shown in Illustration 3.

Illustration 3: Right-click the Server – Select Migrate Database ...

The Analysis Services Migration Wizard – Welcome page appears, as depicted in Illustration 4.

Illustration 4: Analysis Services Migration Wizard – Welcome Page

10.  Click Next.

The Specify Source and Destination page appears.

11.  Type the name of the Analysis Services 2000 instance into the SQL Server Analysis Services source server box.

12.  Click the radio button to the left of the word "Server," which appears just underneath SQL Server Analysis Services destination server.

13.  Type the name of the desired Analysis Services 2005 instance into the Server box, which is now activated.

The Specify Source and Destination page appears, with my local settings, as shown in Illustration 5.

Illustration 5: The Specify Source and Destination Page with My Local Settings

14.  Click Next.

The Select Databases to Migrate page appears, after a scan of the specified server, displaying all Analysis Services databases on the server. The selection checkboxes for all servers are selected by default.

15.  Click the checkbox on the column heading labeled Source Database, unchecking it, and thereby de-selecting all databases.

16.  Click the checkbox to the immediate left of the FoodMart 2000 database, as depicted in Illustration 6.

Illustration 6: Partial View of the Select Databases to Migrate Page - FoodMart 2000 Selected

17.  Click Next.

The Validating Databases page appears, as validation of the metadata begins. We see the cube structure appear in a tree as this occurs. Messages indicating the merging of some dimensions are likely, if our cube structures are similar to those I have in my local FoodMart 2000 database. The resulting Validating Databases page, once validation is complete, resembles that shown in Illustration 7.

Illustration 7: The Validating Databases Page after Metadata Validation for FoodMart 2000

We note, too, the appearance of the following text in the scrollable message box at the bottom of the page:

Validation is complete. Databases validated: 1 out of 1.The structure of the migrated database may differ from that of the source database because of changes in the data model of SQL Server 2005 Analysis Services. Additionally, drillthrough settings, linked objects, and remote partitions will not be migrated. For more information about migrating from SQL Server 2000 Analysis Services, reference the product documentation.

It is important to keep in mind the Analysis Services 2000 features that are not migrated into 2005 when planning the migration of cubes. In most such cases, new features compensate for those that disappear in Analysis Services 2005. Moreover, there are many other new features that had no Analysis Services 2000 counterparts, too, that we might want to consider implementing after migration of the core databases and their cubes, dimensions, measures and other objects.

18.  Click Next.

Migration begins, with the appearance of the Migrating Databases page of the Migration Wizard that appears next. We receive a confirmation message in short order, alerting us to the fact that migration has occurred, as depicted in Illustration 8.

Illustration 8: We Receive Confirmation that Migration is Complete ...

19.  Click Next.

The Completing the Wizard page appears. We see a representation of the metadata of the new database in the tree that is presented, as shown in Illustration 9.

Illustration 9: The Completing the Wizard Page of the Migration Wizard

20.  Click Finish.

The Migration Wizard closes, and we are returned to the SQL Server Management Studio, from whence we began. We can now verify the migration of the Analysis Services 2000 database, by reviewing it within the Management Studio or within the Business Intelligence Development Studio, as we shall see in the section that follows.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM