Tag: Redgate

Ignoring System Generated Key Names in SQL Compare
Recently I ran into a customer that was having issues deploying code from their development system to their production system. The issue was that they often found that the deployment script wanted to drop and recreate their keys. This wasn’t something they wanted, and kept feeling like they needed to edit their deployment script to remove these lines.

This post shows how to avoid having the SQL Compare script generate these changes and ignore the constraint issue.

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 you have tables in two databases, dev and prod. In this case, I have a SaleHeader and SaleDetail in both the Compare1 and Compare2 databases.

The code for these tables looks like this:
```
CREATE TABLE SaleHeader
( SaleID INT NOT NULL PRIMARY KEY
, SaleDate DATE
, SaleAmount NUMERIC(10,2)
)
GO
CREATE TABLE SaleDetail
( SaleDetailID INT NOT NULL PRIMARY KEY
, SaleID int
, LineItem smallint
, ProductID INT
, Quantity INT
, UnitPrice NUMERIC(10,2)
)
GO
```
Making Changes

When the customer makes a change, such as adding a column to the SaleHeader table, they run SQL Compare. This results in something like this:

I see both tables here as different, even though I only added a column to the SaleHeader table. You might think there is a FK or something else, but there isn’t. I only ran this code:
```
ALTER TABLE dbo.SaleHeader ADD SalesPersonID INT
```
Why do I see both tables? Let’s click on SaleHeader. I now see the details of the changes. In addition to my column, there is a difference with the constraint. That’s the Primary Key for this table.

If I look at the SaleDetail table, I see the only difference is the constraint.

What has happened is Microsoft generated a system-generated name for the Primary Key because I didn’t specify one. I can specify one, and should, but if I don’t, Microsoft handles this.

Since these are different objects, with different names, SQL Compare flags this. If I try to deploy my new column, this is the script generated. Note, I’ve only showed the change part, not all the setup.
```
PRINT N'Dropping constraints from [dbo].[SaleHeader]'
GO
ALTER TABLE [dbo].[SaleHeader] DROP CONSTRAINT [PK__SaleHead__1EE3C41F8D88FA16]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[SaleHeader]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[SaleHeader] ADD
[SalesPersonID] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK__SaleHead__1EE3C41FDBE16A10] on [dbo].[SaleHeader]'
GO
ALTER TABLE [dbo].[SaleHeader] ADD CONSTRAINT [PK__SaleHead__1EE3C41FDBE16A10] PRIMARY KEY CLUSTERED  ([SaleID])
GO
```
This code shows that
the constraint is dropped, the column added, and then the constraint rebuilt. Again, with the same system-generated name. That means every script I generate will do this when this table is changed.

The customer was editing this script and removing the ALTER TABLE DROP CONSTRAINT and the ALTER TABLE ADD CONSTRAINT lines (and comments). A pain, and a place where a human can make a mistake.

Let’s fix this.

Change the Options

If I click “Edit Project”, I have a way to flip a switch and prevent this from happening.

After clicking the button, I see the Data Sources tab, where I picked my databases, but in the upper right, I can click the Options item.

This gives me a lot of checkboxes. If I scroll down, I’ll find an “Ignore” section. In here there is an Ignore constraint and index names. I need to check this.

The help on the right side tells me that I can ignore the system generated names. This doesn’t quite work smoothly for scripts folders, as you can read.

Once I click the checkbox and re-compare the databases, the view is similar, though the SaleDetail table doesn’t appear in the list of different objects.

If I generate the script, however, it looks cleaner. No constraint changes.

This is what the client wants.

Summary

Ultimately, you don’t want to have system generated named constraints. Too easy to make mistakes, or someone even manually checking items might think there is a problem. One thing that I recommend is that you slowly rename these constraints to a standard that fits your environment.

SQL Compare has lots of options, and this is just one to make this fit your environment. If you have items that you need customized between certain databases, look through the list of options for something that helps you. Or keep following these tips on the blog.

For this option, if you need to add this in the command line, then use the /icn option.

SQL Compare is amazing and if you need to check what might be changing and happening between your databases, give it a try today.
August 9, 2021

Exporting Lists in SQL Multi Script

SQL Multi Script is a lesser known tool from Redgate Software that is designed to easily allow you to run scripts against many server instances with one click of a button. It’s similar to a Central Management Server, but it returns results a little cleaner, and has a few extra features that make things run better. I have a number of customers using this to deploy to many instances, both for database changes and instance config updates.

Recently a customer wanted to share their distribution list with another person on their team. They asked how to do this, and it turns out to be very simple.

We’ve built import and export into the tool. I’ll look at how you can do this.

First, open SQL Multi Script and then pick the tools menu. Here you will see the export and import items, as shown below.

If I pick export, I get a dialog that shows the distribution lists I’ve created.

I can pick all, one, or a group of them. Note that all of these are exported into a single file, which is useful if I can want to share all my lists with a colleague.

Once I click “Export” I have to choose a file name and location from a standard Windows Explorer dialog. I always think about how I’d sort these in a large list, so I like to pick something that will make sense in a month. In this case, the app and then the list..

Once I click OK, the file is created, and I can see it in the file system. Here I’ll open it in Sublime Text to see what it looks like.

The file is XML, which isn’t ideal, but it it easy to read. I have my XML file below, with all instances using Windows Authentication. If I had used SQL Auth somewhere, this would end up with an encrypted password, which I assume SQL Multi Script can import and decrypt. Here is my file:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>

<!--

SQL Multi Script

SQL Multi Script

Version:1.5.4.1390-->

<databaseListsFile version="1" type="databaseListsFile">
   <databaseLists type="List_databaseList" version="1">
     <value version="2" type="databaseList">
       <name>MixedAuthList</name>
       <databases type="BindingList_database" version="1">
         <value version="6" type="database">
           <name>AdventofCode</name>
           <server>(local)</server>
           <integratedSecurity>True</integratedSecurity>
           <connectionTimeout>15</connectionTimeout>
           <protocol>-1</protocol>
           <packetSize>4096</packetSize>
           <encrypted>False</encrypted>
           <selected>True</selected>
           <cserver>ARISTOTLE</cserver>
           <readonly>False</readonly>
         </value>
         <value version="6" type="database">
           <name>dlm_3_qa</name>
           <server>LOCALHOST</server>
           <integratedSecurity>False</integratedSecurity>
           <username>Joe_Admin</username>
           <savePassword>True</savePassword>
           <password encrypted="1">25eJUgEGChcaG13SEpATBg==</password>
           <connectionTimeout>15</connectionTimeout>
           <protocol>-1</protocol>
           <packetSize>4096</packetSize>
           <encrypted>False</encrypted>
           <selected>True</selected>
           <cserver>ARISTOTLE</cserver>
           <readonly>False</readonly>
         </value>
         <value version="6" type="database">
           <name>dlm_2_integration</name>
           <server>LOCALHOST</server>
           <integratedSecurity>False</integratedSecurity>
           <username>Joe_Admin</username>
           <savePassword>True</savePassword>
           <password encrypted="1">25eJUgEGChcaG13SEpATBg==</password>
           <connectionTimeout>15</connectionTimeout>
           <protocol>-1</protocol>
           <packetSize>4096</packetSize>
           <encrypted>False</encrypted>
           <selected>True</selected>
           <cserver>ARISTOTLE</cserver>
           <readonly>False</readonly>
         </value>
       </databases>
       <guid>4e255407-343c-4b88-a54a-4e981ad2beac</guid>
     </value>
   </databaseLists>

</databaseListsFile>

This is a useful feature for sharing lists in a team. It’s a little clunky, but it works well.

If you’ve never given this a try, download SQL Multi Script today, or get the Toolbelt and give it an eval. You might find it’s quite handy.

July 19, 2021

Creating a Distribution List in SQL Multi Script

SQL Multi Script is a lesser known tool from Redgate Software that is designed to easily allow you to run scripts against many server instances with one click of a button. It’s similar to a Central Management Server, but it returns results a little cleaner, and has a few extra features that make things run better. I have a number of customers using this to deploy to many instances, both for database changes and instance config updates.

The main way to get setup is with a distribution list. This is a list of the instances and database you want to connect to. This post will show how to make one.

The Default List

By default, you have one list, and you can add instances to this. As you can see below, I’ve added a few instance and databases to my list, which is shown on the right side of the main application window.

If I click the “Configure” button, I get a dialog that lets me manage these lists. It is shown below, with a mover in the middle to add or remove databases to the current list. In the upper right is a “New” button to add a list.

Let’s click that. This gives me a simple dialog to add a name. I’ll choose “InstanceMasters” for all the master databases on instances. This is a handy list when I want to add a login to all instances or make a config change.

Once I click “Create” I get back to the mover. This is where I select databases.

I can expand the instance on the left, and see all the databases. One note, the system databases are listed last, so scroll down. I’ll click “Add” in the middle to add this one.

I have a second instance on my machine, so let’s pick that. I’ll click the “Add a SQL Server Not Listed” at the bottom.

I get a connection dialog to specify the name and credentials.

Once I complete this, I go back to the mover and I see my second instance. I’ll add that master database as well.

If I click OK, I now have my distribution list selected and set up. Any scripts I execute will go against these two master databases. If I needed to exclude one of the databases for a script, I could uncheck it, and in the image above, I can change distribution lists with the drop down below the Delete button.

That’s a quick look at lists in SQL Multi Script. The tool lets me run multiple scripts against various databases, execute an ad hoc script, and get all the results with the instance/database name as well.

It’s a very handy tool that not enough people use, so if you have the Toolbelt, give it a try. If down, download a SQL Multi Script evaluation today and see how this can help you in your daily work.

July 12, 2021
Getting the Script from the SQL Compare Command Line
In the last few posts, I’ve written about using the SQL Compare command line for a specific object and shown how to get a report. This post will look at getting the actual script.

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.

When we run the command line or generate the report, we see what’s changed, and even the details of the chances, but how will that get deployed? The actual script is often important to a DBA to ensure that these changes won’t cause problems in a live environment.

To get the script, there is a /scriptfile parameter (or /sf) that will output the file. You add this in similar way as you do the report file, including the path. The important thing here is to ensure that you have write access to the path.

I have added a few differences in my databases, and you can see them in my complete report:

If I want to see what will be run for all these changes, I can add the /sf and get the script. In this case, I’ll add this to the end of the CLI call:
```
/sf:C:\Users\Steve\Documents\changes.sql
```
This produces a script that looks like this:

It’s a normal “SQL Compare” script, with comments at the top as well as the various transaction items.

This is good, because I can see there is a table drop in here. I actually renamed a table, so this is a problem. I might want to then decide how to handle this, or not to deploy this change.

Note: Using SQL Source Control or SQL Change Automation allows this to be handled in other ways.

I can also combine this with a single table inclusion to check one item. For example, I can run this:
```
sqlcompare /server1:Aristotle\SQL2017 /server2:Aristotle\SQL2017 /database1:compare1 /database2:compare2 /include:table:mytable /sf:C:\Users\Steve\Documents\mytable1.sql
```
When I do that, I see my script has a table rebuild in it, which is something else I might be concerned about.

With the other posts on the SQL Compare CLI, we can now choose what to compare, get a report, and see the actual scripts being run. This should allow us to choose the way that we want to deploy changes with SQL Compare from the command line.

I don’t know that these are the best way to deploy to production, but when you need to sync something quickly, get a report and script, and then decide if this works for you.

There are lots of options and ways to use SQL Compare, and I’d urge you to explore a bit as you look to improve your database deployments. If you don’t have it yet, download an eval and give it a try.
July 5, 2021