Microsoft SQL Server
You can configure a Microsoft SQL Server resource type to extract metadata from Microsoft SQL Server databases. Verify that you configure the VIEW DEFINITION permission for the SQL database and configure the SELECT permission for the sys.sql_expression_dependencies of the database.
Objects Extracted
Enterprise Data Catalog extracts metadata of the following schema objects from a Microsoft SQL Server resource:
- •Tables
- •Views
- •Extended stored procedure
- •Table (user-defined)
- •Synonym
- •UNIQUE constraint
- •Triggers
- •Procedures
- •Index
- •SQL table-valued-function
- •Users
- •Partition Scheme
- •Partition Function
- •XML Schema Collections
- •User defined Data type
- •Rules
- •Defaults
- •Constraints
- •SQL_INLINE_TABLE_VALUED_FUNCTION
- •SQL_SCALAR_FUNCTION
- •SEQUENCE
Permissions to Configure the Resource
Before you create a Microsoft SQL Server resource, configure the permissions for the Microsoft SQL Server database user account that you use to connect to Microsoft SQL Server. Enterprise Data Catalog uses SQL Server authentication to connect to the Microsoft SQL Server database. The user account that you use to connect to Microsoft SQL Server must be an SQL Server login account. Configure the VIEW DEFINITION permission for the user account. Configuring this permission lists all the schemas from where the user can load metadata. You must also configure the SELECT permission for the sys.sql_expression_dependencies for the database.
Resource Connection Properties
The following table describes the properties for the Microsoft SQL Server resource:
Property | Description |
|---|
Agent URL | URL to the Enterprise Data Catalog agent that runs on a Microsoft Windows Server. Note: Make sure that you specify the URL in the following format: http:// <hostname>:<connector_port>/MIMBWebServices |
User | Name of the Microsoft SQL Server user account that connects to the Microsoft SQL Server database. The Catalog Service uses Microsoft SQL Server authentication to connect to the Microsoft SQL Server database. |
Password | Password for the user account that connects to the Microsoft SQL Server database. |
Host | Host name of the machine where Microsoft SQL Server runs. |
Port | Port number for the Microsoft SQL Server database engine service. |
Database | Name of the Microsoft SQL Server database. |
Instance | Microsoft SQL Server instance name. |
The following table describes the Additional and Advanced properties for source metadata settings on the Metadata Load Settings tab:
Property | Description |
|---|
Enable Source Metadata | Select to extract metadata from the data source. |
Import system objects | Specifies the system objects to import. The default value is True or False whatever the case might be. |
Schema | Specifies a list of semicolon-separated database schema. |
Source Metadata Filter | You can include or exclude tables and views from the resource run. Use semicolons (;) to separate the table names and view names. For more information about the filter field, see Source Metadata and Data Profile Filter. |
Import stored procedures | Specifies the stored procedures to be imported. The default value is True or False whatever the case might be. |
Import Synonyms | Specifies the synonyms to import. Select the check box to import synonyms. Clear the check box if you do not want to import synonyms. By default the resource is configured to import synonyms. |
Case Sensitive | Specifies that the resource is configured for case insensitivity. Select one of the following values: - - True. Select this check box to specify that the resource is configured as case sensitive.
- - False. Clear this check box to specify that the resource is configured as case insensitive.
The default value is False. |
Memory | Specify the memory value required to run a scanner job. Specify one of the following memory values: Note: For more information about the memory values, see the Tuning Enterprise Data Catalog Performance article on How To-Library Articles tab in the Informatica Doc Portal |
JVM Options | JVM parameters that you can set to configure scanner container. Use the following arguments to configure the parameters: - - -Dscannerloglevel=<DEBUG/INFO/ERROR>. Changes the log level of scanner to values, such as DEBUG, ERROR, or INFO. Default value is INFO.
- - -Dscanner.container.core=<No. of core>. Increases the core for the scanner container. The value should be a number.
- - -Dscanner.yarn.app.environment=<key=value>. Key pair value that you need to set in the Yarn environment. Use a comma to separate the key pair value.
- - -Dscanner.pmem.enabled.container.memory.jvm.memory.ratio=<1.0/2.0>. Increases the scanner container memory when pmem is enabled. Default value is 1.
- - -DskipExternalDatabaseObjects=true. Excludes all external objects such as schema, table, database and view.
|
Track Data Source Changes | Vew metadata source change notifications in Enterprise Data Catalog. |
Auto Assign Connections | Indicates whether the connections must be assigned automatically. |
Enable Reference Resources | Extracts metadata about assets that are not included in this resource, but referred to in the resource. Examples include source and target tables in PowerCenter mappings, and source tables and files from Tableau reports. |
Retain Unresolved Reference Assets | Retains unresolved reference assets in the catalog after you assign connections. Retaining unresolved reference assets help you view the complete lineage. The unresolved assets include deleted files, temporary tables, and other assets that are not present in the primary resource. |
Agent Options | Specify the Enterprise Data Catalog Agent options to run the scanner job. |
You can enable data discovery for a Microsoft SQL Server resource. For more information about enabling data discovery, see the
Enable Data Discovery topic. You can enable composite data domain discovery for a Microsoft SQL Server resource. For more information about enabling composite data domain discovery, see the
Composite Data Domain Discovery topic.
Configuring Enterprise Data Catalog Agent for the Resource on a Windows-authenticated Server
To configure the Enterprise Data Catalog Agent for Microsoft SQL Server, you can perform the following steps:
- 1. Download the SQL Server JDBC driver (version 4.1) to a temporary location.
- 2. Copy the sqljdbc<version>.jar file to the <Enteprise_Data_Catalog_Agent_location>\java\Jdbc\mssql location.
- 3. Copy the \sqljdbc_4.1\enu\auth\x64\sqljdbc_auth.dll file extracted from sqljdbc_4.1. jar file to the C:\Windows\System32\ location.
- 4. Configure the Enterprise Data Catalog Agent to run as a service. To configure the service, you can specify the Active Directory ID used to access the Microsoft SQL Server database as the login ID.
- 5. Run the RestartServerService.bat file.
- 6. In Catalog Administrator, create a Microsoft SQL Server resource with the following Enterprise Data Catalog Agent URL: http://<HostName>:<Port>/MIMBWebServices/
- 7. Run the resource.
Viewing Lineage of External Schema and Database Objects
You can view detailed lineage of external schema and database objects in Enterprise Data Catalog. To view the lineage of the external schema and database objects, use the Connection Assignment option.
Note: For Connection Assignment, make sure that the destination or target data sources that represent the external schema and database objects are available in Enterprise Data Catalog.