Configuring Pyramid Analytics

BI Office for Disaster Recovery

Joey Pruett

June 22, 2015

Intended audience

Introduction

Overview

High-level tasks

1. Confirm deployment of Active Directory and DNS

2. Prepare networking

3. Prepare VPN connection

4. Prepare host servers

5. Deploy SQL Server

6. Prepare on-premises, shared storage

7. Configure Windows Server Failover Clustering

8. Deploy SSAS in failover cluster configuration

9. Configure SQL Server AlwaysOn

10. Use DNS to speed failover and decrease administrative overhead

11. Install and deploy Pyramid Analytics BI Office

12. Test the disaster recovery solution

Conclusion

Related resources

Intended audience

This document is intended for BI project decision makers and the technical personnel who implement the solution. There is a presumed basic knowledge of:

  • TCP/IP networking
  • Windows Server
  • Active Directory
  • DNS
  • SQL Server
  • Windows Server Failover Clustering
  • Pyramid Analytics BI Office

Excellent resources for additional information areMicrosoft Support, Microsoft TechNet Forums, and Pyramid Analytics Support.

Introduction

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).

Overview

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. These technologies include:

  • Pyramid Analytics’enterprise-level, high-availability technologies
  • Microsoft Azure networking and persistent virtual private network (VPN)
  • Windows Server Failover Clustering
  • SQL Server AlwaysOn
  • Active Directory dynamic Domain Name System (DNS)
  • Storage Area Networks (SAN) and iSCSI network-based, centralized storage

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.

With AlwaysOn technology, you no longer need to provide potentially expensive, high-administrative overhead, synchronized SAN, or shared storage between the sites. 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

High-level tasks

The following are the high-level steps forconfiguring, deploying, and testingthe solution in a disaster recovery scenario:

  1. Confirm deployment of Active Directory and DNS
  2. Prepare networking
  3. Prepare VPN connection
  4. Prepare host servers
  5. Deploy SQL Server
  6. Prepare on-premises, shared storage
  7. Configure Windows Server Failover Clustering
  8. Deploy SSAS in Failover Cluster Configuration
  9. Configure SQL Server AlwaysOn
  10. Use DNS to speed failover and decrease administrativeoverhead
  11. Install and deploy Pyramid Analytics BI Office
  12. Test the disaster recovery solution

The remainder of this paper describes each step in detail.

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.

For more information about deploying Active Directory on-premises vs. on Azure virtual machines (VMs), see Guidelines for Deploying Windows Server Active Directory on Azure Virtual Machinesand 3. AD DS: Deploy a Windows Server AD DS-aware application that requires connectivity to the corporate network.

2. Prepare networking

You must configure the Azure virtual network before you create your virtual machines. Use the Azure Portal to add a virtual network and necessary subnets and gateways. In my sample scenario shown in the figure below, clustering is deployed across multiple subnets, which is a common requirement when using on-premises and cloud services together. Multi-subnet clustering is a newer feature and requires some extra planning, care, and configuration.

For more information about preparing your network for deployment on multiple subsets, see Configuring IP Addresses and Dependencies for Multi-Subnet Clusters.

Clustering across multiple subnets

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. In my scenario, I used a dual-homed, single hardware server running Windows Server Routing and Remote Access. After the Azure network, subnet, and VPN are created, an option will be available for you at any time to export the script for your particular edge device – be it Cisco, Microsoft, or other.

A VPN connection to Azure creates a site-to-site, persistent connection between your on-premises and cloud networks. This allows communication between geographically dispersed servers, including Active Directory domain controllers and DNS servers. The technologies of this solution use the connection to copy data to the secondary Azure site. This includes changes to the BI Office relational repository database (in near real-time) and the heartbeat connections between WSFC nodes.

For more information aboutconfiguring a VPN connection (and extending Active Directory), seeConnect an On-premises Network to Azure via Site to Site VPN andAbout Virtual Network Settings in the Management Portal.

Testing connectivity

Azure doesn’t allow the ping command,which makes testing basic TCP/IP network connectivity a bit more difficult. Try connecting to the servers with Windows Explorer using \\serverName. Also, Microsoft provides a tool called PSPing that allows you to ping the machines. The figure below shows a successful connection between an on-premises machine and Azure cloud VPN.

Visual confirmation of a successful connection

4. Prepare host servers

  1. 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.

Selecting the virtual network subnet

ImportantDo 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. Although multiple nodes in the on-premises deployment are not a requirement for DR, it is common to deploy a scaled-out stack of servers to provide redundancy and high-availability at the primary site.

The figure below lists the servers involved in my sample deployment scenario – including an optional iSCSI server, if you are not using a SAN.(For more information about this optional iSCSI server, see the section “Prepare on-premises, shared storage” later in this paper.) Instructions for deploying each server will follow.

Server configuration and application deployments

  1. On all servers, run Windows Update, and install all available updates.

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.

  1. Go to SQL Server Installation Center, and select the first option for New SQL Server stand-alone installation, as shown in the figurebelow. On all servers, install the latest SQL service pack and cumulative update.

Installing stand-alone SQL Server instances on primary and secondary nodes

  1. Enable TCP/IP connectivity in SQL Server Configuration Manager.

Enabling TCP/IP connectivity

  1. Specify the domain service account.

ImportantIf 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.

The following figure shows an example of specifying a domain service account.

Specifying a domain service account

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. If you do not have a SAN, or are deploying a low-cost, low-administration test environment, you can use the iSCSI technology in Microsoft Server 2014 to provide access to centralized data. iSCSI uses Ethernet networks to present the shared storage drives to all nodes. In my example deployment, I am using a separate host server to provide shared storage via iSCSI.

To prepare the storage

  1. Using Server Manager on the server that is designated to host shared storage (the iSCSI target server), install the File and Storage Services role with the File and iSCSI, Storage Services and iSCSI Target Server roles and related features. In Server Manager, there is a File and Storage Services page,as the figure below shows, which allows administration.
  2. On the nodes that are hosting WSFC, you will need to connect the servers to the shared storage.
  1. Windows Server has an application called iSCSI Initiator, which allows you to configure connections to the server that is hosting these storage services and the storage space that has been allocated.You can find detailed instructions for configuring the initiator atInstalling and Configuring Microsoft iSCSI Initiator.You can configure iSCSI options on the iSCSI server by going to Server Manager > File and Storage Services > iSCSI, as shown below.

Configuring iSCSI options on the iSCSI server

iSCSI initiator properties on a WSFC node, showing two connected “targets” or iSCSI drives

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.

NoteSSAS is not cluster-aware, and care must be taken if this application fails at the higher application service level.

  1. To begin configuring WSFC, install WSFC.

To install WSFC

  1. Use the Server Manager. WSFC is a feature listed as Failover Clustering.
  2. Run the configuration validation tests, and pay close attention to any failures or warning. Some warnings can be ignored, depending on your environment. Searching the Internet for the error messages often reveals information that can help you correct the warning or decide to ignore it.

ImportantConsult Microsoft Support before ignoring warnings on production deployments.

Once installed, WSFCis administered with the Failover Cluster Manager.

NoteAfter deploying SQL, some WSFC options are automatically set and managed by SQL Server, and should not be changed in Failover Cluster Manager. For more information, see the “Restrictions on Using the WSFC Failover Cluster Manager with Availability Groups” section in Failover Clustering and AlwaysOn Availability Groups (SQL Server).For more information about availability groups, see the section “9. Configure SQL Server AlwaysOn,” later in this paper.

Do not use the Failover Cluster Manager to manipulate availability groups. For example:

  • Do not add or remove resources in the clustered service (resource group) for the availability group.
  • Do not change any availability group properties, such as the possible owners and preferred owners. These properties are set automatically by the availability group.
  • Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime. You must use Transact-SQL or SQL Server Management Studio.
  1. Open Failover Cluster Manager, and create a cluster.

To create a cluster

  1. In the left panel of Failover Cluster Manager, right-click the Nodes folder, and then click Add node.

Failover Cluster Manager connected to and showing the cluster SSASFSFC.PANA.INTERNAL

  1. Enable AlwaysOn.

To enable AlwaysOn

  1. Open SQL Server Configuration Manager.
  2. Right-click the SQL Server service, open the AlwaysOn High Availability tab, and then selectEnable AlwaysOn Availability Groups.

Enabling AlwaysOn

8. Deploy SSAS in failover cluster configuration

To deploy SSAS in a failover cluster configuration, you will need to:

  1. Use SQL Server Installation Center to install new clustered SSAS instances.
  2. Use SQL Server Installation Center toadd a node to the existing SQL Server failover cluster.
  3. Synchronize SSAS data to Azure.

To install new clustered SSAS instances

  • In SQL Server Installation Center, on the Installation page, select New SQL Server failover cluster installation, as shown in the figure below.

To add a node to a SQL Server failover cluster

  • SelectAdd node to a SQL Server failover cluster, as shown in the figure below. For more information, see How to Cluster SQL Server Analysis Services.

Installing clustered SSAS instances; adding a node to a cluster

To synchronize SSAS data

  • After adding the node to the cluster, you will need to synchronize SSAS data to Azure. If you need assistance, SSAS provides a tool called Synchronize Database. For more information, see Synchronize Analysis Services Databases.

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 repositorydatabasein use before extending BI Office in a DR scenario. Although the installation and deployment of BI Office in DR configuration is detailed in the section “11. Install and deploy Pyramid Analytics BI Office” later in this paper, you must already have the database available from a previous BI Office deployment to allow AlwaysOn to detect and then synchronize that database to other AlwaysOn replicas.

  1. To configure SQL Server AlwaysOn, you will first need to create the availability group in SSMS on the primary node.

BI Office users connecting to the primary instance during normal operation before failover

To create the availability group

  1. Open SQL Server Management Studio (SSMS) on the primary node, and connect to the local SQL Server database engine.
  2. Expand the AlwaysOn High Availability folder, right-click the Availability Groups folder, and then click New Availability Group Wizard.

Creating the availability group

NoteYou should not create listeners during this wizard. Listeners for a hybrid Azure and on-premises solution should be configured manually.

  1. After you have created the availability group, complete the steps in Tutorial: Listener Configuration for AlwaysOn Availability Groups. In our scenario, I used the Internal Load Balancing option, since clients do not need direct access to the BI Office SQL repository database. Only the BI Office application will connect to the database;the client application users who access the application via Microsoft Internet Information Services (IIS) will not connect to the database.
  2. After the availability group listener is completed by using the manual steps above, it might be desirable to adjust the RegisterAllProvidersIP and HostRecordTTL cluster parameters for the listener resource. These parameters can reduce reconnection time after a failover, which may prevent connection timeouts. For more information about these parameters and sample code, see Create or Configure an Availability Group Listener.
  3. Be sure to test things out, as shown in the next sub-section.
Testing the availability group listener

To test the availability group listener

  1. Use SSMS running on the same network.
  2. Connect to the listener from one of the VMs in the same Azure VNet (but not one that hosts a replica).

An easy way to complete this test is to try to connect SSMS to the availability group listener.

For client connectivity, note the following requirements:

  • Client connections to the listener must come from machines that reside in a different cloud service. In my scenario, my secondary BI Office and AlwaysOn SQL servers are in different Azure cloud services. This is required for both the initial testing of connectivity and the actual functioning of the connectivity between the secondary servers.
  • If the AlwaysOn replicas are in different subnets, clients must specify "MultisubnetFailover=True" in the connection string. This results in parallel connection attempts to replicas in the different subnets. Note that this scenario includes a cross-region AlwaysOn Availability Group deployment.

The following are some helpful resources on availability groups: