Tuesday, December 9, 2014

Introducing LABS, our app playground.

A bit about the "LABS" part in Docco labs

So, in addition to our well matured products, we keep creating new apps, usually built around needs we come across during our daily tasks.
While these apps are usually not shared publicly, I've decided to introduce (some of) them to the public. That - in order to expose them to a wider audience (such as yourself) let you use them freely and hopefully get some feedback.

While far from being complete (consider that a one-sentence disclaimer), all of those apps will be available for download upon request (on the first stage) and widely available later.

OK, so, before we lose our TL;DR readers, let's jump right to it! shall we?
(You can start by reading the taglines, and then drill-down to whatever interests you)

*** Validate SSDT Publish Script

Tagline: Make sure you SSDT deployment scripts are not locking down your databases

For those of you using SQL Server Data Tools (a.k.a SSDT) for continuous delivery, you may have encountered cases when a generated deploy script is trying to drop and recreate multi tables.
Why does this happen? well, a typical scenario would be that a developer is trying to add a new column to an existing table, but instead of adding it last, he or she is adding the column in the middle of the table.
What happens then?
Well, since there is no T-SQL syntax of pushing a new column in the middle of a table, the generated script will do the following:

  • create a tmp_xxxx table with the new structure
  • copy over all content from the original table
  • drop the original table 
  • sp_rename the tmp table to the original table
  • restore all FK's indexes etc.
And that's the *SIMPLE* case. When the table has a lot of references, other tables may be affected as well! And if these are large tables and you're in prod, you can only imagine the potential downtime you'd experience by that deployment.

So, that's the main reason for the validation app to be born. Here's what it does:

  • Analyze the SSDT generated .sql script of any build
  • Detect tables re-creations
  • (Now comes the cool part)
  • Connect to the destination database and measure the dropped/re-created table size
  • If the total size is over a predefined threshold - raise a flag and return a 'fail' notice on the build.
So, to summarize, this 'lab' app will save you from unintentionally run deployments that will potentially lock your database for quite a long time.

This is a console app, with a powerful configuration options allowing you to customize many aspects such as scripts locations, destination database connection scripts, size threshold and more.

Here's a sample screenshot:

*** Splunk to SQL Table

Tagline: Turn (almost) any Splunk query into a well-formed SQL Table

If you are unfamiliar with Splunk, it is (wiki quote):
"Splunk (the product) captures, indexes and correlates real-time data in a searchable repository from which it can generate graphs, reports, alerts, dashboards and visualizations"

While having the exporting abilities, run a Splunk query result into a SQL Table is not an easy task. At least if you're not using "Splunk to SQL Table"! :)
So, what this app does is basically the following (assume everything is heavily configurable)

  • Connect to a Splunk server, run a pre-defined splunk query
  • Analyzes the result, converts to a SQL Table with optimal data-types (based on content)
    • (So for example, that OrderId will be "INT" rather than "NVARCHAR(MAX)")
  • Connect to a configured SQL Instance
  • Create the table, push the content in.
Some screenshots? sure!

Splunk search screen:


The App (with relevant configuration)


A new SQL Table

*** Text/CSV file slicer

Tagline: A powerful slicer for large text/csv/tsv files

Whenever in need to split a big "something"-delimited file, this powerful tool will do the job, allowing a lot of different customizations such as:
- Control the naming patterns of the output files (identities, guids, random strings and more)
- Repeat headers on each file
- Control the end-of-line characters (windows/unix compatibility)

While you may find other tools out there, this one has been written to provide more robust option base. Oh, and it's also *REALLY* fast!

The screenshot shows the simple UI implementation, the tool itself is basically a console app.