Archive for August, 2009

Mysqldump equivalent for Microsoft SQL Server

If you end up working in a wide variety of systems, you inevitably end up on system X, wishing that you had a specific tool or feature from system Y.

Today this happened to me, in that I wished I had something like mysqldump, but for MS SQL Server.  The pointy-clicky stuff is fine in certain circumstances, but fails spectacularly in others.

This is a scenario where I didn’t own both boxes, and only have limited access to the source machine.  So detach/attach and many other options are out of the picture.  Copy database fails in a variety of ways in this situation, and the Export Data function also fails, as it’s not smart enough to try to insert table records in the right order to satisfy foreign key constraints.

And to top it off, I’d really like to have a nice snapshot of schema and data (this is a very small db) to push into SVN.

Unfortunately, the built in tools for MS SQL failed quite spectacularly here.  This is a particularly frustrating limitation, as what should have been a 10-minute process to recreate the database on a new box turns into a sticky mess.

Fortunately, there is a solution, and it works quite well.  To top things off, it’s open source (MS-PL) and created by Microsoft.

Enter the Database Publishing Wizard, which is a part of the SQL Server Hosting Toolkit, up on CodePlex.

So now we can do wonderful things like this from the command line:

SqlPubWiz.exe script -d DBNAME -S SERVERNAME c:\db-schema.sql -schemaonly

and

SqlPubWiz.exe script -d DBNAME -S SERVERNAME c:\db-data.sql -dataonly

For the data extract, it actually uses an intelligent ordering, and produces SQL that generally ‘just works’, even with complex foreign key constraints.

There are other options as well if you want to control it to a greater degree.

This is close enough to mysqldump that it makes me quite happy, and even has a simpler syntax (though fewer options).  Also note that this isn’t really ideal for very large databases.  SSIS, bcp and other tools are a better choice in that scenario.

Do you have tools you like better?  Let me know in the comments.

Andrew Hollamon
D’Mention Systems, LLC

Follow my other blog at:
http://dmentionsystems.com/blogs/andrew

Follow my business partner Mike’s blog at:
http://dmentionsystems.com/blogs/mike/

Are you looking for database or software experts to help you with your software project?  Or need to integrate multiple systems or databases?

Contact us here for a free consultation.

The Limits of jQuery or When You Trip Over Your Own Elegance

I admit it, I’m a hater.   I hate JavaScript.

Specifically, I hate the lack of tools, and the terrible and inconsistent integration with various browser DOM’s.

Unfortunately, we’re stuck with it.  It’s either that or Flex/Silverlight when you want real richness in web apps.  And because its absolutely necessary to make a web app rich where you cant use Flex, we go to some lengths to be well versed with it.

Thank God for libraries like Prototype and jQuery.  They make liveable what otherwise reminds me most of caressing a cheese-grater.

In particular, I absolutely love the ability to observe DOM events, and to declare these outside the HTML itself.  The ability to do this is a rare little gem of elegance in what is otherwise a grey murk of mediocrity.

However, it’s not terribly hard to run smack up against the limits of these tools, where you are forced to throw your elegance out the window.

A recent project is an excellent example of this.   It was a questionnaire that made heavy use of JavaScript.  A core requirement was that every form field would do an automatic ajax-save of the answer when the field was changed/blurred.  This worked well, except for one of the pages.

This page had a very dense table with many radio controls in the center, and as it grew from the stakeholders, the end result was a page with over 700 addressable form fields (mostly radio controls).  The ajax save behavior was wired up outside the main page using jQuery event observers.

At this size, we started to see some odd behavior.  In Firefox (and only in Firefox, surprisingly) when we loaded (entered) or exited that large page, FF would block and spike to 100% CPU for approximately 30 seconds.  During this time the window would blank and the entire application UI would be blocked.

After some experimentation we discovered that this was being caused by the event observers.  If we refactored the JS and put onchange=”" attributes on the form field, then the problem went away.

My assumption here is that we crossed some magical threshold in FF memory usage (due to the housekeeping of elements and events that jQuery had to do) that caused the browser to do a major memory re-allocation (on page entry) and then a de-allocation on exit.   This is just a supposition, however.  Due to time constraints on the project, we didn’t use any of the FF debugging tools that are out there.

The lesson to be learned here?   Sometimes your abstractions leak, and sometimes they puke all over the floor.  In this case, a tool used primarily for developer productivity and code elegance turned out to fail ungracefully at scale.  Or maybe the core problem is within FF.  Or maybe I had unrealistic expectations.  I’m sure someone will chime in and say that we should never have had a page that big.

I’d love to hear if others have run into similar problems.

Andrew Hollamon