Docco labs Blog

Get to know – ClipTable

2012-02-02T07:53:00.000-05:00

ClipTableobjective is quite simple –

revolutionizethe way you’re importing data into your SQL Server.

Why:

I’ve beenserving as a DBA for quite some time, performing production as well as developmenttasks.

One of thethings I always found quite time-consuming was import data, any data, into aSQL table format for additional processing.

While italways works at the end, this type of task never became easier.

Here’s atypical scenario:

One of my customers handsme an Excel file containing his user data (let’s say, 50,000 users).
I open the Excel, see thatit contains multiple sheets; one of them contains the data I need.
I choose the direct importoption, using SSIS.
Trying to use Excel asinput, getting an error and after a while, recalling that there’s an issue ofreading Excel files when 64-bit office is not installed (hence missing theJet/ODBC driver)
OK, giving up on the directExcel->SQL path, going for the CSV->SQL
<Taking a deep breath,making another cup of coffee>
Saving sheet as CSV
Trying SSIS again withflat-file input
Battle continues with:

Finding the right row/col delimiters
Finding the best data-types(number, dates, text etc…)
Finding the correct lengthfor each column
Hoping there are no “textqualifiers” (because this means further work and rules out working with bcp)

Importing… Finger crossed…
Worked? Great
Didn't work? Got anannoying unreadable SSIS error? Go back and try again…

Pardon fornot drawing the above as a flowchart, but I hope you’re getting the point.

This is justone example. I’ve personally had so many of them I could write an entire articleon the subject.

I could - butinstead, I’ve spent my time simply writing a solution for this annoyingproblem.

So, the incentivewas there, and my objective was to eliminate the frustration on this process.

How:

First, Iwanted to eliminate the need of having input drivers for any format (my exampleabove was Excel, but the table may come from PDF, Word, any webpage etc…)

So thenatural solution was to use the clipboard. Instead of re-inventing the wheel,we let Windows do the work of translating anything to a readable text format.

Next, weneed to “understand” the data;

This iswhere ClipTable’s smart engine is being used – analyzing thereverse-engineering the live data to understand the correct fields, types, delimitersetc…

And last – Iwanted to quickly access any of the SQL Servers within my organizations (i.e.the “Destination”) and for that, I’ve implemented the Shared Repositorycomponent (which will be discussed on a different blog post)

TheResult:

Taking thesame example from the 1^st paragraph, this could have taken way overan hour.

By usingClipTable, I simply:

Copy the users data fromthe relevant sheet
Letting ClipTable do thefield/type analysis and turn it into a SQL formatted table
Choose my destination
Done!

That’s it – nomore than 2 minutes andall done.

If you wantto see more, go to our website (doccolabs.com) and get it – it’s totally free.

Hope wemanaged to help you save at least an hour on your next import task.

Let us know J

Enjoy a bundle of Smart SQL Solutions

2011-12-01T09:53:00.001-05:00

I'm super excited to share our new product bundle, which is constantly growing.
SQL Stripes is now part of a bigger family -- The "Docco Labs" family!

The objective is always the same - to provide smart SQL solutions, making DBA's life easier and more productive.

Here's a quick recap for each product; (I will post more detailed looking into each and every one of them in the near future):

ClipTable

Make data import as easy as possible: Copy the desired data into your clipboard (yes, I mean Ctrl-C), and in a few seconds - have this data available in an SQL Table!
No more dealing with input drivers, delimiter settings, field size and type headaches.

Database File Explorer

Explore your Database files/filegroups size & table location in a very easy to understand UI
The hierarchy view will help you quickly understand what's stored where

Log Table Viewer

This is a complete SQL logging solutions - just use the server side procedure to write anything to the log, and then look at it with an amazing viewer, containing many kind of views, filters and more

SQL Stripes

Our 1st product, which is a complete monitor and control solutions for multiple server environment

The "Glue"

Although the 3 new products are seperate, there all use a "Shared Repository" (see screenshot), which means you only have to manage one server/group list, and quickly use any of the servers by any of the products

The new products are still in beta stage, this means they are free to use!

Give it a shot and tell us what you think -

www.doccolabs.com

Cheers :)

Doccolabs.com is up!

2011-11-22T11:02:00.001-05:00

There are various great news coming in today!

First, We're happy to announce that www.doccolabs.com is up and running.
Second, We're launching two new products (currently @ beta stages)
These products are the kickoff launch of our new path of extending our offered solutions.

So, what are those new products, you ask?

ClipTable --
A brand new way to import any data into SQL tables.
If you find yourself dealing with various SSMS wizards, trying to import those tricky Excel/CSV and other input files - ClipTable may be the best solution for you!
Out smart parsing engine reads any data you copy into your clipboard, and creates a table out of it.
Give it a shot - the beta is totally free!
http://www.doccolabs.com/products.html#ClipTable

Database File Explorer --
Our production databases usually have many files and filegroups.
Analyzing the locations, space consumption, hard-disk usage and more is usually a difficult task.
Out of the box Management Studio (SSMS) view is just a simple, unsorted, list of all files and filegroups.
So we usually find ourselves building various sys.* queries, into Excel and analyzing from there.
Well, no more!
We've created the perfect tool for such scenario, that gives you an extremely easy interface which helps you quickly understands the drive-space-file map each database is using.

Again, the beta is totally free, you're invited to give it a shot.
http://www.doccolabs.com/products.html#DatabaseFileExplorer

Enjoy!

Continuous Integration on SQL Server

2011-09-06T05:08:00.000-04:00

Yesterday, Igave a presentation at the “Israeli SQL Server User Group”(Part of PASS), discussing the importance of continuous integration, automaticbuild, and the approach of SQL Code handling in general.

Lecturingthere was a pleasure, the audience was great and I’ve got many responses during,and after the presentation.

Presentationaside; what puzzles me the most, is the fact that many SQL Developers don’ttreat their code the way other developers (c#, java etc...) do

SQL Code isnot just ad-hoc scripts; the code is often part of a big enterprise system -

It has toconnect altogether, to compile & build, to use the right references and tobe clean and readable.

Keepingthese guidelines, even before you implement Automatic builds / CI, help you bein control when a team of SQL developers rapidly write new code and modifyexisting one.

Anotherthing worth mentioning is – Automatic deployment in Visual Studio is by allmeans, not intended for production deployment.

The mainconcept is to keep an updated, high-quality development (and maybe QA)environments with the latest code. This will help us:

Provide a ‘Latest’ databaseversion for integration with the other dev teams
Ensure code quality andstability

Forproduction, use whatever you like (diff scripts, 3^rd parties…)

"Bad Code"

Last but notleast – I sensed some excitement around controlling the presentation with mytablet. I’m always happy to see that I’m not the only gadget freak in the room J

So, thereare many solutions out there. I’ve used Unified Remote (for Android) along withthe server-side installation

Control canbe done either through Wi-Fi or BT

get it here:

SQL Code refactoring

2011-08-30T05:36:00.000-04:00

Code refactoring (wiki: "disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior") is a very common method of self-improving our existing code.

Doing this is extremely important and there are many resources, articles and forums discussing this topic.

Our SQL Code (procedures, functions etc…) is no different than any other regular code

And yet, we don’t see this common practice popular enough among SQL Developers.

It is important to distinct between query refactoring, which would usually aim at reformatting the query to better readability and in some cases – better performance, and actual code refactoring.

Frequently refactoring your code helps you make sure you (and other developers in your team) don’t get lost in long, spaghetti, unstructured code as your project grows.

In addition, you’ll be surprise of how much potential bugs you may find, fix and prevent during this process.

So, here are the common items you should be aiming towards:

- Renaming (at least the internals that are not exposed to the outside application)

o There are many tools, including Visual Studio, which will help you detect any rename any references to the renamed object. I strongly advise to use those

- Keeping your procedures and functions small.

o I personally saw too many of those 1000+ lines procedures. This is always a bad practice

o And by the way, the solution is not to split a long procedure into [n] pieces J

- Keeping the same structure/pattern. I’m referring to formatting, variable declaration, naming convention and more

- Using schema prefix for different DALs. (this is a trick we’re using to divide the code into sub-systems) For example:

o Common.BatchDelete()

o CallsDAL.StartTraceCall()

o Etc…

Last but not least – during and after refactoring, always make sure that your project:

- Compiles and builds with no errors

- Deploys successfully

- Passes all relevant unit-tests (to make sure backward compatibility and logic are not harmed)

This is another big topic which I’ll cover in the near future.

Win a free copy of SQL Stripes on SQL Server Quiz 2010!

2010-10-01T02:25:00.000-04:00

We are proud to be one of the prize sponsors for a great event: Beyond Relational's SQL Server Quiz 2010!
This major TSQL challenge will take place during This month (October)

One of the winners will win a SQL Stripes Enterprise license!

For more details - visit this link:
http://beyondrelational.com/quiz/sqlserver/general/2010/default.aspx

SQL Stripes 2010 is out!

2010-05-19T13:05:00.000-04:00

We have some exciting news - SQL Stripes 2010 is officially out!
The beta phase is over, we've fixed all known bugs, added some new features and tweaks and ready to go.

SQL Stripes is now, more than ever, a complete solution for being in control over multiple SQL Servers.
The monitoring capabilities are stronger, the quick analysis options are more robust and the notifications are easy to understand.

Well, what are you waiting for? go grab the new version @ www.sqlstripes.com
For currently registered users - this upgrade is FOR FREE!
For new users - we've extended the trial period to 30 days. Which means you'll have one month to play with the stripes. There are NO LIMITATIONS whatsoever to the trial version.

I'd like to thank all people who contributed to this version, helping out test the various beta versions and suggesting new features.

On our side - the work never stops, we're already started working on the next version, as well as a set of new and exciting products which will be introduced in the near future!

Cheers,
Adi

Using GUID (Unique Identifiers) as table main keys

2010-03-02T11:41:00.000-05:00

Using What?!

Unique Identifier (which from now on will be referred to as ‘guid’) is an algorithm-based 32-byte hex string (128 bit integer) which is supposed to generate completely unique values.

I’d like to compare the chances of two identical generated guids to the chance earth will be destroyed by a meteor in the next five minutes. Now if that happens – you won’t be here to prove me wrong! J

Anyway, here’s one: 5B5D6F63-1FF0-4574-9B77-BD77D716FD66. It is probably unique and there’s no other like it in the world. (See more here - http://en.wikipedia.org/wiki/Globally_Unique_Identifier)

With today’s increasing demand for fully-distributed systems, GUID usage is increasing accordingly, as it allows managing two disconnected databases which later on needs to merge, knowing that each row on desired shared tables is unique.

Here’s a simple example –

Consider a very large company with many branches across the world.
Each branch has local users, and there’s a sync process which merges the entire users list.

The ‘traditional’ way is to maintain a range of numbers for each branch, use some prefix on the username to maintain uniqueness, or just make a combined key of UserId+Another column contains the branch id.
This is a classic sample for using GUID instead – make the UserId column guid and problem is solved.

Sounds like fun! Any reason why I shouldn’t use it?

Well, yes. Before you get all excited and change your “int’s” and “bigint’s” to guid, consider the following:

- Guid column takes 16 bytes, which is double than Bigint. This means that index on the column will also take more space

- Index Fragmentation is an issue and it is most likely to happen.
(See more about it on the Tips section below)

Some Best-Practices Tips:

[Tip #1]

The first, and probably the most important tip is to avoid using CLUSTERED INDEX on guids.
This is because the guid values are random – a physical sort will cause the table to be sorted on every insert.
Still, in many cases you’d want to avoid having HEAP tables, which means another ID (probably an internal identity) has to be used.

When possible - use the NEWSEQUENTIALID function. This will create unique guids on a table but will make sure the new generated guid is greater than the last. (It is still globally unique as long as the machine has its own MAC address).

Let’s see a quick sample of the differences between the NEWID() function and the NEWSEQUENTIALID default value:

Let’s create two tables; each uses one of the techniques:

-- Creating two tables:

IF OBJECT_ID('UsersNewId') IS NULL

BEGIN

CREATE TABLE UsersNewId

(

UserId UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),

UserName VarChar(255)

)

END

IF OBJECT_ID('UsersNewSeqId') IS NULL

BEGIN

CREATE TABLE UsersNewSeqId

(

UserId UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED DEFAULT NEWSEQUENTIALID(),

UserName VarChar(255)

)

END;

-- Now, let's fill the tables with some data

-- Note: I'm using SQL2008+ syntax here; On SQL2005 separate the insert statements

INSERT INTO UsersNewId(UserName) VALUES ('User1'),('User2'),('User3'),('User4'),('User5')

INSERT INTO UsersNewSeqId(UserName) VALUES ('User1'),('User2'),('User3'),('User4'),('User5')

-- Now, let's see the tables

SELECT * FROM UsersNewId

SELECT * FROM UsersNewSeqId

The results:

Note the guid on the 2^nd table looks the same on first sight, but actually it is not. Those are increasing values that still maintain uniqueness.

This is obviously much better for index fragmentation of any time!

[Tip #2]

Related to the above tip – as a DBA, try to keep the guid generation on your side! othewise
If the guid’s are generated by a different layer in your application (DAL or so), the guids cannot be sequential.

Besides, try to think as if the guid’s are actually a new version of your INT identities; You wouldn’t let anyone control the auto-increase now would you?
This is practically the same.

[Tip #3]

In some cases you may want to consider surrogate keys, which will be used as the foreign key between the tables. To use the sample at the beginning of this article – each user will have its own GUID, but with an additional “UserInternalId” column (int) which will be the one to connect the main table to all the rest.

[Tip #4]

Don’t use Guid if you don’t have to! When a table needs an ID which does not have to be unique across the enterprise, keep using the regular numeric id.

Final Words:

Guids are great and simple solution to maintain uniqueness across the enterprise. However, don’t overuse it when not required as it might hurt your databases performances.

This is a very wide subject. I was trying to limit myself to a blog-post size; I might write a follow-up article about this subject

Off-Topic: Few words about Google Buzz

2010-02-11T10:09:00.000-05:00

I've posted a short article about the new (as for today) Google Buzz.
Read more here: http://adisapir.blogspot.com/2010/02/buzz-yay-or-nay.html

Oh, and a quick Stripes update - the non-beta version is near. Except some news soon!

Cheers

The importance of easy access to your SQL Servers

2010-01-18T07:44:00.000-05:00

Sometimes, when access to a machine is not intuitive, admins tend to initiate such a connection “only when required”.

Problem is – the “when” is sometimes undefined.
I see a lot of DBA’s having their “morning routine”, where they’ll connect to each of their servers (either through Remote Desktop, or through Management Studio for SQL-Only checkups) and see that they’re working, that no jobs have failed, that the disk space is OK etc…

But what if the problem occurs five minutes after you disconnect?

Or – what if you didn’t follow your written protocol as to what should be checked, and missed out something?

The surprise is (at least for me personally) that I’ve seen many organizations that as of today (beginning of 2010), actually work that way!

So, (aside from the fact they see SqlStripes and get all excited, and that I am having thoughts about why marketing didn’t get to them earlier J), I wonder how could that be?
How can wide-production systems have SQL-Service monitor at best?

If you have a minute, click on the poll below to state what’s your way of monitoring your SQL Servers

Cheers,

Adi

SQL Stripes 2010 CTP2 is out!

2010-01-04T16:26:00.000-05:00

Hey,
Excited to announce that we got CTP2 fresh out of the oven!
We're getting very close to the final non-beta release. Although, as always, the development never stops (what I'm actually saying is that a new version means start working on the next one ;-)).

Here's some of the new highlights on the new version (differs from ctp1):

Multiple SMTP enhancements
Major Stripe/UI Design Enhancements
New email alert method: Email-Per-Alert (which means the alert description is sent on the subject rather than the message body)
CPU alert is now configurable
multiple bug-fix

So, grab your copy today! and as usual, send your feedback - we're always happy to hear and response

Cheers

Grab CTP2 Here

Happy New Year!

2010-01-01T02:28:00.000-05:00

Wow! Another decade just went by!
I read so many articles discussing how technology had evolved in the last ten years and predicting where it will go from here.
The new anticipated version of SQL Stripes is getting closer (hey, we call it 2010 and that's what the calendar now says ;-))
So, as the new decade begins, we do our best so the new version stands in the standards and requirements of multiple database watching.
We know how important it is to keep track and maintain the growing amount of data everywhere, and will make sure that in 2010, with the new version out it will be much easier!

Cheers & Happy New Year,
Adi

Some information about the next beta...

2009-12-02T04:31:00.000-05:00

We've been getting a lot of positive feedback on the current SQL Stripes 2010 Beta.
This is great! and the work doesn't stop - already working on the next beta version of 2010

So, if you wonder what's planned on the next beta (besides obvious bug-fixes), here's a partial list

UI Design change (yet another one, we're getting there... :-))
Detailed memory information (fully on 2008, partial on 2005 due to some missing DM's)
RSS Alerts implementation
Email alerts - an option to get email per alert (which means - the alert/notification is the subject and not the body)

More to come...

/Adi

SQL Stripes 2010 (beta) & New Website RELEASED!

2009-11-10T17:53:00.000-05:00

I'm very excited to announce that just a few minutes ago, the new SQL Stripes 2010 (beta) along with the new improved website are UP!
The wait is over - you can download the new version and play with it.

If you're a new user - this beta is fully functional for 30 days
Registered users - your license key is good to go! no extra actions required :-)

I'd love to get your feedback; Either comment here or sent an email.
The final version is expected to be released in the beginning of 2010

Cheers,
Adi

Even closer

2009-11-07T10:24:00.000-05:00

As part of the new version, we're going to update our website (www.sqlstripes.com) as well.

You can expect to see the new site, along with the new version (still in beta) in the next few days...

One of the new pages on the new website is a short introduction video. I've decided to post this video here even before the new website is up - have a look:

http://www.youtube.com/watch?v=Q05zyosqdoI

Enjoy! I'll update again as soon as the new website is live!

SQL Stripes 2010 CTP almost done

2009-10-20T18:00:00.000-04:00

I'd like to open my 1st post here with some very exciting news -

Over the last year, there has been very intensive work on the next upcoming version of SQL Stripes.

This is going to be a MAJOR release, containing various new features.

I'd like to share some of them even before the version is out and our website is updated -

A very partial list:

New Stripe Design
Fresh UI
Database I/O Analysis
New real-time alerts
SQL 2008 Native-Client Support (while still being 100% backward-compatible)
Performance Boost
Improved multi-server search

The new beta is going to have an extended trial period, so new users can experience is for a longer period. Registered users will be able to enjoy it for free as part of the registration.

Further updates are coming...