Database Automation: Your Secret Weapon for DB Administration Tasks
Databases are critical for every organization, as are DBAs and database resources.The scope of business today has surpassed all previous levels for companies to stay competitive, and now we have multi-petabytes of data to deal with on a daily basis, spread across multiple locations including private data centers and public clouds. And DBAs not only have to deal with databases and data but also make sure that compliance, security, costs, data protection, availability, DR, migrations, and upgrades (just to name a few responsibilities) are taken care of.
With this amount of disparate data, smart DBAs must “clone” themselves by automating as much as they can. But when that is not sufficient due to the fact that there are simply too many databases to manage, it’s an even smarter move for database administrators to look for tools that can provide a self-service, single-window management terminal of all their database automation needs.
Pushing Changes Through the Pipeline Can Be...Complicated
It is a complex process to set up and manage databases when your application depends on multiple sources of data and needs to remain highly available to meet SLAs and deliver fast response times. When you are running multiple environments ranging from DEV and UAT to PROD, it can be quick and easy for you to make structural changes such as logical and physical database structures to DEV or UAT environments, but achieving such shifts in PROD is not the same.
Then there are the simple database administration tasks that require DBA capability, but the DBA might not be available to perform at a certain time. You may have just completed an incredible database modification and need a complete backup of the database on prod. Or, say your development team has confirmed a fix for a bug that is certified on the UAT database.
If the DBA is smart and has automated tasks such as PROD deployment scripts prior to becoming unavailable, the IT team does not have to wait for the release to be completed because the DBA has already automated database scripts for production deployments. In this way, by automating database tasks, DBAs not only lighten their burden but also help IT teams deliver on time.
Yes, databases can be complex; but they are critical. Automation allows you to standardize the process of database changes across the development, UAT, and production environments, and these changes can then be tested and certified before finally moving to production. This drastically reduces the time to launch new products so that you can stay ahead of the competition and be free to innovate.
Automation At Your Fingertips
So where exactly does the art of automation stand today? With many database engines, there are certain database administration tasks that are already automated. For instance, Oracle provides maintenance windows, where automated cleanup activities and the gathering of database statistics are performed, and storage, as well as other resource utilization, is possible as well.
You can write your own shell scripts to perform certain DBA tasks, such as taking backup, moving archive logs from one storage to another dense storage, etc. DBAs can also create scripts that are a useful deployment of code changes from databases to databases.
But in the age of cloud data centers, keeping options for automation limited to only DBAs may result in a failure to deliver on time. Because you may have multiple databases and multiple environments, each running a different software solution with varied availability and performance requirements, you simply may not have all of your resources available when you need them.
We will be looking at two methods to automate your database administration tasks: the traditional way and another way that uses new innovative tools and leads you to the database automation methods of the future.
Database Administration Tasks - Old School Automation
Traditionally, there have been several methods and tools for automating a majority of database administration tasks. Some of these come built into a database, and others are performed by DBAs and developers either via shell scripts or database code and maintained by configuration managers using different versions for different environments.
Numerous DBA tasks are performed using scripts and should be version-maintained so that they do not create any conflict. These tasks cover everything from database activities such as the creation of logical database structures, including tables, views, index procedures, and triggers to DBA tasks such as adding or extending logical storage for tablespaces and datafiles, creating alerts on overall database utilization, and restoring a database in case of failure.
The best practice is to create your database code as application-centric as possible so that when you move from DEV to UAT to prod, it is done with ease and you are able to reduce time to market for optimal results. Code and data are the two most critical aspects and need to be dealt with separately from database administration tasks.
For instance, a change in logical structure may be easily performed on dev, but you then may need to perform several steps on PROD before deployment, such as a simple case of adding a column with a NOT NULL constraint on the column. On the other hand, you may have a storage requirement on DEV that does not match with the PROD environment, and hence you keep different versions of script to automate them.
Traditionally, you will automate these tasks using tools or schedulers, such as UC4, shell scripts and cron jobs, Oracle Scheduler, and Enterprise Manager Console. But these can become very cumbersome to manage when you have to deal with multiple databases.
Luckily, there are several cloud database orchestration and automation tools that can prove a boon to any DBA by providing a self-service and completely automated environment, where tasks ranging from installation, setup, and deployment to backup and monitoring are all automated and monitored through a single-window system.
Database Smarts: Automate to Innovate
When you are stuck as a developer or a DBA performing database administration activities that you could automate using script and schedulers, then you are basically wasting your and your organization’s time due to redundancy and inefficiency. Bill Gates said "I always choose a lazy person to do a difficult job because he will find an easy way to do it." The assumption here is, of course, that the lazy person is smart; very smart. And that’s where automation comes in.
You may or may not already have an automated creation of procedures and some sort of code to save time, but the real beauty of fully integrated automation when faced with managing multiple databases of different calibers is that you don’t even need much of a DBA background to implement this feature.
With DevOps tools like Chef, Puppet, and SaltStack, you can set up and manage a private cloud such as AWS DBaaS on-premise in just a few clicks. These tools not only make life far easier for your DB team but also improve performance of the application and delivery process. And that’s music to the lazy man’s ears. Now let’s take a closer look at implementing these new tools.
DevOps Tools - Automate into the Future
If you are dealing with DB challenges on a daily basis, you have to know that there are complexities that cannot be handled merely via manual automation or the basic built-in tools. Take for example a new KPI in the application that requires you to make changes in database objects such as packages, procedures, tables and all the dependent source code before being able to deploy them in the application.
There may be multiple people working on the database part to get this new KPI working on the application as databases contain important information that also needs to be a part of any new change. Hence, there is a possibility of missing one item of information during deployment that will result in failure to deploy on time. Not a good thing.
Database DevOps tools can handle these changes from a single-window option and disallow change from moving to production. They can even generate a detailed report on what needs to be deployed, provide insight on the deployment, and allow you to automate for continuous delivery.
Two great DevOps tools mentioned above that help tackle this challenge are Chef and Puppet, both of which focus on platform automation and enable continuous delivery through their single-window configuration, allowing you to manage change configuration, extensibility, compliance automation, and availability.
Let’s take a look at how you can redefine operations as Developer Services with Chef:
- BUILD: Lowers cost of managing and maintaining infrastructure by providing an on-demand and self-service infrastructure that the developer needs.
- DEPLOY: Offers management of infrastructure change via automatic testing of deployments, enabling developers to quickly ship quality software at reduced risk.
- MANAGE: Provides an easy single-window configuration along with full database activity monitoring so that your organization reduces software and configuration risks; also provides insights into speed and efficiency as well as reduced risk of delivery errors.
- COLLABORATE: Reduces friction between teams by enabling full stack transparency and management via separating duties within the team to foster successful collaboration and allow for the delivery of a configured infrastructure with ease.
DBAs and DevOps have increased burdens on their shoulders due to the fact that organizations are becoming more dynamic and widely spread across multiple functions, from business operations to IT infrastructure.
Only companies that are able to deliver new solutions quickly are going to be the ones that succeed, and this requires that they have people and DevOps tools taking care of continuous delivery. There are many DevOps tools in the market that can help you implement Continuous Delivery Automation of your databases.
DBmaestro’s Database DevOps Platform was created specifically for databases and provides visibility into structure, code, and data, while enabling you to easily find conflicts between environments so that you can resolve potential issues, avoid any actual failure, and achieve the successful automation of database administration tasks.
If you enjoyed this article, you might want to read about DBA responsibilities. How and why are they changing?