Monday, February 14, 2011

Planning MS BI shared environment for production systems

I'm reading: Planning MS BI shared environment for production systemsTweet this !
Multinational Corporate Clients generally have data centers hosting their BI environments and different production systems are hosted on such environments. Edition upgrade is a hard fact of the IT world and everyone right from developers till production systems needs to go through this change. The reason why I term this as a hard fact, is because synchronizing with this upgrades is quite demanding.

Business analysis is becoming more competitive, predictive, and intelligent with the passage of time, and the same demands more intelligent means of analyzing data. In a corporate environment, there are many discrete business units, each requiring - developing - hosting their own set of applications on a common BI environment hosted in data centers, managed by a centralized operations team. I know of many clients who started hosting their BI environments with the SQL Server 2005 platform, but they forget the fact that this is not the ultimate version. In a shared environment many applications are hosted on the same SQL Server instance, which might be managed using a cluster on SAN volumes.

The real challenge is when a few applications require application upgrades and the rest do not vote in favor of upgrade. So the challenging question faced is, how to isolate your application and instance out of the 50 odd applications hosted on the same instance. Significant amount of hardware resources (Multi core motherboards, Dedicated servers, SAN volumes, Load Balancers, Clusters, etc..) and human resources (DBAs and Operations Support Staff) are invested for maintaining the environment. Added complexity arises from the security configuration, as corporate environments have Single SignOn configuration using mechanisms like NTLM / ADFS. Asking for a separate instance is almost guaranteed to fail approval.

In my experience, I have observed certain exercises that can help to avoid encountering such circumstances. I am sharing a few of them below:

1) Decouple each service to the maximum extent possible. In raw words, at least do not install all the services like SSIS / SSAS / SSRS all on same box.

2) Document the features used in all your artifacts, so that you are always in control of what an upgrade can cause to which entities. For example, documenting features used in each reports would be beneficial, when you are upgrading from 2005 to 2008 R2. If possible try to create entities having common feature usage on dedicated instances, without ending up with a dozen instances. Use your intelligence and create only as much as you can afford to manage.

3) As soon as a new edition is out, create a track of deprecated, discontinued, upgraded, breaking and value added features. Creating a risk analysis sheet should be regular exercise during the release of each new edition. Digging a well when you are faced with fire is a bad exercise. I mean that when you are faced with the urgency of upgrade and then you start planning the migration strategy is a considerably late exercise and lack of long term planning.

4) DBAs generally freak out with installation of a tool as tiny as Upgrade Advisor too, when it comes to installation on a cluster. Shared instance of SQL Server for different applications makes them completely paranoid. So it's always better to keep green zones on production servers, for installation of tools as a part of contracts / SLAs so that shared stakeholders do not convert your SQL Server production instance into a DMZ.

5) Take extra care for components that are considered as shared by SQL Server architecture itself. Most designers ignore the little detail that SSIS is a shared component in the MS BI Stack, and this can be catastrophic when you are faced with an upgrade on an environment where 20 applications are using the same SSIS instance.

6) Use this guide as a detailed reference material to assess the risk that you might be already running with your existing production systems.

If you have more tips to add to this post, please feel free to share your comments or email me your viewpoint.

No comments:

Related Posts with Thumbnails