[This post was created by Jason Howell, Senior Escalation Engineer in the SQL Server Support team specializing in MDS]
SQL Server 2008 R2 Service pack 1 was released in July 2011, as well as CU#1 for Service Pack 1 a few days afterwards, and CU #2 just yesterday August 15 th , 2011.
The purpose of this blog is to call out the difference in finding and running the download specifically for Service Pack 1, since it is packaged and extracted differently that Cumulative Updates. Other than the download & extraction steps, it is pretty much the same process as other Cumulative Updates for MDS. Steps 1 and 3 are the main differences here as compared to this this prior blog post .
Installing the Service Pack 1 for MDS requires manual intervention in this release, and running the typical Service Pack 1 setup for the other parts of SQL Server itself is not enough to patch MDS databases and websites.
If your server does not have an existing MDS installation, you can install a fresh copy of MDS following these steps in MSDN: Installing and Configuring Master Data Services You can use the SP1 MSI to install a fresh copy of MDS if needed.
If you already have a copy of MDS up and running, you can patch the existing instance with Service Pack 1 updates.
Always test the update and upgrade process in a non-production environment before applying an update and upgrading the MDS database in your production environment.
1. Download SQL Server 2008 R2 Service Pack 1The first step is to download the service pack.
You can use this link to download the English version, or change the language as needed. http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727
For example get the file SQLServer2008R2SP1-KB2528583-x64-ENU.exe
However, wait until you have read Step 3 to run the download.
A. It’s worth noting that the MDS Service Pack 1 download package looks different than the CU downloads.Service Pack 1 does not have a specific MDS download like the CU’s downloads have. Pick the SP1 for x64.
Note: The KB for 2008 R2 Service Pack 1 (KB2528583) is located here: http://support.microsoft.com/kb/2528583
B. What’s in SQL Server 2008 R2 Service Pack 1?Service Pack 1 includes fixes for SQL Server 2008 R2 CU1 through CU6 plus a few other fixes that are not in the RTM CU’s. Read the Kb for an exact list of the additional fixes not in CU1-CU6.
Moving forward, fixes made in the RTM cumulative updates are also ported forward to the next subsequent cumulative update for SP1. However, fixes made to the SP1 cumulative updates will NOT be ported back to the RTM unless there is express need to do so. This makes SP1 updates favorable moving forward to get the most fixes.
The green cumulative updates for MDS are included in Service Pack 1. The orange items below are carried forward in CU#1 for SP1. To get the latest updates, then use SP1 CU2 instead of SP1 itself. The list grows every 2 months, so refer to this list if in doubt which one is latest.
-
RTM & Updates
- 10.50.1600.1 Release to Manufacturer – May 10, 2010
- 10.50.1617.0 RTM Security Patch MS11-049 ( 2494088 ) June 14, 2011
- 10.50.1702.0 RTM CU #1 ( 981355 ) May 18, 2010
- 10.50.1720.0 RTM CU #2 ( 2072493 ) June 21, 2010
- 10.50.1734.0 RTM CU #3 ( 2261464 ) Aug 16, 2010
- 10.50.1746.0 RTM CU #4 ( 2345451 ) Oct 18, 2010
- 10.50.1753.0 RTM CU #5 ( 2438347 ) Dec 20, 2010
- 10.50.1765.0 RTM CU #6 ( 2489376 ) Feb 21, 2011
- 10.50.1777.0 RTM CU #7 ( 2507770 ) Apr 18, 2011
- 10.50.1790.0 RTM CU Security Patch MS11-049 ( 2494086 ) June 14, 2011
- 10.50.1797.0 RTM CU #8 ( 2534352 ) June 20, 2011
- 10.50.1804.0 RTM CU #9 ( 2567713 ) Aug 15, 2011
- etc…
- SERVICE PACK 1 & Updates
Instead of SP1 itself, you could run CU1 or CU2 (or later CU) for SP1 if you have business requirements to do so. There is a specific MDS file available for download for each CU, which contains only the piece to patch Master Data Services.
Since the MDS download contains a *.msi, it is unlike the rest of SQL feature area CU’s which use *.msp patches that require SP1 as a prerequisite to installing. Therefore you could use the SP1 CU2 file for a fresh install of MDS.
- Cumulative Update 1 for SP1: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2544793&kbln=en-us
- Cumulative Update 2 for SP1: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2567714&kbln=en-us
- Future SP1 updates will be linked from this article: http://support.microsoft.com/kb/2567616
When you fill out the request page, as shown above the hotfix website will send you an email with the link to download the CU update.
2. MDS Preparations – stop current activity, backup your MDS Database, check the starting versionThe upgrade process is two-fold: First, Upgrade the binaries. Secondly, upgrade the database schema
Therefore, before we begin, let’s stop all current activity so that we have a clean and safe copy of the MDS database in a consistent state, and make a backup of the MDS database and transaction log to be sure we have a safe copy just in case.
A. Stop the IIS web site or application pool.This will keep users out of the system while we do the upgrade maintenance. You may want to alert the MDS users about the scheduled maintenance according to your businesses' change control processes.
Open Internet Information Services (IIS) Manager and stop the Master Data Manager Web application and the MDS Web service (if enabled). You can do this either by stopping the application pool that contains the Web application and service, or you can stop the Web site that contains them. If you stop the application pool, you stop requests for all applications in that application pool. If you stop the Web site, you stop only traffic to the Web site and any applications and services it contains.
Such as scheduled jobs, reports, or custom applications that interact with the MDS database directly.
You could stop your SQL Agent service temporarily if unsure about scheduled jobs that might affect MDS data.
C. Backup the MDS database and the transaction logYou get to pick the name of your MDS database when you originally set it up with the Configuration Manager, so your database name may vary.
If you use FULL recovery model on the MDS database, you can back up the transaction LOG also.
Use a TSQL query or SSMS backup as you please.
BACKUP DATABASE MDS TO DISK='C:\mybackups\mds_before_sp1.bak'BACKUP LOG MDS TO DISK='C:\mybackups\mds_log_before_sp1.trn'
Object Explorer in SQL Server Management Studio:
D. Query mdm.tblSystem in the current MDS database.
Review value for SchemaVersion and note the value, so you can compare the value after you apply the MDS update to verify that the value has been increased. The SchemaVersion may vary based on the build you are starting with. Some MDS Patches do no schema upgrades, but some do. Depending on which version you start from, Service Pack 1 may or may not upgrade the schema.
USE MDS
GO
SELECT * FROM mdm.tblSystem
3. Run the Service Pack 1 executable and msi’s
The SP1 download SQLServer2008R2SP1-KB2528583-x64-ENU.exe is a self-extracting zip. You can run SP1 for all of SQL if you want (see step 3A) or manually extract just the Master Data Services *.msi if needed (see step 3B).
Double-clicking the .exe extracts SP1 to a temp folder on the root of the drive
After extracting, it will prompt you with security UAC to run the service pack 1 setup.
A. Option 1: Run SP1 for all SQL Server feature areas first, then find the MDS msi.
If you need to patch all of the features of your SQL Server with Service Pack 1, follow the normal steps in the GUI.
Running SP1 will extract the MasterDataServices.msi file but it will not run it automatically.
After you have finished patching SQL Server 2008 R2 with Service Pack 1, you can then find the MSI for MDS in the setup bootstrap folder. It’s kept here for caching purposes:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cache\KB2528583\ServicePack\1033_enu_lp\x64\setup\ masterdataservices.msi
On my computer it is in this folder, but I imagine the language (1033=English) may vary based on which SQL locale you have used in the installation.
Within that folder, click the MSI to run the MDS SP1 setup masterdataservices.msi
B. Option 2: Manually Extract SP1 if you do not need to patch any other parts of SQL.
If you don’t need to patch the other features of SQL Server, or maybe you need to just patch MDS as a separate step, you can manually extract the Service Pack 1 and get to the MSI. Several 3rd party compression tools let you right click on the .exe and extract it to a folder of your choosing as well.
From a command line, you can run:
SQLServer2008R2SP1-KB2528583-x64-ENU.exe /extract
Find the MDS msi in the specified location, in the subfolder:
<Extracted folder>\1033_enu_lp\x64\setup\masterdataservices.msi
4. Run the masterdataservices.msi manually (like any other MDS update)
Run through the installation until it completes
.
Note: If you start the MDS website at this point and visit it, you may get a version incompatible error. This is expected behavior when the schema version increases because the MDS binaries are upgraded, but the database is not yet upgraded. Users cannot use MDS yet if you see this message.
Client Version: 10.51.2500.0
Database Version: 1.0.0.0
The Client version is incompatible with the Database version. Please ask your administrator to upgrade the Client components or the Database components or both.
Note the binaries and scripts are upgraded already to 10.50.2500.0 (the build number of R2 Service Pack 1)
Binaries are the *.exe or *.dll in the Master Data Services installation folders such as
C:\Program Files\Microsoft SQL Server\Master Data Services\Configuration\
C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin
When you right click on them in Windows Explorer, and view the properties, you can check the file version:
5. The Master Data Services Configuration Manager may need to run to complete the MDS database schema upgrade.
A. Run Configuration Manager
It will try to run automatically after the setup completes, or you can run it later from the start menu. Complete the additional steps therein to upgrade your MDS Database and your MDS Websites.
B. Connect and select your database
In the MDS Configuration Manager, click the [Select Database…] button.
Get connected to your MDS database on whichever SQL Server it may reside on. Note if you utilize a named instance, you have to manually type in the servername\instancename because the configuration manager forgets which instance to connect to every time.
C. Upgrade your MDS database if needed
If you see the below red error text "This database requires an upgrade. You cannot change system settings until the database is upgraded" then you need to upgrade your MDS database.
Sometimes you do not see this text, if you already had the schema upgrades are already in place from a prior cumulative update patch, then no further action is required, skip to step 6.
If you get the error after you picked the database server and the existing database name, in the MDS Configuration Manager, use the [Upgrade Database…] button to upgrade the database.
D. Click through the Upgrade Database Wizard
When you run the Upgrade, you will see several screens in the upgrade wizard. It will show which schema upgrades need to happen. It’s easy, so I’ll just show the progress bar:
E. Now check the database SchemaVersion in your MDS database by running the query to confirm the schema is now upgraded.
I expect 1.0.7.0 for Service Pack 1.
USE MDS
GO
SELECT * FROM mdm.tblSystem
6. Start the MDS website and Application Pool and make sure it works.
A. Start the web site and application pools
After the database upgrade is complete, your users can start using the MDS website again. Start the Website and or Application Pools, and remember to enable any SQL Agent jobs or other maintenance you may have disabled for the upgrade timeframe.
B. Browse through your MDS website to make sure the models are present, and that things seem normal.
You can get IIS to help you if you forgot the URL:
You can confirm the version number in the Help menu (blue question mark on the upper right of on the web page).
The MDS website shows the version as 10.51.2500.0 for Service Pack 1.
