Synchronize with bulk insert – xSQL Data Compare

When comparing and synchronizing data between two SQL Server databases if you have many new rows on one database that need to be “copied” over to the other database, by default the sync script that xSQL Data Compare generates will contain one INSERT statement for every new row being copied. Now, that is fine when the number of those insert statements is not very big but when you have hundreds of thousands of INSERT statements the script becomes very hard to manage and the performance will suffer.

It is for that reason that we included the ability to synchronize with bulk insert – to do that the user simply checks the “synchronize with bulk insert” option and specifies a folder where the bulk insert files will be stored temporarily. The resulting sync script is easy to manage and will perform a lot better.

Tracing MSDE and SQL Server Express

About a month ago we released xSQL Profiler and advanced SQL Server profiling tool that allows you to define granular traces, schedule them to run when you want on multiple SQL Servers simultaneously, and automatically collect the trace data into a central data repository which you can freely query - all from one location without any agents on the target servers.

We have mentioned that one of the best things about xSQL Profiler is that it is free for up to two SQL Server instances but what we neglected to specifically mention is that not only does xSQL Profiler support higher editions of SQL Server 2000, SQL Server 2005 and SQL Server 2008 but it also supports MSDE and SQL Server Express. Yes, you can now use xSQL Profiler to trace MSDE and SQL Server Express installations.

Download, enjoy and don’t forget to send us your comments and suggestions!

When “can’t be done” is not an option

When your boss, under fire from his boss, tells you “this must be done by Monday morning…” and you feel / know it can’t possibly be done the way you think it should be done what do you do?

1. tell your boss “yes boss I will take care of it” and then when Monday morning comes you tell him “sorry, I tried but I couldn’t get it done...” and then wait for him to fire you?
2. tell your boss “yes boss I will take care of it”, throw your professional standards and pride off the window and “proudly” deliver a shoddy piece of work on Monday morning and wait for him to shower you with appreciation before he realizes that what you delivered was crap and fires you?
3. tell your boss “this can’t be done” and that he is an idiot for trying to impose such an unreasonable order and just resign from your job?
   
   OR
4. you assure your boss that you understand the urgency of the matter and politely ask for permission to go sit down in your corner, come up with a game plan and then meet with the boss to go over your plan?

Well, this is a no brainer you would say – yes, I know I don’t need to tell you that #4 is the best choice but I have seen enough programmers choose 1 and 2 and less frequently choose 3 that I believe a reminder can’t hurt. While number 4 is the obvious choice it is not necessarily the first thing that comes to mind when you are under pressure.

A small clarification - when I say that all three options 1, 2 and 3 will result in you getting fired I am exaggerating a bit to make the point but even if you don’t get fired at the very least your reputation will be severely damaged.

Choosing option 4 is a testament to your professionalism and experience. It gives you the much needed time to break the job down into more manageable and easier to estimate tasks. While doing that you may discover that it is possible to get it done by the deadline if certain resources are made available to you which would be great! But, even if your initial intuition / estimate is proven right and the job can not possibly be done you go back to your boss with an action plan that details what can be done by the deadline and when the whole job can be completed. At that point your boss has the ammunition he needs to go back to his boss and secure an extension of the deadline or modify the requirement / scope of the job so that you can deliver what may be the most critical part of the job by the deadline. Details aside, this approach leads to “happy endings” – the boss will respect you a lot more and chances are that he will never again tell you that “it must be done by Monday” but will rather ask for your professional opinion on what maybe doable and what not.

Tell me what you think – feel free to leave your comments here.

Google Adwords – increase quality or bid $$

I am sure that in a lot of cases when advertisers are trying to mislead the customers this makes perfect sense but it gets to be incredibly frustrating when it does not work as I would think it should.

Here is the source of my frustration: we have a cool tool for documenting databases, xSQL Documenter that allows the user to automatically generate compiled CHM and HTML documentation for databases on virtually any platform – you can document SQL Server databases, Oracle databases, DB2 databases, MySQL databases, Informix databases, Access databases, PostgreSQL databases.

Now one would think (at least I think) that the keyword/phrase “document database” is as relevant as it can possibly get and a browser who plugs in “document database” on the google search box would be well served when directed to a page like the xSQL Documenter page. But, unfortunately the google algorithm does not “think” so – it keeps pushing me to increase quality or raise the bid and since there isn’t much I can do to improve the quality I am left with the other choice, that is, “raise the bid”! Are they price-gauging or is it just an imperfect algorithm?

Please feel free to comment or tell your google adwords story.

Making changes to your production SQL Server database

Not a week goes by without running into a software developer that is still manually making changes to the production SQL Server databases! What’s wrong with that you may say? It is risky and a big, big waste of time!

Here is a common scenario I run into:

A while back you have developed a web site that uses SQL Server on the back-end. The initial deployment was a breeze and everything worked perfectly. Now it is time to tweak and enhance the website with new features and functionality. In addition of making changes and additions in your code you start making changes to the database as well:

• added a couple of new tables
• modified a handful of tables (added columns, changed column types, added indexes, added /changed constraints, added new relationships etc.)
• added a few new stored procedures and views and modified some more of them
• added / modified a dozen user defined functions
• added / modified triggers
• etc., etc…

And all the while you diligently kept track of every change you were making knowing that you would soon need that log of changes.

After two months of hard work and sleepless nights, after testing and retesting everything you are ready to deploy your new version of the website – you know that deploying the application is a click of a button but the application is not going to work unless all the database changes you made are correctly propagated to the production database. You look at your long list of changes and realize that it is going to take hours to manually apply those changes. You wish you could just back up your development database and restore it on the production server but the production database contains live data which must be preserved.

So, you tell your client that you need to take the website off-line from 10 PM on Saturday night to 6 AM on Sunday morning. And when that day comes you take the site off-line, make a full backup of the database and start applying those changes one after the other. The cascading “can’t change this 'object' because it references this other 'object'” messages start to drive you insane but you have to do this so you keep moving along. After hours of frustration you seem to have reached the end of your list and as the sun is dawning you start getting more cheerful. You click on that “button” and publish your application – you run through some tests and everything seems to be working great!

You write an email to all people involved to let them know that you successfully published the new version of the website – pet yourself on the back and go to take a few hours of much deserved sleep. You have just fallen asleep when the phone rings and you get up again to take your client’s frantic call - customers are getting errors and not being able to complete their transactions – something is very wrong! Your nightmare scenario has come true! You jump into action again, quickly look at the error logs and try to determine if the cause is something you can fix quickly or whether you need to restore the old version of the database and application. Fortunately for you this time you discover that the culprit is a single Stored Procedure that had been modified but which somehow you missed. You change that SP and everything is fine – you are finally relieved, it could have been a lot worst!

The question is why anyone would go through this pain when they can use a tool like xSQL Object which in matter of minutes can identify all the database changes that were made and generate a safe SQL change script that will propagate those changes to the production server. It appears to be even more puzzling when considering that xSQL Object is completely free for SQL Server Express and it is also free for other editions of SQL Server as long as the database contains less than a certain number of objects. Even if you were in the small group of users (less than 10% of our user base) who work with larger databases and have to pay a couple hundred dollars xSQL Object more than pays for itself in just one time you use it. The answer unfortunately lies in our inability to reach out to all those potential users to let them know what they are missing. So, if you read through this please help spread the word – let all your friends and colleagues know that they can download this tool and use it to save many valuable hours of their time.

Free SQL compare tools - no pestering

Most of the software vendors claiming to provide free software do one or a combination of the following:

• load the app with annoying messages trying to push you to purchase the commercial version of their software;
• severely limit the functionality of the application and every time you click on let's say a button that presumably gives you access to some critical functionality you are invited to purchase the commercial version;
• implement trial periods after which you can't use the software anymore.

Are those helpful? Yes, absolutely, they allow the user to get a feel for the product before deciding to purchase but... saying that you are giving something for free to the customer is a bit misleading to say the least.
Enter xSQL Object (schema) and xSQL Data Compare (data) two of the best database comparison and synchronization tools in the market - when you download xSQL Bundle that includes those two awesome tools you get a truly free set of very helpful tools.

• no requirement to register or activate;
• no annoying reminders inviting you to purchase the higher version ;
• full functionality

So, what's the catch? Do we have a paid for version? There is no catch and yes we do have a paid for version that is no different from the free version with one important difference: only when one of the databases in the comparison is on a SQL Server edition higher than SQL Server Express AND there are more than certain number of objects in the database then you get a gentle reminder that this edition of xSQL Object / xSQL Data Compare does not support this database.
Judging from our statistics over 90% of our users fall into the free category that never needs to pay a dime for those tools and is never bothered by reminder messages.