xSQL Data Compare for SQL Server - new build available

We just published a new build of the xSQL Data Compare for SQL Server. Following is the list of enhancements and fixes included in this build:

1. Allows session naming by clicking on the name without launching it
2. Adds tooltip on session panel that shows the left/right databases, useful when the names of Sql Server/Database are too long and can't be fully seen in the UI
3. Fixes an issue with screen resizing
4. Fixes an issue with print wrapping for windows that support printing.
5. Adds a UI formatter for XML data types
6. Improves the process of comparing select tables

You can download the new build from http://www.xsql.com/download/sql_server_data_compare/

Oracle Data Compare released

We have just released a new tool, Oracle Data Compare. The same awesome (to quote a user that called recently) data comparison and synchronization tool, many of you have become accustomed to, is now available for Oracle. You can learn more about the product here, and download a fully functional trial version from here.

Please note that from now until the end of October you can get a license for the low introductory price of $174 - use promo code ORACLEPROMO on checkout.

A big thank you goes to all the beta testers of Oracle Data Compare who gave us a tremendous amount of help during those last crucial months in the product development - we are grateful for all you have done!

How to deploy a SQL Server database to a remote host

CASE 1: you have direct access to both the SQL Server where the source database is and the SQL Server where the target database is.

1. First time deployment
• Backup / restore
1. Backup the database on the source
2. Copy the backup file to the target machine
3. Restore the database on the target
4. Create logins and set permissions as needed
• Compare and Synchronize
1. Create database on the target machine (blank)
2. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
3. xSQL Data Compare to populate the remote database with whatever data you might have on the source that you want to publish (lookup tables etc.)
2. Database exists in the target server
• Compare and Synchronize
1. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
2. Use xSQL Data Compare to push any data you need to push from the source to the target. Caution: be careful not to affect any data that exists on the target already.

CASE 2: You can not directly access the target server but you have a way to deploy SQL scripts on that server. As is indeed the case in most scenarios you also should have a way to get a backup of your database from that remote host. In this case follow those simple steps:

1. Restore the remote database on your local environment
2. Use xSQL Object to compare your source database with the restored database. Generate the schema synchronization script and save it.
3. Use xSQL Data Compare to compare your source database with the restored database. Carefully make your selections to ensure you push only the data you want to push from the source to the target. Generate the data synchronization script and save it. 
4. Deploy your schema synchronization script to the target machine. 
5. Deploy your data synchronization script to the target machine.

Both xSQL Object and xSQL Data Compare are completely free for SQL Server Express with no restrictions or limitations. Furthermore, for other editions of SQL Server the tools are free if the database has under a certain number of objects in it (current limitations are listed here).

Split String SQL function

If you work with SQL Server you have, or at some point will, run into a situation where you have a string of separated values that you may need to involve in a join or simply generate a list out of. So, you need to split the string and dump the values in a table. Following is a simple Table-valued function that takes a string and a divider as parameters and returns a table containing the values into a list form (one value for each row). The parameters are defined as a varchar(1024) for the string of values and char(1) for the divider but you can change those based on your needs.

CREATE FUNCTION SplitString
(
    @SeparatedValues VARCHAR(1024),
    @Divider CHAR(1)
)
RETURNS    @ListOfValues TABLE ([value] VARCHAR(50))
AS  BEGIN
      DECLARE @DividerPos1 int, @DividerPos2 int
      SET @DividerPos1 = 1
      SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, 0)

     WHILE @DividerPos2 > 0
           BEGIN
                  INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, @DividerPos2 - @DividerPos1))
                  SET @DividerPos1 = @DividerPos2 + 1
                  SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, @DividerPos1)
            END
           -- Now get the last value if there is onw
                  IF @DividerPos1 <= LEN(@SeparatedValues)
                       INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, LEN(@SeparatedValues) - @DividerPos1 + 1))

        RETURN
  END
GO

Once you create the function you can call it like this:
   SELECT * FROM [SplitString] ('value1|value2|value3', '|')
This will return: 
   value1
   value2
   value3
  
Note that if the string starts with a divider like '|value1|value2|value3' then the first value returned will be a blank value.

Comparing 1TB database taking too long!

We got a call yesterday from a customer who was using our xSQL Data Compare to compare a 1TB database. He was concerned that the comparison was taking a very long time – when he called it had been running for about 10 hours and was still going! While we don’t think there are many users out there comparing 1TB or larger databases we thought it might be helpful to those few out there if we explained why such compare may take 10 hours or more depending on the environment. Here are the factors to consider:

1. Connection speed - in a typical scenario xSQL Data Compare is running on a client machine let’s call that Client1 and the databases being compared reside on let's say Server1 and Server2. In order to compare those databases the whole 1TB worth of data from Server1 and another 1TB worth of data from Server2 will have to be "brought" over to Client1. So you are gradually transferring 2TB of data over the wire (or over the air) and depending on how fast the connections Client1 – Server1 and Client1-Server2 are this process alone may take not just 10 but 30 or 40 hours.
2. Processing power – xSQL Data Compare running on Client1 needs to pair those millions of rows and compare them one by one. If you have a slow machine, even if the data is readily available it will take a long time to process all that data.
3. I/O and local disk speed – 2TB worth of data is being temporarily stored on the Client1 hard drive and that process alone may take a long time.

In short, when you are comparing large and very large databases don't be shocked if the process takes many hours to complete. The important thing is that, provided you have sufficient disk space on the machine where xSQL Data Compare is running, the databases will be compared successfully and you will be able to generate the synchronization script. 

Script Executor new build 3.5.1.1

A new build of Script Executor that fixes a small issue with the command line logging options is available for download here...

Script Executor is the best tool for executing SQL Scripts against multiple databases on SQL Server, MySQL and DB2.

How to execute multiple SQL scripts from command line?

How to execute multiple sql scripts from command line?

Script Executor provides one of the most efficient ways of executing SQL scripts from the command line. Let’s assume you have a long list of scripts that need to be executed routinely on a number of servers. Here is how you can do that in a few easy steps using Script Executor:

1. Launch the Script Executor user interface. Start a new project via File/New Project;
2. Add database(s) to the project;
3. Add Sql script(s);
4. Configure the mappings via Package/Configure. Mappings determine the databases that the scripts should run against. If there is only one database container and one script container, this step is not necessary;
5. Configure package options via Package/Options;
6. Save the projects and exit user interface;

Step number 4 above is where you will save a tremendous amount of time is you have a big number of scripts that need to be executed against certain databases.

Once the project has been saved it can then be executed from the command line as follows:
ExecCmd /p: /l:logging_type=1;path=

You can download a free trial version of Script Executor from here...