Why BI on Cloud
Many BI Projects fail due to various reasons including Lack of Vision, Overly Complex Design,
Inappropriate Choice of
Tools/Technologies, Poorly Defined Metrics/KPIs, Inadequate Business User/Decision Maker Involvement, Lack of Room/Flexibility for change during implementation phase, Insufficient Funding/Budget
Allocation, Increased Time to Market, and many others.
Of these reasons, Lack of Flexibility for change, Insufficient Funding / Budget Allocation, and Time to Market are few major reasons for failure. Organizations often fail to realize the importance of BI System and hence allocate inadequate funds. Procuring Hardware, Software, and Setting up Infrastructure takes up a lot of time due to processes (approvals etc.) involved at the organization level, thereby increasing the Time to Market, and often does Abstract Today’s Business Intelligence (BI) Systems are analysing huge volumes of data, which is growing at a rapid pace requiring organizations to scale the hardware/infrastructure at the same pace to continue to do BI and make the best use of the available data. Procuring and adding hardware/infrastructure is a time consuming process in any organization and hence leading to delay in getting access to the right information at the right time. To overcome this and the other limitations of on-premise BI Systems, BI Applications can be hosted to Cloud and various benefits of Cloud can be leveraged to deliver efficient/effective BI Solutions to the organization’s decision makers.
This white paper focuses on hosting a Microsoft (SQL Server) Business Intelligence (BI) Application on Cloud using Microsoft’s IaaS offering “Windows Azure Virtual Machines” (Currently in Preview). Since Windows Azure Virtual Machines are currently in preview, these VMs or the steps outlined in this white paper should not be used in a production/mission critical environment.
Here after we will use the terms Cloud, Windows Azure, and IaaS interchangeably.
not offer enough flexibility for change, and hence lead to Project failure. There is a need for a system/offering which allows sufficient flexibility during the phase of implementation of a BI Project, allow sufficient agility, affordable hardware/software, and faster procurement of Hardware / Software and Setting up of Infrastructure. These things are offered by a Cloud Computing offering like Windows Azure. In fact, those are some of the attractive features offered by Cloud Offering making Cloud most
appropriate for hosting BI Applications in various scenarios. Apart from that, following are other advantages of hosting BI Systems on Windows Azure: • Code can be moved from
on-premise to cloud and vice-versa with minimal or no changes. • Procurement, Maintenance, and
Management of infrastructure is taken care by vendor.
• Server, Hardware, and Network failures are handled by vendor and high SLAs are guaranteed.
• The development/administration tools to be used while working with SQL Server in a Windows Azure VM are same as the ones which are used for working with on-premise SQL Server.
• Ability to scale up/down rapidly on-demand. It’s highly elastic! • Eliminates the hassles and
processes involved in procuring hardware in an on-premise scenario.
• Allows you to use your piece of software/applications on the Virtual Machine. Ability to upload an Image (VHD) of your own and use it in the Virtual Machine makes
it super convenient.
• Pay as you go model. Pay for # of hours and decommission after use. Say you need a Windows Server with SQL Server 2012 and 15 TB of storage for 5 days. Provision it, use it, and drop it when done. It’s as simple, convenient, and cost effective as that!
• Very suitable especially for scenarios like Pilot Projects, Startup Companies etc. Hosting BI Application on Cloud Hosting a BI Application on Cloud involves setting up/hosting of various components like Databases,
Integration Services Packages (SSIS), SQL Server Agent Jobs, Analysis Services Cubes (SSAS), Reporting Services Reports (Reports), and SharePoint Portals etc.
Below is a high level architecture of how a Simple SQL Server BI Application can be setup on Cloud. In the above architecture diagram, VM1, VM2, & VM3 can be scaled out by using appropriate techniques for the type of components installed on the respective VMs like Database Sharding can be used for SQL Databases, Multiple Cubes can be created for OLAP Cubes etc. Scaling out offers various additional benefits including Load Balancing, High Availability, and Disaster Recovery etc. Microsoft Windows Azure is both a PaaS as well as IaaS offering since it offers components like SQL Database (formerly known as “SQL Azure”), Reporting Server as part of PaaS offering and it offers components like Virtual Machines, Storage etc. as part of its IaaS offering.
Currently Windows Azure has SQL Database and Reporting Server as part of its PaaS offering and does not have other components like Integration Services, SQL Server Agent, and Analysis Services etc.
SharePoint is available as a SaaS offering as part of Office 365. As a result of this, we can take one of the following two approaches for hosting a BI Application on Cloud:
Hybrid Model (Combination of SaaS, PaaS, & IaaS): Use Office365 for SharePoint, and Windows Azure SQL Database & Reporting Server for hosting data and reports respectively. Use Windows Azure Virtual Machines for hosting Integration Services Packages, SQL Server Agent Jobs, and Analysis Services Cubes etc.
IaaS Model (IaaS Only): Use only Windows Azure Virtual Machines to host all the components end-to-end including Databases, SSIS Packages, SSAS Cubes, SSRS Reports,
Excel/PowerPivot Reports, and SharePoint etc.
Since Data and Reports can be hosted in both PaaS and IaaS models, we will take a look at pros and cons of hosting them in each of the two models.
Windows Azure SQL Database vs. SQL Server Database in a Virtual Machine
Criteria Windows Azure SQL Database SQL Server Database in a Windows Azure VM
Feature Support
Transact-SQL Few Limitations compared to on-premise SQL Server Database. Supports 100% of the features that are supported on-premise. Cross Database Queries Not Supported Supported
Others Does not support Heaps. All tables should have a Clustered Index. No restrictions. Same as on-premise.
Data Storage
Maximum Size Supported 150 GB (In Business Edition) 16 TB (Precisely 16 * 1023 GB on Extra Large VM) Cost of Storage Low compared to SQL Server in VM Little higher compared to SQL Database
Connectivity
SQL Server Management
Studio (SSMS) Supported Supported
SQLCMD Supported Supported
Scalability
Scaling-Up Increase the size by changing the database edition. Increase the size by adding more data disks.
Scaling-Out Can be achieved using SQL Database Federation. Can be achieved using Replication, Scalable Shared Databases, and AlwaysOn etc. Manageability Developer Tools Existing SQL Server Tools such as SSMS, BIDS/SSDT Existing SQL Server Tools such as SSMS, BIDS/SSDT
Control
Virtual Machine No Control Full Control
Operating System No Control Full Control
Distribution of Database
Files etc. No Control Full Control
Authentication SQL Authentication Supported Supported
Windows Authentication Not Supported Supported Exchange Servers
Active Directory Servers
Database Servers
Application Servers IIS/Web Servers On-premise Infrastructure Cross-Premise Connectivity (Virtual Network) VM1 Database/ETL Server • Source Files • Staging • Warehouse • Audit/Log • SQL DBs • SSIS Packages • SQL Jobs • OLAP • Cubes VM2 OLAP/Cube Server • Reports • Websites • SharePoint Portals • Front End Tools (PowerPivot Excel rtc.) VM3 Frontend/Reporting Server
Windows Azure Virtual Network
Windows Azure SQL Reporting Services vs. SQL Server Reporting Services in a Virtual Machine
Following is a simple comparison of Windows Azure SQL Reporting Services and SQL Server Reporting
Services in a Windows Azure Virtual Machine based on few common criteria.
Criteria Windows Azure SQL Database SQL Server Database in a
Windows Azure VM
SLA Availability Standard SLA (99.9%)
High Database Availability can be achieved using multiple VMs in an Availability Set and SQL Server AlwaysOn Availability Groups.
Time to Market
Migrating Existing Application
Some changes might be required in the application layer. Hence moderate time to market.
Very minimal or no changes required in the application layer. Hence faster time to market.
Building a New Application Provisioning and setting up is simple and hence faster time to market.
Provisioning and setting up a VM takes some time and hence moderate time to market.
Criteria Windows Azure SQL Reporting Services SQL Server Reporting Services in Windows Azure VM
SLA 99.9%
99.9% for Single Server.
99.95% for two servers in Availability Set.
Developer Tools SQL Server Business Intelligence Development Studio (BIDS) SQL Server Business Intelligence Development Studio (BIDS), Report Builder Supported Data
Sources Only Windows Azure SQL Database. All data sources same as on-premise Reporting Services.
Report Caching Not Supported Supported
Report Parts Not Supported Supported
Shared Data
Sources Supported Supported
Shared Data Sets Not Supported Supported
Subscriptions Not Supported Supported
Scheduling Not Supported Supported
Scaling Not Supported Supported. Can be achieved by increasing the size of VM.
Conclusion
Based on the above comparisons, we can see that, hosting a SQL Server BI Application in a Windows Azure VM
has more advantages for both SQL Databases and SQL Reporting
Services, and also offers more features like Integration Services, Analysis
Demo: Setting up a Simple SQL Server BI Solution on IaaS (Windows Azure)
Let’s setup/host a Simple SQL Server BI Application (Database, SSIS, SSAS, & SSRS) on Windows Azure (SQL Server on IaaS).
Following is the architecture of the simple solution which we will be setting up as part of this demo. Following is a step by step procedure to setup/host a Simple SQL Server BI Application on Windows Azure.
Note: For this exercise, it is assumed that you have hands-on experience working with all the components of SQL Server (Database Engine, SSIS, SSAS, & SSRS).
Login to Windows Azure Portal www.WindowsAzure.com if you already have an account. If not, then sign up for a free trial
(
http://www.windowsazure.com/en-us/pricing/free-trial/) and create an account and then login.
1. Create a Virtual Network Create a Virtual Network (VNET)
BIOnCloudVNET in Windows Azure (Reference: Create a Virtual Network in Windows Azure). Create a new Affinity Group BIOnCloudAG. Exchange Servers
Active Directory Servers
Database Servers
Application Servers IIS/Web Servers On-premise Infrastructure Cross-Premise Connectivity (Virtual Network) VM1 Database/ETL Server • Source files • Prepareation • Staging • Warehouse • Audit • SQL BDs • SSIS Packages • SQL Jobs • OLAP • Reports VM2 OLAP/Cube Server BIOnCloudVNET Windows Azure Virtual Network
REPORTS
2. Create Virtual Machines Add two Medium-Size Virtual Machines DemoETLVM and DemoOLAPVM to the
BIOnCloudVNET (Reference: Add a Virtual Machine to a Virtual Network).
Choose the SQL Server 2012
Evaluation Edition Template from the Windows Azure VM Gallery.
For simplicity, we have not set any Availability Set for the above VMs. To
achieve High Availability, Availability Set can be configured.
Virtual Machines in Windows Azure are currently available in the following sizes/formats:
VM Size CPU Cores Memory Bandwidth # of Data Disks*
Extra Small Shared 768 MB 5 Mbps 1
Small 1 1.75 GB 100 Mbps 2
Medium 2 3.5 GB 200 Mbps 4
Large 4 7.0 GB 400 Mbps 8
Extra Large 8 14 GB 800 Mbps 16
* Each Data Disk can be of maximum 1 TB (precisely 1023 GB).
3. Add Data Disks to VMs
Attach one 2GB data disk to both ETL & OLAP VMs.
Add Firewall Exceptions
• Add an Inbound Rule/Exception in Windows Firewall for SQL Server Database Engine Port # 1433 (Default Port) in both the VMs by running the following command at the Command Prompt.
• Add an Inbound Rule/Exception in Windows Firewall for SQL Server Analysis Services Port # 2383 (Default Port) in OLAP VM by running the following command at the Command Prompt.
• Add an Inbound Rule/Exception in Windows Firewall for SQL Server Reporting Services Port # 80 (Default Port) in OLAP VM by running the following command at the Command Prompt.
Configure SQL Server
• Create required folders in the newly added drive to hold the database files and update the Server Properties to set the default location for Data, Log, & Backup files location and then restart the SQL Server.
• Change the SQL Server Security to Mixed Mode on both the VMs.
• Restart the SQL Server Service on both the VMs. • Configure the Startup Type of SQL Server Related
Services on both ETL & OLAP VMs depending upon the type of server (ETL/OLAP) and the required functionality (DB, ETL, Cube, Reports etc.).
• Add a SQL User DemoSA to both the servers. 4. Configure Virtual Machines
Add Endpoints to Virtual Machines • Add an Endpoint on Port Number 1433
(Default Port for SQL Server Database Engine) on both ETL & OLAP VMs.
• Add an Endpoint on Port Number 2383 (Default Port for SQL Server Analysis Services) on OLAP VM.
Script to add DemoSA user on SQL Server.
Test Connectivity between VMs Try to connect from DemoETLVM to DemoOLAPVM using the SQL Server User created above. Also, try to connect from DemoOLAPVM to DemoETLVM using the SQL Server User created above. Following are the observations when tried to connect: • Connects to Database Engine both
ways using SQL Server
Authentication (from DemoETLVM to DemoOLAPVM and
DemoOLAPVM to DemoETLVM) • Connects to Analysis Services on
DemoOLAPVM from DemoETLVM.
ETL Deployment on ETL VM
Install Drivers for MS Excel
• Download and install the 64-bit drivers for Microsoft Excel
(Microsoft Access Database Engine 2010 Redistributable). This is for extracting data from sources like Microsoft Excel, Access etc. Deploy ETL Components
• Deploy the ETL Components on the ETL Server.
• Chang the Log On As for SQL Server Agent Service on the ETL Server to
“DemoETLVM\Administrator”. • Note: This is required since the job
needs to access the resources on other VMs on the network. For instance, the package needs to create and process the partitions on a different VM.
• Control Panel Administrative Tools Services SQL Server Agent Log On Update Account and Password. OLAP Deployment on OLAP VM
Deploy OLAP Components
• Deploy the OLAP Components on the OLAP Server.
Reports Deployment on OLAP VM
Configure Report Server
• Configure the Report Server on DemoOLAPVM.
Deploy Reports
• Create required folders on the Report Manager for Data Sources and Reports.
• Create Data Source (s) pointing to appropriate database (s) on ETL/OLAP Server (s).
• Upload the Reports to Reports folder on Report Manager. • Configure the data source of all
the reports and point them to the appropriate data source created above.
Deployment Verification • Monitor the SQL Agent Job to
completion and ensure that the jobs are running smoothly without any access or any other issues. • Connect to SSAS from both OLAP
& ETL Servers and ensure that you are able to connect to and browse SSAS Cubes from both the servers. • Access the reports on OLAP Server from both OLAP & ETL Servers and ensure that you are able to run the reports from both the servers. • Connect to Relational Database
(Data Mart/Warehouse) on the ETL database from Excel, PowerPivot, SSRS, and SSAS etc. from on-premise computer and verify the connectivity.
• Verify the Overall Deployment. That concludes the step by step demo to setup a Simple SQL Server BI Solution on Cloud.
Next Steps
As a next step, you can go over the below resources to setup or use other related features of Windows Azure. SharePoint Deployment
Deploying a SharePoint Farm with Windows Azure Virtual Machines Cross-Premise Connectivity Create a Virtual Network for Cross-Premises Connectivity
Active Directory Setup
Install a Replica Active Directory Domain Controller in Windows Azure Virtual Networks
Install a new Active Directory forest in Windows Azure
Best Practices for Running SQL Server in a Windows Azure Virtual Machine
Following are the recommendations from a Storage and Database standpoint:
• Consider placing the data and log files on separate drives.
• Consider placing the tempdb on a non-persistent disk, since the data is required for the duration of the transaction/operation and not permanently.
• Consider using database page compression. This will help in reducing I/O.
Summary
Cloud Computing offers various advantages over on-premise systems as discussed above. Setting up a SQL
About Aditi
Aditi helps product companies, web businesses and enterprises leverage the power of cloud, e-social and mobile, to drive competitive advantage. We are one of the top 3 Platform-as-a-Service solution providers globally and one of the top 5 Microsoft technology partners in US. We are passionate about emerging technologies and are focused on custom development. We provide innovation solutions in 4 domains:
www.aditi.com
www.facebook.com/AditiTechnologies www.linkedin.com/company/aditi-technologies References
Windows Azure Manage Center: https://www.windowsazure.com/en-us/manage/windows/
Additional Resources
• Data Migration to Windows Azure SQL Database - Tools and Techniques: http://msdn.microsoft.com/en-us/library/windowsazure/hh694043.aspx • Migrating to SQL Server in a Windows Azure Virtual Machine:
http://msdn.microsoft.com/en-us/library/windowsazure/jj156165.aspx • Best Practices for Running SQL Server in Windows Azure Virtual Machine:
http://social.technet.microsoft.com/wiki/contents/articles/11578.best-practices-for-running-sql-server-in-windows-azure-virtual-machine.aspx • Deployment Considerations for SharePoint 2013 on Windows Azure Virtual
Machines:
http://gallery.technet.microsoft.com/Technical-Deployment-db645804 Dattatrey Sindol (aka Datta) is a Passionate Microsoft BI Developer
