SQL Server Commands Reference¶

Syntax¶

snowflake-data-validation sqlserver run-validation \
  --data-validation-config-file /path/to/config.yaml \
  --log-level INFO

Options¶

--data-validation-config-file, -dvf (required)

• Type: String (path)

• Description: Path to YAML configuration file containing validation settings

• Example: --data-validation-config-file ./configs/sqlserver_validation.yaml

--log-level, -ll (optional)

• Type: String

• Valid Values: DEBUG, INFO, WARNING, ERROR, CRITICAL

• Default: INFO

• Description: Logging level for validation execution

• Example: --log-level DEBUG

Example Usage¶

# Basic validation
sdv sqlserver run-validation \
  --data-validation-config-file ./configs/sqlserver_validation.yaml

# Validation with debug logging
sdv sqlserver run-validation \
  --data-validation-config-file ./configs/sqlserver_validation.yaml \
  --log-level DEBUG

# Using full command name
snowflake-data-validation sqlserver run-validation \
  -dvf /opt/validations/prod_config.yaml \
  -ll INFO

Use Cases¶

• Real-time validation during migration

• Pre-cutover validation checks

• Post-migration verification

• Continuous validation in CI/CD pipelines

Syntax¶

snowflake-data-validation sqlserver run-async-validation \
  --data-validation-config-file /path/to/config.yaml

Options¶

--data-validation-config-file, -dvf (required)

• Type: String (path)

• Description: Path to YAML configuration file

• Note: Configuration must specify paths to pre-generated metadata files

Example Usage¶

# Run async validation
sdv sqlserver run-async-validation \
  --data-validation-config-file ./configs/async_validation.yaml

# Using full command name
snowflake-data-validation sqlserver run-async-validation \
  -dvf /data/validations/async_config.yaml

Prerequisites¶

Before running async validation:

1. Generate validation scripts using generate-validation-scripts

2. Execute the generated scripts on source and target databases

3. Ensure metadata files are available in the configured paths

Use Cases¶

• Validating in environments with restricted database access

• Separating metadata extraction from validation

• Batch validation workflows

• Scheduled validation jobs

Syntax¶

snowflake-data-validation sqlserver generate-validation-scripts \
  --data-validation-config-file /path/to/config.yaml

Options¶

--data-validation-config-file, -dvf (required)

• Type: String (path)

• Description: Path to YAML configuration file

Example Usage¶

# Generate scripts
sdv sqlserver generate-validation-scripts \
  --data-validation-config-file ./configs/validation.yaml

# Using full command name
snowflake-data-validation sqlserver generate-validation-scripts \
  -dvf /opt/configs/script_generation.yaml

Output¶

The command generates SQL scripts in the output directory configured in your YAML file:

<output_directory>/
├── source_schema_queries.sql
├── source_metrics_queries.sql
├── source_row_queries.sql
├── target_schema_queries.sql
├── target_metrics_queries.sql
└── target_row_queries.sql

Use Cases¶

• Generating scripts for execution by DBAs

• Compliance requirements for query review

• Environments where direct CLI database access is restricted

• Manual execution and validation workflows

Syntax¶

snowflake-data-validation sqlserver get-configuration-files \
  --templates-directory ./my-templates \
  --query-templates

Options¶

--templates-directory, -td (optional)

• Type: String (path)

• Default: Current directory

• Description: Directory to save template files

• Example: --templates-directory ./templates

--query-templates (optional)

• Type: Flag (no value required)

• Description: Include J2 (Jinja2) query template files for advanced customization

• Example: --query-templates

Example Usage¶

# Get basic templates in current directory
sdv sqlserver get-configuration-files

# Save templates to specific directory
sdv sqlserver get-configuration-files \
  --templates-directory ./my-project/templates

# Include query templates for customization
sdv sqlserver get-configuration-files \
  --templates-directory ./templates \
  --query-templates

# Using short flags
sdv sqlserver get-configuration-files -td ./templates --query-templates

Output Files¶

Without --query-templates flag:

<templates_directory>/
└── sqlserver_validation_template.yaml

With --query-templates flag:

<templates_directory>/
├── sqlserver_validation_template.yaml
└── query_templates/
    ├── sqlserver_columns_metrics_query.sql.j2
    ├── sqlserver_row_count_query.sql.j2
    ├── sqlserver_compute_md5_sql.j2
    └── snowflake_columns_metrics_query.sql.j2

Use Cases¶

• Starting a new validation project

• Learning configuration options

• Customizing validation queries for specific needs

• Creating organization-specific templates

Syntax¶

snowflake-data-validation sqlserver auto-generated-configuration-file

Options¶

This command has no command-line options. All input is provided through interactive prompts.

Interactive Prompts¶

The command will prompt for the following information:

1. SQL Server host

   • Hostname or IP address of SQL Server

   • Example: sqlserver.company.com

2. SQL Server port (default: 1433)

   • Port number for SQL Server connection

   • Press Enter to accept default

3. SQL Server username

   • Authentication username

   • Example: migration_user

4. SQL Server password

   • Authentication password (hidden input)

   • Not displayed on screen for security

5. SQL Server database

   • Name of the database to validate

   • Example: production_db

6. SQL Server schema

   • Schema name within the database

   • Example: dbo

7. Trust server certificate (default: no)

   • Options: yes/no

   • Set to “yes” for self-signed certificates

8. Encrypt connection (default: yes)

   • Options: yes/no/optional

   • Controls SSL/TLS encryption

9. Output path for configuration file

   • Where to save the generated YAML file

   • Example: ./configs/my_validation.yaml

Example Session¶

$ sdv sqlserver auto-generated-configuration-file

SQL Server host: sqlserver.company.com
SQL Server port [1433]: 
SQL Server username: migration_user
SQL Server password: ********
SQL Server database: production_db
SQL Server schema: dbo
Trust server certificate (yes/no) [no]: no
Encrypt connection (yes/no/optional) [yes]: yes
Output path for configuration file: ./configs/sqlserver_config.yaml

Configuration file generated successfully: ./configs/sqlserver_config.yaml

Generated Configuration¶

The command generates a basic YAML configuration file:

source_platform: SqlServer
target_platform: Snowflake
output_directory_path: ./validation_results

source_connection:
  mode: credentials
  host: sqlserver.company.com
  port: 1433
  username: migration_user
  password: "<hidden>"
  database: production_db
  trust_server_certificate: "no"
  encrypt: "yes"

target_connection:
  mode: default

validation_configuration:
  schema_validation: true
  metrics_validation: true
  row_validation: false

tables: []

Next Steps After Generation¶

1. Edit the configuration file to add:

   • Target connection details

   • Tables to validate

   • Validation options

   • Column selections and mappings

2. Review security settings:

   • Consider using environment variables for passwords

   • Update trust certificate and encryption settings as needed

3. Add table configurations:

   • Specify fully qualified table names

   • Configure column selections

   • Set up filtering where clauses

4. Test the configuration:

   sdv sqlserver run-validation \
     --data-validation-config-file ./configs/sqlserver_config.yaml
   

   Copy

Use Cases¶

• Quick setup for new users

• Generating baseline configurations

• Testing connectivity during setup

• Creating template configurations for teams

Syntax¶

snowflake-data-validation sqlserver table-partitioning-helper

Options¶

This command has no command-line options. All input is provided through interactive prompts.

How It Works¶

The table partitioning helper:

1. Reads an existing configuration file with table definitions

2. For each table, prompts whether to apply partitioning

3. If partitioning is enabled, collects partition parameters

4. Queries the source database to determine partition boundaries

5. Generates new table configurations with WHERE clauses for each partition

6. Saves the partitioned configuration to a new file

Interactive Prompts¶

The command will prompt for the following information:

1. Configuration file path

   • Path to existing YAML configuration file

   • Example: ./configs/sqlserver_validation.yaml

2. For each table in the configuration:

   a. Apply partitioning? (yes/no)

   • Whether to partition this specific table

   • Default: yes

   b. Partition column (if partitioning)

   • Column name used to divide the table

   • Should be indexed for performance

   • Example: transaction_id, created_date

   c. Is partition column a string type? (yes/no)

   • Determines quoting in generated WHERE clauses

   • Default: no (numeric)

   d. Number of partitions

   • How many partitions to create

   • Example: 10, 50, 100

Example Session¶

$ sdv sqlserver table-partitioning-helper

Generate a configuration file for SQL Server table partitioning. This interactive 
helper function processes each table in the configuration file, allowing users to 
either skip partitioning or specify partitioning parameters for each table.

Configuration file path: ./configs/sqlserver_validation.yaml

Apply partitioning for production_db.dbo.fact_sales? [Y/n]: y
Write the partition column for production_db.dbo.fact_sales: sale_id
Is 'sale_id' column a string type? [y/N]: n
Write the number of partitions for production_db.dbo.fact_sales: 10

Apply partitioning for production_db.dbo.dim_customer? [Y/n]: n

Apply partitioning for production_db.dbo.transactions? [Y/n]: y
Write the partition column for production_db.dbo.transactions: transaction_date
Is 'transaction_date' column a string type? [y/N]: n
Write the number of partitions for production_db.dbo.transactions: 5

Table partitioning configuration file generated successfully!

Generated Output¶

The command generates partitioned table configurations with WHERE clauses:

tables:
  # Original table partitioned into 10 segments
  - fully_qualified_name: production_db.dbo.fact_sales
    where_clause: "sale_id >= 1 AND sale_id < 100000"
    target_where_clause: "sale_id >= 1 AND sale_id < 100000"
    # ... other table settings preserved

  - fully_qualified_name: production_db.dbo.fact_sales
    where_clause: "sale_id >= 100000 AND sale_id < 200000"
    target_where_clause: "sale_id >= 100000 AND sale_id < 200000"
    # ... continues for each partition

  # Non-partitioned table preserved as-is
  - fully_qualified_name: production_db.dbo.dim_customer
    # ... original configuration

Use Cases¶

• Large table validation: Break multi-billion row tables into manageable chunks

• Parallel processing: Enable concurrent validation of different partitions

• Memory optimization: Reduce memory footprint by processing smaller data segments

• Incremental validation: Validate specific data ranges independently

• Performance tuning: Optimize validation for tables with uneven data distribution

Best Practices¶

1. Choose appropriate partition columns:

   • Use indexed columns for better query performance

   • Prefer columns with sequential values (IDs, timestamps)

   • Avoid columns with highly skewed distributions

2. Determine optimal partition count:

   • Consider table size and available resources

   • Start with 10-20 partitions for tables with 10M+ rows

   • Increase partitions for very large tables (100M+ rows)

3. String vs numeric columns:

   • Numeric columns are generally more efficient

   • String columns work but may have uneven distribution

4. After partitioning:

   • Review generated WHERE clauses

   • Adjust partition boundaries if needed

   • Test with a subset before full validation

Connection Example¶

source_connection:
  mode: credentials
  host: "sqlserver.company.com"
  port: 1433
  username: "sqlserver_user"
  password: "secure_password"
  database: "source_database"
  trust_server_certificate: "no"
  encrypt: "yes"

Connection Fields¶

mode (required)

• Type: String

• Valid Values: credentials

• Description: Connection mode for SQL Server

host (required)

• Type: String

• Description: SQL Server hostname or IP address

• Examples:

  • "sqlserver.company.com"

  • "192.168.1.100"

  • "sql-prod-01.internal.company.net"

port (required)

• Type: Integer

• Default: 1433

• Description: SQL Server port number

• Common Values:

  • 1433 (default)

  • 1434 (SQL Server Browser)

username (required)

• Type: String

• Description: SQL Server authentication username

• Example: "migration_admin"

password (required)

• Type: String

• Description: SQL Server authentication password

• Security Note: Consider using environment variables

database (required)

• Type: String

• Description: SQL Server database name

• Example: "production_database"

trust_server_certificate (optional)

• Type: String

• Valid Values: "yes", "no"

• Default: "no"

• Description: Whether to trust the server certificate for SSL/TLS connections

• Use Case: Set to “yes” for self-signed certificates

encrypt (optional)

• Type: String

• Valid Values: "yes", "no", "optional"

• Default: "yes"

• Description: Connection encryption setting

• Recommendations:

  • Use “yes” for production

  • Use “optional” for development/testing

  • Use “no” only in secure internal networks

Connection Examples¶

Production Connection with SSL/TLS:

source_connection:
  mode: credentials
  host: "sql-prod.company.com"
  port: 1433
  username: "prod_reader"
  password: "${SQL_SERVER_PASSWORD}"  # From environment variable
  database: "production_db"
  trust_server_certificate: "no"
  encrypt: "yes"

Development Connection:

source_connection:
  mode: credentials
  host: "localhost"
  port: 1433
  username: "dev_user"
  password: "dev_password"
  database: "dev_database"
  trust_server_certificate: "yes"
  encrypt: "optional"

Self-Signed Certificate Connection:

source_connection:
  mode: credentials
  host: "internal-sql.company.local"
  port: 1433
  username: "internal_user"
  password: "secure_password"
  database: "internal_db"
  trust_server_certificate: "yes"  # Required for self-signed certs
  encrypt: "yes"

Example 1: Basic SQL Server Validation¶

# Global configuration
source_platform: SqlServer
target_platform: Snowflake
output_directory_path: ./validation_results
max_threads: auto

# Source connection
source_connection:
  mode: credentials
  host: sqlserver.company.com
  port: 1433
  username: sql_user
  password: sql_password
  database: production_db
  trust_server_certificate: "no"
  encrypt: "yes"

# Target connection
target_connection:
  mode: name
  name: snowflake_prod

# Validation configuration
validation_configuration:
  schema_validation: true
  metrics_validation: true
  row_validation: false

# Tables to validate
tables:
  - fully_qualified_name: production_db.dbo.customers
    use_column_selection_as_exclude_list: false
    column_selection_list: []
    index_column_list:
      - customer_id

  - fully_qualified_name: production_db.dbo.orders
    use_column_selection_as_exclude_list: true
    column_selection_list:
      - internal_notes
      - audit_log
    where_clause: "order_date >= '2024-01-01'"
    target_where_clause: "order_date >= '2024-01-01'"

Example 2: SQL Server with Column Mappings¶

source_platform: SqlServer
target_platform: Snowflake
output_directory_path: /opt/validation/sqlserver
max_threads: 16

source_connection:
  mode: credentials
  host: sql-prod.company.com
  port: 1433
  username: migration_user
  password: secure_password
  database: legacy_db
  trust_server_certificate: "no"
  encrypt: "yes"

target_connection:
  mode: default

validation_configuration:
  schema_validation: true
  metrics_validation: true
  row_validation: true
  max_failed_rows_number: 100

comparison_configuration:
  tolerance: 0.01

tables:
  - fully_qualified_name: legacy_db.dbo.customer_master
    use_column_selection_as_exclude_list: false
    column_selection_list:
      - cust_id
      - cust_name
      - email_addr
      - phone_num
    column_mappings:
      cust_id: customer_id
      cust_name: customer_name
      email_addr: email
      phone_num: phone
    index_column_list:
      - cust_id
    chunk_number: 20

Example 3: SQL Server Large Table Optimization¶

source_platform: SqlServer
target_platform: Snowflake
output_directory_path: ./large_table_validation
max_threads: 32

source_connection:
  mode: credentials
  host: bigdata-sql.company.com
  port: 1433
  username: bigdata_reader
  password: readonly_password
  database: analytics_db
  trust_server_certificate: "no"
  encrypt: "yes"

target_connection:
  mode: name
  name: snowflake_analytics

validation_configuration:
  schema_validation: true
  metrics_validation: true
  row_validation: true
  max_failed_rows_number: 500
  exclude_metrics: false

comparison_configuration:
  tolerance: 0.005

tables:
  - fully_qualified_name: analytics_db.dbo.fact_transactions
    use_column_selection_as_exclude_list: true
    column_selection_list:
      - large_blob_column
      - xml_metadata
    index_column_list:
      - transaction_id
    chunk_number: 100
    where_clause: "transaction_date >= '2024-01-01' AND amount > 0"
    target_where_clause: "transaction_date >= '2024-01-01' AND amount > 0"
    max_failed_rows_number: 1000

Issue: SSL/TLS Certificate Errors¶

Symptom:

SSL certificate verification failed

Solution:

Set trust_server_certificate to “yes”:

source_connection:
  trust_server_certificate: "yes"
  encrypt: "yes"

Issue: Connection Timeout¶

Symptom:

Connection timeout: Unable to connect to SQL Server

Solutions:

1. Verify the host and port:

   telnet sqlserver.company.com 1433
   

   Copy

2. Check firewall rules

3. Verify SQL Server is running and accepting connections

4. Test with SQL Server Management Studio (SSMS)

Issue: Authentication Failed¶

Symptom:

Login failed for user 'username'

Solutions:

1. Verify credentials are correct

2. Check SQL Server authentication mode (mixed mode required)

3. Ensure user has necessary permissions:

   -- Grant read permissions
   GRANT SELECT ON SCHEMA::dbo TO migration_user;
   GRANT VIEW DEFINITION ON SCHEMA::dbo TO migration_user;
   

   Copy

Issue: Database Not Found¶

Symptom:

Cannot open database "database_name"

Solutions:

1. Verify database name is correct

2. Check user has access to the database:

   USE database_name;
   SELECT * FROM sys.tables;
   

   Copy

3. Ensure database is online and accessible

Security¶

1. Use encrypted connections in production:

   source_connection:
     encrypt: "yes"
     trust_server_certificate: "no"
   

   Copy

2. Store passwords securely:

   • Use environment variables

   • Use secret management systems

   • Avoid hardcoding passwords

3. Use read-only accounts:

   CREATE USER migration_reader WITH PASSWORD = 'secure_password';
   GRANT SELECT ON SCHEMA::dbo TO migration_reader;
   

   Copy

Performance¶

1. Enable chunking for large tables:

   tables:
     - fully_qualified_name: large_table
       chunk_number: 50
   

   Copy

2. Use WHERE clauses to filter data:

   tables:
     - fully_qualified_name: transactions
       where_clause: "date >= '2024-01-01'"
   

   Copy

3. Optimize thread count:

   max_threads: 16  # Adjust based on server capacity
   

   Copy

Data Quality¶

1. Start with schema validation:

   validation_configuration:
     schema_validation: true
     metrics_validation: false
     row_validation: false
   

   Copy

2. Add metrics validation:

   validation_configuration:
     schema_validation: true
     metrics_validation: true
     row_validation: false
   

   Copy

3. Enable row validation selectively:

   validation_configuration:
     row_validation: true
   
   tables:
     - fully_qualified_name: critical_table
       # Row validation enabled for this table
   

   Copy