Azure SQL With PCF Spring Boot Applications (Part 1 — GeoReplication)

This is part 1 of a 2-part series demonstrating how to leverage advanced Azure SQL (PaaS) features from Java Spring Boot applications running on PCF (Pivotal CloudFoundry) on Azure. The first article will show you how to use a Spring Boot application with Azure SQL Database auto-failover groups to provide resilience to regional outages, and the next article will show you how to use the Azure SQL AlwaysEncrypted feature in Spring Boot applications to provide encryption for PI data in the cloud.

This article demonstrates how to use Azure SQL auto-failover groups in SpringBoot applications running on PCF, configuring  for the seamless integration of the Java apps with Azure services and using Service Broker to connect PCF applications with Azure SQL service.

Note: On the day of publishing, the new Open Service Broker for Azure (OSBA) does not support features described in this article as they are not yet available.

First, let’s look at the setup of PCF applications for high-availability. To prevent app downtime, PCF applications could in active-active or active-standby mode, where two or more identical PCF deployments are installed in different data centers. If one PCF deployment becomes unavailable, traffic is seamlessly routed to the other deployment. Applications are deployed to both PCF deployments in parallel, to achieve identical deployments. For resiliency on the data layer, PCF Applications connect to the Azure SQL database that is geo-replicated between Azure regions. In the event of region outage, all components will failover together. The diagram below shows the topology:

PCF applications are configured to access Azure SQL database via auto-failover group read-write listener, using FQDN <failover-group-name>.database.windows.net. In case the region outage traffic is directed to the secondary region and the SQL Database service detects that the primary database is not accessible and triggers failover to the secondary region, FQDN is seamlessly directing data to the secondary region without requiring application changes or restarts.  For more information on auto-failover group setup please refer to this Microsoft webpage.

Azure Service Broker Setup

Let’s review how the Meta Azure Service Broker should be set up to provide integration with Azure SQL in multiple regions.  Service broker could be installed on PCF as a Pivotal Tile in Ops Manager or just pushed as an application in PCF system org. We will show the Tile deployment mode:

1. Download and install MASB from pivotal network into the Ops Manager and follow documentation on setting up connectivity to Azure Subscription, providing a database to save the broker configuration.
2. In enterprises, the broker is usually set to use pre-configured SQL servers so that databases created in it will inherit security/audit/management settings. Prepare Azure SQL servers in a primary and secondary region. The broker will create and replicate databases in the specified servers. In our example, the following servers were created in two regions:
3. Next, configure both the primary and secondary target SQL servers in the service broker — resources groups, server admin credentials and regions of the created servers in the step above. 
4. Save and Apply changes in Ops Manager to install the service broker.

Spring Boot Application

Now that we have the broker installed, we will deploy a simple Spring Boot application – Spring Music — to PCF and create services it will bind to.

The Spring Music application leverages Spring AutoConfiguration and Spring Cloud Connectors to detect the type of database and provide the datasource to the application. To enable SQL Server for the application, the following is included in the SpringMusic:

• SQL Server driver is included in build.gradle
•  RelationalCloudDataSourceConfig includes “sqlserver-cloud” profile assigned by CloudConnectors when application is running in PCF and has SQL server service bound to it.

@Configuration
@Profile({"mysql-cloud", "postgres-cloud", "oracle-cloud", "sqlserver-cloud"})
public class RelationalCloudDataSourceConfig extends AbstractCloudConfig {

    private static final Logger logger = LoggerFactory.getLogger(RelationalCloudDataSourceConfig.class);


    @Bean
    @ConfigurationProperties("spring.datasource")
    public DataSource dataSource() {
        logger.info("CREATING SQL DS on the Cloud");
        return connectionFactory().dataSource();
    }
}

That’s all that is needed in application code to be able to run in PCF and connect to SQL server.

PCF Application and Services Setup

Now we have to prepare services that would be used by application to integrate with Azure SQL and auto-failover groups.

Run the cf marketplace command to see the services available after installing Azure Service Broker.

Primary Region PCF Application setup

First, we will use Service Broker to create a database in the primary region, set up the auto-failover group, and geo-replicate the database to the secondary region, and push application that will use the failover group connection:

To create a database in the primary region we could use the “azure-sqldb” service:

 cf create-service azure-sqldb PremiumP2 springdemodb -c ./springmusicdb.json 

Where the JSON file specifies SQL server and the database name in configuration options:

{
  "sqlServerName": "springprimarydemo",
  "sqldbName": "springmusicdb",
  "transparentDataEncryption": false,
  "sqldbParameters": {
    "properties": {
      "collation": "SQL_Latin1_General_CP1_CI_AS"
    }
  }
}

And next step is to create an Azure SQL database auto-failover group, that would provide database FQDN for the JDBC URL application will use. There are two plans for the failover service:

In a primary region we run cf command that creates the failover group and replicates the database, and we will use a plan called "SecondaryDatabaseWithFailoverGroup”:

 cf create-service azure-sqldb-failover-group SecondaryDatabaseWithFailoverGroup

springfailoverdb2 -c ./failover.json 

The JSON configuration provides details on the Primary and secondary database servers, failovergroup name, and database to be replicated in a failover group:

{
  "primaryServerName": "springprimarydemo",
  "primaryDbName": "springmusicdb",
  "secondaryServerName": "springsecondarydemo",
  "failoverGroupName": "springfailoverdb2"
}

Once the operation succeeds you can review details of the created Failover group:

Note: if you are getting errors at this point, one of the reasons might be that both primary or secondary servers were not properly defined in the Service Broker/Tile config.

At this point we could "cf push -f manifest.yml" our SpringBoot application to PCF, providing "manifest.yml" that specifies newly created service to be bound to:

---
applications:
- name: spring-music
  memory: 1G
  buildpack: java_buildpack_offline
  path: build/libs/spring-music-1.0.jar
  routes:
    - route: spring-music-eneros.apps.137.135.115.90.cf.pcfazure.com
  services:
   - springfailoverdb2

Once the is application started on PCF we could see that VCAP_SERVICES   information contains jdbcUrl  pointing to SQL failover group FQDN:

Navigate to the application and you could see active spring profiles, and services bound to the application:

Secondary Region PCF Application setup

Now we need to install PCF application in secondary region, and application configuration should be identical so that we do not have configuration drift. We could reuse same manifest.yaml and application binary that was deployed in the primary region. The only difference will be in how we create the service for SQL auto-failover group, since the group and the secondary replicated database already exists.

We will leverage service broker plan called “ExistingDatabaseInFailoverGroup”, which will validate that database and failover group exists and will provide their configuration to application:

 cf create-service azure-sqldb-failover-group  ExistingDatabaseInFailoverGroup

springfailoverdb2 -c ./failover.json 

Deploy application using `cf push -f manifest.yml’.

And we could validate that VCAP_SERVICES point jdbcURL to the same FQDN, and provisioned new user credentials for the secondary application to connect.

The resulting interaction and topology diagram:

Failover test

To test solution initiate failover in the portal, and observe that application seamlessly starts getting the data from the secondary region database. SQL data replication is base on the Always-On feature of SQL server that mirrors the data between the regions asynchronously.

Conclusion

In this example, we demonstrated connecting PCF application with Azure SQL geo-replicated databases, that provide resiliency and high-availability. We went under the hood work of Azure Service Broker and how easy it is for Java applications to consume advanced Azure features through Broker.  

For non-PCF Java applications, Spring and not Spring-based, a similar setup could be achieved by implementing provisioning and replication of Azure SQL databases using scripts, and copying FQDN and user credentials in Java configuration. Another approach might be to leverage new Azure Spring Cloud integration libraries  https://github.com/Microsoft/spring-cloud-azure

Special thanks to Ning Kuang and team of Azure PCF developers for constantly improving Service Broker to meet our customers demands.