Creating a Publish Profile in Visual Studio

One of the factors that allowed my company to get comfortable automating database deployments was SQL Server Data Tools (SSDT) and publish profiles. We started using SSDT with database projects before Azure Data Studio (ADS) existed.

One of our fears was always how to prevent losing data and critical data code. Here were publish profiles to our rescue. We also found that some of our database code had specific values depending on the environment or contained references to other databases. Once again, publish could solve these problems!

While I’d love to say that you could use ADS to manage your database projects, that just isn’t true right now. However, we have a way to help you get a publish profile created. If you don’t want to use Visual Studio yourself, you might want to ask your Developer friends real nice and see if they’d be willing to help you out.

I’ve create a nice YouTube video to show you how you can create your own publish profile. Check it out!

And if you’re curious about what a publish profile might look like, here’s an example of the one created in the video.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>BookCatalog</TargetDatabaseName>
    <DeployScriptFileName>BookCatalog.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=localhost;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False</TargetConnectionString>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

But I’d highly recommend using the GUI in Visual Studio instead of trying to make a file on your own.

Update the Default Branch Name in Azure Data Studio

While performing my videos, I realized that I hadn’t updated my default branch name from the previous value. This week’s video walks you through the process of renaming your default branch from master to main. This video not only covers how to update GitHub but also how to update your local GitHub project. Once again, we’re using Azure Data Studio to work with our Database Projects. You can also use Visual Studio, if you prefer.

Add Database Project to GitHub

Two weeks ago, I showed you how to create a Database Project using Azure Data Studio (ADS). If you’re starting your database journey with source control and/or Continuous Integration/Continuous Delivery (CICD), that’s the first step you’ll need to complete. However, getting a database project created is just the beginning!

Once you have the database project created, you’ll want to get your database project added to source control so that you (and others) can modify and manage your database code. This next step is the beginning of allowing you and others to work on the same databases and minimize the risk of overwriting someone else’s work or deploying the wrong code to Production.

This week’s YouTube video will show you how you can get your database project to GitHub. If you’re using Azure DevOps, the process should be similar.

YouTube video showing how to add a database project to GitHub

Keep following this blog, and I walk through how to setup a process to automatically build and deploy your database code.

Use Azure Data Studio to Clone a Repo from GitHub

One of the more common questions I’ve gotten is how can Database Administrators and Database Engineers embrace database source control without using Visual Studio. I have to admit, a good part about DevOps, automation, and database source control involves using tools that people will embrace.

In this week’s YouTune video, I show how you can clone (import) a repository (database code) from GitHub all within Azure Data Studio. This is a great feature that helps make database source control more accessible to individuals who may not have access or be comfortable using Visual Studio or VS Code.

You will need to be on at least Azure Data Studio (ADS) 1.25.1. In addition, you’ll want to download Git on your machine as well as install the Database Project extension in ADS. This guide is designed for those individuals using SQL Server Data Tools (SSDT).

The YouTube video can be found here or by clicking on the image below.

Thumbnail for YouTube video: Database Projects with GitHub

Creating a Database Project in Azure Data Studio using an existing database

Check out this video on how to quickly get your databases into Database Projects. If you’re wanting to save a copy of your SQL Server database into a version control system, this is one of the first things you’ll need to do.

Database Projects can also help organize your T-SQL code that is used to make database objects. In this video, I show you how to create a database projects that groups T-SQL scripts by schema and then by database object type. No more wondering if that object is a view or table!

It’s the end of the year, and I’ve decided the best way to prepare for the New Year is to start my goals early. One of the things I’ve wanted to do all year is start a video series on how you can improve your database deployment process.

There are all sorts of fancy names that get used including source control, version control, continuous integration, continuous delivery, DevOps, etc… My goal here is make this process easy for you.

When I first started talking about source control and automated deployments, I lived in a world of Visual Studio. I was a data professional, the only real language I know is T-SQL. But I soon learned there were alot of challenges in getting my data peers to embrace using Visual Studio.

Thankfully, Azure Data Studio has created the Database Projects extension to solve this problem. Instead of giving you pages of screenshots, I have a video that will walk you through creating your first Database Project using Azure Database Studio and one of your existing databases.

I’ve created a quick YouTube video to walk you through the process. Check it out and let me know what you think! https://www.youtube.com/watch?v=DgfMycbBQUU