Out-of-the-box shared DB-as-a-Service in vRealize Automation

Published: July 23, 2017 Automation Cloud-ServicesHow-to vRealize-Automation vRealize-Suite

Purpose:

This blog post provides details of shared DB-as-a-Service capability in vRealize Automation. This functionality provided by vRO workflows. A vRO package was created and is available here. Details of the required tools and configuration steps are also provided. Also, the repository details of the package given.

Introduction:

In last few months, I got many requests for shared DB-as-a-Service for private cloud. Specifically, the private clouds built with VMware vRealize Automation. Considering this, I decided to create a vRealize Orchestrator package for this purpose. This will provide such capability out-of-the box in vRA. Import the package, run a single workflow, provide the inputs. The services published in vRA Catalog. Any consumer requiring such service can request the items from Catalog. As of now, this package supports three different types of databases, MSSQL, PostgreSQL and MySQL.

Why Shared DB-as-a-Service:

I want to cover a little background before going into details of the package contents. A dedicated database server is generally used for a production environment or for heavy workloads. For development or for testing we do not need a dedicated database server. The Same policy applies to milder workloads as well. For these use cases, we need a shared database server. Here we create a database instance on the same database server and consume it as per our need.   With the rise of Cloud services, shared DB-as-a-Service became very popular.  There, Database instances created and consumed as and when required. This post gives details of the vRO package which provides this exact functionality. This package used in vRealize Automation environment to provide shared DB-as-a-Service.

How it works:

First, download and import the vRealize Orchestrator package "com.vmware.db-as-a-service.package". After import, as an admin, run the main workflow "Setup Database-as-a-Service Environment". This workflow will set the environment. The workflow asks for the inputs and sets the environment. It publishes Catalog items. End users request for the item and get the services. Users provide details of user name, password and db name to create the databases.

Required environment:

Provided below are the environmental details.

• vCenter Server 6.5
• vRealize Automation Server 7.3
• vRealize Orchestrator 7.3

Database versions.

• PostgreSQL 9.4
• MySQL 5.7.18
• MSSQL 2014

VMware Environmental setup:

• vRealize Orchestrator should have the following configuration
• vCenter Server registered as an endpoint in vRO
• Registered vRealize Automation Appliance (VAMI/CAFE APPLIANCE)
• Registered IaaS server
• A configured vRA environment (For details on how to configure check this link)

Database Environmental Setup:

• A working PostgreSQL server
• Remote connection configured in PostgreSQL server
• Configured MySQL server
• Local root login enabled in MySQL server. Configured passwordless root login. For security, you can configure password (in that case, change the original workflow)
• MSSQL server installed and configured

 For MSSQL Server:

• Mixed mode or windows mode authentication is needed
• The domain user which has access to the VM (runs the PowerShell script) should have admin access to the database to be able to create and delete databases and users.

You can change the configurations as per your need. Let me know what modifications you need and I will incorporate them. Also, configuring all three of the databases is not mandatory. If you have only one database environment, configure that and the rest skipped.

Package source and details:

I have created a GitHub repository from where you can download the package. The package name is "com.vmware.db-as-a-service.package". The package will create a folder "Database-as-a-Service" under the main workflow window. It also has "Helper Workflows", "Database Creation Workflows" and "Environment Setup Workflows" folders. All these under this main "Database-as-a-Service" folder. The "Environment Setup Workflows" contains the workflows related to setting up the environment. The "Database Creation Workflows" contains the workflows which create the actual databases. "Helper Workflows" folder contains workflows to set and get the environment details.

The package contains other related workflows, actions, and resource items.

How to run the workflows?

The first thing to do is to setup the environment. So login to vRO server, import the package. Next, as an administrator run the "Setup Database-as-a-Service Environment" workflow. This will ask for the following inputs:

• vRA Cafe Appliance
• Service to which to add the catalog items
• Entitlement where to add the catalog items
• Approval Policies (if any)

Next three pages ask the database server details. Select the database server VM. Provide user name and password for the database VM's. This will create and publish three different catalog items:

• MSSQL DB-as-a-Service
• MySQL DB-as-a-Service
• PostgreSQL DB-as-a-Service

Update:

Please note, the earlier version of the package only catered to the default instance of an MSSQL server (thanks Rawlinson Rivera for pointing this out). In the updated version, the MSSQL server database creation considers the named instances as well. So for the MSSQL server provide the following added information: Default Instance: Whether default instance is used to DB creation. Please note, the default instance is typical "MSSQLSERVER" or "SQLExpress". If at the time of installing the database, default instance name was selected then this should be "yes". Otherwise "no" should be typed in. Also note, please type in only "yes" or "no". Currently, I have not provided any input validation here. Instance Name: Provide the instance name in which DB to be created. This is typically the named instance. Note, if this is the only instance on the server, still if the default name was not selected, then provide this value here.   All set, now end users can request the catalog items and get respective databases. They need to provide required inputs. The inputs required are 

• User Name
• Password
• Database Name

The workflows create a user in the database server. The password for the user is the one user provided. Also, a database created with the provided name. The created user is the owner of the database. Please watch the following video to see details and working of the service.

 Acknowledgement:

This solution was based on work done by Christophe Decanini. I used the modified package available at this link. The core logic uses the workflows created and described by his. So full credit goes to Christophe for this wonderful package.

To do:

• Workflows for Deletion of an existing database.
• To implement Error checking
• Thorough stability testing

Conclusion:

You can use the package and associated workflows to provide shared DB-as-a-Service in vRealize Automation environment. At this stage, the workflows are for PoC environment only. Though the workflows were tested for performance, not all use cases were covered nor diligent error checking was done.

Till then please go ahead use it and let me know your feedbacks. Do let me know if you need more modifications or something else.