Skip to main content

SQL Server Vault Configuration

SQL Server vaults provide secure connectivity to Microsoft SQL Server databases, enabling comprehensive data quality monitoring across your SQL Server infrastructure.

Overview​

Microsoft SQL Server is a relational database management system developed by Microsoft. DeepDQ's SQL Server vault integration enables Sentinels to execute data quality monitoring queries, Data Catalog to discover and document database schemas, Data Lineage to track data relationships, and DAB Chatbot to provide intelligent querying capabilities.

Configuration Parameters​

Required Fields​

  • Name: A unique identifier for your SQL Server vault
  • Type: SQL Server (automatically selected)
  • Host: The hostname or IP address of your SQL Server instance
  • Port: The port number (default: 1433)
  • Database: The database name to connect to
  • Username: The SQL Server username for authentication
  • Password: The SQL Server password (securely encrypted)

Example Configuration​

Name: Production SQL Server Vault
Type: SQL Server
Host: your-sqlserver.database.windows.net
Port: 1433
Database: your_database
Username: your_username
Password: [encrypted]

Supported SQL Server Versions​

DeepDQ supports the following SQL Server versions:

  • SQL Server 2016
  • SQL Server 2017
  • SQL Server 2019
  • SQL Server 2022
  • Azure SQL Database
  • Azure SQL Managed Instance

Authentication Methods​

SQL Server Authentication​

  • Standard username and password authentication
  • Local SQL Server user accounts
  • Mixed mode authentication support
  • Currently supported by DeepDQ

Connection Requirements​

Network Access​

  • Ensure connectivity to SQL Server instance
  • Configure firewall rules for the specified port
  • For Azure SQL, configure firewall rules and virtual network access
  • Enable TCP/IP protocol on SQL Server
  • DeepDQ Static IP: Contact salesandsupport@deepanalyze.ai to get the Static IP for whitelisting

Database Permissions​

The SQL Server user requires these minimum permissions:

  • CONNECT permission on the database
  • SELECT permission on tables to be monitored
  • VIEW DEFINITION permission for schema discovery
  • VIEW DATABASE STATE for performance monitoring (optional)

Example permission grants:

USE your_database;
CREATE USER [deepdq_user] FOR LOGIN [deepdq_login];
GRANT SELECT ON SCHEMA::dbo TO [deepdq_user];
GRANT VIEW DEFINITION TO [deepdq_user];

Common Use Cases​

Enterprise Data Quality Monitoring​

  • Execute Sentinels for comprehensive data validation
  • Monitor business-critical SQL Server databases
  • Validate data integrity across enterprise applications
  • Track compliance with business rules and regulations

Comprehensive Data Cataloging​

  • Automatic discovery of SQL Server databases and schemas
  • Extract and document table and column metadata
  • Track schema changes and database evolution
  • Maintain enterprise data dictionary

Enterprise Data Lineage​

  • Map data relationships across SQL Server instances
  • Track data flow in complex enterprise environments
  • Monitor dependencies in multi-database architectures
  • Visualize data movement and transformations

Intelligent Data Exploration​

  • DAB Chatbot integration for natural language queries
  • Conversational business intelligence
  • Automated insights from SQL Server data
  • Interactive data analysis and reporting

Best Practices​

Security​

  • Use dedicated SQL Server logins for DeepDQ
  • Implement least privilege access principles
  • Enable SSL/TLS encryption for connections
  • Regular security audits and password rotation

Performance​

  • Create appropriate indexes for monitored tables
  • Monitor connection pool utilization
  • Configure connection timeout settings
  • Use read-only routing for Always On AG

High Availability​

  • Configure connection retry logic for failover scenarios
  • Implement health checks for availability groups
  • Monitor secondary replica synchronization
  • Set up alerts for failover events

Troubleshooting​

Connection Issues​

Login Failed Errors

  • Verify username and password accuracy
  • Check SQL Server authentication mode
  • Validate user account status and permissions
  • Review login audit logs

Network Connectivity Issues

  • Verify SQL Server service status
  • Check firewall and port configuration
  • Validate network connectivity and routing
  • Test with SQL Server Management Studio

Azure SQL Specific Issues

  • Check Azure SQL firewall rules
  • Verify virtual network service endpoints
  • Validate Azure AD authentication setup
  • Review Azure SQL resource availability

Performance Issues​

Slow Query Execution

  • Review query execution plans
  • Check for blocking and deadlocks
  • Monitor SQL Server performance counters
  • Analyze wait statistics and resource usage

Connection Pool Exhaustion

  • Monitor active connection count
  • Review connection pool configuration
  • Optimize connection lifecycle management
  • Implement connection retry policies

Advanced Configuration​

Always On Availability Groups​

For SQL Server Always On deployments:

  • Configure read-only routing for monitoring queries
  • Set up connection strings for listener endpoints
  • Implement automatic failover handling
  • Monitor replica synchronization status

Azure SQL Advanced Features​

  • Configure geo-replication for disaster recovery
  • Implement elastic pools for cost optimization
  • Use Azure SQL Insights for performance monitoring
  • Enable automatic tuning recommendations

SQL Server-Specific Features​

Temporal Tables​

  • Monitor system-versioned tables
  • Validate historical data consistency
  • Track data changes over time
  • Implement audit trail monitoring

Columnstore Indexes​

  • Optimize queries for columnstore tables
  • Monitor compression and performance benefits
  • Validate analytical workload data quality
  • Track segment elimination efficiency

In-Memory OLTP​

  • Monitor memory-optimized tables
  • Validate transaction consistency
  • Track in-memory performance metrics
  • Handle durability settings appropriately