Install Data Quality Services

SQL Server 2012

SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.

DQS Component	Description
Data Quality Server	Data Quality Server is installed on top of the SQL Server 2012 Database Engine, and includes three databases: DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA. DQS_MAIN contains DQS stored procedures, the DQS engine, and published knowledge bases. DQS_PROJECTS contains the data quality project information. DQS_STAGING_DATA is the staging area where you can copy your source data to perform DQS operations, and then export your processed data.
Data Quality Client	Data Quality Client is a standalone application that enables you to connect to Data Quality Server, and provides you with a highly-intuitive graphical user interface to perform data-quality operations, and other administrative tasks related to DQS.

DQS Component

Description

Data Quality Server

Data Quality Server is installed on top of the SQL Server 2012 Database Engine, and includes three databases: DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA. DQS_MAIN contains DQS stored procedures, the DQS engine, and published knowledge bases. DQS_PROJECTS contains the data quality project information. DQS_STAGING_DATA is the staging area where you can copy your source data to perform DQS operations, and then export your processed data.

Data Quality Client

Data Quality Client is a standalone application that enables you to connect to Data Quality Server, and provides you with a highly-intuitive graphical user interface to perform data-quality operations, and other administrative tasks related to DQS.

Important
Apart from the above two DQS components, there is a DQS Cleansing component in Integration Services that performs data cleansing within the Integration Services packaging process, and is automatically installed when you install Integration Services. For information about installing Integration Services, see Install Integration Services. You can also enable DQS integration in Master Data Services to use the DQS matching functionality in the Master Data Services Add-in for Excel. For more information, see Enable Data Quality Services Integration with Master Data Services.

Important

Apart from the above two DQS components, there is a DQS Cleansing component in Integration Services that performs data cleansing within the Integration Services packaging process, and is automatically installed when you install Integration Services. For information about installing Integration Services, see Install Integration Services.

You can also enable DQS integration in Master Data Services to use the DQS matching functionality in the Master Data Services Add-in for Excel. For more information, see Enable Data Quality Services Integration with Master Data Services.

DQS installation is a three-part process:

Pre-Installation Tasks : Verify system requirements before you install DQS.
Data Quality Services Installation Tasks : Install DQS by using SQL Server Setup.
Post-Installation Tasks : Perform these tasks after finishing with the SQL Server Setup to finish installing DQS.

Note
This topic does not include instructions for running Setup from the command line. For information about command-line options for installing Data Quality Server and client, see Feature Parameters in Install SQL Server 2012 from the Command Prompt.

Pre-Installation Tasks

Before installing DQS, make sure that your computer meets the minimum system requirements. The following table provides information about the minimum system requirements for the DQS components:

DQS Component	Minimum System Requirements
Data Quality Server	Memory (RAM): Minimum: 2 GB Recommended: 4 GB or more SQL Server 2012 Database Engine. For more information, see Install the SQL Server Database Engine.
Data Quality Client	.NET Framework 4.0 (installed during the Data Quality Client installation, if not already installed) Internet Explorer 6.0 SP1 or later

Important
Data Quality Server and Data Quality Client can be installed on the same computer, or different computers. Both the components can be installed independently of each other, and in any sequence. However, to use Data Quality Client, you will need a Data Quality Server installed to connect to. DQS will not work with earlier versions of SQL Server. Although Microsoft Excel is not a prerequisite for installing Data Quality Client, Microsoft Excel 2003 or later must be installed on the Data Quality Client computer to perform various operations in the client application such as importing domain values from an excel file or mapping to the source data in an Excel file for knowledge discovery, cleansing, or matching activities. The SQL Server 2012 Cumulative Update 1 (CU1) release enables support for DQS as part of a clustered SQL Server 2012 setup. For detailed information, see here. You can download the CU1 release from here.

Important

Data Quality Server and Data Quality Client can be installed on the same computer, or different computers. Both the components can be installed independently of each other, and in any sequence. However, to use Data Quality Client, you will need a Data Quality Server installed to connect to.
DQS will not work with earlier versions of SQL Server.
Although Microsoft Excel is not a prerequisite for installing Data Quality Client, Microsoft Excel 2003 or later must be installed on the Data Quality Client computer to perform various operations in the client application such as importing domain values from an excel file or mapping to the source data in an Excel file for knowledge discovery, cleansing, or matching activities.
The SQL Server 2012 Cumulative Update 1 (CU1) release enables support for DQS as part of a clustered SQL Server 2012 setup. For detailed information, see here. You can download the CU1 release from here.

For detailed information about the minimum system requirements for installing SQL Server 2012, see Hardware and Software Requirements for Installing SQL Server 2012.

Data Quality Services Installation Tasks

You have to use the SQL Server 2012 Setup to install DQS components. When you run the SQL Server Setup, you have to go through a series of installation wizard pages to select appropriate options based on your requirements. The following table lists only those pages in the installation wizard where the options that you select will affect your installation of DQS:

Page	Action
Feature Selection	Select: Data Quality Services under Database Engine Services to install the Data Quality Server. If you select the Data Quality Services check box, SQL Server Setup will copy an installer file, DQSInstaller.exe, under the SQL Server instance directory on your computer. You must run this file after you have completed the SQL Server Setup tocomplete the Data Quality Server installation. Also, you must perform some additional steps to configure your Data Quality Server before you can use it. For more information, see Post-Installation Tasks. Data Quality Client to install Data Quality Client. (Recommended) Management Tools – Complete under Management Tools – Basic to install SQL Server Management Studio. It provides you a graphical user interface to manage your SQL Server instance, and will aid you in performing additional tasks post installation as listed in the next section.
Database Engine Configuration	Click Add Current User to add your user Windows account to the sysadmin fixed server role. This is required for you to be able to run the DQSInstaller.exe file later to complete the Data Quality Server installation.

Page

Action

Feature Selection

Select:

Data Quality Services under Database Engine Services to install the Data Quality Server.
If you select the Data Quality Services check box, SQL Server Setup will copy an installer file, DQSInstaller.exe, under the SQL Server instance directory on your computer. You must run this file after you have completed the SQL Server Setup tocomplete the Data Quality Server installation. Also, you must perform some additional steps to configure your Data Quality Server before you can use it. For more information, see Post-Installation Tasks.
Data Quality Client to install Data Quality Client.
(Recommended) Management Tools – Complete under Management Tools – Basic to install SQL Server Management Studio. It provides you a graphical user interface to manage your SQL Server instance, and will aid you in performing additional tasks post installation as listed in the next section.

Database Engine Configuration

Click Add Current User to add your user Windows account to the sysadmin fixed server role. This is required for you to be able to run the DQSInstaller.exe file later to complete the Data Quality Server installation.

Post-Installation Tasks

After you complete the SQL Server installation wizard, you must perform additional steps mentioned in this section to complete your Data Quality Server installation, configure it, and then use it.

Action

Description

Related Topics

Complete the Data Quality Server installation

Run the DQSInstaller.exe file. On running the DQSInstaller.exe file:

The DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases are created.
The ##MS_dqs_db_owner_login## and ##MS_dqs_service_login## logins are created.
The dqs_administrator, dqs_kb_editor, and dqs_kb_operator roles are created in the DQS_MAIN database.
The DQInitDQS_MAIN stored procedure is created in the master database.
DQS_install.log file is typically created in the C:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Log folder. The file contains information about the actions performed on running the DQSInstaller.exe file.
If a Master Data Services database is present in the same SQL Server instance as Data Quality Server, a user mapped to the Master Data Services login is created, and is granted the dqs_administrator role on the DQS_MAIN database.

This completes the Data Quality Server installation.

Run DQSInstaller.exe to Complete Data Quality Server Installation

Grant DQS Roles to Users

To log on to Data Quality Server using Data Quality Client, a user must have any of the following three roles on the DQS_MAIN database: dqs_administrator, dqs_kb_editor, or dqs_kb_operator. By default, if your user account is a member of the sysadmin fixed server role, you can log on to Data Quality Server using Data Quality Client even if none of the DQS roles are granted to your user account. For information about the three DQS roles, see DQS Security.

Note
The three DQS roles are not available for the DQS_PROJECTS and DQS_STAGING_DATA databases.

Grant DQS Roles to Users

Enable the TCP/IP network protocol if Data Quality Server will be accessed from a remote computer

If the Data Quality Server and Data Quality Client components are not on the same computer, you must enable the TCP/IP protocol for the SQL Server instance where Data Quality Server is installed using SQL Server Configuration Manager. Otherwise, the remote Data Quality Client applications cannot connect to Data Quality Server.

Enable TCP/IP Network Protocol for SQL Server

Make your data available for DQS operations

Make sure that you can access your source data for the DQS operations, and can export the processed data to a table in a database.

Access Data for the DQS Operations

MCSA SQL Server Certification

Tuesday, December 22, 2015