Database CI/CD and Schema Migration with SQL Server
A series of articles about Database CI/CD and Schema Migration with SQL Server.
- Database CI/CD and Schema Migration with SQL Server (this one)
- Database CI/CD and Schema Migration with SQL Server and GitHub
Microsoft SQL Server is a widely used database management system developed by Microsoft for storing and retrieving structured data. It offers features for data management, scalability, performance optimization, security, and business intelligence.
This tutorial is a step-by-step guide to set up Database Change Management for SQL Server in Bytebase. With Bytebase, a team can have a formalized review and rollout process to make SQL Server database schema change and data change.
Bytebase provides a GUI for teams to perform database changes and retain full migration history. Bytebase Free Plan is sufficient for this tutorial.
Prerequisites
Before you start, make sure you have the following downloaded and installed:
- One SQL Server instance
- Docker
Step 1 - Deploy Bytebase via Docker
-
Make sure your Docker is running, and start the Bytebase Docker container with the following command:
-
Bytebase is now running via Docker, and you can access it via
localhost:5678
. -
Visit
localhost:5678
in your browser. Register an account.
Step 2 - Add a SQL Server Instance to Bytebase
In this tutorial, βan Instance is your SQL Server instance.
-
Login to the Bytebase Console.
-
Click Instances on the left sidebar and click Add Instance.
-
Add an
SQL Server
instance. Pay attention to Environment, let's chooseProd
for this tutorial. Because there's a SQL Review policy enabled onProd
environment. You may go to Environment > Prod > SQL Review Policy to check the details.
Step 3 - Go to the existing Project
In Bytebase, Project groups logically related Databases, Issues and Users together, which is similar to the project concept in other DevTools such as Jira and GitLab. So before you deal with the database, a Project must be presented.
Go to the Projects tab, and you'll see a default project called Sample Project
. Click it, there are two sample PostgreSQL databases. We'll create a new SQL Server database in this project.
Step 4 - Create a SQL Server database via Bytebase
In Bytebase, a Database is created by CREATE DATABASE xxx
. A database always belongs to a single Project. An Issue represents a specific collaboration activity between Developer and DBA for when creating a database, altering a schema. It's similar to the issue concept in other issue management tools.
-
Stay in the project
Sample Project
. Click on New DB to create a new database. -
Fill in the form, and pay attention to these fields:
- Name: can be anything, here we used
db_demo
- Environment -
Prod
- Instance - choose the instance you added in Step 2.
Click Create.
- Name: can be anything, here we used
-
Bytebase will create an issue to create the database automatically and without further configuration, the rollout is approved by default. The issue is Done after the rollout succeeds.
Step 5 - Create a table in SQL Server via Bytebase
In Step 4, you actually created an issue using UI workflow and then executed it. Letβs make it more explicit.
-
Go to project
Sample project
, checkdb_demo
you created and click on Edit Schema. -
You'll be redirected to an issue preview. Fill in the following SQL and click Create.
-
After automatic checks, there's a pop-up window showing a SQL Review violation --
Enforce NOT NULL constraint for columns
. -
Click Back to edit, and paste this and click Create again.
-
After passing the automatic checks, the issue executes and the status becomes
Done
.
Summary and What's Next
Now you have connected SQL Server with Bytebase, and used the UI workflow to accomplish schema change. Bytebase will record the full migration history for you.
In the next post, youβll try out GitOps workflow: store your SQL Server schema in GitHub and Bytebase will pick up the changes to the repo, bringing your SQL Server change workflow to the next level, aka Database DevOps - Database as Code.