xSQL Software official blog moved

Our official blog is now at: http://blog.xsql.com

SQL Jobs on a Mirrored Environment

One of the little challenges a DBA has to deal with when setting up SQL Server mirroring is handling of SQL Server jobs. SQL Server mirroring works at the database level so SQL Server instance level objects are not mirrored. To achieve a transparent failover some of those instance level objects, like certain SQL Jobs related to your mirrored databases, need to be somehow "mirrored" as well. The objective is simple: any relevant jobs that were running on the principal, on failover should start running on the mirror. So we have to make sure the jobs are kept in sync and are activated at the right moment (you don't want those jobs to be running on the mirror until the failover happens).

One approach would be to have scheduled jobs that periodically syncs the SQL Jobs on the principal with those on the mirror and periodically checks the mirroring status to determine if the jobs on the mirror need to be activated.

Another simpler approach would be to use multi-server jobs. You can use a third server, like the witness for example, as the job "master" and do the following:

1. All the jobs that must be "mirrored" should be created on the job "master" server as multi-server jobs. 
2. The target for those jobs is set to be the principal 
3. Create a job on the job master server that checks the mirror status, let's say every minute
4. If a failover has happened and the mirror has become the principal then it changes the target server for those jobs.

Here is a simple stored procedure that checks the mirroring status and switches the target server for those jobs.

CREATE PROCEDURE [dbo].[usp_ChangeJobTarget]
  @DBName varchar(256) = 'MyMirroredDB',
  @Principal varchar(256) = 'MyPrincipal',
  @Mirror varchar(256) = 'MyMirror'
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @current_principal varchar(256)
  DECLARE @principalID int
  DECLARE @mirrorID int
  DECLARE @jobname  varchar(256)

   SELECT @current_principal = principal_server_name FROM sys.database_mirroring_witnesses WHERE database_name = @DBName
   SELECT @principalID = server_id from msdb.dbo.systargetservers WHERE server_name = @Principal
   SELECT @mirrorID = server_id from msdb.dbo.systargetservers WHERE server_name = @Mirror

 IF @current_principal like @Mirror
   BEGIN
    -- principal has changed so we need to change the target for all jobs
    -- first let's get the complete list of jobs that target the principal
   DECLARE @JobList TABLE
    (
      [JobName] [varchar](256) NOT NULL,
      [Changed] bit NOT NULL
    )
    INSERT INTO @JobList
      SELECT [name], 0
         FROM msdb.dbo.sysjobs as a INNER JOIN msdb.dbo.sysjobservers as b on a.job_id = b.job_id
         WHERE b.server_id = @principalID
    -- now for each job on the @JobList we need to change the target from principal to mirror
      SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
         WHILE (@jobname IS NOT NULL)
           BEGIN
              exec msdb.dbo.sp_delete_jobserver @job_name = @jobname, @server_name = @Principal
              exec msdb.dbo.sp_add_jobserver @job_name = @jobname, @server_name = @Mirror
              UPDATE @JobList SET [Changed] = 1 WHERE [JobName] = @jobname
              SET @jobname = null
              SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
           END
     END
  END
GO

A couple of notes to keep in mind:

1. There is a small delay on replicating a multi-server job. In other words, if you change a job or add a new job on the job master server it will take up to a minute for those changes to be reflected on the target server(s). 
2. The above stored procedure only goes in one direction – changes the target from principal to mirror in case of failover. You will need to adjust it to make it work both ways – one simple way to accomplish this is to simply compare the current principal with the target for those jobs and if they don’t match then change the target.
3. The above script is provided as is - use at your own risk.

Last but not least: check out our SQL Server comparison and synchronization tools, xSQL Object and xSQL Data Compare they will make your database deployment a breeze. Also, check out our Script Executor - no other tool in the market today comes close to it.

Kindle time at xSQL Software - get one

Don't "burn" your left over 2011 budget, put it to good use and get a great gift. Purchase a new, full xSQL Comparison Bundle license before Friday, December 16, 2011 and we will send you an awesome thank you gift, a Kindle. We will send the gift to whoever you direct us to send it to, anywhere in the United States and Canada. Here are the levels:

• a Kindle when you purchase a single machine / single user license
• a Kindle Touch 3G when you purchase a  five machine / five user license pack
• a Kindle Fire when you purchase a site license (unlimited activations within one physical site)

You must plug in the discount code KINDLE on the "discount" textbox on the shopping cart page otherwise no gift for you. Also, the purchase must be completed before December 16, 2011 - no exceptions.

Once you complete the order email us at sales@xsqlsoftware.com with instructions as to where you want your gift shipped to (a United States or Canada address only).

If you are not already, follow us on twitter and like us on facebook, we would appreciate it very much.

iPad and SQL Server

In the last few months we noticed a jump in the number of downloads for RSS Reporter and were not sure what to attribute it to. So we started probing the users who were downloading the product and found that the increased interest in our RSS Reporter for SQL Server was directly related to an ever increasing number of SQL Server Database Administrators carrying an iPad around.

What's so great about RSS Reporter? A couple of things:

• You don't need a client, just install the RSS Reporter on a machine with IIS that has access to the  SQL Server instances you are interested on and then access it from anywhere with an internet browser, iPad, iPhone, tablet – anything that can read a standard rss feed;
• Your executives need live data on their iPads, no problem, you add an ad-hoc query on RSS Reporter and simply send them the url;
• You need to see what happened with your SQL Server Jobs last night, no problem. You can get an aggregate report from all your SQL Servers right on your iPad, and you can drill down and see exactly what happened.

If you haven't tried RSS Reporter check it out – it is completely free for a single SQL Server Instance and very reasonably priced if you want to monitor multiple SQL Server instances in one place.

After you try make sure to send us your comments and suggestions as on how we might make RSS Reporter even more useful to you.

xSQL’s Oracle Data Compare vs. the others

When you do a search for data compare tools for Oracle a bunch of competing tools come up and at first look they all seem to be similar but once you start digging a bit deeper most of them get crossed off the list because they don’t amount to more than an amateurish effort. The very few that remain are hard to separate and you are inclined to go with the most known brand - after all, who has the time to thoroughly test all the available products!

We decided to take the time and do a more thorough review of a couple of those products that made the first cut and compared them to our Oracle Data Compare. Here is what we found:

1. Functionality: Oracle Data Compare supports Oracle Geometry, complex data types and unicode data; they DON’T
2. Performance: in the tests we did xSQL’s Oracle Data Compare completed the tasks  30% to 40% faster while keeping the memory consumption under 100 MB whereas the next best one was at 200 MB.
3. GUI: An argument can certainly be made in favor of simplicity but in the world of Oracle professionals having flexibility and control is important and xSQL’s Oracle Data Compare gives the user significantly more control and flexibility over the comparison and synchronization operation.

Download the free trial and see for yourself. If you wish to challenge our findings please do so – we will be delighted to hear from you!  Email us at the info address on xsql.com

Note: the introductory price of $174 expires on October 31, 2011. After that the price goes back to $349. To get the introductory price use promo code ORACLEPROMO on checkout.

Oracle Data Compare new build

Just released a new build of Oracle Data Compare - support for Oracle collections was added. Download your three week trial copy here...

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

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