Voice of the DBA

Tag: Redgate

Search and Replace Three Part Names with SQL Compare

A customer had an interesting challenge in their codebase recently, and I thought this would make a good post. This involves SQL Compare and three part names.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

The Scenario

A customer was using SQL Compare and they wanted to update the database name in a script to point to a new database. They had objects in a development database pointing to another development database. What they asked was: “How can we search and replace across all objects to change db1.dbo.mytable to db2.dbo.mytable?”

There are a couple ways that you can attack this problem, but I’ll show you what I think works best.

Ensuring Three Part Names Appear

There is a switch in a SQL Compare that allows you to include or exclude three part naming in the scripts. This is the switch that says “Ignore Server and Database Names in Synonyms”.

I don’t have that checked, and when I look at my comparison, I see this. There are two synonyms in these database, which you can see in the image, point to different targets.

If I have this option checked, as shown here:

Then nothing appears.

Make sure you don’t have this option checked. I’ll fix that first.

Next, I’ll add a couple more synonyms to show a few differences and then re-run the compare. Now I have 3 objects, 2 of which are different, one doesn’t exist in the comparison database.

Generate the Script

I want to generate a script. I’ll do that by pressing the “deploy” button. I’ll change the option from deploy to generate script. This will open in SSMS.

I click the “Open in editor” button and SQL Compare generates the script and opens SSMS. Warning, it opens a new version of SSMS, not the existing one I have open. Grrr.

I see my script here, which is good.

I can search and replace now, looking to change the database name. Hard to see in the image below, but I’m searching for this:

FOR [AdventureWorks2017].

and replacing it with:

FOR [sandbox].

This will set the synonyms to the new target in the dev environment. In this case, I’m changing from AdventureWorks2017 to Sandbox.

The last thing to do if this script is to set up a new database is to remove the synonym drop statements. We can’t alter synonyms, so we need to drop them. That will be an error in this script if the synonyms don’t exist. Since this runs as a transaction, the entire thing will fail.

This shows a simple way to help create a script that can be used to refresh a dev environment with the proper schema settings when synonyms or other three part naming is in use.

April 25, 2022
Ensuring SQL Compare Checks Synonyms
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.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

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.
April 13, 2022
Flyway Mistakes
I have been doing some testing with Redgate’s Flyway Desktop as a new way of managing code for databases. However, just like Git, I appreciate clients, but I want to know how the CLI (command line interface) works. I spent time learning git add, git push, git checkout and more. Now I have more comfort understanding how SourceTree or GitKraken work.

I wanted to do the same thing with Flyway, just to be sure that I know what the options, switches, and behavior for Flyway operations would be.

The Scenario

I had an existing database, and I wanted to play around with adding this to a DevOps flow. I was looking for a basic experiment, and decided to create a new repo. I copied the default flyway.conf file into this folder and changed it.

The only thing I did was alter the Flyway conf file in my folder to work with SQL Server. I copied the connection string into the flyway.url parameter and set it as follows:
```
flyway.url=jdbc:sqlserver://aristotle:1433;instanceName=SQL2017;databaseName=AdventureWorks2017;integratedSecurity=true
```
When I ran the info command, it failed.

When I ran the same command with a different database, it worked:

I was highly confused. I tried a number of different databases, and some of them worked, not I couldn’t see a pattern.

I checked a number of things, including the database owners, a few of which I changed. I thought it might be some permissions and dropped my sysadmin account and added it back.

I tried connecting with SSMS and with sqlcmd. Both of those tools seemed to work.

I was really stumped.

A Small Conflict

Finally, after a bit of back and forth with a few developers, someone noted that I shouldn’t need the port included in the string. Sure enough, when I removed it, things started working.

Apparently, the JDBC documentation notes the issue. I kept looking at Flyway docs, but they just pass things along to the JDBC driver from the various parameters and environment variables.

There is a note that says provide the port number to stop a round trip to the browser to determine the port number for a named instance. If the port number and name are included, the port takes precedence.

I have two instances, some of which have the same databases on each. The databases that worked were on a different instance (which responds to 1433). The ones that didn’t, weren’t on that instance. I kept examining the \SQL2017 instance, but that wasn’t the one I was logging into with my string.

A silly mistake, but a good one to note. The port is a higher priority than the instance in a Java connection string.

I can’t find a priority in the docs for OLEDB or the native client, but they do all say the address takes precedence over the address parameter.
March 21, 2022
A New Command Palette in SQL Prompt

I’ve gotten used to using the Command Palette in VS Code/ADS/Sublime Text regularly to find some setting or change something. What felt strange at first has become a habit for me. I actually like having this one place to search and get things done.

It’s now in SQL Prompt.

If I press ALT+S, I see this in SSMS. A large command prompt with lots of entries.

This popup lets me do a few things. If I start typing, I can get to most any object or SQL Prompt command from here. It’s like a global search for all things Prompt.

As an example, I can highlight an object and then ALT+S, type “object ex” and I’ll see this:

If I select the “Select in Object Explorer” (or click), the OE will navigate to that item.

Simple, and I could get there in other ways, but having all this in one searchable place, rather than right clicking and looking through a menu, or looking at the top menu bar, is easy. I even see the shortcut in the command palette (CTRL+F12) if I want to memorize it.

I can also use this for commands like insert semicolons, which I can never remember the shortcut for.

I can look for objects, commands, or options, filtering my results with the selection at the top. Or I can look in all areas. I can even look at the see the status of options in the palette, as shown here.

I have some things memorized, and the ALT+S isn’t quite in my muscle memory, but I am finding that I use it when I don’t know where something is located, or can’t remember a shortcut.

It’s a new feature, and the team is looking for feedback, so give it a try and let them know what you think.

If you don’t have SQL Prompt for SSMS and SQL Server, download an eval today and give it a try.

February 28, 2022