Problem
If you have never been involved in Change Management processes but now find yourself part of one, it may seem a bit overwhelming or confusing. However, the reasons for and benefits of these processes are simple and straightforward.
This article will explain Change Management and how DBAs are involved and can benefit from it.
Solution
My Experience
I’ve been a DBA for over 20 years. Looking back on my first job at a small company, it was truly exciting and a huge learning experience. There was only one other DBA, a Senior DBA, who became my mentor and taught me a lot about SQL Server. A few years went by, and our small company was bought out by a much larger, firmly established company with a team of 10 DBAs, with an equal mix of SQL and Oracle DBAs.
I remember the day we met in person after the merger to finally meet this talented team of DBAs, get to know them and work together. As we became comfortable, one of the DBAs commented that we (myself and the Senior DBA) were ‘cowboys’ because we actually made changes in production without following any procedures or approvals. Wait, was that a compliment or an insult?
I don’t remember if I said anything, but I remember sitting in awkward silence afterward. Later, I found out that they were referring to Change Management procedures. At that point, I started wondering what change management is and why it is so important. I needed to figure out what this was all about and how it affected me.
Since then, I have been thoroughly educated about what Change Management is and what role a DBA has. I’ve worked in a few small companies and a couple of large companies, where most had some level of Change Management processes and procedures.
Understanding the DBAs Role to Implement Production Changes using Change Management Processes
Let’s talk about the overall process and the order of the steps used to implement production changes. Some companies may have a large Change Management team, while others may have only one or two individuals who support Change Management. The following is a guide on implementing production changes that involve DBAs.
Who Installs Development Changes?
When a client determines that a change to the Production database is required, the Change Management process starts with the client opening a Change Request ticket. Each Change Request ticket is given a unique ticket number for tracking. If you use a Change Management software app, the new ticket will auto-assign a unique ID to that Change Ticket request.
That ticket is typically assigned to the Development team from the start by the Change Management team. This ticket should include:
- A detailed description of the database changes that are being requested.
- A description of the issues encountered or a new functionality being requested.
- Attachments showing a sample of the existing Production issues or a sample of the correct expected results in the form of screenshots or a video provided by the client.
A Developer will be assigned to this ticket who will write and install SQL code into a development database based on the client’s application requirements. Once the SQL scripts have been successfully applied to the Development database, the Developer will take a screenshot(s) and attach it to the Change Ticket documenting the successful installation. Once all other application coding changes have been made, if any, the Developer will be ready to review rollback requirements for that ticket.
Depending on the complexity of the expected changes, Change Management may require a roll back script to reverse the new changes. A roll back script is preferred since the Production database will recover much quicker from a SQL script than a database restore. But there may be other times when a database restore is required. If this is needed, then the client should have this database restore request documented and approved in the ticket. If a roll back SQL script is required, the Developer will write the SQL script and attach it to the ticket. The Developer should also sign off on the ticket acknowledging that development has been completed successfully and is now ready to advance to the testing phase.
Who Installs Test Changes?
In preparation for the testing phase, the Developer will request to have the Test database refreshed from a copy of the Production database. This needs to happen before installing Development scripts in the Test database, especially if the Test database is stale. Unexpected issues can show up later in the Production database if there are differences between the Test and Production databases during the testing phase. Once all the SQL scripts are applied, the Developer will also take a screenshot of each script showing it was installed successfully and attach it to the Change ticket. Afterward, the Developer will notify the client that application testing is ready.
During this testing phase, it is very important for the client to test the application to verify that all expected database changes were applied and that the application is functioning correctly. Once the client tests and verifies that the application performs successfully, the client then takes screenshots and attaches them to the ticket to show that the application returns the correct results. At this stage, the client will sign off the ticket, acknowledging User Acceptance of those changes in the Test environment. But, if there are any errors, the Change ticket is documented, indicating that testing failed and the Developer needs to go back to the Development environment and rework the SQL code.
If testing is successful, you will work with the client to schedule the Production change. Production changes are typically applied outside regular business hours to minimize downtime, with exceptions. Once a date is chosen, put that date on your calendar so that you can be ready for the installation and not caught unprepared. The client and Change Management Team will determine if the Change ticket constitutes a Standard change, which can be scheduled, or an Emergency, which needs to be applied immediately and can be during regular business hours.
In preparation for the Production change, Change Management will typically create an implementation plan. This includes getting input from you and others involved in this ticket and documenting it in a spreadsheet. It contains a detailed, step-by-step schedule including:
- The changes to be installed
- Who’s making the change
- An estimated time for each step
- An approved roll back plan in case of issues.
Once the implementation plan has been completed, the plan, along with SQL scripts, will be attached to the ticket.
Note: Some companies may not have strict policies on making changes, meaning you may make some changes in Production without an approved ticket. However, some large companies require an approved ticket for all Production changes, whether they are an emergency or not. These companies will monitor every change a DBA makes in Production. I once worked for a very large company that had strict rules. On my first day of employment, I was told that DBAs are not allowed to make a Production change of any kind without a Change ticket. If I violated that policy, consequences could lead to termination.
Who Installs Production Changes?
The DBA will be making changes to the Production database. The Developer’s task is to write and test SQL code and shouldn’t have rights to the Production database at this point due to the Separation of Duties policies (a part of Change Management). So, the DBA will start the process by taking a database backup to have a point-in-time to recover if needed, even if you must run a roll back script to remove database changes. It’s simply good practice to take a database backup prior to making any scheduled Production change.
In addition to taking the Production database backup, Change Management may also request that the DBA provide a script or a screenshot of the original objects to be altered or removed and attach them to the ticket. Be sure each screenshot includes the date/time stamp of your workstation or server where they are being taken to prove that all changes were made on the scheduled day or time. If you do not include this, you may be asked to go back and collect screenshots along with the date/time stamps.
Changes involved with a Standard ticket are generally scheduled during off hours, typically so users and business transactions are not interrupted, as downtime during normal business hours could equal less revenue. The client will notify application users in advance so they know to stay out of the system during the scheduled updates.
Once the Production changes have been applied, the DBA needs to take a screenshot showing that the SQL scripts were applied successfully and attach it to the ticket, providing before/after proof of the Production changes. Once all database changes are complete, update the ticket with your comments, i.e., the changes were successfully installed, and the database is now ready for access. You are now ready to notify the client immediately that changes were successfully applied to the Production database and is now ready for use.
Installing Emergency Production Changes
For an Emergency ticket, you may need to install SQL scripts into Production during regular business hours to resolve an issue. The client will typically notify the application users right away for these cases so they can stay out of the application during the database update process. The client may contact users via email, chat services, or other communication methods to notify them of the existing issue, along with the estimated downtime to implement a fix.
If there are application issues, the users are most likely aware and will wait for you to resolve the issue. Once the client has notified all users, you can start your roll back script process. Do not begin the roll back process until the client has given you the approval to start. Begin by either restoring the Production database from the backup taken earlier or run the roll back script that was attached to the ticket. Since this is an urgent Production issue, the goal is to get the database back to a normal functioning state as soon as possible.
After running the roll back script, take a screenshot showing it was installed successfully and attach it to the ticket. If the ticket only requires a database restore, you can restore the database with the backup that you took prior to implementing the database change. Take a screenshot showing that the Production database was restored successfully and attach it to the ticket.
Completed Production Change
After you have installed the Production changes successfully, your ticket should have several documents attached:
- A spreadsheet containing an implementation plan that lists the steps to be taken, the order of the steps to be implemented, and the estimated time to implement each step.
- A copy of the Production scripts provided by the Developer.
- A script to roll back changes or instructions to restore the database with client approval.
- Screenshots or scripts of the original objects taken prior to being modified or dropped.
- A screenshot after applying each SQL script showing the successful installation.
- Any other objects to be replaced, such as SSRS reports. You can download the original report prior to uploading the new report. This will save you from restoring a copy of the system reporting databases to retrieve one report.
As mentioned before, all screenshots should include the date/time stamp displayed on the server or workstation where the Production changes were made.
After a Production Change has been Implemented
Immediately after installing all SQL scripts, check if you need to grant user rights to new objects created. This will eliminate any delay in users accessing new tables, procedures, views, etc. Notify the client immediately to test the application in Production to verify the changes are successful. This is a very important step because if there are issues, you may need to restore the Production database or run rollback scripts. Once the client has verified that the Production install was successful, the ticket can be updated with comments stating as such and closed with Change Management approval. At this stage, you have now implemented a Production Change ticket using the Change Management process.
Benefits of Change Management
Change Management should be part of every business. Companies have expectations and requirements to keep up with technology and regulations and to keep their data secure. So as a result, businesses and organizations need to make adjustments to their business practices.
Accountability
Now, consider this scenario: Generally, most companies have one person to handle accounts payable and another accounts receivable. This is because if one person handles all the money coming in and going out, it creates a possibility that one person could make account changes or withdrawals without being held accountable.
In comparison, Developers are supposed to interact with clients and write SQL code based on business requirements. The DBAs will then install that code into the Production database. It’s the same concept of accountability: no one person can have full data knowledge and control over production changes.
Separation of Duties
Another benefit is that all production changes will go through the DBAs because of the Separation of Duties requirement as part of Change Management. So, the next time someone asks if changes were made in production recently, you will know exactly what’s been going on because you (the DBA) implemented those changes.
Summary
If you discover your company is implementing Change Management, don’t feel offended thinking your company doesn’t trust you. It’s nothing personal; it’s all about business and accountability.
Next Steps
- Look for my next article about efficiently tracking and installing all Production changes by the DBA. If you work at a large company with a high volume of database changes, keeping track is a necessity. Chances are your access and actions on a Production SQL Server will be monitored. Don’t be surprised if someone contacts you to ask about recent database changes.
- Also, be sure to read my article – Database Change Management and Separation of Duties Guide. This guide provides an overview of the Change Management implementation process.
- Make sure you regularly verify that database backups are current and running successfully. Periodically perform disaster recovery testing by restoring backups to a backup server.
