Self Service: DB2 database restores automation using Concord
At Walmart Labs, we learn the latest technologies, like very own Walmart’s Concord and try to implement our learnings to automate frequent time-consuming activities to save time and human efforts in many folds.
Use Case: Volume and Performance testing (VPT) is a pervasive problem. For TRIRIGA Application in our environment, the application team performs VPT testing to test the different modules, where the software is subjected to a huge volume of data. There is a need to have a strong VPT practice to ensure production systems are set up to perform at peak levels.
What if testing is not successful or not expected? Then, the Application team needs the system where it was before testing and takes help from DBA’s to perform a database restore from a previously backed up image.
If this activity needs to be performed twice a day, means all done for the day for DBA. What to do Next? We analyze our monthly report of DBA support tasks, there were 20+ restores per month requested, and each 400–500Gb restore took around 2–2.5 hours to complete, again it may vary depending upon your database size and network throughput from the net-backup server. So, monthly we were spending 40 odd hours for this activity.
Tip: Reports are the best way to identify the activities or tasks that can be automated in support of work. In my case, I am using Jira and Service now. We can fetch the reports on a weekly or monthly basis and find out the activities that are frequent and can be automated.
Solution: To get rid of repetitive database restores, DBA came up with form-based Concord automation. Also, it integrated with #Slack and Jira, for notifications and reporting purposes, even more, it can be used for but not limited to deployments, installations, access requests.
Furthermore, you can restrict the usage to a particular group of users or LDAP groups, those can only run the flow and it can be run for specific servers by placing server restriction in place.
Get start your first project: Manual efforts for DB2 database restores includes taking the backup of configuration files, force application connections, deactivate database and restore it from tape or disk. These steps need to be performed by DBA, so the idea is to create concord project using Github repository to automate those manual steps into a self-service form so that anyone can run the URL (obviously we need to put some restriction to end-users and servers) and perform restore in a single hit.
Lesson: Before you start the first project, advise is to create Run-book for each step that you follow while doing it manually to save time and for more efficient and reusable coding.
Here are the steps to create Form-Based flow:
- Create a Git repository for your project: The first file that you need to create concord.yml in the repo which will contain the code for dependencies and configuration for plugins like Slack, Jira, etc.
2. The very next file will be the flow, e.g. restore.yml under the flows directory, where we need to define forms and flow (EntryPoint: db_restore) which will be called through URL.
3. Now, we need to write the playbook and roles that we need to call in it. And, Playbook will use the roles to perform the required tasks to perform database refresh activity.
Roles are like functions in our programming that can be called in the playbook to perform certain tasks. The concept of role is simple; it is a group of variables, tasks, files, and handlers that are stored in a standard file structure. Sample code for restore_database role.
Git repo directory structure will be like below:
4. Create Key on concord server and establish SSH connection between concord and Github repo that we have created in the above steps. Navigate to Organizations → Default organization → Secrets; and created new secret in concord, the user interface shows the generated public key (begins with ssh-rsa) and that needs to be added in the Settings — Deploy keys section of the git repository.
5. Create Project: Navigate to Organizations → Default organization → Projects. Provide the name of the project and under the Repositories tab, add a repository and give the name to your repository. And, under the custom authentication button, add the Secret created in the above step.
- SSH URL for the repository in the URL field e.g.
git@github.com:me/myrepo.git;
6. End-User flow: User can hit the URL in a browser and the background process will be started in concord which initiates the form with the required fields that the user needs to enter to start the activity. Here, is the URL format: API/v1/org/{orgName}/project/{projectName}/repo/{repoName}/start/{entryPoint}. e.g. /API/v1/org/Default/project/UDB/repo/concord_repo/start/db_restore
{orgName} — Default or another organization name if created any.
{projectName} — Project name that was created in Step 5.
{repoName} — Repo name that we have given in concord, remember not the one with the name, which we have created in Github.
{entryPoint} — Flow that we have created in the form. For example, our case is db_restore (Refer Step 2).
Conclusion: Try Walmart’s concord to build amazing automation and deployments for various use cases. We have been used this for various use cases as database restores, binaries installations, access requests, database activities to improve performance, but it’s not only limited to these activities. Concord is an orchestration tool that can be used for deployments and continuous deployment platform supporting tools such as Ansible, Managed Containers, Looper, Slack, Jira, and others.
References:
