SQL Server support

Connector features

Data catalog

Dataedo documents metadata of the following objects:

• Tables
• Views
• Procedures
• Functions
• Sequences
• Jobs
• Job steps
• Linked servers

For more details, go to Connector specification section.

Data Lineage

For SQL Server, Dataedo supports both manual and automatic lineage. Automatic lineage is currently available for views and stored procedures - read more about automatic lineage for SQL Server. Dataedo will parse views and show the source of each of the columns:

Data Profiling

You can profile data in tables and views to get a grasp of the data sitting in your database:

Relationships (PK/FK) Tester

With Dataedo, you can check if selected columns are viable candidates to create a relationship between tables:

Linked Servers

The SQL Server Connector imports linked servers into Linked Sources, capturing the connection details with the name assigned to each Linked Server. The import process does not automatically assign the linked source to objects, nor does it automatically associate the source database with the linked server. These assignments can be made manually by the user.

Important note

If a view is created using a Linked Servers connection object, the user must assign the linked server and parse the script to establish lineage.

How to connect

1. Server name - IP address or host name under which the database is available.
2. Port - Port number under which the database is available.
3. Authentication - Way of authentication to be used when connecting to SQL Server:
   • Windows Authentication - Authentication that will use the currently logged-in Windows user. Does not require providing any other information.
   • SQL Server Authentication - SQL authentication. Requires SQL login:
     • User
     • Password
   • Azure Active Directory - Password - Authentication with Azure AD login. Requires:
     • User - AD username
     • Password - Password for the provided username
   • Azure Active Directory - Integrated - This authentication will use the AD account currently logged in Windows.
   • Azure Active Directory - Universal with MFA - Authentication through an external Azure AD service. You can optionally provide a username.
4. Connection mode - Specifies if the connection will be encrypted.
   • Encrypted connection if possible - Least restrictive mode. If encryption is possible, data will be encrypted. Otherwise, it will be sent in plain text.
   • Force encryption, trust server certificate - Dataedo will not connect unless the connection can be encrypted. The SSL certificate on the server will not be verified.
   • Force encryption, require trusted certificate - Most restrictive mode. Dataedo will connect only if the connection can be encrypted and the SSL certificate on the server is valid.
5. Database - Database name. For some types of authentication, it is possible to list available databases by clicking the three-dot icon [...]. To import Jobs from SQL Server, select < SQL Server Agent Objects >.

Connector specification

Supported versions

2008, 2008 R2, 2012, 2014, 2016, 2017, 2019, 2022.

Supported editions

LocalDB, Express, Developer, Standard, Business Intelligence, and Enterprise.

Parallel Data Warehouse

Parallel Data Warehouse is not currently supported.

Imported objects

Object	Imported as
Table	Table
View	View
Procedure	Procedure
Function	Function
Sequence	Sequence
Job	Tasks/Jobs
Job steps	Tasks/Jobs
Linked Server	Linked Sources
Database trigger	Not imported

Tables metadata

Metadata	Imported as
Schema	Schema
Name	Name
Description	Description
Location (external table)	Location (hidden)
Columns	Table columns
Name	Name
Data type	Data type
Null/Not null	Nullable
Description	Description
Foreign Keys	Relationships
Constraint table	FK Table
Referenced table	PK Table
Name	Relationship name
Description	Description
Primary/Unique keys	Unique keys
Name	Key name
Columns	Columns
Description	Description
Triggers	Triggers
Definition	Script
Name	Name
Action	When
Description	Description
Indexes	Not imported

Views metadata

Metadata	Imported as
Schema	Schema
Name	Name
Description	Description
Columns	Table columns
Name	Name
Data type	Data type
Null/Not null	Nullable
Description	Description
Definition	Script

Procedures metadata

Metadata	Imported as
Schema	Schema
Name	Name
Description	Description
Definition	Script
Parameters	Input/Output
Description	Description
Name	Name
IN/OUT	Mode
Data type	Data type

Functions metadata

Metadata	Imported as
Schema	Schema
Name	Name
Description	Description
Definition	Script
Parameters	Input/Output
Description	Description
Name	Name
IN/OUT	Mode
Data type	Data type

Sequences metadata

Metadata	Imported as
Schema	Schema
Name	Name
Description	Description
Definition	Script
Parameters	Input/Output
Name	Name
IN/OUT	Mode
Data type	Data type

Jobs metadata

Metadata	Imported as
Name	Name
Description	Description
Create date	Create date
Modify date	Modify date

Jobs steps metadata

Metadata	Imported as
Name	Name
Command	Script
Create date	Create date
Modify date	Modify date
Output file	Input/Output
IN/OUT	Mode
Data type	Data type
Destination	Description

Linked Servers

Metadata	Imported as
Name	Name
Provider	Connection details
Host	Connection details
Data source	Connection details
Location	Connection details
Catalog	Connection details
Remote name	Connection details

Descriptions & extended properties

Dataedo reads and writes extended properties from/to the following SQL Server objects:

Object	Read	Write back
Tables	✅	✅
Column	✅	✅
Primary keys	✅	✅
Column comments	✅	✅
Unique keys	✅	✅
Foreign keys	✅	✅
Triggers	✅	✅
View	✅	✅
Columns	✅	✅
Function comments	✅	✅
Parameters	✅	✅
Stored procedures	✅	✅
Parameters	✅	✅
Sequences	✅
Jobs	✅

Data profiling

Datedo supports the following data profiling in PostgreSQL:

Profile	Support
Table row count	✅
Table sample data	✅
Column distribution (unique, non-unique, null, empty values)	✅
Min, max values	✅
Average	✅
Variance	✅
Standard deviation	✅
Min-max span	✅
Number of distinct values	✅
Top 10/100/1000 values	✅
10 random values	✅

Read more about profiling in a Data Profiling documentation.

Data Lineage

Source	Method	Version
Views - object level	From dependencies	✅
Views - object level	From SQL parsing	✅
Views - column level	From SQL parsing	✅
Stored Procedures - object level	From SQL parsing	✅
Stored Procedures - column level	From SQL parsing	✅
Polybase External Tables - object level	From sys.external_data_sources and sys.external_tables views	✅
dbt	dbt connector	✅

Column-level data lineage is retrieved using Dataedo SQL parser. Read more about capabilities of Transact-SQL SQL parser

See also: dbt, SSIS

Limitations

Following schema elements currently are not supported:

• Check constraints
• Non unique indexes
• Unique indexes on views (planned)
• Column level lineage for views with recursive CTE

Required access level

Importing database schema requires a certain access level in the documented database.

Granting VIEW DEFINITION permission in the documented database to a new user will allow them to import all objects from the database. Alternatively, you can grant this permission only for specific objects you want to document.

Following objects are accessed during the schema import process:

• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE
• INFORMATION_SCHEMA.PARAMETERS
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
• INFORMATION_SCHEMA.ROUTINES
• INFORMATION_SCHEMA.ROUTINE_COLUMNS
• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.VIEWS
• sys.all_objects
• sys.columns
• sys.computed_columns
• sys.extended_properties
• sys.foreign_key_columns
• sys.indexes
• sys.index_columns
• sys.objects
• sys.procedures
• sys.servers
• sys.sql_dependencies
• sys.sql_expression_dependencies
• sys.sql_modules
• sys.tables
• sys.views
• sysobjects
• sysusers
• sys.sequences
• sys.schemas
• sys.types

Importing jobs and jobs steps uses views from msdb System Database. Requires membership in the public role, due to usage of sp_executesql.

Following objects are accessed during jobs import process (user needs Select permission on those tables to import jobs) :

• dbo.sysjobs
• dbo.sysjobssteps
• dbo.syscategories - used for extended properties
• sys.server_principals - used for extended properties

Importing Linked Servers require additional access:

• master.sys.servers
• master.sys.linked_logins

Learn more

Connect to SQL Server