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. 2. 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. 3. General Distribution Release (GDR) Hotfixes which are usually security updates pushed via Microsoft Update or Windows Services Update Services in your company 4. 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.
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
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.
as needed. Launch Cmd as Administrator cd "C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn"
DQSInstaller.exe -upgrade
Launch Cmd as Administrator cd "C:\Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn"
DQSInstaller.exe -upgradedlls
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.
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.
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"
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"
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
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
DQS_PROJECTS
'C:\mybackups\DQS_Projects_fullbackup.bak';
BACKUP
DQS_STAGING_DATA
'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"
"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)
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
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;
SELECT
FROM
DQS_MAIN.dbo.A_DB_VERSION
WHERE
STATUS=2;
DQS_PROJECTS.dbo.A_DB_VERSION
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.
For more information on Backup and Restore of the DQS Catalogs, see alsohttp://msdn.microsoft.com/en-us/library/hh213068(SQL.110).aspx
Search existing posts or start a new thread on the DQS forum, or open a service request with Microsoft support for further assistance.
This article was written by Jason H - SQL
Richard Mueller edited Revision 31. Comment: Removed (en-US) from title, added tags
Jason H - SQL edited Revision 29. Comment: Adding CU4 tentatively
Jason H - SQL edited Revision 3. Comment: Changing title
Jason H - SQL edited Revision 20. Comment: Added CU2 KB 2703275 Version 11.0.2325