Challenges for cloud based SSIS

These days I am busy convincing my management the right people to build Noah's ark. This is to develop readiness against the biggest change that I see coming as of date. This change is Microsoft's entry into cloud based computing. This is a wave very strong and is propagating in all the major Microsoft based products that form key constituency of a solution design.

The next obvious thought is what is this Noah's Ark ? It's the design patterns for adapting all the Microsoft BI Stack of technologies to operate with Cloud based SQL Server i.e. SQL Azure, reducing the need and/or dependency of SQL Server wherever appropriate. In summary, readiness for business intelligence on the cloud. Though SQL Azure is not even near to the capacity of SQL Server, but it's evolving with a very fast pace. Enterprises have already started rolling their eye balls on Azure, and the basic factors of attraction are cost-effectiveness and ease of maintenance. I can foresee a time in the near future when solution providers would be proposing dual flavor of architectures to bid for a project, one with the traditional way of solution design and other would be cloud flavor of the same architecture. If your prospective client is desperately looking for cost-effective architecture design, cloud based architecture can be a silver bullet and a RFP biased with traditional flavor of architecture design is assured to cripple in the views of the client.

Coming back to the subject of this topic, as of this draft, SSIS is not available on SQL Azure or Azure platform. But in my expectation, I can see this need and feature addition coming in the near future. As we know that SSIS is mostly about in-memory transformation of data and secondary memory is the fuel of SSIS. With cloud / Azure platform, to gain flexibility in the scalability of hardware availability and administration, the tradeoff is to give up absolute control over hardware administration. As of now, ETL using SSIS is still tightly coupled to hardware / memory, and it would be very interesting to think of how SSIS on cloud / azure platform would look like. Imagine each and every data flow transform working on the cloud, and I cannot wait to see how similar or different would be the way in which ETL solutions would be developed on cloud platform compared to the way we develop the same on SQL Server.

Theoretically, one thing seems very clear that the tight coupling with hardware will have to be removed for SSIS to be hosted on cloud platform. And this can mean a very major architecture level change, effectively birth of a ETL system on a new world named cloud / Azure platform, and we would might still call this ETL tool / system / service as SSIS !!