PITCHME.md

SQL Server & Continuous Integration

James Anderson

www.TheDatabaseAvenger.com
@DatabaseAvenger
James@TheSQLPeople.com

---

Agenda

• CI and Data
• Deploying DB changes
• Testing
• Automation

---

Continuous Integration?

Continually integrating code

---

It's not just a suite of tools

Although, that will be my focus today

---

You don't need the Ferrari of build servers

---

You can still get there for less

---

Continuous Integration with SQL Server

Why is deploying database changes so difficult?

---

State based Vs Migration based

---

The Hybrid Approach

---

Let's take a look at SQL Change Automation

+++

+++

+++

+++

+++

+++

+++

+++

+++

CREATE TABLE Customers 
(
	CustomerID INT IDENTITY(1,1) NOT NULL,
	Title NVARCHAR(25) NOT NULL,
	FirstName NVARCHAR(100) NOT NULL,
	LastName NVARCHAR(100) NOT NULL,
	DOB DATE NOT NULL,
	CONSTRAINT [PK_CustomerID] PRIMARY KEY CLUSTERED  
	(
		[CustomerID] ASC
	) WITH (PAD_INDEX = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

+++

+++

+++

CREATE TABLE Config
(
	Setting NVARCHAR(250) NOT NULL,
	[Description] NVARCHAR(1000) NOT NULL,
	[Value] NVARCHAR(100) NULL,
	CONSTRAINT PK_Config_Setting PRIMARY KEY (Setting)
);
GO
CREATE PROCEDURE ConfigSettings 
AS
BEGIN
	SELECT	Setting,
			[Value]
	FROM	dbo.Config;
END

+++

+++

Migration scripts are for stateful objects only.

+++

ALTER TABLE Config ALTER COLUMN [Value] NVARCHAR(MAX);
GO

ALTER PROCEDURE ConfigSettings 
				@Setting NVARCHAR(250) = N'All'
AS
BEGIN
	SELECT	c.Setting, c.[Value]
	FROM	dbo.Config c
	WHERE	(
				(@Setting = N'All')
				OR
				(@Setting = c.Value)
			);
END

+++

+++

+++

Reference Data

+++

+++

INSERT dbo.Config(Setting, Description, Value)
VALUES
('Active', 'Is the appllication active', 'Y'),
('Client', 'The name of the client', 'The SQL People Ltd'),
('Client Email', 'Email address', 'James@TheSQLPeople.com');
GO

+++

+++

---

We're doing it!!

---

Testing

• tSQLt
• PowerShell
• Pester

---

http://tsqlt.org/

+++

Create a test class for a new SP

EXEC tSQLt.NewTestClass 'testFinancialApp';

+++

Create new SP to test

CREATE FUNCTION dbo.ConvertCurrency 
(
    @rate DECIMAL(10,4), 
    @amount DECIMAL(10,4)
)
RETURNS DECIMAL(10,4)
AS
BEGIN
	DECLARE @Result DECIMAL(10,4);

	SET @Result = (SELECT @amount / @rate);

	RETURN @Result;
END;

+++

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS
BEGIN
    DECLARE @actual DECIMAL(10,4);
    DECLARE @rate DECIMAL(10,4) = 1.2;
    DECLARE @amount DECIMAL(10,4) = 2.00;

    SELECT @actual = dbo.ConvertCurrency(@rate, @amount);

    DECLARE @expected DECIMAL(10,4) = 2.4;  

    EXEC tSQLt.AssertEquals @expected, @actual;
END;

+++

Run the tests!

EXEC tSQLt.Run 'testFinancialApp';

+++

Fail

+++ Alter the calculation

ALTER FUNCTION dbo.ConvertCurrency 
(
    @rate DECIMAL(10,4), 
    @amount DECIMAL(10,4)
)
RETURNS DECIMAL(10,4)
AS
BEGIN
	DECLARE @Result DECIMAL(10,4);

	SET @Result = (SELECT @amount * @rate);

	RETURN @Result;
END;

+++

Success

---

Pester Demo

+++

Sample Test

Describe "Get-SQLInfo" {
    It "returns $true" {
        Get-SQLInfo | Should Be $true
    }
}

+++

Test Driven Design

```powershell New-Fixture -Path Temp -Name Get-SQLInfo ```

+++

New function

function Get-SQLInfo {
	param ($a)
    
    if ($a -eq 1) {$true} else {$false}
}

+++

Linked Test Script

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path).Replace(".Tests.", ".")
. "$here\$sut"

Describe "Get-SQLInfo" {
    It "returns $true when `$a = 1" {
        Get-SQLInfo 1 | Should Be $true
    }
	It "returns $false when `$a = 0" {
        Get-SQLInfo 0 | Should Be $false
    }
}

+++

Mocking

Faking the response of a piece of code that we aren't currently testing

+++

Sample Test with Mocking

Context "Calls to Get-Random" {
		Mock Get-Random { return 3 }
		It "Get-Random returns 3" {
			Get-SQLInfo | Should be $true
		}
	}

+++

Sample Test with Mocking Assert

Context "Calls to Get-Random" {
		Mock Get-Random { return 3 }
		It "Get-Random returns 3" {
			Get-SQLInfo | Should be $true
			Assert-MockCalled Get-Random -Exactly 1
		}
	}
}

---

GitLab

---

GitLab Features

* Remote repository * Build server with CI pipelines * Issue management \ Bug tracking * Documentation (I love this)

---

A Quick Tour of GitLab

+++

Login with Active Directory

+++

+++

GitLab Runners

+++

Runner is connected to GitLab

+++

Configure a pipeline with .gitlab-ci.yml

job:
  script: '"C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe"'
  tags:
    - windows

+++

Commit a change to trigger a build

+++

.gitlab-ci.yml V2

build:
  script: 
  - powershell "& 'C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe' 'RR_Test.sqlproj' /p:TargetServer=LocalHost /p:TargetDatabase=RR_Test /p:Configuration=Release /p:GenerateSqlPackage=True /p:DBDeployOnBuild=True /p:ShadowServer=LocalHost" 
  tags:
    - windows

test:
  script: 
  - powershell .\TestHarness.ps1 %CI_PROJECT_DIR% 'LocalHost' 'RR_Test'  
  tags:
    - windows
  artifacts:
    paths:
    - RR_Test/bin/
    untracked: true

---

So now we have automatic testing everytime we make a change.

All is good

+++

But

+++

I want to test the project against all versions of SQL Server

I also want to test upgrading from each version of my project

+++

---

Thanks for listening

Any questions?

www.TheDatabaseAvenger.com
@DatabaseAvenger
James@TheSQLPeople.com