SQL Server DevOps Style — Part 1
People tend to say that developing database solutions is a pain in the ***, but it doesn’t have to be that way. Here is how to flow through SQL Server solutions like a DevOps.
We want to create our databases and data solutions in a reproducible way, together with our teammates, deployable to where ever the server is and overall reliable. In short, do databases DevOps Style.
But how, you might wonder? In this first part of this database DevOps Style, let me show you how to create a simple database solution for SQL Server, using Azure Data Studio. It is available for free and for Linux, Windows, and macOS. You can use Visual Studio and the SQL Server Data Tools if you like, as well.
Prerequisites
As already written you need Azure Data Studio:
Download and Install Azure Data Studio
and a running Docker demo. Please find docker here:
Additionally, you’ll need .Net SDK installed:
Finally and optional, we’ll use Hashicorps Terraform
Start a development SQL Server
When developing our SQL Server solution, we do not want to mess around with production servers, hence we need a local SQL Server instance.
You can install SQL Server Developer Edition locally to your computer if you are on a windows or Linux machine, but let me recommend using a Docker container.
If you already have Docker Desktop installed, you can run the following command to spin up a docker container with a SQL server with the following command:
But wait, we want to do it DevOps style, right — infrastructure should be code as well. Hence we start and configure our SQL Server using Terraform. Yes, you could have used ansible or similar, even a shell script would have done it, but Terraform will help us later to take our server to the cloud.
Lets create a project folder ‘SQLServerDevOpsStyle-01’ inside we create a folder called ‘Infrastructure’ and a folder ‘DevOpsStyleDB’.
Inside the Infrastructure folder, we create a main.tf file and insert the start code for our SQL Server
After creating the main.tf file, just run terraform init plan and apply:
terraform init
terraform plan
terraform apply
Create a database project using Azure Data Studio
Before we can create a database project using Azure Data Studio, you need to add some extension:
- SQL Database Projects
- SQL Server Schema Compare
- [optional] Admin Pack for SQL Server
To install an extension go to the Extensions tab, find the extension you want to install, and hit install. If it is only available as a vsix, download the vsix file and go to File→ “Install Extension from VSIX Package”
Now go to the “Projects” and click on “Create New”. You will be prompted with a screen to set a name for the Database project, a location on your disk, and a workspace to create or add the project to.
Create some tables
Now that we have our database project set up, let’s create two new tables, a schema, and a stored procedure to move some data.
First, create a new schema definition. Right-click on the database project and select “Add Script”
To create a table, right-click on the database project and select add table
https://gist.github.com/JimKnopf2034/692579791d39e4b8cf1ba9bb7fd9300f
To create a stored procedure the workflow is similar, right-click on the project and select “Add Stored Procedure”
Build and deploy locally.
To bring our defined database solution to the server two steps need to be taken. First, build the solution into a dacpac and then deploy the dacpac to our local SQL Server.
To “build” the solution, just right-click on the database project and select build. It is starting up the MSBuild task to create a dacpac from the database solution.
Next, we want to deploy the solution to the SQL server, hence just right-click the database project and select “publish”.
You’ll be asked for the connection to a SQL Server select the local host and specify the desired name of the database or the name of an existing database.
Wait and watch the data studio doing its thing deploying the database project to the server.
How to go on?
In the next part of this series we’ll see how to make changes to our tables, schema, add some procedures and functions and implement deployment to an Azure SQL Database. Additionally, we’ll see how to set up azure DevOps to automatically deploy changes and look into, how to test databases before deploying them.
What is your main challenge, when build database solution? Let me know in the comments!
If you liked this first tutorial, give it a clap, subscribe, and if it helps you in your daily work, consider:
