Overview

The xSQL Data Compare Command Line utility allows for comparing the content of SQL Server databases via the command line. With the xSQL Data Compare Command Line you can:

• Perform automated data comparison and synchronization;
• Compare and synchronize database content as part of your setup and deployment solution;
• Compare database content and execute the data synchronization script in batch files;
• Schedule the comparison via the Windows Task Scheduler or other schedulers.

Data compare options are specified via an xml configuration file. Additional command line switches are used for settings such as encoding, file location etc.

Runtime files

The following files are required to run the Command Line:

• xSQLDataCmd.exe
• xSQL.ObjectSchema.dll
• xSQL.DataCompare.dll
• xSQL.Licensing.dll
• xSQL.xSQLShared.dll
• xSQLDataCmdSchema.xsd
• C1.C1Zip.dll

Command line argument and options

The command line options control the general behavior of xSQL Data Compare Command line utility such as the mode of operation (quiet vs. verbose), file encoding, the location of the log and error file. Each option has a short and a long form. For example /q and /quiet specify the “quiet” mode which suppresses all comparison messages from being displayed on the screen or saved to the output file. The value should be separated from the option using a colon, not a space. For example /l:c:\compare.log saves all comparison messages to the output file c:\compare.log.

The data comparison options and preferences on the other hand are specified via the xml configuration file. When the same command line option exists for an xml-specified parameter, the command line value has priority over the one specified in the xml file.

The xSQL Data Compare Command Line utility supports the following argument and options:

Xml configuration file

Overview

xSQL Data Compare Command Line utility uses an Xml configuration file for all comparison options and preferences.

The Xml configuration file allows you to:

• Specify SQL Server databases to compare;
• Define the data compare options;
• Customize the comparison via the pair-of-tables, where clauses and custom keys;
• Specify the files that store the synchronization script and the comparison logs;
• Choose to log comparison errors to a Windows log or disk file;
• Choose to execute the data synchronization script.

The xml file groups the comparison parameters and options into the following sections:

A simple Xml data configuration file has the following structure:

<?xml version="1.0" encoding="utf-8" ?>
<CommandLineParameters xmlns="http://www.xsqlsoftware.com/schemas/xSQLDataCompareCmdSchema.xsd">
        <LeftDatabase>
                <SqlServer>(local)</SqlServer>
                <DatabaseName>DB_Prod</DatabaseName>
                <TrustedConnection>true</TrustedConnection>
        </LeftDatabase>
        <RightDatabase>
                <SqlServer>(local)</SqlServer>
                <DatabaseName>DB_Staging</DatabaseName>
                <TrustedConnection>true</TrustedConnection>
        </RightDatabase>
        <DataOptions>
                <Option>DefaultOptions</Option>
        </DataOptions>
        <CommandLineSettings>             
                <DataScriptFile>DataScript.sql</DataScriptFile>
                <DataLogFile>DataLog.txt</DataLogFile>
                <ErrorLogName>DataEror.txt</ErrorLogName>
                <CompareData Direction="RightAsLeft"  Synchronize="false"></CompareData>
        </CommandLineSettings>
</CommandLineParameters>
      

The <LeftDatabase> and <RightDatabase> elements define the database on the “left” and the “right” side of the comparison respectively.
In this example these are the databases DB_Prod and DB_Staging located on the (local) SQL Server. The connection to SQL Server is Trusted Connection.
<DataOptionTypes> element specifies the data comparison options. The value DefaultOptions indicates the default data compare options.
<CommandLineSettings> element contains:

The following command invokes the xSQL Data Compare Command Line with this Xml configuration file (assuming that the Xml file is named simpleXml.xml)

        xSQLDataCmd simpleXml.xml
      

Customizing data comparison

Data comparison uses the concept of pair-of-tables to perform database content comparison and synchronization. A pair-of-table is an entity that maps together two tables identified as comparable from the data point along with two unique keys, one for each table. The tables are mapped based on their name equality; for example the table A on left database is paired with table A on the right database. The unique key on each of the mapped tables is selected by evaluating the primary key first, then unique constraints and unique indexes, whichever is found first. Keys are selected in such a way that they are compatible. Data compare requires that each pair of table has a selected key; if not the pair cannot be compared.

The Command Line utility allows for customization of the data comparison by performing the following:

• Defining custom mappings between tables;
• Including or excluding pair-of-tables from the comparison;
• Choosing the data keys for a specific pair;
• Specifying custom keys for pair of tables that don’t have one;
• Including or excluding specific columns from the comparisons;
• Defining “where” clauses that can be used to filter the data.

The <TablePairs> section of the Xml configuration file provides the necessary elements and attributes to achieve these customizations. The <TablePairs> section contains one or multiple <TablePair> elements. Normally you would have one <TablePair> element for every pair-of-table entity that requires customization.

Depending on the value of the TableMappingSchemaTypes element defined under the CommandLineSettings section, the Command Line chooses to perform one of the following actions when encounters custom-defined pairs:

• If the value of TableMappingSchemaTypes is CustomOnly, the Command Line compares only the specified pairs and ignores all other tables which don't have a custom pair defined in the Xml configuration file.
• If the TableMappingSchemaTypes value is Mix, the custom pairs override the default pairs. The command line performs automatic paring for the tables that don't have a custom pair defined and compares them.

A <TablePairs> defined in the Xml file might contain the following elements:

Note: The Command Line always performs pair validation before comparing the data. The validation guarantees that the pairs meet the criteria required for comparing them such as the data compatibly between columns, the existence of a unique key etc. An invalid pair, whether created automatically or custom-defined, is always excluded from the comparison.

The following examples illustrate some common customizations that can be performed with pair-of tables.

1. Excluding a pair-of-table from the data comparison
This Xml fragment excludes the pair that contains Author table from the data comparison. Both the left and the right table are required to be specified even when they have the same name:

<TablePair Action="Exclude">
<LeftTable Name="dbo.Authors"></LeftTable>
<RightTable Name="dbo.Authors"></RightTable>
</TablePair>
      

2. Creating a custom key
This Xml fragment creates a custom key for the pair that contains the table Customer. The key is named CompanyAndCity and contains two columns: CompanyName and City. The key is specified for each table in the pair.

<TablePair Action="Include">
    <LeftTable Name="dbo.Customers">
        <Key Name="CompanyAndCity">
            <Column>CompanyName</Column>
            <Column>City</Column>
        </Key>
    </LeftTable>
    <RightTable Name="dbo.Customers">
        <Key Name="CompanyAndCity">
            <Column>CompanyName</Column>
            <Column>City</Column>
        </Key>
    </RightTable>
</TablePair>
      

3. Filtering the data with the “where” clause
This Xml fragment uses a where clause to filter that data that will compared between two tables in a pair. Remember NOT to include the keyword “where” as part of the clause.

<TablePair Action="Include">
<LeftTable Name="dbo.Customers"></LeftTable>
    <RightTable Name="dbo.Customers"></RightTable>
    <WhereClause>Country='USA'</WhereClause>
</TablePair>        
      

4. Excluding columns
The following Xml fragment excludes the Country column from the pair that contains Customer table. The other columns mapped between the two tables have been listed here for illustration only; by default they are included.

<TablePair Action="Include">
    <LeftTable Name="dbo.Customers"></LeftTable>
    <RightTable Name="dbo.Customers"></RightTable>
        <Columns>
            <ColumnPair Action="Exclude">
                <LeftColumn>Country</LeftColumn>
                <RightColumn>Country</RightColumn>
            </ColumnPair>
            <ColumnPair>
                <LeftColumn>CustomerID</LeftColumn>
                <RightColumn>CustomerID</RightColumn>
            </ColumnPair>
            <ColumnPair>
                <LeftColumn>CompanyName</LeftColumn>
                <RightColumn>CompanyName</RightColumn>
            </ColumnPair>
            <ColumnPair>
                <LeftColumn>City</LeftColumn>
                <RightColumn>City</RightColumn>
            </ColumnPair>
            <ColumnPair>
                <LeftColumn>Region</LeftColumn>
                <RightColumn>Region</RightColumn>
            </ColumnPair>
        </Columns>
    </TablePair>
</TablePairs>       
      

Examples

xSQL Data Compare Command Line sample Xml files are located in the \Samples\CommandLines\DataCmd subfolder under the main installation folder of xSQL Bundle.

Xml Configuration File Schema

<CommandLineParameters>
    <LeftDatabase>
        <SqlServer>
        <DatabaseName>
        <Username>
        <Password>
        <TrustedConnection>
        <ConnectionTimeout>
        <CommandTimeout>
        <SnapshotFile>
        <CreateSnapshot>
    </LeftDatabase>
    <RightDatabase>
        <SqlServer>
        <DatabaseName>
        <Username>
        <Password>
        <TrustedConnection>
        <ConnectionTimeout>
        <CommandTimeout>
        <SnapshotFile>
        <CreateSnapshot>
    </RightDatabase>
    <DataOptionTypes>
        <Option>
    </DataOptionTypes>
    <TablePairs>
        <TablePair Action>
            <LeftTable Name>
                <Key Name>
                    <Column>
                </Key>
            </LeftTable>
            <RightTable Name>
                <Key Name>
                    <Column>
                </Key>
            </RightTable>
            <WhereClause>
            <Columns>
                <ColumnPair Action>
                    <LeftColumn>
                    <RightColumn>
                </ColumnPair>
            </Columns>
        </TablePair>
    </TablePairs> 
    <CommandLineSettings>
        <LogFile>
        <ErrorFile>
        <DataScriptFile>
        <DataLogFile>
        <FileEncoding>
        <CompareData Direction Synchronize>
    </CommandLineSettings>
</ CommandLineParameters >  
      

Error Codes

The Command Line utility returns the following error codes:

---