Just moved to Hexo + VMware Clarity combination. This site is not fully working in Firefox. Try IE/Chrome/Safari Browsers instead.

OOTB Shared DB as a Service complete with Day 2 Operations

Related Categories: #vRealize Suite #vRealize Automation #vRealize Orchestrator         Info-Standard 2017-08-21

Purpose

The [first part of the post]Out-of-the-box shared DB-as-a-Service in vRealize Automation 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]Out-of-the-box shared DB-as-a-Service in vRealize Automation , 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:

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:


yum install postgresql*

systemctl start postgresql.service
systemctl status postgresql.service
systemctl enable postgresql.service
/usr/bin/postgresql-setup initdb
vi /var/lib/pgsql/data/pg_hba.conf

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 only
local   all             all                                     trust

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             0.0.0.0/0         trust

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

And made the following change


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

Finally ran the following:


service postgresql restart
systemctl disable firewalld
systemctl stop firewalld
systemctl status firewalld

Creating the Database:


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

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.rpm
rpm -ivh mysql57-community-release-el7-9.noarch.rpm
yum install mysql-server
systemctl start mysqld
systemctl status mysqld
systemctl enable mysqld
grep 'temporary password' /var/log/mysqld.log
mysql\_secure\_installation
mysqladmin -u root -p version

Edited my.cnf file with


vi /etc/my.cnf

And commented out the bind-address


#bind-address   = 127.0.0.1

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

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;

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 = $FALSE
foreach ($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

  }
}

Database deletion scripts:

I used the following scripts to delete the databases

PostgreSQL:


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

MySQL DB:


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

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 $DatabaseName
Write-Host $DatabaseUser

$DBObject = $Server.Databases\[$DatabaseName\]

Write-Host $DBObject

if ($DBObject)
{
  $Server.KillDatabase($DatabaseName)
  Write-Host "Deleted the Database"
}

if ($Server.Logins.Contains($DatabaseUser))
 {
   $Server.Logins\[$DatabaseUser\].Drop()
   Write-Host "Deleted the user login"
 }

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

Comments