Change Management for DBAs to Install and Track Database Changes

By: Terri Hurley   |   Updated: 2024-11-27   |   Comments   |   Related: More > Database Administration

Problem

Back in the day when I received an email from a developer who requested a Production Change, I would copy and paste SQL code from an email to SQL Server to run against a production database. I never bothered to save that code. But later when I was asked for a history of database changes, it was awkward to locate all the previous email requests asking for production changes. Fast forward to present day, most businesses have embraced Change Management Separation of Duties in companies of all sizes.

Solution

Change Management Separation of Duties is implemented today by many companies to systematically manage, approve, and install production changes. In my previous article, What is Change Management and the SQL Server DBAs Role, I described the Change Management process for developers and DBAs to work together on production changes. It dives into the specifics of the Change Management process from SQL code development, testing, and production deployment.

This article will focus only on explaining the specific process of installing and tracking database changes in the Production environment by the DBA. From this method you will be able to see a history of changes that you or other DBAs have implemented, meet Change Management requirements of maintaining Change history, and be prepared in advance of upcoming scheduled changes.

Step 1: Save Files Needed for Production Changes

When a Change Request ticket is assigned to you for Production installation, you will need to prepare for this upcoming change. To start, create a storage area to track all Production Change Requests. These files can be saved to a server, your local machine, or a third-party tool, such as SharePoint. The example below shows a main folder on the C drive called CHANGE_REQUESTS. Below this main folder is a subfolder named after a specific Change Request ticket number and the date that represents the installation date. The example below was created using the date 8/20/2024 and the Change Request ticket number CR54321. You can use the example below or shorten the folder name to use only the ticket ID or a combination of the ticket ID and the name of the Production SQL Server. Choose any method that works for you.

--Example of tracking separate change requests by saving to disk using the ticket and install date --as part of the folder name.
C:\CHANGE_REQUESTS\08202024_CR54321

As a DBA, you will perform specific tasks for each Change Request. Before the scheduled install date, it's important to review and save several documents that are attached to the Change Request to disk before and after implementing the ticket. You should download and save a copy of the following files for each Change Request:

1. The implementation plan.
2. All SQL scripts you are expected to run.
3. A screenshot before and after database changes were made, if required.
4. Script out specific SQL objects prior to changes being made, if needed. For example, if you're expected to run a SQL script to alter a stored procedure, then you should script out the original object. If you fail to save the original object code, you could potentially set yourself up for a database restore to pull that one object.
5. The original SSRS report (if expected to replace one) prior to uploading the new report. This is helpful if you must roll back to the old report. By keeping a copy of the old report, a restore of the SSRS system databases is not necessary. This ensures that you will also be able keep a history of all prior reports.

Step 2: Put the Change Request Ticket Number on Your Calendar

Take time to enter a task on your calendar reminding you of upcoming Production Changes. This is crucial to being prepared and not being caught off guard. This one step will set the stage for how you will implement all future production changes. Imagine working for a large company that has multiple changes scheduled during the weekend. If you don't prepare for these changes, you could potentially slow down the group effort to start on time. Remember, as a DBA, your duties may include taking SQL backups prior to starting, scripting out several objects, or collecting screenshots. Either way, your prep work must be done before any other participants can start.

Personally, I've always taken the time to review the implementation plan and SQL scripts the day I create the Change Management tracking folders. It gives me an idea of how much work is involved. In some large environments, you may not be involved in reviewing and approving SQL code changes, but rather implementing the approved database changes. If this is the case, you may not be aware of the impending workload on your part (DBA) for any single Production Change Request that's scheduled.

Then, about an hour before the scheduled change, I would prep by taking necessary screenshots, scripting out specific objects, and/or schedule a database backup to start at the time of the scheduled change. Now I'll be ready to start at the exact time the Change Request was approved to start.

In summary, the key to successfully and efficiently installing Production changes is to prepare in advance. Think about an assembly line. If you handle new Production Change Requests the same way every time, you will ensure future installations are efficient. In addition to being ready at the beginning of a Change ticket, you will have all the necessary historical documentation prior to the database changes.

Next Steps

• Be sure to attach screenshots and SQL scripts of objects taken both before and after making changes. The screenshots should include the date/timestamp that changes were made.
• Review my article, What is Change Management and the SQL Server DBAs Role, for details to implement production changes.
• Review my article, Database Change Management and Separation of Duties Guide, for information on Separation of Duties within Change Management.

About the author

Terri Hurley is a Sr. SQL DBA with over 20 years of extensive database administration experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips