This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.First published on MSDN on Nov 29, 2012
This post provides information about the Data Quality Services (DQS) improvements in the SQL Server 2012 Service Pack 1 (SP1) release, and step-by-step instructions to upgrade your current installation of DQS to the SP1 release. The steps to upgrade from the SQL Server 2012 RTM or any of the cumulative update (CU) releases to the SP1 release are the same.
List of DQS bugs fixed in the SQL Server 2012 SP1 release
For detailed information about the DQS bugs fixed in the SP1 release, see the article at http://support.microsoft.com/kb/2674319 , and search for "DQS" or "data quality".
Before You Upgrade
Upgrading DQS to the SP1 release involves upgrading the schema of DQS databases as well. Ensure that you back up your DQS databases before you upgrade to the SP1 release. Although, the DQS installer reminds you about backing up your databases during the process, it does not back up the databases as part of the process. You can manually perform a full backup of your DQS databases by running the following example script in SQL Server Management Studio:
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';
For more information about backing up and restoring DQS databases, see http://msdn.microsoft.com/en-us/library/hh213068(SQL.110).aspx .
How To Upgrade
Download the appropriate SQL Server 2012 SP1 installer file (32-bit or 64-bit) from
On the SQL Server computer, logon using an administrator account that is also a member of the sysadmin role in the corresponding database engine instance of SQL Server 2012 where DQS is installed.
Double-click the downloaded file to launch the update process on your SQL Server computer.
On the License Terms page, accept the terms, and click Next .
On the Select Features page, ensure that all the features for your instance are visible. If you have multiple instances, you can select an instance that you want to update. Shared Features are always updated since there is only one copy to be shared by all instances. Click Next .
The installer checks for files in use to help avoid unnecessary reboots, and warns you if an in-use file is detected so you can manually kill those applications. If you don’t kill the apps prompted during the check, the installer might prompt you for a reboot at the end of the update process.
Click Update to begin the update process. This might take a while to complete (~15-20 minutes) depending on the installed features in your SQL Server instance. After the update process completes successfully, a success message is displayed. Click Close to close the installer page.
We will now upgrade the DQS database schemas. To do so, start Command Prompt, and change your directory to the location where DQSInstaller.exe is available. If you installed the default instance of SQL Server, the DQSInstaller.exe file will be available at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn:
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
At the command prompt, type the following command, and press ENTER:
NOTE : The DQS installer checks for any pending restarts on your computer owing to any other updates that have been recently installed on your computer. If any pending restarts are found, a message appears to notify you about the same, and you can choose to continue or abort the installation by pressing Y or N respectively. We recommend that if there are any pending restarts, you must abort the DQS schema upgrade process, restart your computer, and then perform the schema upgrade by starting from step 8.
The installer prompts you to back up the DQS databases before proceeding. Also, during the database schema upgrade process, current DQS activity will be terminated, and all the DQS users will be disconnected from the Data Quality Server.
Since you have already backed up your databases, type Y or Yes , and then press ENTER to continue with the upgrade.
A completion message is displayed after the successful upgrade of the DQS databases schema.
NOTE : If Data Quality Client is installed on a different computer, you will have to run the SP1 installer file on each computer where Data Quality Client is installed to upgrade it by following steps 2-7 earlier.
Verifying the Upgrade
To verify that you have successfully upgraded your DQS installation, you can do either of the following:
Use Data Quality Client to logon to Data Quality Server, click the
icon in the top-right corner of the Data Quality Client home screen, and then click
About SQL Server Data Quality Services
. The version dialog box appears that should display the version of server and client as
You can check the current assembly version in the DQS_MAIN and DQS_PROJECTS databases by querying the A_DB_VERSION table in each database. To do so, run the following query using SQL Server Management Studio:
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. In this case, it should be 1600 and 11.0.3000.0 respectively. Sample output:
ID UPGRADE_DATE VERSION_ID ASSEMBLY_VERSION USER_NAME STATUS ERROR 1000 2012-04-09 16:27:18.680 1200 11.0.2316.0 DOMAIN\UserName 2 1001 2012-04-09 16:27:25.083 1400 11.0.2316.0 DOMAIN\UserName 2 1002 2012-11-27 17:26:43.180 1600 11.0.3000.0 DOMAIN\UserName 2