Database Sharding, SQL Azure Federation, SQL Server 2012 and Sequence

Almost everyone who is a SQL Server professional directly or indirectly knows the newly introduced Sequence object in SQL Server 2012. This post is not about explaining how to use it. Just google what most famous blogger have to say about Sequence object, and you would find that the only content in the post is how to use it. I was hoping that someone would see it from the angle that I see it, but I didn't find any. So I thought of sharing my viewpoint on Sequence object.

One can manage generating incremental keys/ids using the IDENTITY property. Is Sequence just another means to flexibly manage automated incremental ID generation ? I would say it's one of the means, but I don't see it as the ultimate purpose of it.

Applications enabling answer to complex queries often needs data of a huge size. How easily would you get to read data, depends on strategically how-and-where you write data. Network bandwidth, computing capacity and memory availability are some of the driving factors in read-write operations. When apps face performance pressure, the first step that is typically followed is scaling up. After scale up reaches a limit, scale out is the next measure. Partitioning, Replication, Federation are different approaches to scale out databases. If data is distributed and stored in a way that the query is likely to fetch all the data from a single server / partition, this strategy might work till each database size remains in gigabyte sized limits. If each partition / database is multi terabyte sized, even if entire data to be returned to database is figured out, it would face a bottleneck due to network bandwidth available to a single server due to the amount of data to be returned.

Database sharding is partitioning data horizontally across many servers on commodity hardware. Some areas where sharding is used include:

• NoSQL databases running on Hadoop and MapReduce based infrastructure with database sharding have been able to solve database scalability related challenges on some of the worlds biggest and most aggressively growing datasets. 
• SQL Azure Federation on Windows Azure platform is one of the awaited features in SQL Azure, that would allow multi tenant databases with sharding. 
• Cloud databases often have to be multi-tenant / federated, due to the limits on database sizes as a result of cloud based storage architecture.

The interesting part is, database sharding on relational databases is not available out-of-box, at least in SQL Server. Consider a scenario where the need is to insert a couple of records in order but in different databases. One would need a key/id generation mechanism that is independent of tables, unlike IDENTITY property.

Sequence is independent of tables, which makes it much more scalable. From the feature set it seems that Sequence can help to generate a ordered set of keys for storing data across shards in a sorted format. This can be confirmed only with a PoC, but for now I am hopeful that when SQL Azure Federation  would go RTM, Sequence would definitely make a place in the T-SQL features for the same.

If you feel that database sharding for relational OLTP databases is not a popular practice, then you might want to check out this company that just runs its shop on database sharding - DBShards.com. An interesting whitepaper on database sharding can be read from here.

Excel 2013 BI versus SSRS 2012

1) Excel 2013 has PowerPivot 2012 integrated / inbuilt into it. This means access to vertipaq engine, data processing, and dashboard related benefits. Powerpivot 2012 is able to harness the power of Windows Azure Data Market too. More on the same can be read from here.

2) Excel 2013 had PowerView integrated / inbuilt into it. This means all the reporting related benefits, visualizations like charts - graphs - bing maps, KPIs and dashboarding and other dashboard related components like hierarchies etc. PowerView is designed to interpret DAX queries on BISM model, and integration with Excel means that now Excel is even more capable of reporting BISM data models than PowerView itself. More on the same can be read from here.

3) Excel 2013 can connect to Hadoop on Azure using Hive ODBC Driver. This means excel can exploit data from cloud from related data stored in Azure databases as well as big data stores like Hadoop. More on the same can be read from here.

4) Excel 2013 facilitates sourcing media and other rich content from the web using it's newly introduced Content app and Task pane app. This means very rich integration using a relatively least complex tool compared to other self service tools available in the industry. More on this can be read from here.

5) Excel 2013 ships with some great advancements in visualizations. Users are not expected to be very deeply insighted into reports development, and components that can even make suggestions on the kind of reports suited for the kind of data they are available. More on this can be read from here.

6) Most of the applications provide features to export data into excel, which means any kind of data can be easily imported into excel. Also most of the applications provide plug-in for Excel to provide deeper integration with excel to make the data exchange process easier.

7) Excel 2013 now supports decoupled PivotTable and PivotCharts, that means better representation of trend analysis. And even Excel Services supports this feature. More on this can be read from here.

This list is not an exhaustive list of features, and I am sure there are many more features worth listing. Above listed are just those that I could remember quickly. I am not saying SSRS is a weak tool by any means and on top of that its a server line of technology and integrated with SQL Server. It has great visualizations and available in the form of service which is suitable from a scalability and architecture standpoint.

But clients with

• lots of data stored in variety of data stores
• limited funds and even limited time and patience to wait for IT to facilitate their analytical needs
• totally resistant to learn another tool claiming to be their analytics angel
• and seeking to retain the power of determining the landscape of analytics in their own hands

would definitely find Excel 2013 as their BI magnet.

If you have more Excel 2013 BI features worth listing, feel free to comment the same on this post.

Career in Windows Azure and Career challenges due to Windows Azure

Recently Microsoft made announcement about new feature releases and additions to Windows Azure. Scott Guthrie gave a keynote speech about the guided tour of Windows Azure enhancements, which can be downloaded from here. Some of the very impressive enhancements are as below:

1) IaaS offering: This aspect of cloud platform is facilitated by provisioning a persistent, completely cross premise portable, .VHD format, Windows / Linux based Virtual Machine hosted on windows azure computing platform. This VM can be created with just a few clicks using the web based azure management portal, powered by a thorough monitoring, auditing, metering, scaling and administrative control including immediate ability to RDC the VM. A few ready VM images are also available in the gallery to start with. Also an innovative set of virtual networking capabilities have been offered that seems to seamlessly bridge the gap between private in-premise networks and cloud.

One of the ready available image is of SQL Server 2012 evaluation edition. Others are vanilla server images. If you use this wisely, you have almost a mobile server at your fingertips. Say you are travelling, and you get a call from your office to modify a report and revert in an hour. You can simply connect to the cloud, provided you have a prototype DB schema, you can use this ready SQL Server installation, modify your report and wash off your hands from an issue that can result into some serious office tensions.

2) Rich and collaborative application development and hosting platform: If you read the above section carefully or if you watch the above screenshot carefully, you would find non-microsoft OSes. Microsoft has collaborated with a lot of open-source and third-party application partners and have made a variety of application development platforms available as a service. Apart from SQL Server, Microsoft seems to have adopted mySQL too which is a supported database for asp.net powered windows azure websites. A sad part is that lot of CMS (Content Management System) frameworks are available, but not Sharepoint. Hopefully that should be on the plan.

The happy part here is that IaaS as well as SaaS can work in an integrated manner. Distributed caching, Unified Deployment using standard methods of TFS including cross-platform based methods like Git, cloud based DNS and identity management services are available in the form of Cloud Services which is the PaaS offering from Azure.

Career in Windows Azure: Azure is an aggressively developing cloud computing platform. Many foresighted professionals would definitely get a thought, can this be the new ride towards fortune pay packages ? Azure has many dimensions on its platform like Data Market, IaaS and virtualization and virtual networking, application hosting and development frameworks, hadoop on azure, upcoming services like media streaming, data explorer and data hub are some of the major freeways that gives you your ticket to get into the business of cloud. Few certifications are also available like 70-583 : PRO : Designing and Developing Windows Azure applications. Honestly, I wont opt appearing this exam as it got released in Feb 2011 and Azure drastically changed in June 2012. So I would still take an interest to pursue it, but wait to appear the final certification till the product gets stable.

Azure skills are definitely niche skills, but not so rich in demands that you would be weighted in gold for having it. To make a career you need to sell your thoughts at the right platform. If you can pull out few interesting POCs or Azure based solutions on 3-month free trial version of Azure, and flaunt around by sharing the same on Codeplex that can gain you some real good value. Dumb thesis of white papers describing genesis of any aspect of Azure won't give you any real business in Azure, thou it can give you some recognition in the technical journalism arena.

Career Challenges due to Windows Azure: This section is for those who believe that cloud poses threat to practitioners who are not ready to adapt to change. Adaptive attitude towards technology is one of the phenomenon that any successful technology practitioner would have experienced throughout one's career. RFPs (Request for Proposals) are getting more and more competitive, OPEX (Operational expenditure) and CAPEX (Capital Expenditure) are being measured edge to edge for delivery excellence, and with services like IaaS and SaaS, cloud is inducing a mind shifting game in the business.

In earlier days and may be even today, application developers consider themselves expert if they know caching and SOA. With azure platform, distributed cache that geo-replicates and synchronizes itself using SDKs like Data Sync, CDN and app fabric is a simple matter of config that can be achieved using clicks. What should I do with your caching expertise that works only in-premise, takes tons of resources, licenses and non-flexible solutioning that forces me to use all the products from microsoft stack instead of a hybrid cost-effective hybrid-platform solutioning ??

Many DBAs still bank on their hyper-V, SCOM, virtualization and clustering skills as their magic business. I created a SQL 2012 ready image with all standard HADR config within minutes. If I own a SMB scale application, do I really need you who just knows the IaaS part, if azure promises to manage it for me with 99.95% operational availability ? With Microsofts support for cross-platform and open source, this market has become more competitive than ever. Party has just started and more guests from non-microsoft world are invited with open hands. As per a report, by 2014, 80% of the new softwares would be traded on cloud as a service. Those who still have not got the point are marching their way to museums :)

Migrating SQL Server and application server deployments across cloud environments

Big names in the cloud business are Amazon, RackSpace, Azure (which is still catching up) and others. For those who don't know, Amazon makes more money from it's cloud business than selling books on the web. Another interesting fact is that Amazon had built its vast infrastructure for its e-commerce based business and then started optimally using the same infrastructure by using the same for its cloud business. Google seems to be following the same path, and recently joined the cloud business. Like other cloud players including Azure, google offers cloud services like Google App Engine, Google Compute Engine, Google Cloud Storage, and Google BigQuery. The talk of the town and star of the fleet is IaaS offering of Google, i.e. Google Compute Engine, which is powered using 600,000 cores and can process queries in parallel on such huge computing power using its Exacycle technology. Its still in beta and to the best of my knowledge it supports just Linux based VMs on KVM workloads.

This news is interesting to learn, but how does it affect us ? Systems Portability from one cloud to another cloud is a key factor in considering any cloud environment. At times a part of the system architecture might needs to be ported to any particular cloud environment due to its suitability for the architecture for reasons like geographic location, services offered, pricing, proximity with the application hosting environment etc. Managing SQL Server instances and deployments across multiple clouds is a part of this problem statement.

Like Data Market available on the Azure platform, there also exists a cloud marketplace. Any business can become huge with the right idea, one should just have an eye to figure out that idea. System or software configuration is typically considered a DBA or Server admin or side activity. Can you think of a business just out of it ? Can you think of migrating a part of full solution from one cloud environment to another in a matter of minutes or hours ? Can you think of migrating your application and/or database servers from private cloud to public cloud and vice versa in a matter of minutes or hours ?

If the answer is "not really", RightScale is a company that one should take a look. This company deals in the business of providing ready server templates which are basically configuration and scripts that you can readily submit to the cloud vendors, and your server along with software installation would get setup in minutes. Almost every standard and non-standard server based technologies as well as operating systems are supported. So with these server templates, one does not have to worry about the targeted cloud environment. A sever template having any particular configuration can be applied to Amazon cloud environment in the same way as it can be applied to Google cloud environment. Clients are abstracted from the details of converting the configuration syntax, methods and operational details from one cloud environment to another. The potential of this such companies can be capped from the fact that even companies like Google depend on such third-party cloud translators to help clients smoothly migrate from one cloud environment to another.

Supported cloud environment include: Amazon Web Services, Data Pipe, Google Cloud Platform, IDCF - Yahoo Japan, Korea Telecom, LogicWorks, Microsoft Azure, RackSpace, SoftLayer, Tata and others private cloud environments. Server portability along with software installation, configuration, monitoring, as well as auditing across such a huge variety of cloud environments is a silver bullet for any cloud based architecture design for various aspects. It opens up new doors that can't be even thought of if this cloud portability is not on the table. Still challenges like data migration/replication/synchronization have to be dealt with, which has another solutions too.

For a better Azure adoption, it is very necessary to have such translation mechanism inherently available on the Azure platform itself. There are many players just in this business of selling configurations and images. They are not visible to Architects till you fall in need of them in time of crisis to migrate your server and systems deployments from one cloud to another. 

Data Integration Services on SQL Azure platform

They say that any knowledge never goes waste, but in IT parlance this saying can be redefined as any knowledge or DATA never goes waste. In my career till date, my experience has been that any successful business would have lot of external data processing as a part of its business functions. Competitive Sales Intelligence for example is one of the category of data that many IT organizations would keep of processing to defines best analytical insights for their sales team. Companies like Facebook and Google accumulate hoards of data and makes a fortune out of advertising business. But even these giants depend on external data providers for their business functions. Example of one such data provider is Factual, that provides data to Facebook. Monetizing on carefully curated and certified business databases has become a very big business.

Cloud takes this platform of sharing and trading data one step ahead. Windows Azure Marketplace provides DataMarket for the same purpose where applications can share and trade data.

1) Private cloud and public cloud comes into question, as organizations might want to share their datasets but limited to the scope of the organization only. Microsoft codename "Data Hub" claims to provide a flavor of managed self-service enterprise data integration on the cloud, which generally takes a huge team and data centers to serve the same needs of an organization. This platform is expected to provide private data-market to enterprises which can be very interesting in terms of agility and cost-savings.

2) Any sizeable organization would generate and consume lot of internal as well as external data. Integration and sharing of data is implementation of the solution after the source of data has been recognized. Data discovery from within and outside organization for business needs, is a bigger challenge in itself. Microsoft Codename "Data Explorer" can be seen as self-service SSIS on Azure platform. It provides data discovery from the windows azure marketplace as well as provides features for self-service data mashups from a variety of standard data sources. Hadoop is not yet included in the supported data source list, but if its gets included in the future, this platform can reap immense value and can acts organizations private Google blended with SSIS to create self-service data mashups and again publish the same as a source of data using Data Hub.

Power of Hadoop combined with cloud based tools like Data Hub and Data Explorer can generate business for lot of data providers as well as bring immense value to organizations. Also it would enable better use of data and provide cost-savings in enterprise data integration.

 

How to use MS BI with Hadoop and Why to use Hadoop with SSAS

IT Professionals who use DBMS, SQL, ETL, Reporting and/or Advanced Reporting, and Analytics consider this as the end of data ecosystems. But this is just mainstream IT sphere in the world of data. I do not intend to emphasize of the potential riding on Hadoop, as there are tons of reference material available for the same. If you want to quick check the direction of wind, you can simply fly a kite, you dont need a satellite weather report. Translating it into plain terms, if you want to get a hint of Hadoop's potential, just google on what data and analytics related companies are upto these days. You would find that database giants like TeraData, Microsoft, Informatica and others are ramping up big efforts to provide support for Hadoop. The big businesses that run on Hadoop are Facebook, Yahoo, LinkedIn, Twitter and others. This suffices to conclude that if you are a vetern opportunist in industry, Hadoop is one of the most promising targets.

The challenge of Hadoop starts with bringing it to mainstream IT, which is mostly warehousing data, reporting it and providing analytics. This methodology generally requires activites like data profiling, data cleansing, ETL, and creating warehouses / marts.

1) From the mainstream database world, Hadoop is a source as well as destination. Its more like a content management system functioning in the the form of a database. Hadoop is a MPP system that can run on parallel nodes reaping peta-byte scale data processing speeds. Cloud is one of the most appropriate infrastructure for the same. Windows Azure was already supporting Hadoop VM installations and now a new offering in underway which is known as Hadoop based services for Windows Azure.

2) Why to use Hadoop when we already have SSAS with the power of BISM ? Well any analytics professional would have this question. Theory does not wet the apetite of a practitioner, so the best answer is a case study video featuring how Klout leverages Hadoop and Microsoft BI Technologies to manage BIG Data.

3) Microsoft has announced a connector SQL Server Connector for Apache Hadoop, its a old news. But if you pay attention to detail it says its Sqoop based which is an open source tool provided by Cloudera which imports data from SQL Database into Hadoop Clusters. It can be a very nice to learn tool to start building your skill stakes in the Hadoop world. Any application would have to pump-in and pump-out data from Hadoop, so import export of data from SQL based databases to Hadoop is an inevitable process.

I plan to make MS BI, Analytics and Visual Business Intelligence coupled with BIG Data and cloud as my new regime. I would be sharing my experiences, thoughts and views on the way through my journey. I have introduced a new section on my blog titled Hadoop, BIG Data and Cloud and added a few useful links under the same. I would be adding more to this section, to keep a watch on the same.

In my views, a rolling stone gathers no moss. I intend to earn the same amount of money and respect and recognition I earn in a year, in a months time. I believe that if one has got a dream like this, one needs to be insightful and embrace the change, be a part of the change and make efforts to change the world thats not ready to change.

For my regular blog readers: My blog has remained silent for around 6 months, and my authoring presence has been going south. To my surprise from the blog statistics I was able to make out that the site visits have remained constant and at times have gone even high that it was ever, even without any activity on my blog. This gives me motivation to keep moving on and being an MVP I also feel an obligation on my shoulders to keep sharing my experiences. The reason for my low authoring activity have been my personal life, and I am turning on the lights of my blog after 6 months straight.

In the time that I took almost a break from blogging, two platforms that I have evidenced influencing the IT Industry limited to the scope of my perspective as a Solutions Architect for Business Intelligence and Analytics, are Android and Hadoop. I would discuss about Android at some other time, this post is about Hadoop.

SQL Azure Federation Architecture Solution

Federations is one of the most effective aspect of any architecture design, which basically provides de-centralization of operations, effectively providing parallelism, scalability, de-coupling and multi-tenancy. Cloud has become a dominant platform for any kind of architectures, and federations on the cloud for multi-tenant solutions are even more interesting and challenging. In Microsoft parlance, this would translate to SQL Azure Federations. A lot of reference material is already available on the same, so I would not go into explaining the same again. Below are some very useful links that answers some very important questions like:

1) What are Federations ?
2) What is Federation Architecture ?
3) What kind of solutions can use Federation Architecture ?
4) How to scale an application with SQL Azure Federations ?
5) Sample application using SQL Azure Federations for multi-tenant solution
6) Data Migration on SQL Azure Federation

Azure Design Patterns

I have been very busy these days with personal matters, and have not been able to extract time for blogging. I apologize to my readers for the same. Microsoft Azure cloud environment is a rapidly growing cloud platform. With the release of Hadoop connectors for SQL Server, support for Hadoop implementations and Azure Table Storage, Azure provides a reasonable support for unstructured data which is going to be the need of the future. But along with this one more area is growing largely that churns our lot of business and effectively lot of data too - Social Media.

Microsoft has newly released Windows Azure Toolkit for Social Games version 1.0. Integration with social media has become a necessity for almost every other sizable organization today. LinkedIn is one of the best examples where organizations are trying to hook in the social connections of employees for a more intelligent recruitment. Going social is one of the best moves by Azure team.

Azure is classified more from a compute and storage criteria, but from different perspectives of development if would make sense to categorize it into more detailed criteria. When you speak of development, design pattern eventually comes into picture, thou in database world it is used less compared to application world. Buck Woody has put up a very nice website to study these design patterns, and I feel its definitely worth checking out. Its called Azure Design Patterns.

SSIS on Cloud with SQL / Windows Azure : Future Applications

Generally cloud and ETL have a typically known application of facilitating ETL, which mostly harnesses the elasticity of computing resources. ETL on cloud is ideal for applications where data is already stored on the cloud. Regular line of business applications would process data from OLTP sources and load the data on another data repository on the cloud. For ETL to source and load data from in-premise data sources, WCF and related RIA services are employed. Using Amazon EBS cloud and customized VM images, you can setup your ETL on cloud and Amazon EBS out-of-box supports SSIS standard version. Right when Azure was in it's CTP, I had authored two articles on SQL Azure, on how to read and write data to SQL Azure using SSIS and SSRS 2008 R2. But this is something that is already well-known. What's new ?

Semantic Web, Unstructured Data and technologies that store, process, analyze, warehouse such data and extract intelligence out of the same is the new challenge that is at the horizon of the IT industry. Few front line IT majors have tsunami sized data generated everyday due to the virtue of popularity of social media, and such organizations like Google, Yahoo, Facebook, etc have already started wrestling with these challenges. The benefits of processing unstructured data, and driving your business based on the extracted intelligence are very clear from the example of these companies where they within a duration less than a decade, their advertising revenues are worth billions and still skyrocketing. All standard business house have lots of unstructured data like emails, discussion forums, corporate blogs, recorded chat conversations with clients etc. Organizations generally have aspirations to build a knowledge base for all the different areas of business functions, but when they start seeking consulting on the strategy to implement the same, they find themselves in a whirlpool of processes and financial burdens. Unstructured data has the potential to generate data for such knowledge base.

Still the question in your mind would be, what has this to do with SSIS and Azure? Regular applications of SSIS are known to everyone, and more than that professionals would be knowing that SSIS is not supported on Azure cloud platform, which might be the motivation of reading this post as the subject line reflects that is might be supported now. SSIS is an in-memory processing architecture, and implementing the same of shared / dedicated cloud environments has its own challenges. I am also sure that SSIS Team must be on its way to bring it to the crowds in the time to come. But my interest in on the future application of SSIS on unstructured data, and hosting it on Azure cloud platform.

When SSIS would gain such capability, applications like Extractiv would be common across enterprises. To build application like Extractiv using SSIS, solution design would be too crippled as SSIS is not inherently blended with cloud. One day I would like to see SSIS packages getting executed as a crawler service in Sharepoint, which would crawl entire site and data on Sharepoint portals like FAST Server, extract entities from unstructured data and populate next generation of warehouse on the Azure cloud platform, that would be queried using LINQ for HPC kind of technologies. For me, applications like Extractiv are very fascinating, as they inspire to foresee ideas, that are window to opportunities which most are not able to envision right now.

SQL Server AlwaysOn and SQL Azure Data Sync for HADR

HADR i.e. High Availability and Disaster Recovery is not a very familiar term for BI professionals, but it indeed is the life line in the DBA community. There are great features in SQL Server for the same purpose, but to the best of my limited DBA knowledge, it considerably depends on the kind of topology and configuration adopted for the same purpose. SQL Server 2008 R2 was a special treat for BI professionals, but now SQL Server Denali is committed to be fair to everyone, and SQL Server AlwaysOn is the new offering in the HADR category. CTP1 is available with partial features of this service. A new topology, new server requirements and new set of expectations are set with the advent of this service. For more information on SQL Server AlwaysOn, check out the SQL Server Denali AlwaysOn portal.

SQL Azure Data Sync is another such service on the Azure platform that can be seen as a HADR provider for cloud. Ofcourse data centers take care of the disaster recovery part of the data stored on cloud, but in situations where data is exchanged in-premise and off-premise across data consumers, data sync can play a very active role in the high availability part of the data stored on cloud.

Imagine a solution the encompasses SQL Server AlwaysOn coupled with SQL Azure Data Sync ! I am sure this recipe has the potential to beat even RAID based solutions in the future. I can't predict much about the future, but for the present, AlwaysOn and SQL Azure Data Sync are worth keeping a watch whether you are on earth or on cloud.

Azure Books - Windows Azure Book and SQL Azure Book : Review

In cloud dictionary, Azure in considered a union of SQL Azure and Windows Azure platform. Cloud platforms are a very buzzing choice for almost every kind of business. A cloud based platform proposed directly by Microsoft with potentially promising integration features for .NET as well as SQL i.e. application as well as database is one of the must try choice, even if industry leaders like Amazon EC2 are available. I myself have been a part of the book "Microsoft SQL Azure: Enterprise Application Development", in the capacity of one of the official technical reviewers.

Recently I have been engaged by Packt Publishing, to provide an open review of the book "Microsoft Azure: Enterprise Application Development". Shortly I would provide my review after reading this book completely. There are not many books in market today on Azure platform, and these books can be helpful to anyone who intend to start from zero knowledge on Azure platform. Azure is quite mature today from the time it went RTM, and those who are yet to parse the dictionary meaning of Azure in the parlance of cloud, this is very high time one should pick up a book to get a procedural education of Azure platform. Below are the links to these books.

Update:

I have been very late in sharing the review as my new job keeps me quite busy, so I have not been able to spend time on this item. Below is the review of the book "Microsoft Azure: Enterprise Application Development".

This book is almost of the same flavor as the SQL Azure book. Each chapter explain a different topic followed by a working example with step-by-step explanation. Certain topics like Web role, Worker role, Monitoring and Analytics, Table & Blob storage etc are very important topics from a Windows Azure perspective and those are reasonably well covered. I would have liked more depth in these chapters, but the chapter provides more references for curious minds who wish to dig more deeper in any particular topic. All-in-all I feel that this is the best start-up handbook for professionals who are fresh to Windows Azure environment and intend to come up to speed on Windows Azure environment.

Book on SQL Azure - Reviewed by Siddharth Mehta

Packt Publishing generously provided me an opportunity in the past year to be part of a book on SQL Azure titled "Microsoft SQL Azure Enterprise Application Development", in the role of Technical Reviewer. I am very passionate about cloud based business intelligence, and MS BI on the cloud is one of my favourite topics. This book became available at stores in Dec 2010. As I have been a part of this project, I can confirm one thing, this book is worth every penny. I am not trying to sell the book, but as I have been a part of this project in a deep technical role, I had the opportunity to have a thorough analysis of the subject when the book was in drafts. If you are looking out for a book on SQL Azure plus a decent starting lessons on Windows Azure, this book is definitely for you. Book doesn't expect any level of maturity on cloud and you can start from zero and reach at least intermediate level at the end of this book. In case if you have/had procured the book and had a chance to go through the book, please feel free to share your view with me.

Project Atlanta and Quest Software's Azure Services : Cloud based services related to SQL Server

Entire blogosphere related to SQL Server is cheering the release of SQL Server Denali CTP1, and everyone seems to be in a race to figure out the new features in this CTP. Some of the road blocks for developers to test this CTP are limited supported operating systems and number of issues during installation. SSIS seems to be loaded with a lot of enhancements with some great sounding support for data quality with new tools and controls.

Coming to the topic of this post, after the availability of Database Manager for SQL Azure, SQL Azure Reporting is one step forward in bringing SaaS model in SQL Server ecosystem. Project Atlanta and Quest Software's Azure Services are two of the cloud based SQL Server related SaaS / PaaS (as I have not analyzed it thoroughly) services.

Microsoft's official description for Project Atlanta is as follows: "Microsoft Codename Atlanta is a cloud-based service that analyzes installations of Microsoft SQL Server 2008 (and later) and provides proactive alerts to help you avoid downtime and adhere to Microsoft's best practices. Detection of potential issues is based on knowledge provided by experienced Microsoft Support engineers who keep it up to date to leverage the most recent experience in supporting SQL Server deployments around the world". Though it sounds very exciting, there is another dimension to this offering, which Brent has described in his post. Do not miss the conversation between Brent and the Program Manager of Project Atlanta. This is one case study where one can take few lessons, that can be helpful in developing proprietary solution offerings on similar lines.

Seems like Quest software already had the same idea in mind. An excerpt about the video where executives from Quest Software shares their views and perspectives about their cloud based IT services on Windows Azure platform in as follows: "Quest is an early adopter of the Windows Azure platform. They’ve built out an extensive services framework as well as a few initial service offerings that sits on top of Windows Azure Framework and Windows Identity Framework. One of the key elements in the design of Quest’s framework is secure communication and authentication between all the service components and layers, whereby encryption is based on certificates stored within the dedicated certificate store provided by Windows Azure". This is one another case study from which lessons can be taken for your future solution offering on Windows Azure / SQL Azure platform.

If you analyze the trend of the wind, you can easily make out the SQL Server product vendors are gearing up to provide cloud based solutions, some of which might be effective and some might be not. From a solution provider point of view, when your client might request a PaaS / SaaS solution on Windows Azure or SQL Azure, case studies like these can provide good insight from an architecture as well as designing of solution offering standpoint. I am wondering what would be Red Gate 's answer to these services, as SQL Toolsbelt might not be sufficient when cloud comes into picture.

Data storage on Windows Azure and SQL Azure

Windows Azure, SQL Azure, Blob storage, Local storage, Table storage, Queue storage, web role, worker role, Content Delivery Network, Dev Sync etc.. are some of the buzz words which indicates that there are Azure bees around you. If you are not keeping the pace with the advancements that are constantly being made available on this platform, it might feel like a very tough time when you are faced with a situation when you need to develop a POC or an architecture design that works on Azure platform. Fortunately there are a lot of whitepapers and MSDN library that can help you to get started.

But the first thing that clicks my mind when I start the architecture / solution design, is with some basic questions like, how will I capture data and where will I store data. Maintaining session state, implementing security, auditing, logging, error control and reporting, need for a collaboration platform, scalability and performance considerations, migration considerations etc.. are all vertical placeholders in a architecture design than runs across the solution. But when I need to figure out my horizontal figures, my source and destination are the basic blocks. If you have figured out the data storage as Windows Azure either as a destination / staging location / hosting platform, the answer for data storage is not as simple as "enterprise edition of 64-bit SQL Server", whether it's a BI solution where you might use Azure as a temporary / permanent staging location or an Application solution where you might use Azure in place of on-premise SQL Server. With cloud this gets quite more complex, and factors like pricing and storage capacity comes into consideration.

Recently there's a very nice article published on technet, that is worth keeping in your arsenal as a part of intelligence building exercise on Windows Azure platform, to tackle your first encounter with Windows Azure environment. This article provides nice overview on data storage offerings on Windows Azure platform, and the same can be read from here.

SQL Azure Reporting versus SSRS : Challenges in cloud based reporting

SQL Azure Reporting is soon expected to be available on Azure platform, and as the name suggests, it is expected to cater reporting out of SQL Azure. A nice comparison of cloud based SQL Azure reporting and on-premises SQL Server Reporting Services can be read from this page.

Nice part about this cloud based reporting is that one can author report from cloud and host it on the cloud. But when authoring is done on the cloud, the only data source available would be SQL Azure. This makes the scope quite limited for the use of such reports. Earlier too, it was possible to create reports out of SQL Azure databases using SSRS, and one of my popular articles on how to implement the same can be read from here. Instead of being a PaaS / SaaS solution, I see it just as an extension to SQL Azure feature set, something similar to export data functionality. Support for reporting from on-premise data using WCF / RIA services would have made this reporting solution much admirable.

It's also being heard on a few blogs, that one can now wash away their hands from maintaining the reporting infrastructure as reporting is available on cloud, but there are a lot of questions before this statement. In my views, there must be some exceptional reasons to implement a reporting infrastructure for a cloud based database. Instead of it, the best solution is a PaaS / SaaS reporting solution.

Probably it's too early to make any comments right now, and you can get more information from this PDC video. In summary the good news is that it's the first ray of hope to see BI related features and services on cloud. You can sign up for the SQL Azure Reporting CTP from here.

MS BI POC and SQL Azure POC for better ROI on future projects

In my views, POCs ( Proof Of Concepts ) is a very versatile way of creating and promoting business, especially for vendors and solution providers. There was a very interesting article on POCs on BeyeNetwork some days back, that talked about how decisions based on limited scoped POCs can be catastrophic for an enterprise.

If POCs are carried out with a viewpoint of extracting results that can be translated into patterns / benchmarks that can catalyze agile and/or economic BI solution development methodology as well as provide avenues for appliance / product development, such POCs have a very high probability of an assured Return On Investment (ROI) against the investments done for conducting such POCs. For example, MDX is quite a complex query language, and developers need time and experience to master the same. If templates or CLR class libraries are created using ADOMD.Net and MDX, same can be reused via template explorer in SSMS and .Net application development, which can effectively boost up the development process and reduce the learning curve. This is very similar to DAX available in Powerpivot. Also if such libraries are developed with a broad vision in mind, it can be promoted as a off-the-shelf component. And I am sure that the ROI would be exponentially more compared to investments.

Below is a brief list of few such POCs, which has the potential to provide a very high ROI, provided it's carried out with a vision and target in mind.

1) SaaS solution development methodology using MS BI Stack and SQL Azure

2) Pattern development for synchronizing in-premise SQL Server and SQL Azure

3) Plugging / Integrating Cloud gateway tools at different phases of BI lifecycle

4) Economic BI solution development using SQL Azure / SQL Express , SSRS and Sharepoint Foundation 2010

5) MDX Script library in compiled / script format

6) Pattern development for economic cloud database (SQL Azure) consumption in BI and application development projects

7) Pattern development for Inter cloud integration and multi cloud incorporation into MS BI projects for provider-dependency free cloud consumption in MS BI projects.

I am not claiming that these are the best ones, but I can say that these are the best ones in my mind.

Connection error in SQL Azure

Some of the major reasons for connection issues in SQL Azure are as follows:

• Lock Consumption
• Log File Size
• Uncommitted Transactions
• Transactions blocking system calls
• TempDB Usage and Size
• Excessive Memory Usage
• Maximum Database Size
• Transaction Termination
• Denial of Service Attacks
• Network Problems
• Failover Problems

Confused / Curious / Impressed with the above points ? Well, I cannot take credit for what I have summarized from a SQL Azure whitepaper. Same can be read from here. If not the entire whitepaper, at least the above list can be quite handy while troubleshooting for the root cause of connection issues with SQL Azure. Also with little modification, this list can be translated into development standards for application interfaces to connect with SQL Azure.

Use of multiple cloud environments with MS BI solution without changing architecture

In my earlier post, I said that one of the critical feature for selecting a cloud vendor for use with your existing BI solution, is to check the feasibility whether this requires an architecture change and if yes, what is the magnitude of the required change. The complexity in this case is much elevated when an enterprise has contracted with more than one cloud vendor. Actually it's a wise policy to have more than one cloud vendor and replicate your data across these cloud environments. In case your primary cloud vendor is down for some reason, you can always use your secondary cloud as a contingency server.

Here we are talking about using cloud as a file server and not as a computing server. From a design point of view, one can think of this architecture where we would be using cloud as a SAN (Storage Access Network). There are many interesting use of this design, whereby without changing the regular architecture, BI applications can take advantage of cloud. For ex., say that your ETL solution powered by SSIS requires to create huge staging at various stages of ETL processing and say this staging is in the form of some files like RAW Files, Excel files or Flat files. on various cloud environments. Or consider a scenario where a central repository of master data feed is created in the form of some files on various cloud environments which would be read by centralized or federated reporting applications. In such scenarios, client and/or server machines needs to be able to access cloud storage without a major architecture shift. For this requirement an intelligent cloud gateway is required which would select the appropriate cloud environment and also enable easy access these cloud storage environments.

One such product of this flavor is Gladinet Cloud Desktop. This tool has very interesting features to act as a shell for various cloud environments. If you want to cloud enable your client machines or servers, this tool can act as a very intelligent gateway for managing your cloud. The USP of this tool is that it adds the required cloud storage environments as a local virtual directory on the machine of which it's installed, and the software takes care of maintaining a cache between the cloud environment and local storage. By using the APIs of this product or by programmatic control, this product can be used to manage your cloud based storage without changing the architecture design of your existing solution. Though there are great complexities involved and I do not claim that this is perfectly suited for the design in consideration, but the capabilities that this product brings to the table for managing cloud based environment is very unique and can be utilized in many scenarios.

In summary, if your BI solution involves dealing with multiple cloud environments, I feel that this product can be very effective in such scenarios. Other services provided by Gladient worth checking out are Gladinet CloudAFS and Gladinet Cloud Backup which provides live backup of SQL Server to the cloud. I do not have enough balance in my pocket to pay the bills for various cloud environments as well as this product to develop a POC. But in case if you do or your enterprise is looking out to develop a POC for any solution that involves multiple cloud environments, then this product is worth giving a shot. I would like to hear back on this from those who were able to digest this idea that I have attempted to present in this post.

How to select the best cloud environment for your business intelligence solution ?

Amazon EC2, Rackspace, Salesforce, GoGrid are some of the famous cloud providers, and Microsoft Azure is probably the newest kid on the block. Cloud is gaining popularity day by day, and businesses as well as solution providers would want to move their existing applications or base their future applications on the cloud. But there are a few factors which should be considered for evaluating different cloud providers that suit your needs. Below is a brief list of such requirements in alignment with BI / MS BI needs.

1) Platform migration without architectural changes : Applications that are already developed when needs to be migrated to cloud, change in architecture of the application due to limitations or constraints of the cloud vendor is out of question. For this requirement the cloud vendor should be providing IaaS and not just PaaS services. The reason I mentioned PaaS and not SaaS is that if one is considering to use SaaS , this would make sense for your future requirements but not for your existing applications. Amazon EC2 provides a major set of services to cater this requirement. Unfortunately, to the best of my knowledge, Microsoft Azure is not up to the mark till date for this requirement.

2) Support for Private Cloud : There might be some very sensitive business logic in WCF (Windows Communication Foundation) services or similar other interfaces that one might not want to expose on the cloud. So the cloud vendor should also be supporting private cloud like Amazon VPC or Windows Azure Appliance Solution.

3) Software Licensing : Many cloud providers do not facilitate use of corporate licenses that enterprises would have procured, and many software vendors do not provide license for the use of product on cloud. Licensing needs to be levelled from software vendor as well as cloud vendor, so that software licenses can be easily used / reused on cloud environment.

4) PaaS Support for operations related to data : In a typical BI project, involving ETL, one can expect more than one database and different forms of staging ( in different file formats ). Cloud vendor should be supporting multiple relational DBs and storage formats required to support data storage. For example, SQL Azure and Project Houston provide a nice platform for data storage, design and operations. Windows storage provides three different kinds of storage formats. But in case if both are required to operate in the same environment, where I need to pull of data from SQL Azure and store in a file format provided by Windows Azure ( say BLOB storage ), both are not on the same platform. If your project has such dependencies, this should be taken care before considering your cloud vendor.

5) Ease of backup and restore operations : Most cloud vendor provides features that flush out any data as soon as you stop paying for the instance i.e using the instance. For permanent data, a separate dedicated storage needs to be purchased. Backing up and restoring this instance can be one big concern. Also enterprises might want portability for such instances, where one might want to create images on the cloud and use it for multiple projects or solutions. Amazon Machine Images is one such example in alignment to this requirement.

Please note that I have considered Amazon for the sake of discussion, but by no means this implies that Amazon EC2 is the best cloud vendor for your requirements. It always depends on the requirements of an enterprise, billing rates and services provided by cloud vendor in accordance with the requirements of the enterprise. The above listed factors should be evaluated at early stages before adopting the cloud environment and considering a cloud vendor.

Microsoft Business Intelligence ( SSIS / SSAS / SSRS ) on Amazon Elastic Compute Cloud ( EC2 )

One of the reasons, SSIS is making it's entry into most of the data warehousing eco-systems, is due to it's competitive pricing and the immense support that it has inherently got from SQL Server as a database engine. But when you are in a competitive world, you need to hit the best possible estimates that wins over others. Pricing is a very big factor, especially when your organization is acting as a service provider as well as solution provider. Difference is that as a solution provider, you develop a solution for your client, deliver it and you are done. As a service provider you are expected to host the solution and provide it as a service for the agreed duration of time within the agreed Service Level Agreements (SLA).

Windows Azure and SQL Azure are growing consistently in the cloud computing environment, but still they are not mature enough to provide an integrated environment for SaaS flavor of BI solution on Azure platform. One such cloud computing environment is provided by Amazon Elastic Cloud Compute known as Amazon EC2. IT cannot work in isolation and neither can MS BI, so it's immature to consider just Azure for cloud requirements related to MS BI.

EC2 is a huge platform and has got a lot of services and products. Below is my flavor of understanding and explanation of same, considering the services of interest in alignment to MS BI.

1) Amazon Web Service (AWS) is the medium by which Amazon provides IaaS (Infrastructure as a Service) to it's clientele. Technically speaking, these services are made available in the form of a web service. All the services listed below fall under the classification of AWS. You should read this page to check out for the relational databases supported by Amazon.

2) Amazon EC2 : A web service that provides resizable compute capacity in the cloud. This can be considered a super version of Windows Azure. Consider Amazon EC2 as your processor and Amazon Elastic Block Storage as your hard drive on the cloud. Other services of interest available with EC2 are Amazon CloudWatch, Elastic-Load Balancing, and Elastic IPs.

3) Amazon SimpleDB : A web service for running queries on structured data in real time. It provides the core functionality of a database, real-time lookup and simple querying of structured data without the operational complexity. SQL Azure can be considered similar to this.

4) Amazon Simple Notification Services (SNS) : By using Amazon SNS, developers and businesses can send notifications or messages to applications or people using a “push” mechanism and have these messages delivered over protocol of choice. Probably there is no equivalent of this in Azure services stack.

5) Amazon Simple Storage Service (S3) : A simple web services interface that can be used to store and retrieve large amounts of data, at any time, from anywhere on the web. This is similar to Windows Azure Storage.

6) Amazon Virtual Private Cloud (VPC) : A secure and seamless bridge between a company’s existing IT infrastructure and the AWS cloud. Amazon VPC enables enterprises to connect their existing infrastructure to a set of isolated AWS compute resources via a Virtual Private Network (VPN) connection, and to extend their existing management capabilities such as security services, firewalls, and intrusion detection systems to include their AWS resources. This is an advanced / flexible version of what Windows Azure Appliance Solution is trying to provide.

The main service of our interest, if we intend to start our development or production using MS BI stack on Amazon cloud, is Amazon EC2 running Microsoft Windows Server and SQL Server. Express edition and Standard edition of SQL Server 2005 and SQL Server 2008 are available. This would mean that enterprise level applications can't be executed on this environment.

If I consider a scenario where I need MS BI capabilities for a few months with a handful of developers, instead of buying servers, hardware and software licenses, it would be a cost effective way to start piloting your solution with the kind of platform and services provided by Amazon EC2. If you are thinking about the cost differences, a Standard Edition SQL Server for a Workgroup license of 5 developers would cost at least a couple thousand dollars (at least in 5 digits), and with EC2 it would not cost you even in 4 digits for a couple of months. You don't believe this ? Well, check it out using AWS Simple Monthly Calculator. As I keep telling, cloud is all over the place and if you don't find cloud, cloud would find you !!!