SQL Server support

Applies to: Dataedo 23.x (current) versions, Article available also for: 10.x

Connector features

Data catalog

Dataedo documents metadata of following objects:

  • Tables
  • Views
  • Procedures
  • Functions
  • Sequences
  • Jobs
  • Jobs steps

For more details, go to Connector specification section

Image title

Data Lineage

For SQL Server Dataedo supports both manual and automatic lineage. Automatic lineage is currently available only for views. Dataedo will parse views and show source of each of the columns:

Image title

Data Profiling

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

Image title

Relationships (PK/FK) Tester

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

Image title

How to connect

  1. Server name - IP address or host name under which database is available
  2. Port - port number under which database is available
  3. Authentication - way of authentication to be used when connecting to SQL Server:
    • Windows Authentication - authentication that will use 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 AD account currently logged in Windows
    • Azure Active Directory - Universal with MFA - authentication through external Azure AD service. You can optionally provide username.
  4. Connection mode - specifies if connection will be encrypted.
    • Encryped connection if possible - lest restrictive mode. If encryption is possible, data will be encrypted. Otherwise it will be send in plain text.
      • Force encryption, trust server certificate - Dataedo will not connect unless connection can be encrypted. SSL certificate on server will not be verified.
      • Force encryption, require trusted certificate - Most restrictive mode. Dataedo will connect only if connection can be encrypted and SSL certificate on server is valid.
  5. Database - database name. For some types of authentication it is possible to list available databases by clicking three dot icon [...].

Image title

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 stpes Tasks/Jobs
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

Descriptions & extended properties

Dataedo reads and writes extended properties from/to 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 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 Profliling 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 retived 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 certain access level in 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 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

Learn more

Connect to SQL Server

Found issue with this article? Comment below
Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.