The ‘A’ in DBA should stand for Automation

One of the functions I created, which fully automates SQL Server installation

I’ve worked as a Database Administrator (DBA) for over 6 years now, all with Version 1 — and I love what I do. But there are some tasks that can be less enjoyable than others. The main “culprits” being;

• Database Patching
• Database Installs & Configuration

These are some of the most common tasks we have as DBA’s, and when you have to do a lot of them, it can get very repetitive. Over the last two years or so, I’ve developed PowerShell scripts to automate these tasks for both Oracle and SQL Server. In this blog post I’ll go through;

1. How long they take to do manually
2. The time saved through automating them
3. A summary of what each script does.

Problematic Processes

To give a non-DBA an idea of how long these processes would normally take, here is a rough estimate of each;

How often are Installs done?

Installation frequency will vary massively depending on the customer, but for the customer I currently work for we get 3–4 SQL Server installations every month on average, and around 1–2 Oracle installs per month. To give a rough idea of how much time this would take;

This combines to a total of 60 days spent on installs on average per year.

The customer I work with also had an Oracle upgrade project taking place in 2021, where the majority of the 127 Oracle instances they have were migrated to Oracle 19c — each of which required a new install on a new server. The Oracle Install script saved us over 100 days of manual work during the upgrade project.

How often is Patching done?

Again, this will vary by customer. For the customer I work for we apply Oracle patches bi-annually , and SQL Server patches quarterly. The customer has 127 Oracle databases and 355 SQL Server instances. If these were patched manually, the time taken would be;

That’s a combined total of 964 hours every year, or a staggering 128 working days — spent solely on patching the estate. This time does not include sending emails, organising downtime with the users, or testing to make sure the application works the same after patching.

The Solution: Automation

Any developer will tell you that when you have a task that’s repetitive and/or time consuming, you should try to automate it. So that’s what I done for each of the above tasks. I developed the scripts using PowerShell, as our customers estate is almost entirely hosted on Windows, including the Oracle databases. PowerShell is excellent for automating anything on Windows, and it’s installed by default on every version of Windows since Windows Server 2008 R2 SP1

Automating the above tasks has seen a huge reduction in the time we spend on them. The table below shows the new time taken for each compared to it being done manually;

As you can see, automating these processes has saved us a huge amount of time. These are of course all rough estimates, but it’s just to give you an idea of how much time we’ve saved through automation. Here’s a bar chart to further demonstrate the time saved — the SQL Patching time barely shows up on this scale;

Bar chart for days spent on each task per year.

A brief summary of each script is below. Each script has extensive logging and error checking throughout, which is displayed to the user during execution and saved to a log file so it can be viewed afterwards.

SQL Server Patching

This script is the one that saves us the most time. The function is passed a list of servers, and it will do X (default is 5) servers concurrently as it iterates through the list. For each server the script will;

1. Automatically get the SQL Server version of the instance to be patched
2. Get the latest applicable patch for that version from a given fileshare
3. Upload and apply the patch
4. Reboot the server as necessary
5. Create extensive logs for each server, and send a summarised report along with the logs to a specified email when all patching is completed.

Example Usage;

Install-SPMultipleSqlPatches -Servers “Server1”, “Server2”, “Server3” -PatchFileDirectory “C:\SQLPatchDir\”

Where “PatchFileDirectory” is the location of where you store all SQL Server patches. This command will patch all three given servers concurrently.

The function can be scheduled via a Task Scheduler job, so no DBA intervention is needed. You could schedule it to run monthly for example — the script will only do something to a server on the list if a newer applicable patch is found on the given share — so the DBA just needs to add newer patches to the share and when the script runs again, it will apply any new patches. It is compatible with SQL Server 2008 and up.

Oracle Patching

This script fully automates the patching process for Oracle on Windows. You pass the function the server name and the path of the Oracle Home you want to patch, and the script will do the rest. The steps are;

1. Detect the Oracle Version of the home
2. Get the latest applicable patch for that version from our share
3. Update OPatch on the server
4. Stop all databases, services and processes running from that home
5. Patch the home
6. Bring up the databases in upgrade mode and run datapatch
7. Restart the databases in Open Mode
8. Confirm the patch has been applied to the home and each database within the home.

The script works on Oracle 11g and up. As Oracle patches occasionally have unique steps and it’s not as predictable as SQL Server patching, this script would not normally be scheduled to run automatically in the background. The DBA would normally kick it off themselves after reading through the patch ReadMe file and ensuring it does not include any additional steps. The user gives it the hostname and the home to patch, and can monitor it as the script goes through the steps.

Example Usage;

Install-v1OraclePatch -TargetServer “ORAHOST01” -OracleHome “D:\Oracle\ora19\dbhome_1”

SQL Server Installation

This script fully automates SQL Server installs on Windows. Installs now take 15 minutes and are fully automated, down from a full day manually, so the script saves about 1 day of work per instance. The script also improves database standards, as it ensures all new instances will be identical, nothing is missed during the setup, and all parameters are set in accordance with best practices. The script carries out the following tasks when called;

1. Perform pre-requisite checks on the server, to ensure the install can go ahead
2. Upload and install SSMS and SQL Server on the server. The SQL Server version is passed via a parameter, and can be any version from SQL Server 2016 and up. It Installs Enterprise Edition if environment is Production, Developer Edition if Non-Production.
3. Sets several SQL Server parameters based on best practices
4. Configures the tempdb in accordance with best practices
5. Sets up SQL Server maintenance jobs, such as Database Backups, Index Optimization, Integrity checks, Update DB stats etc.
6. Setup a monitoring database and jobs, which will monitor and log active sessions for all databases on the instance, which is extremely useful for any future investigation
7. Apply the latest applicable patch to the instance.
8. Generate a report of the new instance, and send it, along with the detailed logs, to a team email address for review.

Example Usage;

Install-v1SqlServer -TargetComputer “SQLHOST01” -InstanceName “SQLPRD01” -Environment “Production” -SqlVersion 2019

This will upload and install Enterprise Edition of SQL Server 2019 on SQLHOST01, with an instance called SQLPRD01. It will carry out all of the above steps.

Oracle Installation

This script fully automates Oracle installs on Windows. Installs take around 60 minutes, down from a full day manually. As with the SQL Server script, this also improves our standards, as it makes every new install identical and in accordance with our standards. The script performs the following tasks when called;

1. Perform pre-requisite checks on the server, to ensure the install can go ahead
2. Upload and install the specified Oracle software (works with 18c+)
3. Configure an Oracle Listener, with default settings unless otherwise specified when the script is called.
4. Apply the latest applicable patch to the home
5. If CDBName is specified when calling the script, create a Container Database with the specified name, using a dbca template in accordance with our standards
6. If PDBName is specified when calling the script, create a Pluggable Database with the specified name within the Container Database
7. Configure exports and RMAN backups for the new databases

Example Usage;

Install-v1Oracle -TargetServer “ ORAHOST01” -OracleInstallVersion 19 -CDBName TESTCDB1 -PDBName TESTPDB1

This example will carry out all the above tasks, and create a new 19c installation with a CDB TESTCDB1, with a PDB TESTPDB1 within the container.

Final Remarks

Automation is something I’m passionate about and I’m lucky to work for a customer that actively encourages it. As you can see from the rough figures in this post, it can end up saving you, your team, and the customer a massive amount of time. The initial creation of automation scripts is time consuming, but the payoff can be amazing. Automating a task not only improves your automation and scripting skills — but when you automate a process, you get to know that process extremely well, so you learn that way as well. It also means you can spend more time working on interesting tasks and less time doing the repetitive ones.

If you would be interested in the code for any of these scripts, let me know either through email or a comment below, and I’d be happy to share them and go into more detail. You can reach me on patrick.cull@version1.com.

Photo by Kevin Ku on Unsplash

About the author:
Patrick Cull is a Senior Database Administrator here at Version 1. Follow our Medium Publication for more Data blogs, or visit www.version1.com to find out more about our services.