Chapter 8 - Managing Database Change
Describing best practices for handling database changes from the earliest development stage, through the quality assurance (QA) check stage, and finally to implementation in the production environment, this chapter gives practical advice for Database Administrators (DBAs) on handling issues that arise when supporting a 24 x 7 operation where database implementations must not be a point of failure.
Preparing for a Changing Environment
Although many companies already have processes in place that deal with information systems change management, some frequently have uncontrolled development environments, little or no QA formalities, and impromptu production implementations. In the development environment, the need for a controlled process is evident in situations where the constant changing of objects or code disrupts ongoing work. In the QA environment, the need becomes evident when testers are uncertain what version of the code they have and whether it has changed since they began testing. In the operations environment, the lack of a controlled change process is perhaps the highest risk of all, because when a disruption of production service occurs, the result may be financial loss for the company.
So how does your information systems environment measure up? Can you tell what changes were made last month, or what changes were made on a certain project? Can you provide information on when and why a change was made, and who authorized it, tested it, or implemented it?
As your company grows, you will likely need more formal processes for dealing with changes, especially in regard to changes made to the database. The management process described in this chapter will help you gain control over the process of change to the database servers, and ultimately this will benefit both you and your company.
The development cycle begins with planning in the development group and ends with the support of the finished system by the operations group. The development group has different goals and priorities than the operations group. When changes are planned for the database server, the Database Administrator essentially has to mediate between these two groups, balancing the needs of all parties involved.
Among the primary goals of the operations group is stability and availability. Its responsibility is to resist changes that may have a negative impact. This includes any new system, or change to a current system, which would represent an unknown process, or which could be identified as raising known issues in the production environment. Without the ability to identify exactly what changed when and for what purpose, operations staff face repeated "fire-fighting" issues. Accomplishing the goal of stability requires control, measurement, and up-to-date support information for all systems. Appropriate operations staff should have input into system design and must be informed of any system changes in a timely manner.
The main goal of most development groups is to provide flexibility and constant improvement in response to user needs. This is an area of rapid design and change. The developers need freedom to work, yet they also require a stable environment and some isolation from other ongoing development. They should be able to verify the effect other development projects may have on their work, to identify potential design problems before they reach production.
Database Administrator Goals
The goal of the DBA is to be responsive to both environments, balancing fast action with cautious evaluation. How can this be done most effectively? While there is certainly more than one approach, the guidelines presented here may help answer this question.
It is important to decide on a level of control that is both supportable by your group and required by your business, in relation to the size of your Information Systems (IS) department, the number of ongoing development projects, and the number of production systems that must be maintained. While the need for control tends to be driven by the operations group, the need for a stable process tends to come from the developers as their numbers grow. The process must actually begin at the source of change: So, for a development project, it must begin with the development group.
In many ways, contributing to change control is part of the responsibility of the DBA. The primary role of the DBA is to protect the integrity of the data, both as an asset in itself and as private information accessible only to authorized personnel. A stable, successful production database environment exists only with appropriate change management, characterized by controlled, tested, and documented releases.
Managing the Development Environment
In an ideal environment, all databases would be designed and implemented by a cohesive team of DBAs with impressive development skills and years of production support experience. In the real business environment, this work is usually done by a disparate group of people with a wide variety of project priorities, skill levels, and personal development styles. The logistics of coordinating development of database objects by a large disjointed group can become enormously challenging to the DBA, who is responsible for supporting the resulting database in production. But when the operations group makes a call because of a problem on the database server, it will be the DBA's phone, not the one belonging to the developer, that rings. Therefore, this should become a priority: to understand and control (document, test, protect) all database changes, whether they are made to the stored procedure or function code, the physical database structures, the data, or the storage components upon which everything rests.
As you take steps toward conforming to a change control process, you will need to make decisions and develop standards based on the following considerations:
How will you duplicate and refresh data in the development environment?
What is the best development environment you can provide?
How will you track changes made in association with various development projects?
How will you review and test the changes?
How will you implement the changes?
How will the new system or change be supported in production?
Development Database Process
Often by the time a DBA is included in project meetings, the project team is no longer considering actual design, but simply how to implement the solution they came up with. This is unfortunate, because for the development DBA, understanding why the design is set in a certain way can reveal alternate, better solutions. Occasionally, you will also discover that a team is trying to reinvent a process that is already in place and available to them. It is also important during the design phase to involve a representative from operations, who may have important information on standards or requirements from an infrastructure standpoint. Clearly, open communication is key.
A database is only as good as the code written against it, and it is the responsibility of the DBA to facilitate that process. Ongoing communication between team members is vital to success, and can be encouraged by the following means:
Daily or regularly recurring brief triage meetings within each role group, or within all roles working on a specific system, that give updates on key or time-critical system and business issues and the action needed
Regular status reports in a format that is easy to distribute and read (e-mail, for example), delivered to IT management and the business community on key performance indicators for operations (for example, metrics against service level agreement, help desk log statistics, progress toward division goals, and so on)
As database technology progresses, it is becoming more focused on work that was once considered design or development related. One good way to foster skills in the new areas of Microsoft® SQL Server™ 2000 is to become more involved in development projects.
Control: Helping or Hindering?
An effective development environment requires a significant amount of control and monitoring of the development database. This is not to say that you should restrict the developers from being able to do their work, but that you are protecting the integrity of the database design while providing a safe environment for all the developers to work together. When you first implement a controlled change process in the development environment, it is a good idea to meet with the developers and explain the advantages of the new system, while reassuring them that they will still be able to do their job in the most effective (if not the absolute fastest) manner possible.
Keep in mind that in order to help the development teams be successful, you must be able to quickly support their requests, regardless of ongoing production problems. This will almost certainly require consistent or rotating allocation of one or more DBAs to the role of development support.
The amount of autonomy you give the developers will naturally be inversely proportional to the amount of time you can devote to supporting the development environment. Do not attempt to lock down the development environment too far if you do not have the resources or the skill sets within your team to support the influx of requests you will get. Also, be sure you have the agreement of upper management before you begin the process of implementing the ideas presented here.
Assuming we are dealing with a standardized database where all production objects are owned by dbo, then these objects should be changed only by a DBA. No developers should have db_owner or db_ddladmin permissions. Instead, each of them should be given individual permissions to create procedures, functions, and, at your discretion, views. But, again, the original dbo objects will remain in place until you make the change.
In this scenario, the developers each have their own schema, their own work area for code, even though they are all working in the same database. Each owner name is a different schema. Everyone can have a different copy of the same stored procedure, and multiple test copies without causing anyone else problems. Their changes to their own copy of an object will not affect everyone else on the development system until they are finished with it. They will still be able to share their work, by granting permissions on the objects they are finished with.
This layer of security isolates and protects each developer's work from the others. It also has a few advantages that become clear in the process of development. The first advantage is control over structural changes. When the developers require table changes, you will be involved immediately. This will give you input into the foundation of the database, the tables which drive the code. In designing any new tables or table changes, you will also be alert to modeling standardization requirements the developers may overlook. Another advantage here is that if you are the only one making table changes, you will already be aware of the changes if a second developer requests additional changes to the same object. A simple e-mail at this point can avert a design clash.
The second advantage is the opportunity to review and optimize the code before it causes a production problem. At some point the developers are going to ask you to create their objects as dbo. Take the time to look through their code and make sure it follows standards of documentation, proper formatting, good usage, and design. If you see a problem, fix it, but also send a note to the developer explaining the reason for the change. The developers will begin to adjust to the new standards and will produce better code, shortening the review process over time. The respectful exchange of information and the mutual desire for high quality design and code will foster a superior development environment.
Also take the time during the code review process to make sure that adequate comments are provided in the database code. At minimum, you want to have a sample syntax line, a statement of purpose, a notation on who wrote the code and for what system, and a list of modifications and associated Service Request numbers. The sample syntax line, which should be valid for testing using SQL Query Analyzer, is the single most useful part of this. It will allow you to simply highlight that line and run the code, so you can check the execution plan, resources consumed, and so on.
An additional hidden benefit of this strategy is creating a unit test environment. This gives you the opportunity to create your implementation scripts, and test them by implementing them to the dbo schema of the database. Be sure to make notes at this time regarding what server and database the object belongs to, and whether any changes will be required at implementation time, such as re-pointing code to the production version of a linked server. When you are finished, the developer can then run their code in this environment. All dbo objects are part of the unit test environment (although, the data is not unless you protect it). Developers quickly become accustomed to requesting that their objects be promoted to dbo ownership, at which point they can update their code and conduct unit tests before having a very public test in front of the QA team.
Creation of dbo objects
Restricted to DBA use only
Code for stored procedures and functions
Created by developers with their own user name. For example, dbo.usp_sample would be copied to new procedure sample.usp_sample for all changes.
You may choose to treat this as a stored procedure, or you may wish to exercise more control over the creation and use of views.
Because the use of triggers should be carefully examined, DBAs should create and control this code.
Table and materialized views
DBAs make changes to tables after reviewing the design.
Indexes should be created by experienced DBAs. Although they are fluid, indexes should be controlled even in development.
How Control Can Become a Hindrance
Despite the various advantages in the development environment discussed above, please note that it is possible to take the idea of control too far. Although you may find individual projects where the lack of sufficient time or skill on the part of the development team will cause the development DBA to do all of the database coding, do not encourage this as a standard solution. While any good DBA should have the appropriate skills to code difficult stored procedures, this service should be an exception to the rule, when there is a requirement for advanced database knowledge.
Under no circumstances should you set up a database group whose purpose is to keep such control over the development environment that only DBAs have permissions to write all the database code. This system of development has several faults. First, it turns into a game of keep away with the data, which limits the amount of creativity that can be employed to find the best design. Secondly, it will discourage developers from learning better database coding techniques. This is not beneficial for the company, the developers, or the database group.
It is best to fix the problem at its source: train the developers. A good guideline is to employ the minimum level of control that is required. Control should serve a useful purpose and contribute to the manageability of the environment rather than just add complexity.
Duplication of the Production Database
Regardless of the size of your development team, you will need to create a development database to avoid endangering the production database with untested code. If this is not a new system, you will have to copy the production database to the development server. The easiest way to do this is to restore a backup to the development server.
However, in most cases the development server is smaller than the production server, and you will not usually need the entire production image. If you need a subset of the data, you will not be able to restore the database from a production backup. Instead, you will have to script the production database and develop a set of processes to transfer the data (preferably from a standby image). This can be done using either a saved Data Transformation Services (DTS) package or a set of Transact-SQL scripts. If you use a source control application, you can generate the scripts from that application, but you will still need a data transfer process. Make sure to design this process so it permits restoration of tables either individually or in related groups. Taking the time to simplify this process now will make it easier for you to respond quickly to developer requests for fresh or repaired data.
If you have secure data on your production server, such as credit card, financial, or other private information, you should skew this data by some secret algorithm. For example, all credit cards could be set to the same fake number; or if you want to show different numbers for each one, start at a particular number and increment by one. If you load the data using a DTS package or a Transact-SQL script, you can make these data transformations during the transfer process. These steps will keep your sensitive production data secure.
In some scenarios, the developers or testers create test case data, and this must also be preserved. Make an effort to check if your developers or testers have data they wish to keep, so they will not have to waste time recreating it every time you refresh the tables. To make this easier, the test data restore should be a separate process that runs after the regular data refresh.
Logins will have to be created both for the developers and for a set of test users. Each developer and test user should have an individual login, and should be required to use it rather than sharing one login with a group. This makes administration simpler, and can save you some valuable time when you need to find out who is running a particularly resource-intensive query or when you want to run SQL Profiler to gauge the performance of the queries involved.
The test user accounts should each represent a unique permissions profile or role in the production database. You can create these as test scenario logins, or your developers and testers may choose real users who characterize the permissions. Isolating permissions like this to imitate the production environment will allow the developers to do more thorough unit testing. It is not a good practice to just duplicate all the production logins on the development (or QA) server.
If you have restored the database from production, you may find that the Security Identifiers (SIDs) for your database logins no longer match the server logins. The most noticeable symptom of this is that your user will be able to login, but unable to connect to the database; the error message will either state that the server user is not a valid user in the database or, if the database in question is the default database of the user, that SQL Server cannot open the user default database. You can re-link the SIDs between syslogins and sysusers databases by using sp_change_users_login. This approach lends itself to generating a script to automatically synchronize the users as part of the data load.
Using Command Line Scripts for Implementation
Although almost all implementations to a database can be performed using SQL Server Enterprise Manager, the best practice is to use command line scripts. Scripts have several advantages in database change implementations. First, they provide you with a permanent, verifiable record of exactly what you did to the database. Not what you think you did in the GUI, but what you actually did. Secondly, a script gives you peace of mind and objectivity. If you follow this entire formalized plan, you will have tested your implementation scripts twice before running them in production, and while there is always a chance something unexpected may occur, it is reduced to a very small percentage. This means that if your implementation window is at 4 A.M., and you have another server that needs attention at the same moment, you will still be able to walk through the scripts without worrying that you missed something. As an added bonus, if you are unavailable for any reason, a stand-in can do the implementation.
When the development process is complete and the code is ready for unit testing, create the implementation scripts and store them in a secure area. Resist the urge to use sp_change_object_owner to change the owner to dbo on objects delivered for unit test. This is very fast, but if you do this, you have missed out on your only opportunity to pretest your implementation scripts, and your first opportunity to test those scripts before the production implementation. Take a few extra minutes to script each object with the fully defined production implementation method, and implement the changes to the unit test environment of the development server as a trial run. The unit test area of the development server is simply the dbo schema in the development database. For the purpose of keeping your database clean, it is usually a good idea to remove the copies of the objects belonging to the developers once you have implemented them as dbo objects. This is a good opportunity to ensure that all of your implementation scripts are complete and in order.
Its important that you create and run the implementation scripts, even if development and QA team members have to wait for you to finish. You may not get another chance to test your implementation scripts.
On rare occasions, or when you first begin to apply control to your environment, you may find that the development group has no record of what changes were made. There are a variety of reasons this can occur, none of them good. However, you are left with the task of identifying a few small changes in the vastness of the database. It is possible to do an item-by-item compare between two databases during off-hours.
The first rule of using scripts for database implementations is to script everything; ideally, nothing should be done through the graphical interface (and if you must do that, be sure to document that very carefully). The second is to provide supporting documentation. Your scripts should contain comments regarding what is happening in the next section, and any observations you made when you last ran it (that is, how long it took, how many rows were affected, and so on). Adding application roles? Changing database files? Linking servers? Updating rows? Script it all, so you can easily reproduce your steps.
To make this simpler, you can save the change scripts created in the Diagram pane in SQL Server Enterprise Manager. Rather than saving your changes to the Diagram, use the change script it creates. Take care to review these automatically generated scripts first and make appropriate changes. For example, if you add a column into the middle of a table, the change script will copy the data into a temp table, drop the original table, create the new one, populate the data and then rebuild the indexes. This might be a minor action on a small table, but if it is a table with millions of rows and many indexes, you could be unpleasantly surprised if you did not review the code first.
The third rule is to protect the scripts. Because they will be used for the implementation, the scripts should be treated like production objects. Keep them in a secured directory, or even better in a version control project that is accessible only to your group. Because of the obvious differences between a standard script and a change script (for example, the difference between a CREATE TABLE and ALTER TABLE script), you should maintain a separate folder for implementation scripts.
Organization is the key to managing scripts. Do not create a separate script file for each action, but instead for each logical step in the implementation. For example, if you number your script files, your script storage area might have a structure like this:
Although you do not really need the Readme script, this is a simple way to explain what the implementation was for, and store any other notes that might be necessary to remind, educate, or reassure the person running the scripts. Storing it as a SQL file makes it easily visible from SQL Query Analyzer, and also makes it clear that it relates to the other script files, rather than being a document or e-mail that relates to the project but not to the act of implementing it. Numbering the scripts organizes them into the order or hierarchy required during production implementation.
If you are doing a very large implementation, you may consider creating a batch script that executes numerous procedures by looping through them in osql. If you would like to try this, remember that it is important to build error handling into a batch process, so if the process encounters a problem during implementation, you will know which changes completed before the error occurred.
Source Storage Methods
The easiest way to start storing code is to put it in a protected folder, and use a simple directory structure. Another popular alternative is to use version control software. This has some immediate advantages over directory storage, but it requires a little more administration. It also allows you to easily grant read access to developers in a way already familiar to them, so they can feel reassured that they have the latest version of the code.
Version control software, however, is not integrated with SQL Server, and it can be time-consuming to maintain for a database, especially in regard to change scripts. You will need to be sure that everyone in your group understands how it works, and is committed to using. After all, the main weakness of any storage system for database scripts is that you have to manually update it.
Version control programs have features that allow you to label a set of scripts as part of one release, making it easy to get the most recent copy of all changes. You will need to define and rigidly uphold agreements on how to comment objects to be identified in a certain grouping for a release. You must also maintain change scripts separately from version scripts, since they are fundamentally different.
To step up to this level of source control, choose one target application at a time, and incorporate your archived scripts from your simple file directory as you go.
You can search the directories by going to the Windows taskbar, clicking Start, and then clicking Search. You can search either by filename or by keyword. Version control software will also allow you search code text or comments.
One way to organize your code is by application, like this:
Another way is to use the same basic structure to store information on your servers, if your company does not maintain a configuration management database. For example:
It is also useful to store related documents for applications, implementations, or server configurations. Store e-mails outlining varying opinions, any design documents, approvals from the QA person, or anything else. In six months when someone asks you what happened to the AddressLine7 where they were storing their customer comments, you will be able to give them an accurate answer. If it later becomes apparent that the design could have been better, you will have adequate documentation on any alternate designs that were suggested, and reasons (if any) that they were discarded.
The first opportunity to test your production implementation scripts occurs when you create them to change the database for the development environment. As soon as you have a stored procedure to implement to the "unit test" area of the development server, you should create the implementation script and save it to the related directory. Your script should contain code comments indicating the eventual target production server and database.
Expecting the Unexpected During Implementation
Even at this early date, you should be thinking ahead to what could go wrong during this implementation. This risk analysis is vital to the success of your process, and may change what is implemented or how it is done.
At minimum, you should make a list of what you think is likely to be a problem, and what would be good alternative actions in the event that things go awry. You must also make sure that when you have implementation problems of a non-fatal nature, you alter the implementation scripts as you use them, adding documentation or code that will help you during a later investigation into the problem. This provides two advantages: it allows you to be more prepared to discuss what went wrong, and it allows your group to learn from the implementation experience.
If you can predict problems by thinking through the possibilities, or based on problems you have encountered in the past, make a chart showing these risks and what you could do to correct the problem. Also list any risks that will prevent the implementation from being completed. No matter what kind of risks you identify, even if you cannot identify any, you must have a rollback strategy for your production implementation.
You should never plan a system change without also planning how to undo it. Your rollback strategy is simply a script for how to undo every change you have made, and put everything back the way it was before you started. Even if you have planned everything out correctly, the need for a rollback can still occur. If a call comes to roll the implementation, you may have a very short amount of time to undo all the changes.
If your database is small enough, you might simply elect to use a restore of the database as your method. If this is an option, you should consider using a marked transaction to coordinate rolling back to the first change in your implementation. However, in a larger database, or an implementation that includes changes to elements outside the table or code objects, you will need a more detailed plan, and possibly even the prewritten code to undo the change. These scripts may be tested during the first unit test or QA implementation.
Managing the QA Environment
The QA environment operates under different principles and priorities than the development environment. The goal of the QA person is to test the code in a controlled environment where there is no chance for interference from either development or operations staff. Without this isolation, the test is considered to be invalid, and must be redone. The QA person has final approval of all implementations, and often moderates cases in which the weighing of necessity versus risk is the deciding factor in going forward with an implementation.
It is in your best interests to develop a sense of teamwork with some people in the QA team. In fact, this is of mutual advantage. When they find a problem with a system, they may come to you for supporting documentation to explain why a system change should not be implemented. If you find that a development change that you consider to be risky is being pushed through to the QA team, you can accompany the QA implementation with an objective document outlining your concerns, the risks, and recommended mitigating action.
Another important function of the QA team is to perform milestone testing. All too often, testing is left to the last part of the project. At this point, the sum of all the changes must be tested simultaneously. Because it is all tested together, if one piece fails, the entire implementation must wait until the next release date. The reason for this is the same as the reason for the controlled environment: The concern of the QA team is that if one part of the code is removed from a set of changes, this may also have unexpected effects due to dependencies in other code.It is a good practice to have the QA representative involved in the unit tests and project milestone tests to identify any potential problems early, when implementation dependencies are still fluid.
Implementing in QA
When you transfer the changes to the QA environment, you have another chance to test your implementation scripts. If you do not have a controlled development environment, this will be your one and only chance to test and refine your scripts before implementing to production.
Always do a backup of the QA database before you start, and keep the backup until the production implementation is complete. This is a firm record of what was in QA before the test process began, and may become an essential part of testing if things do not go as planned. Keep in mind that if the testing fails, you will have to roll the changes out of QA. Make sure your rollback scripts are ready in advance, and view this as a chance to test your rollback scenario with a timer running.
Handing Off to QA
Another rule when running QA servers is that after you have implemented your change scripts, and you have informed the QA representatives that testing can begin, do not make even the smallest change without asking a QA representative for approval. Altering the implementation, even in the smallest detail, can invalidate the test. You should always consult the QA staff before working with their servers. It is a good practice to limit developer access to a QA server. The reason for this is that they are the experts on the system. To hand it off, they should have supplied documentation in sufficient detail so the QA team can handle the system without intervention. Any omissions found can then be addressed in this environment rather than in production.
Implementation the QA process described here is not difficult work, but it is meticulous. Indeed, the closer you get to the production environment, the more attention you should give to the details. Add notes into your scripts and related files regarding the time it took to load a change, or any unusual conditions. Make sure that you have verified the scripts to include, and the order in which they should be run.
The QA database servers are owned by the QA team and should be treated like a production environment. It is critical that you never make any changes to QA servers without getting approval from all involved QA personnel. Make sure that the QA environment emulates production as closely as possible. Any and all deviations from production should be carefully and completely documented, and communicated to the team.
Part of maintaining a QA environment includes keeping appropriate operations personnel informed as to the state of the project. Operations staff may raise issues now that will ease the transition to production support later.
QA generally does not require as fast a response time as either development or production, unless the QA team is in the middle of a testing phase. Generally, you do not need to run regular backups in QA either, unless restoring from production would be time-consuming or awkward to do on short notice. However, you should do as many backups as necessary to be able to restore to a point before each implementation. You may face some challenges regarding differences in server names, allowable logins, or hardware constraints. Enlist the help of representatives from QA and development to determine what variations are acceptable, and what negotiated fixes are affordable and supportable. You cannot, for example, agree to change the database name in every database code object, nor would QA personnel be happy with such a resolution. However, you might need to alter linked server code to reference a server that has a different name.
A single point of contact in each group should be in charge of change requests in QA, and everyone should know which people to ask before making a change. Everything should be documented, and this information should be available to the entire group. This information can be distributed by way of e-mail or a collaborative Web site. The key is accuracy, completeness, and accessibility.
Significance of the QA Phase
The QA phase is your chance to prove that you have performed due diligence for an implementation. Does it work? Does it scale? Does it affect any other system? Document all of this, record your concerns, and communicate all of these to everyone involved in the project, including Operations, Development, QA, Project Management, and, of course, Database Administration. The stability, accountability, and learning inherent in the process is the key to success.
Managing Production Implementations
Production implementations are usually viewed as a risky but necessary operation: change to the database. Care must be taken to make sure that system disruption is minimal, and that the corporate data is preserved at all costs. Failure on either of these counts will incur an immediate and tangible loss for the company.
Owning the Change: Production vs. DBA
Who should change the production database? This can be a topic of debate in a group of DBAs. The development DBA is closer to the change, and understands all the related parts; but ultimately the production DBA is responsible for the system.
In the end, it is a question of ownership. The person doing the change should have a good understanding both of the system in question and the change being performed. They must have the authority to take drastic corrective action if necessary without undue delay, and they must possess all the appropriate contacts among those in operations who can help them with related issues outside the DBA realm.
Determining an Implementation Window
If this is a 24 x 7 system, your project team will have to negotiate an implementation window that is acceptable to all groups affected by the change. In a truly mission critical system, even the shortest downtime may be unacceptable, so you may need to provide a read-only database as an alternative to access. In this case, the team should try to identify a small number of key users and re-point their database connections to a different server through ODBC.
To do this effectively, you will need to have a list of the IT contacts involved, and the user contacts both at management level and staff level. It is essential for the IT department to maintain credibility with the users by clearly communicating to them the nature of service interruption and the expected duration; any change to this situation must also be communicated. In addition, if you are supplying alternative access, interim support of the read-only system must also be provided, and the users and help desk personnel should be kept informed.
Planning the Implementation, Twice
You should compile an implementation plan and distribute it to everyone in IS who will be involved in or affected by the implementation. The plan is simply a list of the steps, who is responsible for each, the times that everything is expected to occur, and who to contact in order to initiate the next step. Be sure to include cell phone and pager numbers for everyone on site or on call during that timeframe.
You should make a secondary plan that accommodates these variables. Experience with your application and infrastructure will help you gauge the level of detail required. Note any showstoppers that would cancel the entire implementation and invoke the rollback plan.
As a group, the implementation team should create a backup plan. This is a little different than a simple rollback strategy for the database. If anyone's section fails, the group should have an overall plan for evaluating the situation, making a decision, and then proceeding or canceling the implementation.
When a Good Plan Comes Together
The beginning of the ownership of the system by operations begins with a release readiness review, also called a "go/no go" meeting, which determines whether this system or change to a system is ready for implementation. Although operations staff should be involved throughout the project, this will be the first meeting that is run by them, rather than by the development or QA teams.
The purpose of this meeting is to allow members from each team to indicate their final approval (or disapproval) and any issues they wish to raise. This is the last chance to alter the implementation schedule short of an all-out crisis. All groups should be prepared for the deployment. It is important that operations staff members indicate that they are sufficiently knowledgeable and prepared to take on operational support of the product or system.
Any votes of a no-go should be seriously considered; if no resolution can be reached by those present, the meeting should be adjourned with a no-go status until resolution is reached or the objection is overridden by senior IT staff. In any case, the objection, the reasons for it, and any risks that are brought up should be recorded for future reference.
The agenda for this meeting should include:
The readiness of the release itself.
Alterations to the physical environment.
The preparedness of the operations staff and processes.
The installation plan.
The contingency plan.
Potential impacts on other systems.
If you have a formal change control process that requires registration for implementation, be sure you have followed their procedures. It may be your responsibility to file a change request for the database server. If there is no formal change process in your operations department, you will need to send an e-mail announcement to people in operations who may be affected by the change or any difficulties encountered during or after the implementation. This would include, at minimum, the support staff, the help desk technicians, and network and security administration. There should not be anyone who can reasonably claim they should have been informed and were not.
Coordinating Application Changes
The first step of your implementation should always be a backup of the current system. Once this is complete and verified, you are ready to begin. But the DBA group is rarely the lead on the implementation. Normally, this person is going to be someone from the project team who has responsibility for deployment. You should call your contact person and inform them that backups are complete and the database team is ready, and then await their signal to begin changing the system.
Support During Implementation
Except in the very smallest of shops, there should always be a second DBA on standby for an implementation. In a large implementation, you may have several DBAs involved in the deployment of the system, but you should still have a standby in the event that someone becomes ill, or in case of related or unrelated system failures. Remember, even if another production server goes down during an implementation, the deployment must continue as planned. Every reasonable effort should be made to uphold the DBA end of the contract.
In addition to supporting an implementation, you should also be sure that people are in place to support you. Be sure you have handy the contact numbers of the on-call server room technicians, network administrators, or security administrators whose help you may need during the implementation.
Sometimes, in spite of all your plans, something truly unforeseen will occur during a production implementation. If you are lucky it will be something minor. But, regardless of what it is, you should make a note of it in your implementation script. Whatever code you had to run, whatever action you had to perform: everything must be recorded. Do not wait until the crisis has passed to start making notes. The sequence of events and steps taken may later be significant, either for support calls you end up making, for later analysis, or for similar future implementations. Remember that meticulous accuracy is more important than anything else at this stage. Due diligence requires that even if you make a mistake, you record that too. Your thoroughness now might save someone else making the same mistake later on, and will help the group learn as a whole. Not just about technical issues, but about teamwork as well. Good teamwork is a vital part of the success of an implementation team, which crosses many departments in IT, and it relies on the irreproachable accuracy of the information provided by team members.
Remember that Rollback Plan?
In the event of all out failure of the deployment, you will have to put your rollback plan into action. This rarely happens, but just like paying for insurance, you will appreciate the value of a good rollback plan the first time you have to use it. While you are running your rollback scripts or your database restore, make some notes in an e-mail explaining what caused the implementation to be called off. It might have had nothing to do with the database at all, but this should still be noted and the appropriate people informed as a matter of responsibility. Remember to inform all of the people in IS who were originally notified that the deployment would occur. Unless you have a direct link of communications with the user community, leave that contact up to the project managers or to the change control group.
Sounding the All Clear
After you have completed your portion of the implementation, immediately notify your contact person, so the next part of the implementation can proceed. Your task now is to stand by in case the group needs something, most notably a rollback. If the implementation completes successfully, the testers test the application in the production environment to be sure that everything is indeed working, and the users are informed that they can resume working in the system. You should make sure that the contact person has an action item to call you to inform you that you can stand down. It is a good idea to know what time they plan to be finished, and to make it generally known what time you will give up waiting for your phone call.
Business As Usual: Implementation Review
After implementation, a meeting should take place to evaluate the success (or failure) of the project. Everything including the project plan, the design, the arguments, the solutions, the crises, and the final outcome will be examined. The point of this process is to learn from the events that occurred; if you have things to add, take your documentation with you to share. And remember to share your positive remarks, not just the negative ones.
Once this is complete, the development cycle for that application begins again in the visioning of the next release.
The processes described here should give you a good foundation to begin structuring your own environment for better manageability. In addition to the development cycle and basic operations covering change control, several broad concepts are touched on here that you should further research before implementing on a grand scale: source and version control, data modeling, and the Microsoft Solution Frameworks (MSF) and Microsoft Operations Frameworks (MOF) guidelines. If you want to incorporate all of these ideas into your mode of operations, keep in mind that this will be a gradual process requiring sponsorship by your management. The end result is well worth the effort.
Microsoft Operations Framework: http://www.microsoft.com/mof/
Planning, Deploying, and Managing Highly Available Solutions at Microsoft TechNet: http://www.microsoft.com/technet/prodtechnol/sql/default.mspx
Microsoft Solutions Framework: http://www.microsoft.com/technet/itsolutions/msf/default.mspx
IT Infrastructure Library, best practices: http://www.itil.co.uk/
Microsoft SQL Server 2000 Administrator's Companion, Chapter 3 "Roles and Responsibilities of the Microsoft SQL Server DBA"
For samples that may help in managing database change, see the SQL Server 2000 Resource Kit CD-ROM in the folder, \ToolsAndSamples\DBManagement.