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...

Wrong product, wrong time!

I recently met and spent a few hours with one of our "competitors" who had dedicated over 10 months of his life to building what he thought would be the best data compare tool for SQL Server and then a lot more time trying to promote his work. Now disillusioned he was on a quest to salvage what he could from this investment. I asked him how he made the decision to develop a data compare tool for SQL Server and here is his rationale he presented:

1. I have a lot of experience with SQL server;
2. Building a tool that compares data in two SQL databases seemed like an easy thing that I could do in a couple of months so the risk wouldn’t be very big; 
3. There were a lot of data compare tools in the market so there must be a strong demand for such tool.

I asked him what went wrong and here is what he said:

1. This turned out to be a lot harder than I thought. Instead of two months I quit my job and spent 10 grueling months and I still wasn’t happy with the results.
2. Two months after the release I had gotten a handful of downloads mostly from acquaintances and zero feedback.

I tried to make him feel better by telling him that he was just unlucky but in fact luck is the last thing one can blame for a situation like this. This is the result of what plagues many of the programmers I have known over years, namely;

1. Overestimating their ability while underestimating the effort required to get something done;
2. Misjudging the market;
3. Often believing they know better than the customer what the customer really wants!

The candid conversation I had with him made me think about this. The software business is a great business if you pick the right product and introduce it at the right time but if one of those two factors is wrong then it is a terrible business. Unlike in many other businesses there is basically nothing that can be salvaged in the case of a failed software business. The World's hard drives are cluttered with billions of lines of code that have never seen the "joy" of actually "doing something for real" – they have only been "called upon" by their creator during the development. It is kind of sad!

So before you jump into developing some “me too” tool just because you think you can do it please stop and ask yourself a few questions (not an exhaustive list by any means):

1. What is my goal? What will success look like?
2. Who am I building this tool for? How will this tool help them? How much are you really willing to pay for such tool if one was available today? Don’t overestimate this! In fact whatever number you spill out first divide it by two.
3. Who are the competitors? How is the market divided between them? Which ones do you believe you can take market share from and why do you believe that?
4. Have I thoroughly investigated the competing products? What do they do right? Where do they lack?
5. How fast can I bring this product to market (whatever time you come up please double or triple it)? How likely it is that during this time one or more of the competitors will release new and improved products that may pre-empt your move?
6. What will be that “killer” feature that is going to make the customers choose your product instead of those more established competing products?
7. What if things don’t work out? What is my risk?

If you use SQL Server and wish to check out the best SQL data compare and synchronization tool in the market you can download it from here. It is free for SQL Server Express with no limitations and also free for other editions of SQL Server if the databases have a relatively small number of objects.

xSQL Comparison Bundle new build available

Just published a new build of xSQL Comparison Bundle that fixes the following:

1. Licensing issue with the Lite Edition. Our database comparison tools are currently free for SQL Server Express however, some users of the version 4 of the xSQL Comparison Bundle may have received the message "The Lite Edition does not support the databases you are trying to compare" even though the databases being compared were SQL Server Express databases.
2. Scripting issue with identity columns based on data types that support precision pr length such as numeric.

You can download the new build of xSQL Compare Bundle from: http://www.xsql.com/download/sql_server_comparison_bundle/