Using a PostgreSQL cloud managed dabase service for your SAS Viya deployment in Azure

Cloud managed database services on Azure for PostgreSQL

One of the cloud managed database services that is available within the Microsoft cloud is Azure Database for PostgreSQL. This is a fully managed dabatase service that comes in three different flavors.

Single server
Flexible server (Preview)
Hyperscale

I'm not going to explain in depth what each of thse flavors entail. But each flavor serves a different use case and depending on your requirements, you end up selecting either one. The latest flavor that has become available from Microsoft is flexible server. It's the new kid on the block.

Internal or External PostgreSQL database?

Why is this interesting you might ask? Well if you are about to do a deployment of the latest version of Viya on Azure Kubernetes Service, you have a decision to make. Am I going to use the PostgreSQL database that SAS ships with deployment or am I going to use the cloud managed database service that Azure provides. There are a couple of advantages to using a cloud managed database service, like:

Automatic updates. During scheduled windows your database is upgraded to the latest available version.
Backups are taken care of. No need to provision storage or write scripts to make backups of the database.
Fail-over. Most managed database services provide built-in fail-over functionality.

For most deployments I've done so far on Azure Kubernetes Service of Viya, I tend to use Azure Database for PostgreSQL - single server. However this has some drawbacks. One of them not being able to shut down the database. Enter Azure Database for PostgreSQL - flexible server, a service that is currently in preview.

Azure Database for PostgreSQL - flexible server

This flavors comes with a couple of exiting new features:

Zone redundant high availability. You can either deploy it within the same availability zone, or decide to deploy it across multiple availability zones.
The ability to stop and start the server gives you more control over cost. For example, you can turn off the database when you are not using it.
Compute and storage are separated, meaning that these can scale independently from each other.

If you are like me and keep close tabs on your cloud expenses, the ability to stop / start the server in combination with stopping your kubernetes cluster when your are not using it is a nice way to reduce your cloud expenses.

In the remainder of this blog I will explain the steps to setup a PostgreSQL database and connect it to your SAS Viya deployment.

Setting up PostgreSQL for flexible server

Please be aware that at the time of writing this blog post, the PostgreSQL for flexible server is still in the preview phase and is limited to specific regions.

Setup a separate subnet
Create the PostgreSQL flexible server
Connect your Viya deployment to the PostgrQL server

Setup a separate subnet

It's a good practice to make sure that the database we are going to deploy is not publicly accessible. This means that our database will not receive a public IP address through which we are able to connect to it. The only connections the database is going to accept is from the Virtual Network, in which we also have our Azure Kubernetes Service running.

To setup a database that is only accessible from within our Virtual Network, it is required to setup a separate subnet within your Virtual Network that is dedicated to your PostgreSQL database.

Go to your resource group and select the Virtual Network where you deployed your Azure Kubernetes cluster.
Select subnets in the menu bar on the left side of the screen.
Then click on "+ subnet " on the top of screen.

Then setup the following parameters for the subnet.

IP address range: 192.168.3.0/24 (Pick an IP range this is within the range of your virtual network).
Delegate subnet: Select MicrosoftDBForPostgreSQL/flexibleserver.

Now click on Save and we have created the required subnet and are now ready to move on to the next step in which we will create the database.

Creating the PostgreSQL flexible server

Now we are ready to create the PostgreSQL flexible server.

Go to the search box and type in "Azure postgresql."
Then select Azure Database for Postgresql flexible server.

You will then end up in a landing page from which you are able to create the PostgreSQL database.

Click on Add to create a new database. This will bring up the wizard we are going to go through to create the PostgreSQL database. On the first page of this wizard we need to provide the following information.

Name of the server to be created.
The region in which you would like to deploy the database.
Workload type: make sure you select a workload type that matches the minimum number of required connections for your SAS Viya deployment (1024).
High Availability: if you want to deploy across multiple zones within a region you can select to do so here.
Version: which PostgreSQL version you would like to deploy. Needs to be version 11 or 12.
Administrator account. Setup the user and password for the admin account.

Make sure to take a note of the username and password as we will be needing this later on for the deployment.

Let's move on to the second page of the wizard where we can configure the following settings.

Public or Private access: select private access here.
Vnet: select the virtual network to create the server in.
Subnet: select the subnet to create the server in; this is the subnet we have created in an earlier step in this blog.

Click on review + create and after a while, you will end up with an Azure PostgreSQL flexible server instance that you an use for your SAS Viya deployment.

Now let's make the required modifications to our SAS Viya deployment to make use of our newly created database. As you might be aware we now use overlays to apply certain customizations through kustomize. These customizations are gathered in a file called kustomizations.yaml. This file is used by kustomize to generate a YAML file with our modifications, which then can be applied to the cluster to deploy SAS Viya.

To modify the deployment to use the PostgreSQL flexible server, we need to add the following overlays to the kustomization.yaml file.

transformers:
- sas-bases/overlays/external-postgres/external-postgres-transformer.yaml

secretGenerator:
- name: postgres-sas-user  # THIS IS FOR INTERNAL USE ONLY - DO NOT EDIT
  literals:
  - username={{ EXTERNAL-ADMIN-USERNAME }}
  - password={{ EXTERNAL-ADMIN-PASSWORD }}

configMapGenerator:
- name: sas-postgres-config  # THIS IS FOR INTERNAL USE ONLY - DO NOT EDIT
  behavior: merge
  literals:
  - DATABASE_HOST={{ EXTERNAL-HOST }}
  - DATABASE_PORT={{ EXTERNAL-PORT }}
  - DATABASE_SSL_ENABLED="true"
  - DATABASE_NAME=SharedServices
  - EXTERNAL_DATABASE="true"  # THIS IS FOR INTERNAL USE ONLY - DO NOT EDIT
  - SAS_DATABASE_DATABASESERVERNAME="postgres"

Modify the parameters to reflect your environment and regenerate the deployment yaml file. Run a kubectl apply and sit back and relax. If all goes well, you now have successfully deployed SAS Viya with Azure PostgreSQL flexible server.

Using flexible server in combination with your SAS Viya deployment allows you to completely start / stop your SAS Viya enviornment, with all the benefits of using a cloud managed database. I'm excited about what the flexible server has to offer and I'm looking forward to it becoming General Available.

Have you already done a deployment of SAS Viya on AKS and did you deploy it with an external Postgres database? Let me know in the comments.