Configuring Pyramid Analytics BI Office for Disaster Recovery
by Joey Pruett , Presales Engineer and IT, Pyramid Analytics
Pyramid Analytics BI Office is a highly available, enterprise-level BI suite that offers best-in-class performance and integration with the Microsoft BI stack and underlying server technologies. BI Office fully supports and uses Windows Server Failover Clustering (WSFC), as well as SQL Server AlwaysOn technologies to provide disaster recovery via failover, both on the internal local area network (LAN) and between geographically dispersed, multi-site, multi-subnet environments.
If a disaster occurs, BI Office clients can be automatically redirected within minutes – so that you can meet your business’s service level agreements (SLAs).
In this whitepaper, you will learn the high-level steps and tips necessary to deploy BI Office in a disaster recovery (DR) scenario by using technologies that are already available in your existing on-premises and cloud-based Microsoft and Pyramid Analytics’ technology stack.
I will describe a three-node deployment, shown in the figure below, with the primary two nodes being on-premises and a third node in an Azure cloud – ready to seamlessly deliver BI Office in the event of a disaster at the primary, on-premises site.
The SQL relational repository database that contains BI Office’s underlying content and settings are automatically synchronized with the secondary site database. SQL Server Analysis Services (SSAS) cubes can be configured for automatic synchronization between each cube processing.
Three-node deployment used in this scenario
The following are the high-level steps for configuring, deploying, and testing the solution in a disaster recovery scenario:
1. Confirm deployment of Active Directory and DNS
This disaster recovery scenario assumes you have deployed Active Directory with DNS spanning on-premises and Azure sites. Clustering requires all of the nodes to be member servers in the same domain.
2. Prepare networking
You must configure the Azure virtual network before you create your virtual machines.
3. Prepare VPN connection
Once you have configured the Azure network subnet and VPN, Azure provides a PowerShell script, customized for your subnets, to automatically configure your on-premises Edge device for connecting to the VPN. A VPN connection to Azure creates a site-to-site, persistent connection between your on-premises and cloud networks
4. Prepare host servers
When creating Azure VMs, select the virtual network subnet you have created, as opposed to an affinity group or region. Selecting the same virtual network will place all VMs on the subnet and in the same region.
Important Do not install and deploy applications in this step. Use static IP addresses whenever possible and follow best-practice guidelines, such as multiple network interfaces, when you are deploying production solutions.
5. Deploy SQL Server
In order to add a new AlwaysOn, secondary replica on the Azure server, you will install stand-alone SQL Server instances on all nodes – primary and secondary.
Important If you plan to use Kerberos authentication and/or delegation, you must use an Active Directory domain service account for the SQL Server Database Engine. Although it is possible to use different, multiple domain service accounts for each different installation of SQL Server, it is recommended that you use the same service account for the SQL Server services across all machines/servers.
Sever configuration and application deployments
6. Prepare on-premises, shared storage
Shared storage is a requirement to deploy SSAS leveraging WSFC. Often there is an existing SAN designed to host data that’s accessed by multiple, clustered applications.
7. Configure Windows Server Failover Clustering
SQL Server applications, including AlwaysOn features, use the robust options of WSFC to allow for and to monitor failover-triggering events. The SQL Server relational engine is cluster-aware, and more deeply integrates and communicates with WSFC to provide detection and automated failover at more levels of the solution. Specifically, SQL can notify WSFC when application and service-level failures occur, thus prompting failover.
Note SSAS is not cluster-aware, and care must be taken if this application fails at the higher application service level.
8. Deploy SSAS in Failover Cluster Configuration
To deploy SSAS in a failover cluster configuration, you will need to:
- Use SQL Server Installation Center to install new clustered SSAS instances.
- Use SQL Server Installation Center to add a node to the existing SQL Server failover cluster.
- Synchronize SSAS data to Azure.
9. Configure SQL Server AlwaysOn
The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximize the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases—known as availability databases—that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.
Important You must have an existing Pyramid Analytics BI Office SQL repository database before configuring SQL Server AlwaysOn. Most companies will have an existing repository database in use before extending BI Office in a DR scenario.
BI Office users connecting to the primary instance during normal operation before failover
10. Use DNS to speed failover and decrease administrative overhead
You can configure your DNS settings to decrease the time that clients need to connect to the secondary replica after failover. You can also automate DNS changes.
Adjust the HostRecordTTL on the DNS entry for the name used by BI Office clients to access the application. For information and instructions for tuning your DNS settings, see the “Tuning the SQL Server Failover Cluster Instance DNS Settings” and “Testing Application Connectivity” sections in SQL Server 2012 Multi-Subnet Cluster Part 4.
11. Install and deploy Pyramid Analytics BI Office
Although you already had to install and deploy BI Office before configuring SQL Server AlwaysOn (see section “9. Configure SQL Server AlwaysOn”), in this step you will need to install and deploy it again.
The first deployment, which you likely already had in place, provided the repository database that’s needed to complete the AlwaysOn deployment. This is because AlwaysOn requires that a database be created and available in order to configure itself and to replicate the existing database to the secondary, tertiary, and other nodes.
For the subsequent installation and deployment of BI Office in this step, however, you will have to create or connect to an existing SQL repository database.
12. Test the disaster recovery solution
There are multiple ways to test your DR solution. There can be failures at multiple levels in the solution stack – from networking to the application layer. However, in a true DR event, the entire primary site will be suddenly disconnected. You can use the Failover Cluster Manager to simulate failover at different levels, but testing DR requires a “pull the plug” procedure.
BI Office clients connecting to the on-premises, primary AlwaysOn replica during normal operation, before disaster
In this paper, you’ve seen the high-level steps and tips for deploying BI Office in a DR scenario, with two nodes that are on-premises, and a third node in an Azure cloud.
BI Office offers a robust and well integrated DR solution. BI Office fully supports and uses WSFC and SQL Server AlwaysOn technologies, so if there’s a disaster at the primary, on-premises site, BI Office clients are automatically redirected within minutes.
Look for more details in future updates of our DR features and implementation instructions.
The following are some of the resources mentioned that can be helpful for the tasks discussed in this paper: