on February 15, 2019
I’m excited to have just clicked ‘publish’ on four new videos in the brand new Advocate playlist on Redgate’s YouTube channel.
These videos step through setting up and working with a project in one of my favorite Redgate tools: SQL Change Automation. The awesome thing about SQL Change Automation is that it gives you two things:
- Fine-grained control to tune and order the execution of changes to your database with a migrations-first approach to source controlling your code
- A view of the state of your database, which is crucial for code reviews, controlling environment drift, and communicating about changes between developers and database administrators
The tricky thing is that there’s a lot to learn when starting out with SQL Change Automation if you haven’t used a migrations-first approach in the past, or aren’t used to working in Visual Studio.
That’s where these videos come in: you can follow along with the demos in your own installation of Visual Studio and Redgate’s SQL Toolbelt, or just watch and learn. I take it slow and don’t assume that you’re an expert in Visual Studio.
In one hour of demos, you will learn…
- How to set up a SQL Change Automation project with Microsoft’s Northwind sample database
- What a baseline is and how to create one
- What programmable objects are, and how to edit one offline in Visual Studio
- What the offline schema model is, and how to work with it in Visual Studio
- How to use branches and SQLCompare with SQL Change Automation projects
- How to automate the production of database documentation with the SQL Change Automation PowerShell cmdlets
- How to build and deploy with SQL Change Automation PowerShell cmdlets
Why aren’t you working in SSMS in the videos?
If you prefer working in SQL Server Management Studio or Azure Data Studio, you can choose to make database changes there, then import the changes into SQL Change Automation as scripts that you tweak as desired in Visual Studio. That’s a valid workflow, and I often use it myself.
In these videos I show how to do all the work in Visual Studio – because although I’m a long time SSMS fan, it’s pretty convenient to know how to do your work in multiple tools.
Tools used in the videos
- Microsoft Visual Studio 2017
- Redgate SQL Toolbelt - Free Trial available
- VSCode - I use this for PowerShell script execution - you could use the PowerShell ISE instead, or just execute scripts from the command line
Using SQL Toolbelt Components with SQL Change Automation: Create Your Demo Environment
This is the first of four videos on using SQL Toolbelt components (SQL Prompt, SQL Compare, and SQL Doc) with SQL Change Automation Projects. Follow along to set up the demo environment used in the next three videos.
1:07 - Download the Northwind Sample Database script from Microsoft: http://bit.ly/msftnorthwind
2:15 - Create the Northwind Database and the empty NorthwindDev database from Visual Studio
4:20 - Create the SQL Change Automation project and create a baseline script
8:30 - View pending deploy of the project to the NorthwindDev database
8:53 - View location of baseline script, programmable objects, and offline schema model in Solution Explorer. Check for ‘Unsupported’ folder.
10:04 - Deploy to the NorthwindDev database
Using SQL Prompt with SQL Change Automation in Visual Studio
Learn how SQL Prompt can help you quickly improve TSQL scripts in Visual Studio with SQL Change Automation.
The PowerShell script shown is available at: http://bit.ly/2IfgmUK
In this video:
00:20 - Overview of programmable objects in SQL Change Automation
01:15 - Overview of the offline schema model in SQL Change Automation 2:10 - Demo of refactoring a stored procedure (a programmable object) begins
7:30 - Saving the “offline edit” to the programmable object triggers option to deploy to development database
9:08 - Test full build and deploy of first committed change in VS Code with PowerShell components
13:15 - Demo of editing an index on a table with SQL Server Data tools and generating a migration script begins. This demo shows the “offline schema model”
20:19 - Deploy the edited migration script
22:40 - Perform build and deploy with PowerShell cmdlets
24:00 - Review release artifact
Using SQL Compare with SQL Change Automation
Learn how you can use SQL Compare with SQL Change Automation projects in Visual Studio to compare database state at different points in your project.
00:16 - How the SQL Compare is used by SQL Change Automation under the hood
1:03 - Demo begins with a view of the Northwind SQL Change Automation Project in Visual Studio
1:50 - Demo of setting up the source and target in SQL Compare, with discussion of how this comparison uses programmable objects and the offline schema model
4:23 - View of the results of the comparison
5:06 - Demo of creating and checking out a branch in Git via Visual Studio 6:37 - Demo of refreshing SQL Compare after the branch switch in Visual Studio, and how and why the results change
7:53 - Demo of switching back to the master branch and a few of how this once again changes the comparison being performed (in a good way)
Automating SQL Doc with SQL Change Automation
Learn how to automate the production of database documentation using the SQL Change Automation PowerShell cmdlets to produce SQL Doc documentation.
The PowerShell script shown is available at: http://bit.ly/2V14Lui
In this video:
1:06 - Start of demo in VSCode, walk through of some key points of the script
3:28 - Explanation of why I am specifying a SQL Server instance for the documentation and not using LocalDB
3:45 - View of the resulting documentation in a .zip output file begins