Database Server Upgrades – The Plan, The Template, and The Task List

Database Server Upgrades - The Plan, The Template, and The Task List

A wise person once said, "To fail to plan is a plan to fail". This holds true in most areas of life and is no exception when it comes to upgrading servers. The key to a successful upgrade is planning. In this article, I present practical steps to help ensure your upgrade is successful by assembling an upgrade team, making sure you have the right plan, and having a good working database server upgrade template.

Inventory your server:

The first thing you should do before upgrading your server is inventory it. Make a list of all the applications installed on your database server and make a list of all ODBC connections on your server. Note the drive letters and note what databases or files are on each drive. Note the operating system and service packs installed. After making a list of all these items, get the install documentation for each application. Print screen the ODBC connections. List the drive letters and what is on each drive. The idea is to know everything about your server. In my experience, there are often times supporting applications on database servers and sometimes a database server is not always a dedicated server. Make sure you have a completed list, install instructions, print screens, and any other necessary supporting documentation.

Map Applications To database:

Make sure you have a complete list of applications and their supporting databases. Often times, a database supports several applications. This list is important because it will help you identify application owners and application testers. After you believe you have a complete list, send out a communication to the developers, application owners, business units, and possibly other groups. The purpose of sending out a communication is to see if they have added applications that you were not aware of or if application owners may have changed since you created your initial list. They should send any corrections to the list back to you. Be sure to update your spreadsheet and send it out again to all appropriate groups, so they have a final version of the application to database list.

Identify the upgrade team:

Upgrading a database server is a team effort. Meet with each department and identify the team members who are helping you on the upgrade. As a database administrator, often times your role is to both perform the database work, and also to coordinate the effort of all the groups and individuals involved. The list below outlines the teams and individuals involved, as well as, their responsibilities.

DBA Role- A DBA installs and configures SQL Server, SQL Server's service packs and feature packs. Also, a DBA is responsible for backups and restoration of databases, moving logins, setting up linked servers, moving DTS and SSIS packages, and moving jobs. In addition, a DBA is responsible for checking the integrity of each database, updating statistics and usage, and setting up any specialized sql procedures such as mirroring, replication, log reading, etc.
Networking Role- You will need an individual to install the operating system, install network patches, set up drives, and set up any other operating or networking configuration.
Developers Role- You will need developers who are familiar with the applications the database supports. Sometimes, developers have special setups in configuration files that need to be changed when moving to a new server. Developers can also be helpful in troubleshooting connectivity problems that arise. The developer is also utilized as the first initial tester of the application.
Application Testers Role - This is a business person who uses the applications every day. Some companies have a QA department to fill this role. It is the application tester's job to use the application and collect test samples of the data before the upgrade. Test samples can be obtained through print screens or other methods. After the upgrade, the application tester once again use the application and verifies that it brings up the same information and functions comparable to the way it did before the upgrade. It is more beneficial to have a business user test the application rather than a developer. In my experience, an individual who uses the application every day uses the application differently than a developer and can catch more inconsistencies. Additionally, an application tester has a vested interest in making sure the application works correctly. Often times, it is beneficial for each application tester to create test scripts in advance, so that each part of the application is tested thoroughly. After testing, signed test scripts should be submitted to the DBA, so that the DBA has verification that the application was fully tested.

Planning and Communication:

Two of the reasons projects fail are improper planning and ineffective communication. Communication is one of the most essential components during the upgrade. People prefer to be informed about how the upgrade is progressing. Proper communication helps solve any unexpected issues that arise and allows them to be handled by team consensus rather than one individual.
Before the upgrade a number of meetings should take place. After each meeting, a summation email should be sent to all participants. The email should communicate any decisions that were made, any assumptions that were discussed, and the completion date of tasks that were assigned to individuals. At the conclusion of the email, invite feedback from the participants. Feedback ensures that what has been written is accurate and gives participants an opportunity to add any missed items, ask for clarification, or change an item that was inaccurate. Send out a final email when all the changes have been made. Below is a list of items that need to be discussed and agreed upon during the meetings.

1. Agree on when server preparation work needs to be completed. If you are moving to a new server, preparation work can be completed ahead of time. Often times, this is a coordination effort between more than one group. For instance, the networking group will install the operating system and other configurations and then give it to the DBA group to install SQL Server. The network group needs to agree upon the date by which they will have their work completed, so the next group can perform their configurations.

2. Communicate the full range of applications that are affected. End users understand application availability, whereas IT staff thinks in terms of server availability. Communicate which applications will be affected during the upgrade. If you state that a particular server will not be available, an end user may not realize that this means their application will not be available during the upgrade.

3. Agree on when a development environment can be set up that is as close as possible to mirroring the production environment, so that a practice upgrade can take place. By upgrading a development environment, you will identify steps that need to be accomplished in order to have a successful upgrade. All groups will need to be involved when upgrading a development environment. You will need to make sure applications are working and processes are running as they do in production. If you do not have the hardware to create a complete development environment, consider creating a virtual machine environment to practice the upgrade.

4. Agree on the server upgrade time and date - The DBA should propose a date and time to do the upgrade. Each stake holder will then have the opportunity to suggest alternate dates or arrange their schedule, so that the upgrade can be performed on that day.

5. Create a list of contacts - A phone list of business and cell phones should be constructed ahead of time in the event that an issue comes up which requires the input of others. It is better to get a group consensus when an issue arises than to assume you are choosing the best course of action. This ensures that the right decision was made and that it was a group that made the decision.

6. Communicate the time when each group is needed during the upgrade - Since the DBA will be performing most of the work during the upgrade, it is not necessary to have other groups involved until they are needed. Developers are not needed until the upgrade has been completed, so that they can do the initial testing. Business testers are needed to get samples ahead of time and after the upgrade has been completed. Communicate a projected time when each group is needed.

7. Upgrade Status Communication - During the upgrade, it is helpful to let team members know the status of the upgrade. It could be that the upgrade is running ahead of schedule, behind schedule, or that you have run into unexpected issues and need to alter the time for developers and testers to arrive. Also, it is good to let everyone know that the upgrade is complete and they can log onto their system. One effective way to communicate the status is to set up a voice message on a phone number. Communicate to everyone that they need to call the phone number and listen to the message before they come to the office. The following are some examples of some helpful upgrade status voice messages.

"The upgrade is going as planned, we should be done by 8:00 pm. Please check the notification before you come in to make sure the status has not changed".
"The upgrade is running behind schedule, the new time is 10:00 pm. Please check the notification before you come in to make sure the status has not changed".
"We have run into XX issue. We need to meet and discuss what course of action to take".

8. When the upgrade is complete, communicate by email to the affected groups that the upgrade was successful and that their applications are now on-line and available for use. Communicate who should be notified in the event of unexpected errors that may be related to the upgrade.

9. Develop a rollback plan - Sometimes, things do not go as planned, so a rollback plan should be developed. This rollback process will need to be started if the server is not upgraded by an agreed upon deadline.

The Process of upgrading the server:

Attached is an upgrade plan template of detailed steps that are used in most upgrades. When you upgrade your development environment, you will be able to customize a task list for your environment. Each task should have a projected start date and time, the task description, the person who is responsible for the task, the completion date, and comments. The upgrade plan should have three sections:

1. Pre-Upgrade preparation - This section should contain anything that can be done ahead of time. If you are moving to a new server, many things can be done. Networking can install the operating system and patches, configure drives, and do any other configurations to get the server ready. SQL Server, supporting service packs, and feature packs can be installed. Supporting applications can be installed. If you are upgrading your current server, there will only be a limited number of things you can do ahead of time. You will still be able, however, to inventory the server, generate scripts, and document any configuration options. The key to this section is to minimize the work that needs to be accomplished on the upgrade day.

2. Upgrade Day - This section should contain any task you need to perform on the day of the upgrade. In the time column, you should enter an estimated time to ensure that you are on schedule. Also, after the upgrade is completed, developers need to come in to the office to test the applications they support. They should help troubleshoot connectivity problems and application issues as they arise. After developers test their applications, business application testers arrive and test the application. Application testers should be in constant communication with the developers and DBAs to troubleshoot any issues that arise. During testing, the application tester documents each successful test by print screens or other methods. At the end of testing, the application tester sends the DBA a signed copy of the test results.

3. Rollback Plan - In the event the upgrade does not go as planned, it is important that you spend time on a rollback plan. Make sure you document where any restore backups are coming from, applications that need reinstalled, and any other groups that need to be involved. A good rule of thumb is to "plan for the worst and expect the best".

Below is a birds-eye view of the upgrade process:

1. Upgrade the development environment. This will help you put a complete task list together.
2. All prep work is completed on the production server ahead of time.
3. On upgrade day, application testers follow their testing scripts and gather print screens and other documentation that will be used to compare to the results after the upgrade.
4. The upgrade begins in production. The DBA performs the upgrade.
5. Developers are notified that the upgrade is complete.
6. Developers test their applications.
7. Application testers are notified and they test the applications using their testing scripts.
8. Application testers send signed test scripts and test verification to the DBA.
9. On the first business day, after the upgrade, the DBA and developers should come in early to field any issues that arise. It is better to fix any potential problems when one or two individuals are reporting the issue than when whole departments are experiencing the problem.

In conclusion, this paper has documented ways to have a successful upgrade. The topics of discussion include successful ways to identify stakeholders, assemble an upgrade team, plan the upgrade, and communicate within the business. The best way to know when you have included all the tasks in your upgrade plan is to upgrade a development environment. Properly planning your upgrade beforehand saves time and resources on upgrade day. Every individual knows their place and what is expected of them ahead of time. I have used this method for many years and I try to continually improve the process. Since every environment is different, each upgrade plan will include different tasks. Feel free to take the template and customize it to your environment.

Database Server Upgrades – The Plan, The Template, and The Task List