Setting up a New SQL Change Automation Project

It’s been awhile since I’ve set up a new project in Redgate’s SQL Change Automation project. I’ve mostly used this in Visual Studio, but I need to demo something to a client, so I decided to document the process of getting started here.

There are often updates to this product, as we release every week or two, so the first thing was the click on the banner in SSMS and upgrade to the latest version. Once you have things working, you don’t need to do this often, but you should to it quarterly at least.

2020-07-07 18_00_55-SQL Change Automation

My Environment

I’ve got multiple copies of my databases on one instance. I have 1 for development, 1 for QA, and one for Prod. This is a good PoC setup for getting familiar with the product.

2020-07-09 10_13_51-SQL Change Automation - Microsoft SQL Server Management Studio

The flow is we only make changes in SimpleTalk_1_Dev with SSMS. Everything else will happen through automation.

These databases already have objects in them, but they’re not overly complex.

2020-07-07 17_56_15-SQLQuery1.sql - ARISTOTLE_SQL2017.Sandbox (ARISTOTLE_Steve (54)) - Microsoft SQL

I have SQL Change Automation (SCA) installed in SSMS, versions 4.2.20176 and 18.5 respectively. Let’s see if we can do some development.

Creating a Project

When I open the SCA tab in SSMs, I see the open and new project options.

2020-07-08 10_22_54-SQL Change Automation - Microsoft SQL Server Management Studio

We are creating a new project here, so let’s click the button to do that. Once I do that, I get a dialog that appears. I’ve entered a name and then select my folder where I keep code.

Note, I’ve picked a folder name here, and the project will create a subfolder under this with the name of the Project as the folder name. In this case, my actual project files will be in E:\Documents\git\SimpleTalkDemo\SimpleTalkDB.

2020-07-09 11_24_25-New project setup

I do want the project folder under version control. Before I have SCA do anything, I use a command line and initialize this for git. If you don’t know how this works, I’ve got a post on that.

2020-07-08 19_41_49-SQLServerCentral – The #1 SQL Server community

Now I can go back and connect to the development source. This is the dev database. There doesn’t need to be anything in here, but in this case, it’s a copy of the schema from production. Once I enter the credentials and pick the database, I come back to this screen and I see the first part of my project.

2020-07-08 19_43_53-New project setup

Clicking Next gives us the filter screen. This uses the filter files from SQL Compare to determine which objects we are including for development purposes. For most projects, we don’t filter anything. We want all objects to be included in our project.

2020-07-08 19_44_00-New project setup

I’ll leave this alone and click Next. The last screen is the baseline screen. This allows me to set a baseline or not. A baseline is a view of what the target database looks like right now. In this case, this is the production database.

This process will look at the objects in that database and create a script that builds up a base database to the same state as production right now. That is useful because all items we add to this project depend on the state being this way. You can read more about baselines in the documentation.

2020-07-08 19_44_07-New project setup

I will configure this to connect to my production database and once that’s correct, I’ll click “Create” to set up the project.

2020-07-09 11_26_26-New project setup

The project gets created fairly quickly, and once it’s complete, I get a summary. As you can see below, the project was set up and there are some notes about what was done.

2020-07-09 11_27_07-SQL Change Automation - Microsoft SQL Server Management Studio

You can see that one migration script was generated and this was for my baseline. This contains all the objects in my current production database, which won’t be deployed unless I have an empty database, like a CI database.

There were also 19 programmable objects, which are views, functions, and stored procedures. These are items that we don’t want to track each change to, but only the state at a point in time.

All of this is reflected in my project folder, which you can see below. There are subfolders below some of these for the various object code.

2020-07-09 11_30_14-SimpleTalkDB

I’ve got a new project. Now I want to commit this to version control first, and then I can start development work.

2020-07-09 11_32_04-cmd

If you’ve never worked with SQL Change Automation, download an eval and give it a try today. I’ll be documenting the process as I build out a demo for a customer, and you can follow along.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

1 Response to Setting up a New SQL Change Automation Project

  1. Pingback: A Quick SQL Change Automation Build | Voice of the DBA

Comments are closed.