Skip to content
ADL Docs
App Design Patterns

SQL Server PSA

The SQL Server Persistent Staging Area (PSA) starter solution introduces two ways to derive data delta / differential from a sample operational system using Stored Procedures.

The PSA is a historized (time-variant) archive of all data changes that were presented to the data solution. It is a foundational part of many data solutions. Much like a transaction log, the PSA ensures that downstream solutions can be easily modified if and when required.

Persistent Staging Area

By default, changes are detected by performing a direct comparison, a Full Outer Join, between the PSA and the sample operational source system. Any changes detected will be stored in the PSA. Alternatively, a second template can be used to first land (‘stage’) the data in the landing area, and perform the Full Outer Join there.

These are just two of many ways that technical change data capture can be implemented. Templates can be added and modified to achieve the desired outcome for different approaches.

The sample data and generated code apply to SQL Server family databases; SQL Server, Managed Instances, and Azure SQL.

This sample also includes the generation of:

  • Sample data
  • Deployment script (deployment.ps1)
  • Registration into the control framework (optional)
  • Documentation

Additional information:

  • This sample is prepared for integration with the DIRECT control framework. It is not necessary or mandatory to use this, and the code can be easily removed from the templates if required.

Deploying the sample

Section titled “Deploying the sample”

When selecting this sample in ADL, the contents are geared towards having a local instance of SQL Server. Please check the Connections to make sure these are referring to the intended local SQL Server, Managed Instance or Azure SQL database.

After generating the output using the ADL Code Generator, a Powershell deployment script (deployment.ps1) is available in the output/databases folder.

Running this script, for example using Visual Studio Code or equivalent will create the tables, stored procedures, and deploy the sample data.

Terminal window
# Run from the output directory
.\Databases\Deployment\deployment.ps1

After this, the procedures are ready to be run, for example by running the following SQL:

EXEC [SP_STG_PROFILER_CUSTOMER_CONTACT]

Troubleshooting

Section titled “Troubleshooting”
  • The Powershell deployment script may be blocked by some virus checkers, please check these settings if the file is not appearing and the virus checker reports an issue when generating code.