Unveiling Discrepancies: More Ways to Compare Synonyms in SQL Compare

When working with database development and deployment, especially across different environments, identifying even subtle discrepancies is crucial. Imagine running comparisons and missing key differences simply because of how your comparison tool is configured. This was the exact scenario encountered by a user during a recent Proof of Concept (PoC). They discovered that when changing databases, synonyms were being overlooked by SQL Compare. Surprisingly, the solution to reveal these hidden variations is quite straightforward.

This article delves into how to ensure SQL Compare accurately detects synonym differences, offering a deeper understanding of comparison nuances. If you’re already leveraging the power of SQL Compare from Redgate Software, consider this a vital tip to enhance your workflow. For those unfamiliar, SQL Compare is an indispensable tool for database professionals, and you can download an evaluation to experience its capabilities firsthand.

The Scenario: Spotting the Subtle Synonym Shift

Consider a common development practice where synonyms are used to abstract database object locations. In a development environment, you might have feature-specific databases like Sales_Feature and Finance_Feature. Within Sales_Feature, a synonym might point to an object residing in Finance_Feature. This setup often functions smoothly in development.

However, upon transitioning to production, the database landscape typically shifts. You might have production databases named simply Sales and Finance. The synonym, intended to point to the Finance database in production, now needs to reflect this change, even if the underlying code originates from development databases. While addressing the broader development lifecycle challenges in such scenarios is important, our immediate focus is on accurately detecting these synonym modifications.

Let’s illustrate this with a practical example using “Dev” and “Prod” databases on the same instance. We’ll create a synonym in one database pointing to an object in another. This mimics a development-to-QA or development-to-production scenario.

First, in the SimpleTalk_1_Dev database, we create a synonym:

USE [SimpleTalk_1_Dev]
GO
CREATE SYNONYM dbo.MyTable FOR Compare1.dbo.MyTable
GO

Next, we create a similar synonym in the SimpleTalk_5_Prod database, but crucially, we point it to a different location:

USE [SimpleTalk_5_Prod]
GO
CREATE SYNONYM dbo.MyTable FOR Compare2.dbo.MyTable
GO

Now, if you run SQL Compare to compare these two databases with default settings, you might be surprised by the result: No differences are reported for the synonyms. Despite the synonyms clearly pointing to distinct objects, SQL Compare, in its default configuration, doesn’t highlight this variation.

The Solution: Fine-Tuning Your Comparison for Synonym Accuracy

The reason SQL Compare initially overlooks these synonym differences lies in its intelligent default settings. It’s designed with the understanding that when moving databases from development to production, synonyms might naturally point to different instances or databases. By default, SQL Compare prioritizes identifying other types of discrepancies, assuming variations in database or server names within synonyms are intentional and less critical to flag as differences.

To unveil these synonym variations, we need to adjust the project options in SQL Compare. By clicking “Edit project” and navigating to the “Options” tab, you’ll find a setting labeled “Ignore database and server name in synonyms.” This option is checked by default.

2022-03-30 16_03_33-New project_

Unchecking this “Ignore database and server name in synonyms” option and rerunning the comparison reveals the previously hidden synonym difference. SQL Compare now accurately identifies the variation between the synonyms in SimpleTalk_1_Dev and SimpleTalk_5_Prod.

This adjustment is equally effective when dealing with different instances but identical database names, a scenario frequently encountered in database deployments.

This same option to refine synonym comparisons extends across Redgate’s suite of database lifecycle management tools. If you utilize SQL Change Automation, SQL Source Control, Flyway Desktop, or any of the automation components within Redgate Deploy, this setting ensures consistent and accurate synonym difference detection throughout your database DevOps pipeline. If you’ve experienced seemingly missed synonym deployments, verifying this option is a crucial troubleshooting step.

For organizations seeking to streamline database development and deployment through DevOps practices, exploring dedicated tools is essential. Download a trial of Redgate Deploy to discover how it can enhance your database lifecycle management.