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/

SQL convert vertical list to horizontal

Let's say you have a table that looks something like this:
CREATE TABLE [Source_Vertical](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [GroupID] [int] NOT NULL,
 [Col1_Group] [varchar](50) NULL,
 [Col2_Group] [varchar](50) NULL,
 [Col1_Individual] [varchar](50) NULL,
 [Col2_Individual] [varchar](50) NULL)

You don't know how many rows there are for each GroupID and your job is to generate a list that contains all values associated with a given GroupID in a single row. Here is a simple way to do it:

First: create a target table that looks something like this:
CREATE TABLE [Target_Horizontal](
 [GroupID] [int] NOT NULL,
 [GroupValues] [varchar](max) NULL)

Next copy, adjust based on your needs and execute the following simple script (note I used a vertical line as a value separator - you can use whatever you need, just make sure the character you choose does not exist inside the actual values of the columns):
DECLARE @GroupID int
DECLARE @GroupID_Current int
DECLARE @Col1_Group varchar(50)
DECLARE @Col2_Group varchar(50)
DECLARE @Col1_Individual varchar(50)
DECLARE @Col2_Individual varchar(50)
DECLARE list_cursor CURSOR
    FOR SELECT * FROM dbo.Source_Vertical ORDER BY GroupID
OPEN list_cursor
FETCH NEXT FROM list_cursor INTO
 @GroupID,
 @Col1_Group,
 @Col2_Group,
 @Col1_Individual,
 @Col2_Individual
 SET @GroupID_Current = ''
WHILE @@FETCH_STATUS = 0
 BEGIN
 
  IF @GroupID_Current <> @GroupID
   BEGIN
    INSERT INTO Target_Horizontal (Id, GroupValues) VALUES(@GroupID, @Col1_Group + '|' + @Col2_Group + '|' + @Col1_Individual + '|' + @Col2_Individual)
    SET @GroupID_Current = @GroupID
   END
  ELSE
   BEGIN
    UPDATE Target_Horizontal SET GroupValues = GroupValues + '|' + @Col1_Individual + '|' + @Col2_Individual WHERE GroupID = @GroupID
   END
 
  FETCH NEXT FROM list_cursor INTO
   @GroupID,
   @Col1_Group,
   @Col2_Group,
   @Col1_Individual,
   @Col2_Individual
 END
CLOSE list_cursor;
DEALLOCATE list_cursor;

Finally you can take the results from the target table and dump them on Excel or wherever you need to.  Hope this helps someone!

No one can beat our data compare tool, no one!

Yes, this may sound a bit cocky but we are tired of being modest and polite. We don't have an army of salesman to knock on your doors and we don't own and manipulate every user group there is but we focus on making our products the best there is and making them available to you at the lowest possible price including free. So, let's state it loud and clear – no one can beat our data compare tool, no one.

1. Performance: throw in a large database and most of the competing tools will not be able to handle it at all or will take forever and a day to complete the job. Try it and see how xSQL Data Compare will knock the socks off all the alternatives.
2. Usability: everything you need, nothing you don’t, all where it should be, one click compare – nothing approaches the efficiency of this interface. 
3. Value:
1. Free for SQL Server Express – can anyone beat this?
2. Free for other editions of SQL Server when the databases being compared have a limited number of objects – anyone out there can match this?  
3. Site license option, unlimited number of installations in your organization for a very modest price – can anyone come close to this?

Dare to compare for yourself, download our data compare and evaluate it against any and all competing tools in the market. Let us know what you think.

LinkedIn – great concept but practically useless!

The other day I had a need to get introduced to an individual, let’s call her Jane Doe, who worked for company X. I searched for her on LinkedIn and was delighted to find that she was “in” my network – only one step away, directly connected to my friend “Jon”. So, I ask Jon to introduce me to Jane explaining why I wanted to talk to Jane. Jon’s response was the shocker – he said “I am really sorry but I barely know Jane (translation: I don’t really know Jane). I will send her an email but I am not sure if she will respond or not!” But, that defeats the very purpose of this media I said to myself!
Well. after this I decided to examine my contact list and to my dismay I realized that gradually a good number of individuals that I can’t remember ever meeting has made it into my list of connections! Who are those people? Why are they on my list? What does it really mean to me to have those people in my list and what does it mean to them to be on my list? I certainly can’t vouch for them nor can I rely on them for anything since I don’t know them BUT the very fact that they are on my list of direct connections constitutes some sort of an endorsement.  What a mess! What is now the real value of this thing to the end users other than to sales professionals who may use it as a cheap way to compile prospect lists or to marketing professionals who can run highly targeted ads?  What motivates someone who has no prospecting interest to waste much valuable time there? I can't find a good answer!
I am not trying to bash LinkedIn here – the concept was great and the implementation near perfect but we the people seem to have ruined a good thing as we often do. 

Tech Evangelists – exclusive territories, great potential

If you "live" online and have solid knowledge of databases, especially SQL Server, this may be the perfect gig for you. As a technology evangelist you will promote the use of our database tools through blogging, reviews, demonstrations, presentations etc. You will be compensated for your effort based on your performance. Here are the guidelines:

1. if you are selected to be the exclusive tech evangelist for a given territory we will show you the current status of affairs in that territory and establish a baseline based on historical data;
2. you will receive $0.1 for every unique visitor to our site, originating from your territory, over the established baseline. 
3. you will receive $0.5 for every unique product download (your territory only) over the established baseline.
4. you will receive 15% commission on all sales on your territory regardless of how the customer found us.

You can learn more about the products you will be promoting from our site at http://www.xsql.com/ - if what you read there sounds all greek to you then you know this is not for you. If you think you might be the right person for the job please get in touch with us via email (sales@xsql.com) and briefly tell us who you are and why you think you can excel on this.

$250 for the most creative use of xSQL Data Compare

Have you used xSQL Data Compare to solve a challenging problem? Send us a short essay describing your scenario and how you made use of xSQL Data Compare and you may win $250.

How will this work?

• All entries submitted from now until November 30, 2011 will be evaluated by an internal commission for:
  • Creativity
  • Challenge level
  • Presentation
• The commission will select 3 winners and the winning entries will be announced no later than December 16, 2011. The prizes are:
  • First place winner -> $250
  • Second place -> $100
  • Third place -> $50

Please note that by submitting your entries you assign xSQL Software the right to anonymize and publish your essay online.

Please submit your entries via email to info@xsql.com