Upgrade DQS: Installing Cumulative Updates or Hotfix Patches on Data Quality Services
Section 1: In Microsoft SQL Server 2012, there are several kinds of product updates or patches
These types of patches are described in more detail in article http://support.microsoft.com/kb/935897
1. Cumulative Updates (CU) are publicly downloadable hotfixes, available from the link at the the top of each CU Knowledge Base Article and generally released every 2 months.
Critical On-demand Hotfixes are released less frequently, and you must contact Microsoft Customer Support to receive the download instruction for these hotfixes, and it will be given only if you are affected by the issue described in the corresponding Knowledge Base article.
General Distribution Release (GDR) Hotfixes which are usually security updates pushed via Microsoft Update or Windows Services Update Services in your company
Service Packs are publicly downloadable and release less often than CU patches.
In Addition to SQL Server 2012 patches, there are Microsoft Updates that may patch the .Net Framework 4.0 which may requires additional upgrades in DQS itself.
Section 2 - Warning about Removing SQL Patches and Hotfixes
The SQL Server 2012 patched binaries include the DQS pieces can be uninstalled from Windows control Panel > Programs when you select Show Updates.
However once upgraded, the DQS schema catalogs cannot be downgraded.
Therefore, if you find a problem or incompatibility (for example in another feature of SQL Server not working properly with your application), to remove the patch, you may have to restore the DQS catalogs to avoid mismatches since there is no downgrade possible, or export the KB's and import them into the downgraded DQS after rebuilding the catalogs using DQSInstaller.exe.
Obviously this undesirable. If you get into a bind and have to uninstall a patch which does contain a DQS schema change, then you can export your KBs and projects to .dqs file individually using the Knowledge Management activity in Data Quality Client, or do export of all KBs at once using dqsinstaller.exe -exportkbs as discussed in this article Export and Import DQS Knowledge Bases Using DQSInstaller.exe
Section 3 - Three Approaches to patch Microsoft SQL Server 2012 Data Quality Services
If you already have SQL Server 2012 RTM installed, you can Download the .exe package for the product update, and double-click it to extract and run the package. Next, select the instances you wish to patch and run the package to completion.
Product Updates will patch the necessary binaries, registry keys, and files to update SQL Server 2012 and DQS server side features. This will patch all the features for the selected instance, not just the Data Quality Services alone.
Product Updates will NOT update the schema of the DQS catalogs, or any related MDS catalogs. Also the product updates will NOT update the SQL CLR assemblies kept the DQS catalogs. Therefore, extra steps may be required if the DQS catalog schema needs to be upgraded, or the DQS CLR assemblies have been upgraded by the product update.
For the Data Quality Client feature, there are no extra steps beyond running the product update package and selecting Shared Components to upgrade the feature.
A. To upgrade a previously configured DQS server instance
If you have previously run the DQSInstaller.exe to build the DQS_MAIN, DQS_PROJECTS, and DQS_Staging_Data catalogs in one or more of the updated instances, you may need to continue to Step 2 to do the necessary upgrade steps to upgrade the database schemas.
This chart helps to show which product updates require schema upgrades as well as UpgradeDlls when schema upgrades are not required.
| Release | KB article + Download link | DQSInstaller -Upgrade required | DQSInstaller -UpgradeDlls required |
| SQL Server 2012 RTM | NA | Not Applicable | Not Applicable |
| SQL Server 2012 RTM CU1 | 2679368 | Yes | No |
| SQL Server 2012 RTM CU2 | 2703275 | Yes | No |
| SQL Server 2012 RTM CU3 | 2723749 | Yes (when upgrading from RTM or CU1) No (when upgrading from CU2) |
No (when upgrading from RTM or CU1) Yes (when upgrading from CU2) |
| SQL Server 2012 RTM CU4 | 2758687 | Pending Research | Pending Research |
| SQL Server 2012 Service Pack 1 | 2674319 | Yes | No |
| SQL Server 2012 SP1 CU1 | 2674319 | Pending Research | Pending Research |
| .Net Framework Updates or Windows Updates |
NA | No | Sometimes (depends on which assemblies are patched) |
For DQSInstaller -Upgrade required scenarios
If the product upgrade you have installed does require a schema upgrade, follow details in Section 5 as needed.
Launch Cmd as Administrator
cd "C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn"
DQSInstaller.exe -upgrade
For DQSInstaller -UpgradeDlls required scenarios
If the product update you have installed does not require a schema upgrade, you may need to recompile the .Net assemblies with the command:
Launch Cmd as Administrator
cd "C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn"
DQSInstaller.exe -upgradedlls
Repeat the schema upgrade or dll upgrade on each instance that has been recently patched, since DQS can be installed on multiple instances in a side-by-side fashion.
B. If you have not previously configured DQS, you do not need to do any upgrade steps.
If you do have already SQL Server 2012 RTM (or later product update) installed but have never previously run the DQSInstaller.exe on that instance, you can install the product update with no required extra steps.
In other words, no DQS schema upgrade is required if you install RTM and haven't used DQS before, because you have never installed the DQS catalogs. If unsure, use Management Studio, and using the object explorer pane, connect to the instances of SQL Engine which are installed. Explore the list of databases to see if you have DQS_MAIN and DQS_PROJECTS databases. If you do not have those two databases, your DQS is not yet configured for that particular instance. Repeat on other SQL Server 2012 instances in case you have multiple instances installed on the server.
When you are ready to configure DQS for the first time, run DQSInstaller.exe from the start menu for the first time following the steps outlined here . At that time the last installed product update scripts will be used, and the matching schema changes will be installed at that initial configuration time.
C. Consider installing the RTM+Product Updates as one combined setup to save time
If you do not yet have your instance of SQL Server 2012 RTM installed, consider using the Smart Setup feature in SQL Server 2012 to "Scan for Product Updates" when installing SQL Server 2012 RTM to automatically include the patch as one unified installation (slipstream) to save time and extra steps.
Here is a blog showing a detailed runthrough showing the steps you could take to get the product updates included in RTM itself. Blog Here
Note that SQL Server 2012 setup will run the step to "Scan for Product Updates", but that scan will only see and download Microsoft Update patches (GDR and Service Packs only), and will not list Cumulative or On-demand Hotfixes which fix the known issues in DQS and other features. To get the fixes included in those Cumulative Updates or Hotfixes, you can manually point the downloaded package for the latest fix. To do so, you can manually download the CU updates into a folder, and point the RTM Setup to that folder where you have saved the .exe download. To do so, you may use the command line to launch RTM setup such as:
Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="C:\MyUpdates"
If you use this Smart Setup approach, you will still need to configure DQS for the first time following the steps outlined here. At that time the updated scripts will be used, and the matching schema patches will be installed at that time with no additional DQSInstaller -upgrade step required.
On Windows Clusters if you intent to install SQL Server 2012 with Data Quality Services as clustered instances, see KB 2674817 to understand the limitations of DQS on a cluster, and the added support for clusters in SQL Server 2012 RTM Cumulative Update 1.
Section 4 DQS Errors are expected soon after a SQL product update is installed
After a patch is installed on an previously configured DQS instance, that DQS instance may no longer function until additional action is taken to upgrade the DQS feature. This is expected behavior.
DQS will log a warning since there is a mismatch between the binaries and the database schema:
In DQS Client, the users may receive a warning message:
“DQS Server binaries version ({0}) do not match DQS Server database version ({1})”.
The DQS Server logs may show warning message:
“DQS Server binaries version ({0}) do not match DQS Server database version ({1})”.
If you attempt to use the Data Quality Client to connect to the DQS Server shortly after the patch, the following error may appear
SQL Server Data Quality Services
--------------------------------------------------------------------------------
Message Id: LogOnWindowFailedToConnect
Cannot connect to (LOCAL).
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65581. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'microsoft.ssdqs, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. ``Assembly in host store has a different signature than assembly in GAC.`` (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException: ``
``at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
``at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)
``at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
``at System.Reflection.Assembly.Load(String assemblyString)
This is expected behavior. These errors are to be expected and the following steps are recommended to remedy the errors.
Section 5-Details of Running DQSInstaller.exe -upgrade
To upgrade the DQS catalog schemas to the latest patch version, and refresh the DQS assemblies in SQL CLR, this manual step is required.
Not every service pack or cumulative update hotfix will have an required upgrade to the DQS schema, but most will. See the chart in Step 1 to access if you need to upgrade the schema.
Use the -Upgrade switch on the DQSInstaller to upgrade the database catalogs. The -Upgrade switch will extract and run the scripts needed to patch the DQS_MAIN and DQS_PROJECTS database.
When installing the scripts, current DQS activity will be terminated, and the DQS users will be locked out of DQS temporarily.
The upgrade step will also recompile the SSDQS assemblies in SQL CLR with the new version, so that the binaries match between the server and the client.
A. Launch the command prompt (as elevated administrator )
On systems where UAC is enabled launch the command prompt as Administrator.
You must use an account that is administrator, and the account must also be a member of the sysadmin role in the corresponding database engine instance of SQL Server 2012.
Start menu > cmd
right click on the CMD.exe icon and select"Run As Administrator"
B. Change directory
Change directory into the appropriate instance database engine program files location, binn folder:
Switch to the folder where the SQL Database Engine program files are installed, and run the DQSInstaller.exe.
cd "C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn"
C. Run the DQSInstaller -upgrade command
This command will detect the proper instance name to upgrade if you have switched to the instance folder in Step B.
DQSInstaller.exe -upgrade
Optionally you may use the instance switch if the instance name needs to be overridden.
DQSInstaller.exe -upgrade -instance MSSQLServer
DQSInstaller.exe -upgrade -instance NamedInstanceName
D. The DQS Installer will soon pause for you to make backups
The command prompt will show the text:
It is advised to backup the databases before upgrading.
Would you like to continue? [Yes/No]
The DQS installer is pausing so you may remember to make safe backups of your DQS databases.
Before responding to the prompt, at this point, you may manually make a backup of the DQS databases. The installer will not do it for you.
This is an example script you can run in SQL Server Management Studio to do a full database backup of each catalog.
USE MASTER;
GO
BACKUP ``DATABASE DQS_MAIN ``TO DISK=``'C:\mybackups\DQS_Main_fullbackup.bak'``;
BACKUP ``DATABASE DQS_PROJECTS ``TO DISK=``'C:\mybackups\DQS_Projects_fullbackup.bak';
BACKUP ``DATABASE DQS_STAGING_DATA ``TO DISK=``'C:\mybackups\DQS_Staging_fullbackup.bak';
GO
For more information on Backup and Restore of the DQS Catalogs, see also http://msdn.microsoft.com/en-us/library/hh213068(SQL.110).aspx
Also, in case of an emergency you can export KB projects using this -exportkbs switch on DQSInstaller.exe and import them later using -importkbs switch.
Launch the command prompt as elevated administrator on systems where UAC is enabled, switch to the folder where the SQL Database Engine program files are installed, and run the DQSInstaller.exe with the exportkbs switch to export the knowledge bases.
cd "C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn"
DQSInstaller.exe -exportkbs "c:\mybackups\exportedKBs.dqsb"
E. Type Y to Continue the DQSInstaller upgrade
Confirm by typing Yes to continue the DQSInstaller upgrade to completion once you have backed up the databases, or skipped the step 4.
The DQSInstaller will run for several minutes, and if successful will report the success confirmation
"DQS Installer finished successfully."
If no schema upgrade is required, DQSInstaller will report failure text immediately.
DQS version is up to date, no upgrade scripts will be run.
Action 'Upgrade data quality schema' finished with errors, aborting installation.
DQS Installer finished with errors Please see installation log at C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Lot\DQS_install.log
Press any key to continue...
Note: If you get the error that no schema upgrade is required, yet you are still seeing the error "Assembly in host store has a different signature than assembly in GAC." then you may instead use the -upgradedlls switch to upgrade the SQL CLR assemblies used by DQS since they have become mismatched.
Start > Cmd.exe (run as administrator)
cd "C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn"
DQSInstaller.exe -upgradedlls
If the schema upgrade fails for some other reason, please look for the error in the output log and search for that error on the internet, or post the error onto the DQS forum, or open a service request with Microsoft support for further assistance.
Example location: C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Lot\DQS_install.log
F. After upgrade, check the DQS database versions
After upgrading the schema, you can check the current version in DQS_MAIN and DQS_PROJECTS by querying the A_DB_VERSION table in each database.
Note: This table is not present in SQL Server 2012 RTM (build 11.00.2100.60) but is present after running DQSInstaller -upgrade successfully in Cumulative Update 1 or later.
SELECT * ``FROM DQS_MAIN.dbo.A_DB_VERSION ``WHERE STATUS=2; ``
SELECT * ``FROM DQS_PROJECTS.dbo.A_DB_VERSION ``WHERE STATUS=2;
Each upgrade step will show an entry, and the date of the upgrade. The maximum VERSION_ID and ASSEMBLY_VERSION on the latest date is the current version.
Example output - latest version is the key. Status 2 indicates success. If an error has occurred, the error will be listed in the Error column.
| ID | UPGRADE_DATE | VERSION_ID | ASSEMBLY_VERSION | USER_NAME | STATUS | ERROR |
| 1000 | 2012-03-21 19:09:01.583 | 1200 | 11.0.2316.0 | DOMAIN\UserName | 2 | |
| 1001 | 2012-03-21 19:09:06.240 | 1400 | 11.0.2316.0 | DOMAIN\UserName | 2 | |
| 1002 | 2012-06-27 15:09:37.750 | 1500 | 11.0.2325.0 | DOMAIN\UserName | 2 | |
Cross reference the Version Numbers in the chart below to better understand the schema version of the DQS catalogs.
| Release | KB article + Download | Binary Version | VERSION_ID | ASSEMBLY_VERSION | Comment |
| SQL Server 2012 RTM | NA | 11.00.2100.60 | NA | NA | Not Applicable - table does not exist for RTM |
| SQL Server 2012 RTM CU1 | 2679368 | 11.00.2316.00 | 1400 | 11.0.2316.0 | |
| SQL Server 2012 RTM CU2 | 2703275 | 11.00.2325.00 | 1500 | 11.0.2325.0 | |
| SQL Server 2012 RTM CU3 | 2723749 | 11.00.2332.00 | 1500 | 11.0.2325.0 | No DQS schema change since CU2 |
| SQL Server 2012 RTM CU4 | 2758687 | 11.00.2383.00 | |||
| SQL Server 2012 SP1 | 2674319 | 11.00.3000.0 | |||
| SQL Server 2012 SP1 CU1 | 2765331 | 11.00.3321.0 |
G. Backup the recently upgraded DQS databases after the upgrades
For good measure, back up the DQS catalogs after the upgrade.
This is an example script you can run in SQL Server Management Studio to do a full database backup of each catalog.
USE MASTER;
GO
BACKUP ``DATABASE DQS_MAIN ``TO DISK=``'C:\mybackups\DQS_Main_fullbackup.bak'``;
BACKUP ``DATABASE DQS_PROJECTS ``TO DISK=``'C:\mybackups\DQS_Projects_fullbackup.bak';
BACKUP ``DATABASE DQS_STAGING_DATA ``TO DISK=``'C:\mybackups\DQS_Staging_fullbackup.bak';
GO
For more information on Backup and Restore of the DQS Catalogs, see alsohttp://msdn.microsoft.com/en-us/library/hh213068(SQL.110).aspx
Need More help?
Search existing posts or start a new thread on the DQS forum, or open a service request with Microsoft support for further assistance.
See Also
- [[articles:Data Quality Services (DQS)]] (parent page to this article)
- DQS Documentation on MSDN
- Installing Data Quality Services
Credits
This article was written by Jason H - SQL