Shared Database as a Service Featured Image

OOTB Shared DB as a Service complete with Day 2 Operations

Purpose:

The first part of the post dealt with “Out of the box Shared DB as a Service”. In addition to other points, where I explained about Shared DB as a Service using a vRO package. Limitation of that package is, it only creates the DB in real Database. Created databases do not appear in vRA items list. Also any Day 2 operation is not possible on the created Databases. While this post provides a complete package which adds the created databases as items in vRA. In addition, supports all Day 2 operations. Also now the package provides actions for out of the box Day 2 operations. This post details about that package.

Introduction:

I received a lot of good feedback on the first version of the package. Also, I received many requests for making it fully functional. In its current form, it is very basic. Only creating shared databases in the database server actually achieves not much. So, I decided to take it further and give a complete feature set. Making it ideal for proof of concept environments. The functionalities are all there. The only point is, it is not fully tested for all the gotchas. So feel free to update the original workflows and packages.

How Shared DB as a Service package works:

Like the original package, this time also all the workflows are available as a single package with the name “com.vmware.db-as-a-service-v3.package”. Please follow the below-mentioned steps to import and run the workflows.

  • Login to vRO client and go to Admin mode
  • Import the package in the Package manager
  • Once imported, go to Design mode.
  • You can find a new folder with the name “Database-as-a-Service”. Expand it and you can find the workflow “Setup Database-as-a-Service Environment”
  • Run this workflow and this will setup the entire environment

Folder structure:

Shared DB as a Service folder structure

Important Information:

Before you can run the workflows please check the important information provided below:

  • In the vRO server you need to add vRA CAFE and IaaS server as an endpoint.
  • vCenter server where the Database VM’s live, needs to be added as an endpoint in vRO. All these are mentioned in the video.
  • After the workflow run, log into vRA portal, go to Design –> XaaS –> Resource Actions –> Select “Delete a Database”. Edit this action. In the second page select “Destroy”.
  • There was a knows issue with MySQL connector for vRO in vRA 7.2. I found the same issue in vRA 7.3 as well. Please check this VMware KB Article to solve the issue.

Known limitations:

The following limitation is there in the current form.

  • While creating the databases, the workflow does not check whether the user already exists or not. It only checks for the Database existence
  • If a user already exists, then the database creation will fail
  • While deleting the database, the user is also deleted
  • MySQL database name does not support ‘-‘ in the name

The above happens because of the assumption that a single user will create a single database. Clearly, this is a limitation. To remove this limitation, I need to update the Database creation scripts. At this stage, my primary focus is to give the functionalities. Anyone requiring more flexibility, please update the core scripts for database creations. In the video, I have covered how you can change the scripts.

Database creation scripts:

I am no authority on these databases. I created and configured the databases the quickest and easiest way. These do not conform the best practices :). These are applicable to a PoC environment only. Provided below are the steps for setting up the database environment.

PostgreSQL:

I used CentOS 7.3 as base OS.

Install steps:

In the pg_hba.conf file check for the below lines and make changes as per your requirement

Note, I gave all permission to everyone in the local machine. Also, I trusted everyone from everyone. Next, modify postgresql.conf file.

And made the following change

Finally ran the following:

Creating the Database:

Used pgAdmin version 4 as the front-end management component for managing PostgreSQL databases.

MySQL DB:

Edited my.cnf file with

And commented out the bind-address

I also enabled password less login for root user. So that while running the scripts, it will not ask for the password:

I used HeidiSQL 9.4.0.5125 as the front-end management component for managing MySQL databases.

Creating the Database:

MSSQL DB:

Creating the Database:

Database deletion scripts:

I used the following scripts to delete the databases

PostgreSQL:

MySQL DB:

MSSQL DB:

Please note: NOT A SECURITY BEST PRACTICE. Do not do it in production. I only did it for PoC environment.

The Video:

Please watch the following video for a detailed demo and further explanation.

 

Conclusion:

This concludes this particular use case “Shared DB as a Service”. Please remember, the workflows I created are for PoC purpose only. My primary aim was to create the structure with the functionalities. Since the structure is ready, you can change the structure to suit your need. The vRA and vRO part is pretty much ok. I would strongly suggest the change or change the Database creation, change, and Deletion Shell/PowerShell scripts to make those more robust.  

I specifically made this use case to show that you can do amazing things with vRealize Automation. It is a tool using which you can build whatever you want. When you have the tool you do not need everything out of the box. You can build your own items. With this thought, I am closing this post. 

Till the next one, keep safe and happy automating!!!!

 

 

 

No Comments

Leave a Comment