Skip to content

Getting started

Jump to bottom Edit New page
Mark Gidman edited this page Jul 14, 2016 · 19 revisions

AliaSQL is a command line tool for database deployments

It depends on having a database scripts folder with these 4 folders:

  • Create
  • Update
  • Everytime
  • RunAlways
  • TestData (optional, required for TestData action)

Example usage:

AliaSQL.exe [Action] [Database Server] [DatabaseName] [Scripts path] [Username] [Password]

Create Action

  • Creates database and runs all scripts in Create folder.
  • Runs all new scripts and changed scripts in Everytime folder.
  • Runs all scripts in RunAlways folder.
  • Logs to usd_AppliedDatabaseScript 
AliaSQL.exe Create .\sqlexpress Demo ./scripts

Update Action

  • 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 that have not yet been ran or have been modified.
  • Runs all scripts in RunAlways folder.
  • Logs to usd_AppliedDatabaseScript 
AliaSQL.exe Update .\sqlexpress Demo ./scripts

Rebuild Action

  • Drops and recreates database then runs all scripts in Create and Update folders.
  • Runs all scripts in Everytime folder.
  • Runs all scripts in RunAlways folder.
  • Logs to usd_AppliedDatabaseScript 
AliaSQL.exe Rebuild .\sqlexpress Demo ./scripts

TestData Action

  • Run all scripts in TestData folder that have not yet been ran - expects target database to already exist.
  • Logs to usd_AppliedDatabaseTestDataScript 
AliaSQL.exe TestData .\sqlexpress Demo ./scripts

Baseline Action

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 (but does not execute) all scripts in Create and Update folders that have not yet been ran - expects database to already exist.
  • Adds the usd_AppliedDatabaseScript table and a record of all scripts to an existing database.
  • Logs to usd_AppliedDatabaseScript
AliaSQL.exe Baseline .\sqlexpress Demo ./scripts

Kickstarter

It is recommended to start with the AliaSQL Kickstarter that creates the Create, Update, and TestData folders and provides the Visual Studio runner, create an empty C# console app then install AliaSQL.Kickstarter from the package manager console:

Here is an example of a Kickstarter project

Here is what the Visual Studio runner looks like

Operation

It is recommended to prefix your scripts with numbers to ensure the run order (0001-AddTestTable.sql, 0002-AddSecondTable.sql). You can nest folders in the Create, Update, and Everytime folders if you want to organize your scripts in some way.

Create and Update Folders

Create and Update scripts are for tables, constraints, etc which are altered via a series of transitions from one instruction to the next. You create the table with script #1 and add a column in script #2.

Everytime and RunAlways Folders

Both Everytime and RunAlways scripts are for views/functions/stored procedures which are more similar to your client application code. This allows you to treat them like source code and edit them over time. You create a stored procedure in script #1 and edit that same script over time as the need arises.

Differences between Everytime and RunAlways

AliaSQL keeps track of the contents of scripts in the Everytime folder. New or modified scripts in this folder will run every time. See some suggestions for creating Everytime scripts.

Scripts in the RunAlways folder are run every time even if the files don't change.

Modes (aka Actions)

In Create mode, each script in the Create folder and Everytime folders will be executed. After it executes, a log entry is made to the usd_AppliedDatabaseScript table with the script name and date it was run.

In Update mode each script in the Create folder, Update folder, RunAlways, and Everytime that haven't already been ran will be executed. Changed scripts in the Everytime folder will also be executed. After it executes, a log entry is made to the usd_AppliedDatabaseScript table with the script name and date it was run.

In Rebuild mode the target database will be dropped, recreated, then the Create and Update commands will run.

In TestData mode each script in the TestData folder that hasn't already been ran will be executed. After it executes, a log entry will be made to the usd_AppliedDatabaseTestDataScript table with the script name and date it was run. Generally these scripts are for local development and would contain test data not for production.

In Baseline mode each script in the Create folder and Update that haven't already been ran will be logged to the usd_AppliedDatabaseScript table with the script name and date it was run. The scripts will not execute. If you have an existing database that you created scripts from and want to use it with AliaSQL this mode is useful to "catch up" the existing database by logging that all scripts have already been applied.

Transaction Support

AliaSQL runs the SQL scripts in a transaction by default. If a script contains commands that are not supported in transactions it will run script in separate transactions separated by the GO keyword.

Known Issues

There are likely some additional commands in SQL scripts that will fail when running in a transaction but aren't on Microsoft's list of commands not supported in transactions such as "sp_fulltext_database". These need identified and handled on as case by case basis. In the mean time you can add a comment in your SQL script with one of the reserved words like this to force the script to run in nontransactional mode.

     --NOTRANSACTION

Notes

The sample code has Psake set up to build the code, run the (limited) unit tests, create the nuget package, and zip it all up. This is designed to work with Visual Studio 2013 and SQL Server 2012. It should work against SQL Server 2008 and will compile against older Visual Studio versions with a change in the /p:VisualStudioVersion= setting in default.ps1.

I like to create a console application in my solution that contains the Create/Update/TestData folders and a simple program to execute AliaSQL.exe from Visual Studio. Here is an example of this https://github.com/ClearMeasure/AliaSQL/blob/master/source/Database.Demo/Program.cs There is a Nuget package (AliaSQL.KickStarter) that will set it up with the necessary folders and the program in a (hopefully empty) console application to make this as easy as possible.

There is an example database console application with sample scripts available in the source. It includes helper batch files to Rebuild, Update, and populate Test Data to the Demo database.

There is also a database diff batch file that will compare the Demo database against the current set of Create and Update scripts and will generate a .sql file with the schema changes. Redgate SQL Compare is the better choice but this is free using SQLPackage.exe that comes with SQL Server Express.

We have a demo project with AliaSQL, ASP.Net MVC, some sample scripts, and even a boring video showing its usage here: https://github.com/ericdc1/AliaSQL-Demo

Add a custom footer
Add a custom sidebar
Clone this wiki locally