SQL Server DBA

The Role of General Purpose Data Integration Tools

My first foray into SQL Server Data Tools left me sitting on the fence as to its usefulness (see my previous post, Beginning SSIS).  As I dig into the literature on data integration, I am beginning to appreciate the enormity of the problems faced and realise that my initial doubts about SSIS and SQL Server Data Tools were probably quite naive. The real benefit of such tools is their ability to scale gracefully as the size and complexity of the project increases.


 

With my KPI project approved, I am launching into the literature around data integration and ETL. Reading the literature is both exciting and daunting; it raises more questions than it answers – and chasing these questions always spins me off onto related (and not-related) tangents so that the core goals become blurred. It can be overwhelming but, I will give myself this week and next to firm up the core direction / question for this project.

My first foray into SQL Server Data Tools left me sitting on the fence as to its usefulness (see my previous post, Beginning SSIS).  As I dig into the literature on data integration, I am beginning to appreciate the enormity of the problems faced and realise that my initial doubts about SSIS and SQL Server Data Tools were probably quite naive. Quite often, when faced with a simple task it is easier to create your own custom solution – but the costs of fully-tailored solutions quickly increase as the complexity of the project increases. This is the where general-purpose tools like SQL Data Tools are so powerful. They provide a consistent, flexible and robust environment for handling data integration. The real benefit of such tools is their ability to scale gracefully as the size and complexity of the project increases.

 

I think that many of us, and especially professional developers, have a little gremlin on our shoulders that tells us “you could do this better…”. This is almost certainly true of small, simple projects. And for truly massive problems an off-the-shelf solution is unlikely to perform as well as a custom tool. As examples, just look at the growth of specialised tools like Apache’s Cassandra or Google’s map-reduce which were initially in-house solutions to very large problems, and have since grown to have wider-application.

 

 

Very few companies have the budget and time to develop custom solutions. In many ways commercial products, like the SQL Server suite of tools, are a more viable alternative offering quicker time to release, lower development costs and lower maintenance costs over the lifetime of the solution. And while it may be true that a very good developer might be able to produce something faster and more efficient – the fact is there are some amazingly good people working on these products, applying optimisations, bug-fixes and error handling for the vast majority of use cases.

 

 

So I will put aside my innate desire to do-it-myself (I like to think it as a hunger to be challenged versus any sense of inflated abilities :p ). Still I was right in my earlier post when I said that SQL Server Data Tools were overkill to that particular simple task – but reflecting on some of the larger challenges of data integration I can see the broader benefits of SSIS and SQL Server Data Tools.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s