Rolling update demo

rollingupdate_01_prepare_database.sql

-- Create a test database
create database hrDB;

use [hrDB];
go

create schema dbo;
go

-- Create a user that can insert values while the schema updates are executed
create login hruser with password = 'hruser@123';
go

CREATE USER hruser for login hruser;
go

-- Make the user db_owner
EXEC sp_addrolemember N'db_owner', N'hruser'

-- Create the initial EMPLOYEE table
create table hrDB.dbo.EMPLOYEE (
  id        INT identity primary key,
  firstname nvarchar(50),
  lastname  nvarchar(50)
)
go

rollingupdate_02_insert_firstname_lastname.sh

#!/usr/bin/env bash

DB_USER=hruser
DB_PASSWD=hruser@123

# Reads names from names.csv line by line and creates
# EMPLOYEE records with FIRSTNAME and LASTNAME values
while read p; do
 sleep 1
 while IFS=',' read -ra PERSON; do
    FIRST_NAME="${PERSON[0]}"
    LAST_NAME="${PERSON[1]}"

    docker exec dockar-databases_mssql-research-server_1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U ${DB_USER} -P ${DB_PASSWD} \
   -Q "INSERT INTO hrDB.dbo.EMPLOYEE (FIRSTNAME, LASTNAME) VALUES ('${FIRST_NAME}', '${LAST_NAME}')"

    echo ${FIRST_NAME} ${LAST_NAME}
 done <<< "$p"
done <names.csv

rollingupdate_03_add_fullname.sql

-- Add fullname column
alter table EMPLOYEE
  add fullname nvarchar(100);

-- Create a trigger that merges FIRSTNAME and LASTNAME
-- and sets FULLNAME with this value
create or alter trigger MergeFirstAndLastName
  ON EMPLOYEE
  for insert
as
  begin
    UPDATE e
    set e.fullname = i.firstname + ' ' + i.lastname
    from inserted i
      inner join EMPLOYEE e on e.id = i.id
    where i.FirstName <> ''
  end
go

-- Fill the FULLNAME column for the existing records
update EMPLOYEE
set EMPLOYEE.fullname = EMPLOYEE.FirstName + ' ' + EMPLOYEE.LastName
where EMPLOYEE.fullname is null
go

rollingupdate_04_insert_fullname.sh

#!/usr/bin/env bash

DB_USER=hruser
DB_PASSWD=hruser@123

# Reads names from names.csv line by line and creates
# EMPLOYEE records with FULLNAME value
while read p; do
 sleep 1
 while IFS=',' read -ra PERSON; do
    FIRST_NAME="${PERSON[0]}"
    LAST_NAME="${PERSON[1]}"

    FULL_NAME="$FIRST_NAME $LAST_NAME"

    docker exec dockar-databases_mssql-research-server_1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U ${DB_USER} -P ${DB_PASSWD} \
   -Q "INSERT INTO hrDB.dbo.EMPLOYEE (FULLNAME) VALUES ('${FULL_NAME}')"

    echo ${FULL_NAME}
 done <<< "$p"
done <names.csv

rollingupdate_05_drop_firstname_lastname.sql

-- Cleaning up

-- Drop the trigger that merged FIRSTNAME and LASTNAME into FULLNAME
drop trigger  MergeFirstAndLastName;
go

-- drop FIRSTNAME and LASTNAME columns
alter table EMPLOYEE
  drop column FirstName, LastName
go

rollingupdate_docker-compose.yml

version: "3.6"
services:
  mssql-research-server:
    image: microsoft/mssql-server-linux:2017-CU7
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=adminPasswd01
      - MSSQL_PID=Developer
    volumes:
      - mssql-research:/var/opt/mssql/
    ports:
      - 1433:1433

volumes:
  mssql-research:

rollingupdate_names.csv