Thursday, November 3, 2016

SQL Data Compare Command Line Wizard - Automate Data synchronization in minutes


xSQL Data Compare V9 comes with a Data Compare Command Line Wizard that makes automating the data synchronization process a breeze:

  1. Run the xSQL Data Compare Command Line Wizard which you will find under All Programs / xSQL Software;
  2. Follow the intuitive configuration steps to completion (you will end up with an xml config file); You can choose whether the database synchronization script will be executed or not. 
  3. Create a simple batch file that calls "xSQLDataCmd.exe YourXMLConfi,xml" and does whatever else you might need it to do like archiving old sync scripts etc. 
  4. Schedule a job that executes the batch file daily or however often you might want to. 

What do you need this for? There are multiple scenarios where this would come in handy:
- synchronizing your web database with your internal databases, like updating inventories, updating orders, updating order status, updating customer lists etc.
- synchronizing your branch office databases with your central repository;
- restoring a particular table or column(s) in case of a mishap.
- etc.

Best of all: the xSQL Data Compare is completely free for SQL Server Express, no strings attached. That may change in the future but for now enjoy using it!

6 comments:

  1. I am trying to use command line option for comparison tool. Can you please tell me how to export the results in XML/JSON file? In my case two DBs can be same or different. I want to get that result in some file.

    Atul

    ReplyDelete
    Replies
    1. Hello Atul, currently there is no option to export the comparison results in XML/JSON - the only option is to save the synchronization script. You can, if you wish, configure the command line to only compare the databases and generate the synchronization script but not execute the script. You can review the script to see where the differences are.

      Delete
  2. Thanks for your response, I have following more queries, appreciate your response.

    1. I use the UI wizard -> run the compare result. In the "Comparison Result" window when I select any row then I get details about the result, it shows Left rows/Right Rows & different Rows in detail windows. I was playing with the tool & that detail windows just disappeared, Can you please tell me how to bring back that detailed window?

    2. Can you please tell me on what basis it compares the rows in two table. My use case I want to compare based on predefined attributes/columns only, not on the basis of all column values, i.e. treat the rows as equal even if the value(s) of some column(s) are different. In the nutshell I am looking for some functionality to exclude some columns while comparing rows..

    To do that I tried this, I selected the option in the mapped objects (of the table) --> mapped columns, unchecked the unwanted columns, also defined the custom key on which I do not want comparison, it still shows results in two tables as different.

    Can you please tell me how to do that?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. @Atul - please see the reply as a separate comment below - we mistakenly posted it as a separate comment instead of as a reply to your question so you may not get a notification.

      Delete
  3. @Atul - there are two possibilities for the comparison results tabs to disappear. If there are no differences between the left and the right table then there will be nothing to show on the details tabs for that table pair so the tabs will not appear at all. The other possibility is that you go into the Comparison Options and uncheck all three options: "synchronize rows that are different", "synchronize rows that exist on the left database only", and "synchronize rows that exist on the right database only.

    Regarding your use case: first, the rows are paired based on the selected comparison key. By default if a primary key exists then the xSQL Data Compare will select that as the comparison key but you can choose your own comparison key. Please note, you are choosing the key based on which you want the rows to be paired. Then, on the "Mapped Columns" tab for that table pair you can uncheck the columns you do not wish to compare - those columns will be completely ignored during the comparison and synchronization operation as if they don't exist (do not uncheck the column(s) you have selected as the comparison key).

    Let us know if you still have trouble with this.



    ReplyDelete