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
- •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.
Prerequisites
Download the MSSQL JDBC driver JAR files from the Internet and copy the files to the MSSQLServerScanner directory in the <Informatica installation directory>/services/CatalogService/ScannerJars/externalDependencies directory. You must restart the Catalog Service.
Basic Information
The General tab includes the following basic information about the resource:
Information | Description |
|---|
Name | The name of the resource. |
Description | The description of the resource. |
Resource type | The type of the resource. |
Execute On | You can choose to execute on the default catalog server or offline. |
Resource Connection Properties
The following table describes the properties for the Microsoft SQL Server resource:
Property | Description |
|---|
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. Note: The database name and the resource name must not be same. |
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. |
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 |
Custom 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. |
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. Verify that you installed the Enterprise Data Catalog Agent as a Windows service. For information on how to install the agent, see Appendix D: Enterprise Data Catalog Agent in the Catalog Administrator Guide. To configure the service, you can specify the Active Directory ID used to access the Microsoft SQL Server database as the login ID.
- 2. Stop the Apache Tomcat 9.0 MITI service.
- 3. Download the JDBC driver for Microsoft SQL Server version 7.4.1 to a temporary location.
- 4. Run the .exe file to extract the files to the Microsoft JDBC Driver 7.4 for SQL Server folder in the temporary location.
- 5. From the extracted folder, copy the \sqljdbc_7.4\enu\auth\x64\sqljdbc_auth.dll file to the following directories on your machine:
- - C:\Windows\System32
- - <EDC Agent Installation_Directory>\jre\bin
- 6. Copy the \sqljdbc_7.4\enu\mssql-jdbc-7.4.1.jre8.jar file to the <EDC Agent Installation_Directory>\java\Jdbc\mssql directory on your machine.
- 7. In the Windows Services console, double-click on the Apache Tomcat 9.0 MITI service, select the Log On tab.
- 8. Enter the service account credentials to connect to the Microsoft SQL Server database on the domain, and click OK to start the service.
- 9. Add the service account to the Administrators group on your machine.
- 10. In Catalog Administrator, create a Microsoft SQL Server resource with the following Enterprise Data Catalog Agent URL: http://<HostName>:<Port>/MIMBWebServices/
- 11. 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.