How to check whether you can safely deploy to production database

The time when source control systems were used only for code development is gone. Today, database versioning is quite common, even in small companies.

As there are many different source control systems, with different features and options, there are no strictly defined rules how to use them. Each company can create its own standards and rules for using a source control system for database versioning, following the best practices and recommendations.

Depending on which source control system is used, and what the usage schema and environment flow are, the goals of using source control systems are safety, easier team-based database development, and deployment automation.

In a multi-environment system, each environment represents a state through which a database must pass, until it reaches its final destination – production. For example, development of new features and fixing of existing issues is carried out in Development, then they are tested in Integration (or Testing) and Production is where your live databases are.

One of the common rules is that all deployments come from source control. In other words, all database changes must go into source control first and then be deployed to production.

Does it always have to be so?

It should, but unfortunately, it isn’t. Despite all the benefits that a source control system brings, the key factor is still human. If there’s no discipline, developers can skip a step or two, ignore a rule, or do something that doesn’t comply with the standards. A developer in a time crunch might have applied a change directly to production, skipping both the development and testing environments, and never adding the change to source control.

The good news is – in a versioned environment, this can be easily fixed. Furthermore, possible problems caused by this undisciplined approach can be prevented by checking the production database before deployment.

No matter how small the change is, and how small the difference between the production and the source control version is – it’s still a difference. Even just changing a column data type from int to e.g. nvarchar(50) in production, makes a difference that can cause headaches when deploying a new version from source control. Next time the new version is deployed to production, either scheduled, or on demand, deployment can fail.

If lucky, all that will happen is that the latest version cannot be deployed. In a worse scenario, even some data can be lost.

How can be made sure there were no direct modifications of your production database?

Before the deployment starts, check whether there are any differences between the production database and the source control system version that was deployed last. Another option is to compare the live database to a full database backup created immediately after the last deployment if they are available.

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects in live databases, full and differential database backups, databases versioned in source control systems, script folders and database snapshots. It generates comprehensive reports for the differences and can automate the synchronization process.

1. Start ApexSQL Diff
2. Once the New project window is shown set the following in the Data sources tab:
   • Select Database as a source type
   • Select the production database as the source
   • Select Backup as a destination type
   • Select the full database backup taken immediately after the last deployment as the destination

   

3. Click the Compare button
4. To switch how to view differences, on the View tab, click one of the buttons under the Grouping section. By default, the Group by difference type option is selected:

   

5. All objects in the Results grid will be grouped as equal, different, in source or in destination only.
   If there are no differences, all objects will be shown as equal.

   If there are any different objects, check out their differences in the Script difference view panel:

   

6. To create a synchronization script, check the objects in the Results grid that should be synchronized
7. Click the Synchronize button from the Home tab to initiate the Synchronization wizard:

   

8. In the first step of the Synchronization wizard, the synchronization direction will be shown, while in the second step, any dependent objects will be automatically checked and added in the synchronization script:

   

9. Under the Output options step, leave the Create a synchronization script action and choose the Save script to a file option:

   

10. In the last step, review the Summary and warnings and click the Create script button to finish the process:

    

    Get back to developers, investigate the differences, and decide the best course of action in this specific situation. The generated script can be used in desired sandbox to synchronize the testing database with the production one.

    To avoid deployment problems, caused by changes in production that didn’t come from source control, use SQL server comparison tool ApexSQL Diff. If there are any differences detected in production, it’s not recommended to make any further changes to the production database without testing them first.