I was running a PoC for a customer and they noticed that synonyms were being missed when they changed the database being used. It was surprising to me, but it turns out the solution is simple.
This post explains how to fix this.
The Scenario
Imagine that I have a synonym in my database. In the development environment, I have this pointed to one object, but in production, it will be pointed to a different object. The name of the object is likely the same, but the server (instance) name or the database name might be changed. I see this all the time where I might have these databases in my dev environment:
- Sales_Feature
- Finance_Feature
In Sales_Feature, I have a synonym pointed to Finance_Feature, which works most of the time.
When I get to production, I have “Sales” and “Finance” databases. I need the synonym pointed to Finance, even though I might take code from these dev databases and commit to version control. There’s certainly a development challenge here in this scenario, but let’s ignore this. Let’s just assume we want to determine that we’ve actually made a change here.
In my scenario, I’m going to look at Dev and Prod, though on the same instance. I’ll create a synonym in one database that points to a different database. Here’s a quick set of test code:
USE [SimpleTalk_1_Dev] GO CREATE SYNONYM dbo.MyTable FOR Compare1.dbo.MyTable GO
Now, I’ll create a similar synonym in another database, but pointing to a different location. Think dev and QA here.
USE [SimpleTalk_5_Prod] GO CREATE SYNONYM dbo.MyTable FOR Compare2.dbo.MyTable GO
If I run SQL Compare, the differences are shown here:
There is no difference. Clearly the synonyms are different, but why don’t I see them?
The Solution
It turns out that SQL Compare assumes you might have these pointed to different instances or databases when moving to production from development. It assumes you only care if there is some other difference.
If I edit the project options, I can see this. When I click the “Edit project” and go to the Options tab, I can scroll down. I’ll see the “Ignore database and server name in synonyms” option checked by default.
If I uncheck this and then run the compare again, I see this:
Now I see a difference in the two databases. This also works if you have two different instances and the same database names, which is a more common occurrence.
This same option applies in all our products that use the SQL Compare engine, so SQL Change Automation, SQL Source Control, Flyway Desktop, and the various automation components, all of which are in the Redgate Deploy. If you find your synonyms aren’t being deployed, make sure you have this option unchecked.
If you don’t have any tools to help manage database development and deployment in a DevOps way, download a trial.