Voice of the DBA

Recently a customer was trying to sync up production and development. They’d somewhat lost control of both environments and wanted to build a plan of how to sync them. This post covers a process I suggested to them to tackle this challenge.

The Scenario

Production is running. It’s got all sorts of objects, and most of them are probably valid. You’d be surprised how often I find broken code in production. Anyway, way want to ensure developers have visibility into what production looks like.

However.

Development is also in progress. They have lots of changes they’ve made in development, some of which need to be pushed to prod, some of which are in flight. They don’t want to just sync prod->dev or dev->prod.

How they get things deployed and keep track of work isn’t something I asked. I’m not judging; I know most people are just trying to get through the day.

So they wanted to know what’s different and mark those objects for someone to work on.

The Solution

This is just one way to solve the problem. My approach here is to create a picklist of work that can be assigned to others. Since the customer has SQL Compare and is familiar, I used this tool to help me.

First, we back up development.

Next, I created a project that points from prod->dev. Worse case, we break development and restore it. We certainly don’t want to “break” production with too many mouse clicks.

For the sake of this demonstration, let’s say these are the databases:

• Production:  way0utwest_prod
• Development: way0utwest

I set this up in SQL Compare, ensuring I have things pointing the right direction.

When I compare the databases, I see changes that are in all states. Some only in one or the other, some different, some the same. At this point, I don’t really care about counts or what’s different, so I don’t need to expand this list.

Now I select all objects and click “Deploy” at the top, which takes me to the next screen. Here I’ll create the script. Click Next.

At the next screen, I see the deployment script, but I’ll click the Summary tab. This gives me a list of what changes would be made to dev. Keep in mind we are trying to clean up development.

The summary gives me a list of the change at a high level. What operations on which objects. You can see I have a number of objects in the image above. In the upper right side of the image there is also a “Copy” button. Click this.

Now paste this into any editor. I’ll use Notepad. Now I have a list of changes needed for each object.

This is the picklist of work. Someone needs to go through this. You could paste this into Teams/Slack or anywhere, but really, this is the gross list of things to go through.

From here, they can take each item and create a work ticket for this. A work item in Azure DevOps Boards, a ticket in Jira, an entry on a Kanban board, it doesn’t matter. Use your work system to create these work items and then assign to people.

If necessary, repeat this process as you move forward until you have a list of things that ensure all objects from prod are in development. You also then delete things in dev that you will not deploy, and leave those items that are in flight.

You could also select the various categories in SQL Compare (different in both, only in one) above and generate separate pick lists for the types of work needed.

Ultimately the developers will know how to resolve these issues, so let them do the work. Just organize it for them.