One interesting question which an attendee asked me during my last DevOps presentation was around how to handle scenarios when you need to execute a script manually in Production and which approach will better suit if you have to perform such actions more frequently – State based or Migration based?

First things first, I would never recommend you to make any database changes (primarily schema or reference data) in Production environment outside of the normal automated delivery process – since that’s going to be a perfect recipe for disaster!

Once you have your deployments automated, there should be no reason for executing manual scripts directly in Production.

What do you mean by Database Drifts?

Any change to the database schema or reference data that was made directly in the database environment – outside of the normal automated delivery pipeline – can be termed as Database Drifts.

Even though database drifts needs to be avoided all together, it still happens in real world projects due to multiple reasons. This is not a sign of a mature Database delivery pipeline.

Database Drifts in State based approach

Let us understand how things are going to be if you try to handle database drift scenarios with a State based approach using SQL Server Data Tools.

In State based approach, the database code stored in the source control is the ultimate source of truth. A database drift causes the source control and the destination database to be out of sync. However any database drifts will be automatically reverted back during the very next database deployment since the changes are not present in the source control.

If you want to identify what scripts ran against an environment, you can use basic capabilities of SQL Server Data Tools like Schema and Data Comparison —

Schema Comparison

SSDT provides the ability to identify differences between any 2 database models – which can be a combination of Offline Database Projects, Connected Database or Database Snapshot file.

SSDT generates a difference script which can be executed against the target so that it matches the state of the source database. As part of the Schema Comparison functionality, it displays the list of objects which are different between the source and target database, and you have the option to include/exclude the objects before generating the change script.

Data Comparison

SSDT lets you analyze the data differences between the source and target databases and generate a change script to update the target database to match with the source.

Database Drifts in Migration based approach

It is more problematic to handle database drift scenarios in Migration based approach than the State based approach.

Since the database is the source of truth in the Migration based approach, and you have made direct changes to the database itself – you wont be able to track or validate the changes since you do not have the current state of the database in the source control. Keep in mind making direct changes to the database in Migration based approach, deletes the original state and you cannot revert back the changes without knowing who made the changes and why.

During a future deployment, if there is a conflict between the drift and the new migration then the migration will fail and you can manually look at the issue and review the scenario. However if there is no conflict , you wont be able to distinguish the manual changes made and the new migration will be deployed against the database.

As a good database delivery practice, make sure that all migrations are idempotent before executing any script – meaning to ensure that the script has not been executed already.

For both State based and Migration based, if you want to execute any adhoc sql script against a particular environment, make sure that the operation is an idempotent one – meaning that running the script more than once has no additional impact. You can achieve it using ‘IF EXISTS‘ statement.

For scenarios when you want to execute a script in selective environments only, having a conditional check for environments is also a good safety net to have —

IF (@@SERVERNAME = ‘PRODUCTION DB’ AND CONVERT (Date, ‘2018-04-19’)=CONVERT(DATE,getdate()))
BEGIN
     DELETE FROM Employee
     WHERE [EmployeeId] = ‘9999’
END

If you have any questions regarding Database Drifts and how to handle them, please put a comment below and I would be happy to assist.

Categories: DevOps, SQL Server