In order to maintain a stable and protected production system, many companies, including ours, have implemented a multi-tier development, quality assurance (QA), staging, and production environment. This article will discuss the processes and tools we use to move data and structures from development to production, as well as the challenges we have encountered along the way.

The Development Environment

The development environment is often referred to as the “Wild West” because developers have the freedom to create and change procedures and functions as needed. However, to ensure a controlled and organized development process, we collect all structural changes and publish them once a week to the development environment. This allows developers to address any issues caused by database changes and ensures that only unit-tested procedures are checked into version control.

We use Embarcadero ERStudio to maintain our database model and Visual Source Safe (VSS) for version control of our SQL code. Source code control and versioning have proven invaluable in maintaining a reliable and consistent development process.

The QA Environment

Once unit testing is complete in the development environment, we perform a build of the QA environment. We use Embarcadero ChangeManager to migrate structures from development to QA. This tool accurately captures differences in structures, although sometimes we need to generate differential scripts multiple times to ensure all changes are captured.

For migrating lookup table data, we use RedGate’s Data Compare utility. This tool allows us to move data between environments accurately, although occasional script generation may be required to ensure all data is migrated correctly.

By using these tools, we have established a repeatable and safe process that allows us to perform multiple QA builds per week as we move towards a release.

The Staging Environment

The staging environment serves as a backup cluster in case of a catastrophic loss of production. It is also where we eliminate any remaining issues before moving into production. We restore the most recent backup of the production environment on the staging system and generate scripts using Embarcadero ChangeManager and RedGate Data Compare. Unlike in QA, we do not run the compare multiple times until perfect. Instead, we edit the script to address any failures and test it against the restored production database on staging.

This process is repeated until the script, with all structural and data changes, can be run without error in the staging environment. Another compare is then performed to ensure all changes have been successfully migrated.

Releasing to Production

After thorough testing in the staging environment, we are ready to release to production. The production release process is relatively simple. We run the script that has been tested against staging as part of the code release process. Before running the script, we ensure that all user connections are bled off and isolate the database. We also perform a backup for added security.

If the script runs without errors and passes a consistency check, we have successfully updated the production system. However, if any errors occur during the production release, we are prepared to apply the backups to ensure the continuity of the production system.

Lessons Learned

Throughout this process, we have encountered various challenges and learned valuable lessons. The weekly development build can be both a blessing and a curse, depending on the number of changes. Communication between development teams and the DBA team is crucial to prevent surprises caused by dependencies on other teams.

The QA builds, due to their nature and frequency, can be problematic. Ensuring incremental changes are accurately implemented without data loss requires careful attention. Delays in the QA build process can impact the QA team’s productivity and slow down development.

While the staging build is relatively painless, the time required to get everything right can affect schedules. Project managers need to allocate sufficient time for the build process to ensure its success.

One of the biggest wins in this entire process has been the documentation and adherence to the documented process. By identifying and addressing shortcomings, we have been able to improve the process and incorporate other DBAs and development teams more seamlessly. The success of this development effort has even led to the migration of these processes to other projects.

In conclusion, building a multi-tier production release environment in SQL Server requires careful planning, the use of appropriate tools, and effective communication between teams. By following a structured process and continuously improving it, companies can ensure a stable and protected production system.

Remember, it’s always better to be prepared and take extra precautions, such as performing additional backups, to safeguard the production environment.