Upgrade Master Data Services

There are three scenarios for upgrading to Microsoft SQL Server 2012. Choose the scenario that fits your situation.

Important

  • Back up your database before performing any upgrade.

  • The upgrade process recreates stored procedures and upgrades tables used by Master Data Services. Any customizations you have made to either of these components may be lost.

  • Model deployment packages can be used only in the edition of SQL Server they were created in. You cannot deploy model deployment packages created in SQL Server 2008 R2 to SQL Server 2012.

  • The Repair button is displayed during upgrade only after a database that was restored from a backup was selected. The Repair button does the following: enables the CLR on the SQL instance, enables the service broker and service broker queue, and enables other necessary services.

Upgrade from SQL Server 2008 R2 without Database Engine Upgrade

This scenario can be considered a side-by-side installation, because both SQL Server 2008 R2 and SQL Server 2012 are installed in parallel, on either the same computer or separate computers.

In this scenario you continue to use SQL Server 2008 R2 to host your MDS database. However, you must upgrade the schema of the MDS database, and then use the SQL Server 2012 web application to access the MDS database. The MDS database can no longer be accessed by the SQL Server 2008 R2 web application.

If you choose to install both versions on the same computer, you can do so because the files are installed in a different location.

  • In SQL Server 2012, by default, the files are installed at drive:\Program Files\Microsoft SQL Server\110\Master Data Services.

  • In SQL Server 2008 R2, the files are installed at drive:\Program Files\Microsoft SQL Server\Master Data Services.

To perform this task, complete the following steps.

  1. Install Master Data Services and any other features you want.

    1. Open the SQL Server 2012 Setup wizard.

    2. In the left pane, click Installation.

    3. In the right pane, click New SQL Server stand-alone installation or add features to an existing installation.

    4. On the Feature Selection page, select Master Data Services and any other features you want to install.

    5. Complete the wizard.

  2. When the installation is complete, upgrade the MDS database schema.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

      Important

      To upgrade the MDS database schema, you must be logged in as the Administrator Account that was specified when the MDS database was created. In the MDS database, in mdm.tblUser, this user has the ID value of 1. For information on changing this user, see Change the System Administrator Account (Master Data Services).

    2. In the left pane, click Database Configuration.

    3. In the right pane, click Select Database and specify the information for your SQL Server 2008 R2 database instance.

    4. If the Repair Database button is enabled, click it.

    5. Click Upgrade Database to start the Upgrade Database Wizard. For more information, see Upgrade Database Wizard (Master Data Services Configuration Manager).

  3. When the upgrade is complete, create a SQL Server 2012 web application.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

    2. In the left pane, click Web Configuration.

    3. In the right pane, from the Website list, select one of the following options:

      • Default Web Site, then click Create Application.

      • Create new site. A new web application is automatically created when the website is created.

  4. Now associate the web application with the upgraded MDS database.

    1. In the Associate Application with Database section, click Select.

    2. Select the MDS database.

    3. Click Apply.

Upgrade from SQL Server 2008 R2 with Database Engine Upgrade

In this scenario you will upgrade both the database engine and Master Data Services application from SQL Server 2008 R2 to SQL Server 2012.

To perform this task, complete the following steps.

  1. Open Control Panel > Programs and Features and uninstall Microsoft SQL Server 2008 R2 Master Data Services.

  2. Upgrade the database engine to SQL Server 2012.

    1. Open the SQL Server 2012 RC0 Setup wizard.

    2. In the left pane, click Installation.

    3. In the right pane, click Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2.

    4. Complete the wizard.

  3. When the upgrade is complete, add the Master Data Services feature.

    1. Open the SQL Server 2012 Setup wizard.

    2. In the left pane, click Installation.

    3. In the right pane, click New SQL Server stand-alone installation or add features to an existing installation.

    4. On the Installation Type page of the wizard, select the Add features to an existing instance option.

    5. On the Feature Selection page, under Shared Features, select Master Data Services.

    6. Complete the wizard.

  4. When the MDS installation is complete, upgrade the MDS database schema.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

      Important

      To upgrade the MDS database schema, you must be logged in as the Administrator Account that was specified when the MDS database was created. In the MDS database, in mdm.tblUser, this user has the ID value of 1. For information on changing this user, see Change the System Administrator Account (Master Data Services).

    2. In the left pane, click Database Configuration.

    3. In the right pane, click Select Database and specify the information for your database instance.

    4. If the Repair Database button is enabled, click it.

    5. Click Upgrade Database to start the Upgrade Database Wizard. For more information, see Upgrade Database Wizard (Master Data Services Configuration Manager).

    6. Click Apply.

  5. When the upgrade is complete, create a SQL Server 2012 web application.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

    2. In the left pane, click Web Configuration.

    3. In the right pane, from the Website list, select one of the following options:

      • Default Web Site, then click Create Application.

      • Create new site. A new web application is automatically created when the website is created.

  6. Now associate the web application with the upgraded MDS database.

    1. In the Associate Application with Database section, click Select.

    2. Select the MDS database.

    3. Click Apply.

Upgrade from SQL Server 2012 CTP3, RC0, or RC1

In this scenario you must upgrade the database engine, followed by the MDS database schema.

To perform this task, complete the following steps.

  1. Upgrade the database engine and install the latest MDS files.

    1. Open the SQL Server 2012 Setup wizard.

    2. In the left pane, click Installation.

    3. In the right pane, click Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2.

    4. Complete the wizard.

  2. When the database engine upgrade is complete, upgrade the MDS database schema.

    1. Open Master Data Services Configuration Manager.

      Important

      To upgrade the MDS database schema, you must be logged in as the Administrator Account that was specified when the MDS database was created. In the MDS database, in mdm.tbl.User, this user has the ID value of 1. For information on changing this user, see Change the System Administrator Account (Master Data Services).

    2. In the left pane, click Database Configuration.

    3. In the right pane, click Select Database and specify the information for your database instance.

    4. If the Repair Database button is enabled, click it.

    5. Click Upgrade Database to start the Upgrade Database Wizard. For more information, see Upgrade Database Wizard (Master Data Services Configuration Manager).

    6. When the upgrade is complete, close Master Data Services Configuration Manager.

  3. Open Internet Information Services (IIS) Manager and restart the MDS Application Pool.

Upgrade in Two-Computer Scenario

This scenario involves upgrading a system in which SQL Server is installed on two computers: one with SQL Server 2012, and the other with SQL Server 2008 R2 or SQL Server 2012 CTP3, RC0, or RC1.

If SQL Server 2008 R2 is installed, you continue to use SQL Server 2008 R2 to host your MDS database on one computer. However, you must upgrade the schema of the MDS database, and then use the SQL Server 2012 web application to access the MDS database. The MDS database can no longer be accessed by the SQL Server 2008 R2 web application.

  • In SQL Server 2012, by default, the files are installed at drive:\Program Files\Microsoft SQL Server\110\Master Data Services.

  • In SQL Server 2008 R2, the files are installed at drive:\Program Files\Microsoft SQL Server\Master Data Services.

To perform this task, complete the following steps.

  1. Install Master Data Services and any other features you want.

    1. Open the SQL Server 2012 Setup wizard.

    2. In the left pane, click Installation.

    3. In the right pane, click New SQL Server stand-alone installation or add features to an existing installation.

    4. On the Feature Selection page, select Master Data Services and any other features you want to install.

    5. Complete the wizard.

  2. When the installation is complete, upgrade the MDS database schema.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

      Important

      To upgrade the MDS database schema, you must be logged in as the Administrator Account that was specified when the MDS database was created. In the MDS database, in mdm.tblUser, this user has the ID value of 1. For information on changing this user, see Change the System Administrator Account (Master Data Services).

    2. In the left pane, click Database Configuration.

    3. In the right pane, click Select Database and specify the information for your SQL Server 2008 R2 database instance on the other computer, if SQL Server 2008 R2 is installed on the other computer.

    4. If the Repair Database button is enabled, click it.

    5. Click Upgrade Database to start the Upgrade Database Wizard. For more information, see Upgrade Database Wizard (Master Data Services Configuration Manager).

  3. When the upgrade is complete, create a SQL Server 2012 web application.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

    2. In the left pane, click Web Configuration.

    3. In the right pane, from the Website list, select one of the following options:

      • Default Web Site, then click Create Application.

      • Create new site. A new web application is automatically created when the website is created.

  4. Now associate the web application with the upgraded MDS database.

    1. In the Associate Application with Database section, click Select.

    2. Select the MDS database.

    3. Click Apply.

Upgrade with Restoring a Database from Backup

In this scenario, SQL Server 2008 R2 or SQL Server 2012 CTP3, RC0, or RC1 is installed on one computer or two computers. In this scenario, a database was backed up on a version before SQL Server 2012 RTM prior to upgrade, and the database will be restore.

  • In SQL Server 2012, by default, the files are installed at drive:\Program Files\Microsoft SQL Server\110\Master Data Services.

  • In SQL Server 2008 R2, the files are installed at drive:\Program Files\Microsoft SQL Server\Master Data Services.

To perform this task, complete the following steps.

  1. Install Master Data Services and any other features you want.

    1. Open the SQL Server 2012 Setup wizard.

    2. In the left pane, click Installation.

    3. In the right pane, click New SQL Server stand-alone installation or add features to an existing installation.

    4. On the Feature Selection page, select Master Data Services and any other features you want to install.

    5. Complete the wizard.

  2. Restore the database that was backed up.

  3. When the installation is complete, upgrade the MDS database schema.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

      Important

      To upgrade the MDS database schema, you must be logged in as the Administrator Account that was specified when the MDS database was created. In the MDS database, in mdm.tblUser, this user has the ID value of 1. For information on changing this user, see Change the System Administrator Account (Master Data Services).

    2. In the left pane, click Database Configuration.

    3. In the right pane, click Select Database and specify the information for your SQL Server 2008 R2 database instance.

    4. If the Repair Database button is enabled, click it.

    5. Click Upgrade Database to start the Upgrade Database Wizard. For more information, see Upgrade Database Wizard (Master Data Services Configuration Manager).

  4. When the upgrade is complete, create a SQL Server 2012 web application.

    1. Open the SQL Server 2012 version of Master Data Services Configuration Manager.

    2. In the left pane, click Web Configuration.

    3. In the right pane, from the Website list, select one of the following options:

      • Default Web Site, then click Create Application.

      • Create new site. A new web application is automatically created when the website is created.

  5. Now associate the web application with the upgraded MDS database.

    1. In the Associate Application with Database section, click Select.

    2. Select the MDS database.

    3. Click Apply.

Troubleshooting

Issue: When you open the SQL Server 2008 R2 Master Data Manager web application, a “client version is not compatible with the database version” error message is displayed.

Solution: This issue occurs when you try to access a SQL Server 2008 R2 Master Data Manager web application. Because the web application is associated with a SQL Server 2012 database, it no longer works. You should use the SQL Server 2012 web application instead.

This issue may also occur if you did not stop and restart the MDS Application Pool in IIS when upgrading the MDS database schema. Restart the MDS Application Pool to correct the issue.

See Also

Concepts

Install Master Data Services