SAP on AZURE: HIGH AVAILIABILITY setup for SAP BusinessObjects Business Intelligence 4.2 SP8 with SQL server on Windows
This blog describes the High Availability (HA) setup of SAP BusinessObjects Business Intelligence 4.2 SP8 with MS SQL Server on Windows in MS Azure Cloud. It uses SQL DB AlwaysOn Windows Cluster to provide the HA for database layer and SAP CMS Servers cluster & loadbalanced Tomcat Web Servers for HA in application & web layer. FRS component is in SOFS Clsuter.
By setting up HA solution, we can protect SAP BO/BI & DB (on SQL Server) against Infrastructure & VM failure and removes all the Single Point of Failure (SPOF) for the environment. This document can be used as reference for deployment of HA environment for the SAP BO/BI workloads on Azure and not intended to cover other aspects of system design like infra, network, security, and performance.
Following are the major components of the setup.
SQL SERVER DB Cluster – MS SQL Server is one of the supported database for SAP BO/BI. To achieve the HA of DB, used SQL AlwaysOn WSFC Cluster. Internal Load Balancer(ILB) is used to define the Virtual IP of Listener. Virtual name/IP of AlwaysOn Listener will be used in ODBC Connection setup of CMS Cluster server so that Application connection are redirected to Primary DB after DB failover/failback.
CMS Cluster – Redundancy is achieved by setting up two CMS servers in active-active mode. CMS cluster is defined at Server Intelligence Agent(SIA) configuration in Central Configuration Manager.
Web Application Server – A set of Tomcat Web Application server needs to be deployed in active-active mode for HA setup of Web tier. Load balancing and HA will be achieved by setting up virtual IP in ILB and mapping the virtual IP to hostname in DNS. Tomcat Webservers have config files in which CMS Cluster name and multiple server hostnames are defined.
In cost-conscious approach, we can combine CMS servers and Tomcat Web Server in same pair of VMs and have one virtual IP for both the components.
File Repository Server(FRS) – FRS location needs to be common for both of the CMS cluster servers. This can be achieved by creating a highly available SMB/NFS share. In this setup, already existing SOFS cluster (for SAP Netweaver Deployment) is reused. We can also use Azure File Share or Azure Netapp Files(ANF) available for the SAP landscape in Azure.
Following are the details of the reference setup which are described in this blog:
|Hostname||Description||OS Version||DB Version|
|azwinbo42sv1||CMS Node1||Windows 2016||–|
|azwinbo42sv2||CMS Node2||Windows 2016||–|
|azwinboweb1||Tomcat WebServer 1||Windows 2016||–|
|azwinboweb2||Tomcat WebServer 2||Windows 2016||–|
|azwinbowebha||Virtual hostname for WebServer Cluster||–||–|
|azwinbosqdb1||SQL Server DB Node1||Windows 2016||SQL Server 2017|
|azwinbosqdb2||SQL Server DB Node2||Windows 2016||SQL Server 2017|
|azwinbosqllsnr||Virtual hostname for AlwaysOn Listener||–||–|
|sofssycl1||FRS Share SOFS Node1||Windows 2016||–|
|sofssycl2||FRS Share SOFS Node2||Windows 2016||–|
The setup has used Azure availability set features which provides 99.95% of VM availability. Same setup can be used for availability zone based deployment which provides 99.99% of availability apart from FRS share SOFS cluster. We need to use zone redundant FRS share option.
This deployment can be extended for Disaster Recovery (DR) in paired Azure region by setting up third SQL Server DB VM and setting up AlwaysOn replication (async method). CMS & Tomcat webserver can be replicated to DR region using Azure Site Recovery(ASR).
Overview of Installation Steps
Following are the high-level installation steps for SAP BO/BI HA setup:
- Read the required Installation Guide, SAP Notes, SAP on Azure docs and download the installation media.
- Preform the Preparatory Steps.
- Define the ILB for SQL DB, CMS & Tomcat WebServer.
- Install the SQL DB and Create CMS & AUDIT databases.
- Install the first CMS server
- Install additional CMS server and configure cluster
- Setup the SQL AlwaysOn DB Clsuster
- Install the Tomcat Web Application Servers
- Reconfigure the File Repository Server (FRS) in SMB share
- Test the High Availability failover.
- Deploy the VM in Availability Sets as per the system architecture and Choose Operating System as Windows Server.
- Include data and log disk on each of the DB VMs and disks for CMS & Web Tier VMs.
- Add disk to SOFS Nodes for File share cluster for FRS share.
- 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.
Define the Internal Load Balancers (ILB)
- ILB for SQL DB
- Define the front end IP
- Define the backend pool of SQL DB servers : azwinbosqdb1 & azwinbosqdb2
- Define the health probe port
- Define the Load balancing rules
Make sure “session persistence” set to “None” and Floating IP is enabled.
- Create an A-record entry in the DNS server.
- ILB for Tomcat WebServer
- Define the front end IP
- Define the backend pool of SQL DB servers : azwinboweb1 & azwinboweb2
- Define the health probe port
- Define the Load balancing rules
Make sure “Session persistence” is set to “Client IP and protocol” and “Floating IP” is disabled.
- Create an A-record in the DNS server.
Install the SQL DB and create CMS & AUDIT Databases on DB VM1
- Login to Node1(azwinbosqdb1) of SQL DB VM as administrator.
- Start the SQL DB install on VM1 and select the features.
- Define the Instance Name
- Use domain DB users for SQL server agent and Database Engine
- SQL Server configuration
- Database Engine configuration. Select the data and log drives.
- Installation of SQL DB is successful.
- Enable TCP/IP configuration and define the TCP port.
- Create Database BOCMS & BOAUDIT for the CMS and Audit database
- Create a db user. In this deployment created db user ‘boadmin’.
- Set the default database for the Database User to the CMS database.
- Assign publicand sysadmin roles to user ‘boadmin’ under user properties -> Server Roles
- Only Assign the db_ownerand public roles for ‘BOCMS’ and ‘BOAUDIT’ databases under user properties -> User Mapping.
Install the First CMS Server
- Login the first VM (azwinbo42sv1) for CMS Server as administrator.
- Create ODBC Data source for CMS DB
- In Control Panel -> Administrative Tools -> ODBC Data source (64 bit) -> System DSN.
- Enter the db username & password
- Click on Client Configuration to update the DB port.
- Select the DB Name
- Test the DB connection
- Repeat the above steps to create Data source for Audit DB. Make sure to choose the Audit DB BOAUDIT.
- Install the CMS Server
- Run the Setup.exe
- Select the Language Package. Default is English.
- Select Custom to choose the application to be installed.
- Choose the location of the installation. Installing it on I drive.
- Uncheck the ‘Web Tier’ and ‘Sybase SQL Anywhere DB’
- Start the New SAP BO/BI platform deployment
- Select DB type for CMS Repository: SQL Server using ODBC
- Select DB type for Audit DB : SQL Server using ODBC
- Configuration Parameters
- Connection details for CMS & Audit DBs
- CMS configuration parameters
- Configure SMD agent & Introscope are optional and can be done later.
- Start the installation
- Post Installation Steps
- Login to Central Management Console and complete the steps.
- Installation is completed
Install Additional CMS Server and configure cluster
- Login to second VM(azwinbo42sv2) for CMS Server as administrator
- Create ODBC Data source for CMS & Audit DB
- Install the additional CMS Server (Including only relevant screenshots)
- Run Setup.exe
- Select the Installation type as Custom / Expand.
- Choose the Installation location. Used I drive.
- Select Features. Uncheck WebTier and SQL Anywhere DB
- Select the Expand the existing SAP BO/BI deployment
- Select the SQL Server as DB type for CMS and Audit Repository.
- Local SIA Node name
- Existing CMS Server (node 1) details
- Cluster Key (Key created during CMS first node installation)
- Enter CMS port for additional server
- Connection details for CMS and Audit DB repository. Must create System DSN ODBC connection in advance from Control Panel.
- Select automatic Server start.
- Configure HTTP Listening Port
- Configure Subversion Repository.
- Choose configuration option for SMD agent and Introscope Manager.
- Start the Installation
- Finish the installation.
- Configure the CMS Cluster
- Open the Central the Central Configuration Manager in one of the CMS server.
- Stop SIA and right-click to select Properties.
- Go to configuration Tab
- Click on Change Cluster Name to and enter the Cluster Name
- Below screen shot shows cluster is already configured and its name is BOCMS-BID-HA
- Start SIA
- Login to Central Management Console and verify the cluster.
Setup SQL DB AlwaysOn Cluster
- SQL DB setup and configuration on Node2 (Refer to the steps in SQL DB setup on VM1)
- Install the SQL Server DB on second VM (azwinbosqdb2)
- Create Databases BOCMS and BOAUDIT.
- Define the same TCP port for connection.
- Create db user ‘boadmin’ and configure its access to the databases.
- Restore DB backup of BOCMS & BOAUDIT from Node1 with ‘Restore with Norecovery’ option.
- Setup WFSC AlwaysOn cluster for SQL DB.
- Install Failover Clustering and .Net Framework 3.5 features on both the cluster Nodes.
- Create cluster. Select the Node1 of cluster
- Define the cluster IP in DNS.
- Update the Cluster IP and start resources.
- Add second node to cluster
- Define the Cloud Witness to cluster in an Azure Storage account.
- Enable AlwaysOn High Availability on both nodes of SQL Servers from Config Manager
- Define AlwaysOn Availability group from SQL Studio.
- Define the SQL Listener
- Use the virtual IP defined in ILB and define the listener name in DNS.
- In the Windows Cluster Rolespane, right-click the availability group name, and then select Add Resource > Client Access Point
- In the Name box, create a name for this new listener.
- Click Next and Finish.
- Take the availability group cluster role offline. Stop the Role.
- Right click on IP Address and add dependency.
- Set the Cluster parameters using Powershell Commands.
- Check the SQL AlwaysOn Cluster.
Download the “sap_syncronize_always_on” script from the below link.
- In both CMS Servers, Update the SQL Server ODBC connection for BOCMS & BOAUDIT to point to listener IP in place of SQL server hostname.
Install the Tomcat Web Application Servers
- Login to VM1 (azwinboweb1) as administrator.
- Setup the Webapp Server
- Run setup.exe
- Select Web Tier as installation type
- Choose the Installation Path
- Choose Features
- Define parameters
- CMS Server Details
- Select Introscope Manager config option
- Start the Install
- Post Install message and Finish the setup
- Check and update the cluster info properties file which defines the CMS cluster name and servers hostname and IP addresses.
- Repeat all the above steps to Install Tomcat Web Server 2 on VM2 (azwinboweb2).
- Update RESTful Webservice to point to virtual hostname/IP of Tomcat WebServer.
- Issue is described in SAP Note 2576124
- Open Applications tab in Central Management Console and select RESTful web service.
- Update the properties of RESTful webservice to virtual/logical hostname of Tomcat WebServer from actual CMS Node1 hostname and change the port number to Tomcat port(default 8080). If this is not changed then during CMS Server Node1 unavailability, Fiori based BI Launchpad will not work.
Reconfigure the File Repository Server (FRS) in SMB share
- Created an SMB share in SOFS cluster which is pre-existed for SAP Netweaver setup “\\sapglobalhost\sapmnt\BID\FRS”
- Open the Central Configuration Manager(CCM) and Stop the one of SIA.
- From CMS Server and copy the contents of FRS directory to SMB share.
- Start SIA from CCM.
- Login to Central Management Console and Disable the FRS.
- Update the property for all the highlighted services to point to the New Location for Input, Output and temporary FRS directories.
- Click Save and Close.
- Enable the Services in CMC.
Test the High Availability failover
Details of failure tolerance process for each component.
Database Tier – SQL DB AlwaysOn will keep synching the Primary and Secondary DB, in case of unavailability of Primary DB, will failover to secondary DB. Client connection to DB are using listener IP which is virtual & defined in ILB and get redirected to the active primary DB.
CMS Server Tier – Two servers on CMS are running in active-active mode in which both the nodes are available for connections from web tier. CMS cluster details are maintained in properties file in Web server. In case of failure of any one of the node, other node will still be available for existing and new connections.
Web Tier – A pair of web servers are running in active-active mode. Users are using virtual IP/hostname defined ILB and connections are redirected to either of the two Web Servers. In case of unavailability of one of the web servers, ILB will direct connection to the available web server.
FRS – It is part of highly available SOFS cluster and in case of any of the SOFS node is down, file share will still be available from another VM of the cluster.
There are mainly 2 kinds of HA testing needs to be performed:
- Planned – In case of planned unavailability of the one the node of the cluster pair. This can be tested by stopping the services on the node and verifying that SAP BO/BI is available to users.
- Unplanned – This can be tested by crashing the VMs and removing the storage from VMs. In windows environment, VM crashing can be achieved by the tool ‘notmyfault’.
This completes the Highly Available setup of SAP BO/BI 4.2 SP8 with SQL Server DB on Windows.