OOTB Shared DB as a Service complete with Day 2 Operations

Published: August 21, 2017 Automation Cloud-Services How-to vRealize-Automation vRealize-Suite

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 thebelow 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:

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:

yum install postgresql*systemctl start postgresql.servicesystemctl status postgresql.servicesystemctl enable postgresql.service/usr/bin/postgresql-setup initdbvi /var/lib/pgsql/data/pg_hba.conf

Copy

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

# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all all 127.0.0.1/32 identhost all all 0.0.0.0/0 trust

Copy

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

vi /var/lib/pgsql/data/postgresql.conf

Copy

And made the following change

#listen_addresses = 'localhost' # what IP address(es) to listen on;listen_addresses = '*'

Copy

Finally ran the following:

service postgresql restartsystemctl disable firewalldsystemctl stop firewalldsystemctl status firewalld

Copy

Creating the Database:

sudo -u postgres psql -c "CREATE USER usr WITH PASSWORD 'passwd'"su - postgres -c "createdb testdb -O usr"

Copy

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

MySQL DB:

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpmrpm -ivh mysql57-community-release-el7-9.noarch.rpmyum install mysql-serversystemctl start mysqldsystemctl status mysqldsystemctl enable mysqldgrep 'temporary password' /var/log/mysqld.logmysql_secure_installationmysqladmin -u root -p version

Copy

Edited my.cnf file with

vi /etc/my.cnf

Copy

And commented out the bind-address

#bind-address   = 127.0.0.1

Copy

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

mysql_config_editor set --login-path=client --host=localhost --user=root –password

Copy

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

Creating the Database:

mysql -u root -e "CREATE DATABASE IF NOT EXISTS testdb;"mysql -u root -e "CREATE USER 'usr'@'%' IDENTIFIED BY 'passwd';"mysql -u root -e "GRANT ALL PRIVILEGES ON testdb.* TO 'usr'@'%';"mysql -u root -e "FLUSH PRIVILEGES;

Copy

MSSQL DB:

Creating the Database:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$currenthost = hostname$defaultInstance = "notdefault"if($defaultInstance -eq "yes"){ $ServerName = "$currenthost"}Else{ $ServerName = "$currenthost\SHAREDINS"}$server = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server $ServerName$dbExists = $FALSEforeach ($individual_db in $server.databases) { if ($individual_db.name -eq "testdb") { Write-Host "Database already exists." $dbExists = $TRUE }}if ($dbExists -eq $FALSE) { try{ $login = New-Object Microsoft.SqlServer.Management.Smo.Login -ArgumentList $ServerName, "usr" $login.LoginType = 'SqlLogin' $login.PasswordPolicyEnforced = $false $login.PasswordExpirationEnabled = $false $login.Create('passwd') Write-Host "Login usr created" } catch{ Write-Host "Can not create Login usr" exit } try{ $database = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -argumentlist $server, "testdb" $database.Create() Write-Host "Database testdb created" } catch{ Write-Host "Database testdb cannot be created" exit } try{ $user = "usr" $db_user = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -argumentlist $database, $user $db_user.Login = $user $db_user.Create() Write-Host "Database user usr created" } catch{ Write-Host "Database user usr cannot be created" exit } try{ $server = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server $ServerName $data = New-Object Microsoft.SqlServer.Management.Smo.Database $data = $server.Databases.Item('testdb') $data.SetOwner('usr', $TRUE) $data.Alter() Write-Host "Database owner changed to usr" } catch{ Write-Host "Cannot set database ownership to usr" $database.Drop() $db_user.Drop() $login.Drop() exit }}

Copy

Database deletion scripts:

I used the following scripts to delete the databases

PostgreSQL:

su - postgres -c "dropdb POSTGRESQLDB"su - postgres -c "dropuser POSTGREUSER"

Copy

MySQL DB:

mysql -u root -e "DROP DATABASE MYSQLDB;"mysql -u root -e "DROP USER 'MYSQLUSER'@'%';"

Copy

MSSQL DB:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$currenthost = hostname$defaultInstance = "yes"if($defaultInstance -eq "yes"){ $ServerName = "$currenthost"}Else{ $ServerName = "$currenthost\MSSQLINSTANCE"}$server = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server $ServerName$DatabaseName = "sajalmsdb"$DatabaseUser = "sajal"Write-Host $DatabaseNameWrite-Host $DatabaseUser$DBObject = $Server.Databases[$DatabaseName]Write-Host $DBObjectif ($DBObject){ $Server.KillDatabase($DatabaseName) Write-Host "Deleted the Database"}if ($Server.Logins.Contains($DatabaseUser)) { $Server.Logins[$DatabaseUser].Drop() Write-Host "Deleted the user login" }

Copy

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!!!!