Getting started
AliaSQL is a command line tool for database deployments
It depends on having a database scripts folder with these 3 folders:
- Create
- Update
- TestData
AliaSQL.exe [Action] [Database Server] [Scripts path]
Create database and run all scripts in Create folder. Logs to usd_AppliedDatabaseScript
AliaSQL.exe Create .\sqlexpress ./scripts
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. Logs to usd_AppliedDatabaseScript
AliaSQL.exe Update .\sqlexpress ./scripts
Drop and recreate database then run all scripts in Create and Update folders. Logs to usd_AppliedDatabaseScript
AliaSQL.exe Rebuild .\sqlexpress ./scripts
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 ./scripts
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
AliaSQL.exe Baseline .\sqlexpress ./scripts
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
It is recommended to prefix your scripts with numbers to ensure the run order (0001-AddTestTable.sql, 0002-AddSecondTable.sql).
In Create mode each script in the Create folder that hasn't already been ran 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 and Update folder that haven't already been ran 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 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.
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.
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.
--NOTRANSACTIONThe 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/Seed folders and a simple program to execute AliaSQL.exe from Visual Studio. Here is an example of this https://github.com/ericdc1/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.