SQL Server Commands Reference¶
Overview¶
This page provides comprehensive reference documentation for SQL Server-specific commands in the Snowflake Data Validation CLI. For Teradata commands, see Teradata Commands Reference. For Amazon Redshift commands, see Redshift Commands Reference. For Snowflake-to-Snowflake commands, see Snowflake Commands Reference.
Command Structure¶
All SQL Server commands follow this consistent structure:
Where <command> is one of:
run-validation- Run synchronous validationrun-async-validation- Run asynchronous validationgenerate-validation-scripts- Generate validation scriptsget-configuration-files- Get configuration templatesauto-generated-configuration-file- Interactive config generationrow-partitioning-helper- Interactive row partitioning configurationcolumn-partitioning-helper- Interactive column partitioning configuration
Run Synchronous Validation¶
Validates data between SQL Server and Snowflake in real-time.
Syntax¶
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¶
Use Cases¶
Real-time validation during migration
Pre-cutover validation checks
Post-migration verification
Continuous validation in CI/CD pipelines
Run Asynchronous Validation¶
Performs validation using pre-generated metadata files without connecting to databases.
Syntax¶
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¶
Prerequisites¶
Before running async validation:
Generate validation scripts using
generate-validation-scriptsExecute the generated scripts on source and target databases
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
Generate Validation Scripts¶
Generates SQL scripts for extracting metadata that can be executed separately.
Syntax¶
Options¶
--data-validation-config-file, -dvf (required)
Type: String (path)
Description: Path to YAML configuration file
Example Usage¶
Output¶
The command generates SQL scripts in the output directory configured in your YAML file:
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
Get Configuration Templates¶
Retrieves example configuration files and optional query templates.
Syntax¶
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¶
Output Files¶
Without --query-templates flag:
With --query-templates flag:
Use Cases¶
Starting a new validation project
Learning configuration options
Customizing validation queries for specific needs
Creating organization-specific templates
Auto-Generate Configuration File¶
Interactive command to generate a configuration file by prompting for connection parameters.
Syntax¶
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:
SQL Server host
Hostname or IP address of SQL Server
Example:
sqlserver.company.com
SQL Server port (default: 1433)
Port number for SQL Server connection
Press Enter to accept default
SQL Server username
Authentication username
Example:
migration_user
SQL Server password
Authentication password (hidden input)
Not displayed on screen for security
SQL Server database
Name of the database to validate
Example:
production_db
SQL Server schema
Schema name within the database
Example:
dbo
Trust server certificate (default: no)
Options: yes/no
Set to “yes” for self-signed certificates
Encrypt connection (default: yes)
Options: yes/no/optional
Controls SSL/TLS encryption
Output path for configuration file
Where to save the generated YAML file
Example:
./configs/my_validation.yaml
Example Session¶
Generated Configuration¶
The command generates a basic YAML configuration file:
Next Steps After Generation¶
Edit the configuration file to add:
Target connection details
Tables to validate
Validation options
Column selections and mappings
Review security settings:
Consider using environment variables for passwords
Update trust certificate and encryption settings as needed
Add table configurations:
Specify fully qualified table names
Configure column selections
Set up filtering where clauses
Test the configuration:
Use Cases¶
Quick setup for new users
Generating baseline configurations
Testing connectivity during setup
Creating template configurations for teams
Row Partitioning Helper¶
Interactive command to generate partitioned table configurations for large tables. This helper divides tables into smaller row partitions based on a specified column, enabling more efficient validation of large datasets.
Syntax¶
Options¶
This command has no command-line options. All input is provided through interactive prompts.
How It Works¶
The table partitioning helper:
Reads an existing configuration file with table definitions
For each table, prompts whether to apply partitioning
If partitioning is enabled, collects partition parameters
Queries the source database to determine partition boundaries
Generates new table configurations with
WHEREclauses for each partitionSaves the partitioned configuration to a new file
Interactive Prompts¶
The command will prompt for the following information:
Configuration file path
Path to existing YAML configuration file
Example:
./configs/sqlserver_validation.yaml
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¶
Generated Output¶
The command generates partitioned table configurations with WHERE clauses:
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¶
Choose appropriate partition columns:
Use indexed columns for better query performance
Prefer columns with sequential values (IDs, timestamps)
Avoid columns with highly skewed distributions
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)
String vs numeric columns:
Numeric columns are generally more efficient
String columns work but may have uneven distribution
After partitioning:
Review generated WHERE clauses
Adjust partition boundaries if needed
Test with a subset before full validation
Column Partitioning Helper¶
Interactive command to generate partitioned table configurations for wide tables with many columns. This helper divides tables into smaller column partitions, enabling more efficient validation of tables with a large number of columns.
Syntax¶
Options¶
This command has no command-line options. All input is provided through interactive prompts.
How It Works¶
The column partitioning helper:
Reads an existing configuration file with table definitions
For each table, prompts whether to apply column partitioning
If partitioning is enabled, collects the number of partitions
Queries the source database to retrieve all column names for the table
Divides the columns into the specified number of partitions
Generates new table configurations where each partition validates only a subset of columns
Saves the partitioned configuration to a new file
Interactive Prompts¶
The command will prompt for the following information:
Configuration file path
Path to existing YAML configuration file
Example:
./configs/sqlserver_validation.yaml
For each table in the configuration:
a. Apply column partitioning? (yes/no)
Whether to partition this specific table by columns
Default: yes
b. Number of partitions (if partitioning)
How many column partitions to create
Example:
3,5,10
Example Session¶
Generated Output¶
The command generates partitioned table configurations with column subsets:
Use Cases¶
Wide table validation: Break tables with hundreds of columns into manageable chunks
Memory optimization: Reduce memory footprint by validating fewer columns at a time
Parallel processing: Enable concurrent validation of different column groups
Targeted validation: Validate specific column groups independently
Performance tuning: Optimize validation for tables with many LOB or complex columns
Best Practices¶
Determine optimal partition count:
Consider the total number of columns in the table
For tables with 50+ columns, start with 3-5 partitions
For tables with 100+ columns, consider 5-10 partitions
Column ordering:
Columns are divided alphabetically
Related columns may end up in different partitions
After partitioning:
Review generated column lists
Verify all required columns are included
Test with a subset before full validation
Combine with row partitioning:
For very large, wide tables, consider using both row and column partitioning
First partition by columns, then apply row partitioning to each column partition if needed
SQL Server Connection Configuration¶
SQL Server connections require specific configuration in the YAML file.
Connection Example¶
Connection Fields¶
mode (required)
Type: String
Valid Values:
credentialsDescription: 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:
Development Connection:
Self-Signed Certificate Connection:
Complete SQL Server Examples¶
Example 1: Basic SQL Server Validation¶
Example 2: SQL Server with Column Mappings¶
Example 3: SQL Server Large Table Optimization¶
Example 4: SQL Server View Validation¶
Validate SQL Server views alongside tables for comprehensive migration verification.
Note: View validation creates temporary tables internally to materialize view data for comparison between SQL Server and Snowflake.
Troubleshooting SQL Server Connections¶
Issue: SSL/TLS Certificate Errors¶
Symptom:
Solution:
Set trust_server_certificate to “yes”:
Issue: Connection Timeout¶
Symptom:
Solutions:
Verify the host and port:
Check firewall rules
Verify SQL Server is running and accepting connections
Test with SQL Server Management Studio (SSMS)
Issue: Authentication Failed¶
Symptom:
Solutions:
Verify credentials are correct
Check SQL Server authentication mode (mixed mode required)
Ensure user has necessary permissions:
Issue: Database Not Found¶
Symptom:
Solutions:
Verify database name is correct
Check user has access to the database:
Ensure database is online and accessible
Best Practices for SQL Server¶
Security¶
Use encrypted connections in production:
Store passwords securely:
Use environment variables
Use secret management systems
Avoid hardcoding passwords
Use read-only accounts:
Performance¶
Enable chunking for large tables:
Use WHERE clauses to filter data:
Optimize thread count:
Data Quality¶
Start with schema validation:
Add metrics validation:
Enable row validation selectively: