Press "Enter" to skip to content

Category: Deployment

Creating a Database Publish Profile in Visual Studio

Elizabeth Noble shows us how to create a database publish profile using Visual Studio:

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.

Click through for a video and a sample of what a publish profile looks like.

Comments closed

Combining SendTo and Powershell

Mark Wilkinson shares a script with us:

If you are not familiar, SendTo options are those available when you right click on a file/folder in file explorer and select the Send To option in the menu. When you use this option, the currently selected files/folders are passed to the SendTo shortcut as a space delimited list of files and folders. This is important to know so you better understand what needs to be done to read that list.

I can confirm that this works well for deploying script out, especially when they need to go to multiple servers or multiple databases on servers. That functionality takes a bit more effort to write, but combine Mark’s code with Jess’s and you are well on your way.

Comments closed

Deploying Bacpacs to Azure SQL Database via Terraform

John Martin shows how to deploy a database schema (in bacpac format) via Terraform:

It’s all well and good deploying Azure SQL Database resources as we did in the previous post. However, databases tend to work a little better with a schema and some data in them. One of the options for getting data from an on-premises SQL Server database into Azure SQL Database is via a bacpac. This is, at its core, an export of the schema and data into a single file which is then created and loaded to Azure SQL Database. Much the same as a MySQL dump operates.

Read on for one way to do this.

Comments closed

Optimizing a SQL Server 2019 Project for a Dedicated SQL Pool

Kevin Chant shows us how we can modify a database schema intended for SQL Server 2019 to work best with an Azure Synapse Analytics dedicated SQL pool:

In this post I want to cover how you can transform your SQL Server database schema for a dedicated SQL Pool if you are using Azure DevOps. Because I covered it at Data Toboggan over the weekend and it can be very useful.

By the end of this post, you will know one way you can transform the schema of a database project for SQL Server 2019 if you are using Azure DevOps. So that you can make it optimal for dedicated SQL Pools.

Click through for the process and an example. Note that this isn’t a quick “check this box and you’re done” type of solution, but if you already have a proper star schema, this will help you think through some of the things you’ll need to do.

Comments closed

Deploying ADS Database Projects Manually

Elizabeth Noble continues a series of videos on database projects in Azure Data Studio:

This week, we’ll talk about one of the easier ways to deploy your database changes. One of the benefits of database projects is that they can generate data-tier applications (DAC). The data-tier applications can be bundled into what is called a DACPAC. This is a collection of files that can be used to deploy your database.

Click through for the video.

Comments closed

Issues Deploying Azure Synapse Analytics via ARM Template

Paul Andrew hits on some growing pains:

Just last week we heard the announcement from Microsoft that Azure Synapse Analytics is now generally available (GA)… A full year on, plus a few weeks, since first seeing Synapse at the big USA conferences in November 2019.

Today I’ve been attempting to use the resource with a view to implementing it for several customer projects. Although GA, it would seem that many part of the technology are far from ready.

In this brief blog I’m exposing some of the pain I’ve faced so far in simply trying to deploy a second instance of Azure Synapse Analytics using ARM templates.

Click through for three that Paul found. I’d expect that most of these will be tidied up in the next few months.

Comments closed

Deploying to Multiple SQL Server SKUs with Azure DevOps

Kevin Chant wants to deploy to all of the SQL Servers:

To give the above pipeline a bit more context the below types of SQL Server databases were updated after being unit tested (initial unit testing yaml courtesy of Sander):

– Three SQL Server 2019 instances in three Docker containers. Representing Integration, Staging and Production environments.
– At the same time the Git repository in Azure Repos would sync with a GitHub Repo. Which would then start a GitHub Action to update another database.
– An Azure SQL Database.
– Finally, a Synapse Analytics SQL Pool was also updated.

Read on to learn how.

Comments closed

Automating Database Deployments: Why Not?

Grant Fritchey asks a question:

Building out processes and mechanisms for automated code deployments and testing can be quite a lot of work and isn’t easy. Now, try the same thing with data, and the challenges just shot through the roof. Anything from the simple fact that you must maintain the persistence of the data to data size to up time, and you have real problems in front of you.

However, adopting database deployment automation and testing has enormous benefits. Faster, safer, production deployment enhances the protection built around your production systems. Whether we want to use the loaded term of DevOps or not, the benefits of this style of development and deployment are easily documented and measured.

So, why are so few people doing it?

Grant gives some of the outline and lays out one response. I am seeing a lot more automation over time, but one underappreciated facet in this is a lack of trust for automated processes from humans. I think a good percentage of DBAs don’t trust that the automated process will get things correct, especially when dealing with complex chains of dependencies. An automated process may be less likely to make a mistake in a step, but it will also be unable to reason through an ambiguity and could perform an undesirable action in the event of unexpected circumstances. That’s a pretty big risk for DBAs who are concerned about their data. I can see a few other reasons as well, but this is one which I don’t hear often enough in these discussions.

Also, Grant asks people to fill out the State of Database DevOps survey, especially those people who are not automating database deployments.

1 Comment

Deploying to Azure SQL Database via GitHub Actions

Kevin Chant shows us how to use GitHub Actions to deploy updates to Azure SQL Database:

After my last post I wanted to test deploying to Azure SQL Database using GitHub Actions. To check that it all runs smoothly.

By the end of this post you will have some ideas about how you can deploy Azure SQL Databases using GitHub Actions. Both for a basic test and more complex deployments.

In my last post here I showed you how you can use GitHub Actions to deploy a free monitoring framework called SQLWATCH to on-premises versions of SQL Server. I thought I would test using the same Azure SQL Deploy Action for Azure SQL database deployments.

Click through for the process.

Comments closed