How to use AliaSQL

AliaSQL

Introducti​on I am looking to improve our database change management. For years we have worked off of a common "development" database that we periodically compared with Redgate SQL Compare and generated change scripts. For the most part we have been doing continuous deployment - unless the code corresponded with a database change. In that case it required manual intervention. So we introduce AliaSQL database change management tool. AliaSQL is a command line tool for database deployments.


How to start with AliaSQL


Following steps to start working with AliaSQL:


To get started, create an empty C# console app then install Nuget package AliaSQL.Kickstarter from the package manager console. It will create Scripts folder, AliaSQL.exe and update App.config, Program.cs files. Scripts folder contains 4 other folders Create, Everytime, TestData & Update. Change DatabaseName & DatabaseServer App.config key as per your database. Add schema_compare.ps1 power shell script for database compare and get database change script file. You can get schema_compare.ps1 file from https://github.com/ClearMeasure/AliaSQL . Run schema_compare.ps1 file in Powershell ISE to get database change script under scripts\Update folder with .sql.temp extension. Verify it and convert it to .sql extension and include in project. And put your all Static Data scripts under Update folder in proper sequence. Run console app and it will give you different command options to apply database changes in your database.

Scripts location decision

Once Create command execute it will create database in SQL server if not exist and execute all scripts under Create folder. It will also log executed scripts to usd_AppliedDatabaseScript SQL table.


Put your all schema_compare.ps1 generated database change scripts under Update folder. Once Update command execute it will execute all scripts under Update folder. It will also log executed scripts to usd_AppliedDatabaseScript SQL table so it will not execute second time of Update command execution.


Put your all Static Data scripts under Everytime folder with proper sequence.

Command Usage

AliaSQL console application provide 5 different type of commands:


1. Create: Create database and run all scripts in Create folder. Runs all new scripts and changed scripts in Everytime folder. Logs to usd_AppliedDatabaseScript sql table.​


2. Update: Run all scripts in Create and Update folders that have not yet been ran. If target database does not already exist it will be created. Runs all scripts in Everytime folder. Logs to usd_AppliedDatabaseScript sql table.


3. Rebuild: Drop and recreate database then run all scripts in Create and Update folders. Runs all scripts in Everytime folder. Logs to usd_AppliedDatabaseScript sql table.


4. TestData: Run all scripts in TestData folder that have not yet been ran - expects target database to already exist. Logs to usd_AppliedDatabaseTestDataScript sql table.


5. Baseline: Logs (but does not execute) all scripts in Create and Update folders that have not yet been ran - expects database to already exist. This adds the usd_AppliedDatabaseScript table and a record of all scripts to an existing database. This is useful when you have an existing database that you want to bring into change management without running all of your current scripts against it. Logs to usd_AppliedDatabaseScript.