Skip to content
ADL Docs
App Design Patterns

SQL Server Deployment

Purpose

Section titled “Purpose”

Generate a deployment script that contains deployment calls for all generated (output) code.

Motivation

Section titled “Motivation”

Once the contents is created, a central deployment script can also be generated.
This can call each individual process or file to run the full deployment to a target in one pass.

Applicability

Section titled “Applicability”
  • All design metadata

Design Pattern

Section titled “Design Pattern”

N/A

Schema Type

Section titled “Schema Type”

N/A

Output Type

Section titled “Output Type”
  • Powershell script (ps1)

Implementation guidelines

Section titled “Implementation guidelines”

This template covers all design metadata.

Considerations and consequences

Section titled “Considerations and consequences”

N/A.

Extensions

Section titled “Extensions”

N/A.

Code

Section titled “Code”
<#
    Deployment script using SQLCMD.
    This relies on a trusted connectoin (-E).
    For user name and password this can be replaced by -U and -P.


    From the output directory, run .\Databases\Deployment\Deployment.ps1.
#>




#   Data Objects


{{!-- Source --}}
{{#each dataObjects}}
{{~#if @first}}# Source objects
{{/if}}
{{#hasClassification classifications "Source"}}
Write-Host "Processing .\Databases\\{{lookupExtension dataConnection.extensions "datastore"}}\\{{lookupExtension dataConnection.extensions "location"}}\Tables\\{{name}}.sql"
sqlcmd -S . -d {{lookupExtension dataConnection.extensions "datastore"}} -i ".\Databases\\{{lookupExtension dataConnection.extensions "datastore"}}\\{{lookupExtension dataConnection.extensions "location"}}\Tables\\{{name}}.sql"
{{/hasClassification~}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Samples --}}
{{#each dataObjects}}
{{~#if @first}}# Sample data
sqlcmd -S . -d {{lookupExtension dataConnection.extensions "datastore"}} -i ".\Databases\Deployment\SampleData.sql"
sqlcmd -S . -d {{lookupExtension dataConnection.extensions "datastore"}} -i ".\Databases\Deployment\SampleDataUserManaged.sql"
{{/if}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Landing. Note that object(s) with the user managed classfication are omitted by design --}}
{{#each dataObjects}}
{{~#if @first}}# Landing objects
{{/if}}{{#hasClassification classifications "Landing"}}{{#hasClassification classifications "User Managed"}}{{else}}Write-Host "Processing .\Databases\\{{../../../conventions.landingAreaObjectDatastore}}\\{{../../../conventions.landingAreaObjectLocation}}\Tables\\{{name}}.sql"
sqlcmd -S . -d {{lookupExtension dataConnection.extensions "datastore"}} -i ".\Databases\\{{../../../conventions.landingAreaObjectDatastore}}\\{{../../../conventions.landingAreaObjectLocation}}\Tables\\{{name}}.sql"
{{/hasClassification~}}
{{/hasClassification~}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Persistent Staging --}}
{{#each dataObjects}}
{{~#if @first}}# Persistent staging objects
{{/if}}{{#hasClassification classifications "Persistent Staging"}}Write-Host "Processing .\Databases\\{{../../conventions.persistentStagingAreaObjectDatastore}}\\{{../../conventions.persistentStagingAreaObjectLocation}}\Tables\\{{name}}.sql"
sqlcmd -S . -d {{lookupExtension dataConnection.extensions "datastore"}} -i ".\Databases\\{{../../conventions.persistentStagingAreaObjectDatastore}}\\{{../../conventions.persistentStagingAreaObjectLocation}}\Tables\\{{name}}.sql"
{{/hasClassification~}}
{{/each}}




#   Drop script for Data Objects


<#
{{!-- Source --}}
{{#each dataObjects}}
{{~#if @first}}# Source objects
{{/if}}{{#hasClassification classifications "Source"}}DROP TABLE IF EXISTS [{{../../conventions.sourceObjectDatastore}}].[{{../../conventions.sourceObjectLocation}}].[{{name}}];
{{/hasClassification~}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Landing --}}
{{#each dataObjects}}
{{~#if @first}}# Landing objects
{{/if}}{{#hasClassification classifications "Landing"}}{{#hasClassification classifications "User Managed"}}{{else}}DROP TABLE IF EXISTS [{{../../../conventions.landingAreaObjectDatastore}}].[{{../../../conventions.landingAreaObjectLocation}}].[{{name}}];
{{/hasClassification~}}
{{/hasClassification~}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Persistent Staging --}}
{{#each dataObjects}}
{{~#if @first}}# Persistent staging objects
{{/if}}{{#hasClassification classifications "Persistent Staging"}}DROP TABLE IF EXISTS [{{../../conventions.persistentStagingAreaObjectDatastore}}].[{{../../conventions.persistentStagingAreaObjectLocation}}].[{{name}}];
{{/hasClassification~}}
{{/each}}
#>


#   Stored Procedures


{{!-- Landing --}}
{{#each dataObjects}}
{{~#if @first}}# Landing procedures
{{/if}}{{#hasClassification classifications "Landing"}}{{#hasClassification classifications "User Managed"}}{{else}}sqlcmd -S . -d {{lookupExtension dataConnection.extensions "datastore"}} -i ".\Databases\\{{../../../conventions.landingAreaObjectDatastore}}\\{{../../../conventions.landingAreaObjectLocation}}\Stored Procedures\\{{name}}.sql"
{{/hasClassification~}}
{{/hasClassification~}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Persistent Staging --}}
{{#each dataObjects}}
{{~#if @first}}# Persistent staging procedures
{{/if}}{{#hasClassification classifications "Persistent Staging"}}sqlcmd -S . -d {{lookupExtension dataConnection.extensions "datastore"}} -i ".\Databases\\{{../../conventions.persistentStagingAreaObjectDatastore}}\\{{../../conventions.persistentStagingAreaObjectLocation}}\Stored Procedures\\{{name}}.sql"
{{/hasClassification~}}
{{/each}}


#   Drop script - Stored Procedures




<#
{{!-- Landing --}}
{{#each dataObjects}}
{{~#if @first}}# Landing objects
{{/if}}{{#hasClassification classifications "Landing"}}{{#hasClassification classifications "User Managed"}}{{else}}DROP PROCEDURE IF EXISTS [{{../../../conventions.landingAreaObjectLocation}}].[SP_{{name}}];
{{/hasClassification~}}
{{/hasClassification~}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Persistent Staging --}}
{{#each dataObjects}}
{{~#if @first}}# Persistent staging objects
{{/if}}{{#hasClassification classifications "Persistent Staging"}}DROP PROCEDURE IF EXISTS [{{../../conventions.persistentStagingAreaObjectLocation}}].[SP_{{name}}];
{{/hasClassification~}}
{{/each}}
#>


#   Run script - Stored Procedures


<#
{{!-- Landing --}}
{{#each dataObjects}}
{{~#if @first}}# Landing objects
{{/if}}{{#hasClassification classifications "User Managed"}}{{else}}{{#hasClassification classifications "Landing"}}EXECUTE [{{../../../conventions.landingAreaObjectLocation}}].[SP_{{name}}];
{{/hasClassification~}}
{{/hasClassification~}}
{{/each}}
{{!-- Spacer --}}
{{#if false}}


{{else}}


{{/if}}
{{!-- Persistent Staging --}}
{{#each dataObjects}}
{{~#if @first}}# Persistent staging objects
{{/if}}{{#hasClassification classifications "Persistent Staging"}}EXECUTE [{{../../conventions.persistentStagingAreaObjectLocation}}].[SP_{{name}}];
{{/hasClassification~}}
{{/each}}
#>