SAP on AZURE: SAP BusinessObjects Business Intelligence Platform Setup with Azure SQL DB (Managed PaaS database)
This blog describes the setup of BusinessObjects Business Intelligence Platform 4.2 SP8 with Azure SQL DB which is a managed PaaS database solution in azure. Azure SQL server/DB is supported from BO/BI version 4.2 SP8 onwards, refer to SAP BusinessObjects BI 4.2 SP8 Product Availability Matrix (PAM).
Also the High Availability and Disaster Recovery deployment of the system is described in the blog.
Azure SQL DB
Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. PaaS capabilities that are built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.
Best for modern cloud applications that want to use the latest stable SQL Server features and have time constraints in development and marketing.
A fully managed SQL Server database engine based on the latest stable Enterprise Edition of SQL Server. SQL Database has two deployment options built on standardized hardware and software that is owned, hosted, and maintained by Microsoft.
With SQL Server, you can use built-in features and functionality that requires extensive configuration (either on-premises or in an Azure virtual machine). When using SQL Database, you pay-as-you-go with options to scale up or out for greater power with no interruption. SQL Database has some additional features that are not available in SQL Server, such as built-in high availability, intelligence, and management.
Sizing Models for Azure SQL DB
SQL Database offers the following three purchasing models:
- vCore based
- DTU based
vCore based Model
The vCore-based purchasing model lets you choose the number of vCores, the amount of memory, and the amount and speed of storage. The vCore-based purchasing model also allows you to use Azure Hybrid Benefit for SQL Server to gain cost savings. Service tier options in the vCore model include General Purpose, Business Critical, and Hyperscale. The service tier generally defines the storage architecture, space and I/O limits, and business continuity options related to availability and disaster recovery.
General Purpose model is most suitable business workloads. It offers budget-oriented, balanced, and scalable compute and storage options. Resource options and limits are described here.
Business Critical model Offers business applications the highest resilience to failures by using several isolated replicas and provides the highest I/O performance per database replica. Resource options and limits are described here.
For SAP BO/BI environment, it’s convenient to use vCore based model and chose either General purpose OR Business critical SKUs.
DTU based Model
The DTU-based purchasing model offers a blend of compute, memory, and I/O resources in three service tiers, to support light to heavy database workloads. Compute sizes within each tier provide a different mix of these resources, to which you can add additional storage resources.
Service tiers in the DTU-based purchase model are differentiated by a range of compute sizes with a fixed amount of included storage, fixed retention period for backups, and fixed price.
The serverless model automatically scales compute based on workload demand, and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed, and automatically resumes databases when activity returns. Resource options and limits are described here
Serverless Model is more suitable for intermittent, unpredictable usage with lower average compute utilization over time. This model can be used for non-prod environment of SAP BO/BI deployment.
Part I – Standard Setup of SAP BO/BI with Azure SQL DB.
In this section, describing the step by step procedure for a basic installation SAP BO/BI with Azure SQL DB. High Availability and Disaster recovery setup is not included in this section.
Following are the details of the reference setup which are described in this blog:
|azwinbocms1||CMS Server||Windows Server 2016|
|azwinboweb01||Tomcat WebServer||Windows Server 2016|
|azpaassqldb01||Azure SQL Server||–|
In cost conscious setup, we can install CMS Server and Webserver in the same VM.
- Read the required Installation Guide, SAP Notes, SAP on Azure docs and download the installation media.
- Deploy the VMs as per the system architecture and Choose Operating System as Windows Server.
- Include disk on each of the VMs for CMS & Web Tier VMs and create the drives.
- Join the VMs to the Domain.
- Define Page File in Temp Disk (D Drive).
- Check that necessary Ports are open in Windows firewall.
- Disable the Continuous Availability Feature in Windows using the instructions in this link.
Deploy the Azure SQL DB and Create CMS & AUDIT Database
- Go to Azure marketplace and search for Azure SQL.
Select Resource Type as Database server and click on ‘Create’.
- Enter the Database Server details.
Enter Azure DB name, resource group, location, server admin user & password.
- Summary Screen and then click on ‘Create’.
- Once Azure SQL Server is created, create the CMS & AUDIT DB.
Above screenshots shows the creation of CMS DB. Repeat the steps to create the AUDIT DB.
- In the firewall rule, Add the vnet of the VMs for CMS & Web Server.
Add the vnet of the apps VMs will allow connection between Azure SQL Server DB’s and VMs. Also change the setting to ‘No’ for Allowing Azure services and resources to access this Azure DB server.
SAP BO/BI CMS Server Installation
- Login to CMS server (azwinbocms1) as administrator.
- Setup ODBC connection with Azure SQL DB
- Download ODBC driver version 13.1 and install on CMS server.
- Goto to Control Panel -> Administrative Tools -> ODBC Datasource (64-bit) -> System DSN. Click on ‘Add’ to create connection for CMS database.
Test the connection and click on ‘Finish’.
- Repeat the above steps to create connection for AUDIT database.
- Install CMS Server
- Run setup.exe.
- Select the Language Package. Default is English.
- Select Custom/Expand Option to choose the application to be installed.
- Choose the location of the installation. Installing it on I drive.
- In the ‘Select Feature’ screen, Uncheck the ‘Web Tier’ and ‘Sybase SQL Anywhere DB’.
- Choose the Option, Start a New SAP BO/BI platform deployment.
- Select DB type for CMS Repository: MS SQL Server using ODBC.
- Select DB type for Audit DB: MS SQL Server using ODBC.
- Continue with Installation/configuration parametes.
- In the screen to enter Configuration details for CMS & Audit DBs. Check the DB server & DB name. Enter the DB username & password.
- In next screen, continue with entering other configuration details and CMS Installation.
SAP BO/BI Web Server Installation
- Login to webserver (azwinboweb01) as administrator.
- Run setup.exe
- Select Web Tier as installation type
- Choose features, configure, and enter CMS server name and port to continue with the installation.
Part II – High Availability (HA) setup of SAP BO/BI Platform with Azure SQL DB
HA for Azure SQL DB
Azure SQL DB is highly available by design. The goal of the high availability architecture in Azure SQL Database is to guarantee that your database is up and running minimum of 99.99% of time. The high availability solution is designed to ensure that committed data is never lost due to failures, that maintenance operations do not affect your workload, and that the database will not be a single point of failure in your software architecture. There are no maintenance windows or downtimes that should require you to stop the workload while the database is upgraded or maintained.
There are two high availability architectural models:
Standard availability model that is based on a separation of compute and storage. It relies on high availability and reliability of the remote storage tier. This architecture targets budget-oriented business applications that can tolerate some performance degradation during maintenance activities.
Premium availability model that is based on a cluster of database engine processes. It relies on the fact that there is always a quorum of available database engine nodes. This architecture targets mission critical applications with high IO performance, high transaction rate and guarantees minimal performance impact to your workload during maintenance activities.
We can also have Availability Zone based deployment in Business & Premium Tier. More Details on HA for Azure SQL DB is available in the link.
SLA for Availability, RPO and RTO are described in the link.
HA for CMS, Web Server & FRS
We can achieve HA in the CMS server by adding additional CMS servers and including them as part of cluster. Webserver HA can be done by adding additional webserver and defining an Internal Load Balancer to route the user connections to either of the webserver. FRS can use highly available File Share which can be deployed using SOFS/ANF/SIOS Datakeeper in Azure.
Refer to the blog for guidance on the HA setup of CMS, Webserver and FRS in Azure.
Part III – Disaster Recovery (DR) setup of SAP BO/BI Platform with Azure SQL DB
DR for Azure SQL DB
Azure SQL DB provide auto-failover group across region for DR setup. Auto-failover groups provide read-write and read-only listener end-points that remain unchanged during failovers. Whether you use manual or automatic failover activation, failover switches all secondary databases in the group to primary. After the database failover is completed, the DNS record is automatically updated to redirect the endpoints to the new region.
- In Azure portal, Select the Azure SQL server (azpaassqldb01) and click on Failover Group -> Add group.
- Click on Create a new server
- Enter the Secondary SQL DB server details and Azure region.
- Define the Failover Group name, select the Databases for CMS & AUDIT DB.
- Failover Group is successfully created.
Note the Read/Write Listener details which will be used for connection from ODBC DSN in CMS Servers for CMS & Audit DB.
Also update the firewall rules for Secondary DB server to be accessible for specific Vnets in which CMS & Web Servers are part of.
- Update the server name with Listener and test the connections for CMS & AUDIT DB.
DR for CMS, Web Server
Setting up Azure Site Recovery (ASR) from Primary to DR region for CMS, Web servers are done for the DR setup. ASR will be replicating the VM images and in case of disaster, we can activate the VMs in the DR region. CMS Servers will get auto connected to the Azure SQL DB as Listener names are maintained in the ODBC DSN.
Once ASR syncing is completed and replication status is ‘healthy’, we can perform ‘Test Failover’ to isolated DR vnet and test the CMS & web server connection to the Azure SQL Server DBs.
During DR activation, need to failover (using ASR Disaster Recovery -> Failover) the CMS and Web Servers VMs in the DR region. In the DB layer, Azure SQL Server DBs which are part of Failover Group, need to be failed over from primary region to DR region.