T-SQL Version Deploy
I would like to introduce to you my solution to run SQL script on database.
You can use this solution as a tool that you can integrate into your methodology of environment (Dev, Test and Production) SQL script deployment.
All SQL script that you would like to run on particular databases, should be at a path that the SQL Server can retch to. If you are working with TFS, SVN, Git or any other CI. Please make sure that you can build a solution that can get all desired file into your path (local or network).
For example, supposed you have TFS, all dev department save SQL script into a folder in the TFS.
The folder contains this version script and older version scripts.
The TFS guys will get all SQL file from certain check-in number to check-in number, and will deploy the files into a network path.
Then you can activate this solution to run all files into the right database from the network path.
Prerequisite
How should each SQL script file look?
Every script should have 2 lines of comment. Each line should start and end with triple “-“.
The first line is should be the version marker and look as follows:
--- Script for version ____________ ---
The second line will be for the script remark:
--- ________________________ ---
These lines of comment will both be stored in a user table - [VerDeploy].TextFromAFile, they will also appear in a summary email at the end of the execution.
Example 1:
Batch Separator- Hard Coded –“GO”.
Each script is split into several parts according to the “batch separator”.
Each “batch” will run separately.
TODO – In the future the “Batch Separator” will be configurable by the user.
CLR Integration –
This solution is works with 2 assemblies.
CLR_Util – Assembly that contains usp_clr_ExecuteByDotNet – A clr stored procedure that executes each script and if there are any errors records them to a table.
The reason that I'm using a .Net in own way to run a SQL script is because of error 111
The main use is to run scripts that get error 111. That’s in the case of create\alter a new View\SP\UFN\etc...
2. RegexFunction – An assembly that contains RegExSplit - clr Table valued function that can separate text
into several rows by regular expression syntax.
Code Information
VerDeploy.usp_Setup_RunScripts(Stored Procedure)– Main Procedure
Parameters:
@DatabaseName (sysname) -- The Database name that you want to execute your script on.
@ScriptPath (NVARCHAR(255)) -- Optional Local Path (Group 1 – At least one).
@MapPath (NVARCHAR(255)) -- Optional Network Path(Group 1 – At least one).
@debug (BIT) -- Print Info massages.
@IsAllFolder (BIT) -- Run all scripts within the specified folder and sub folders.
@MailRecipiants (NVARCHAR(255)) -- Mailing addresses to send results(mail address separated by ;).
VerDeploy.usp_Util_SetAGToAsync(Stored Procedure)- Set Always On availability groups to Asynchronous
If you are planning to run some scripts that will create indexes or will change indexes, the best practice is to change availability groups to asynchronous.
VerDeploy.usp_Util_SetAGToSync(Stored Procedure)- Set Always on availability groups to Synchronous.
VerDeploy.usp_Util_GetVersionRemarks(Stored Procedure)- Get 2 First lines (comments) from etch SQL script and stores it in VerDeploy.TextFromAFile(USER_TABLE).
VerDeploy.usp_Util_RunScript(Stored Procedure)-Run Script Within transaction
VerDeploy.usp_Util_INNER_RunScriptFromFileTable(Stored Procedure)-
VerDeploy.usp_clr_ExecuteByDotNet(CLR Stored Procedure)
RegExSplit(CLR – Function)
VerDeploy.usp_SendMail(Stored Procedure)-Sends summary mail to recipient(s).
VerDeploy.usp_Util_MapNetworkDrive(Stored Procedure)- Map network drive in case that the SQL script are located in network shared path.
VerDeploy.usp_Util_UnMapNetworkDrive(Stored Procedure)- UnMap network drive if a drive has been mapped before.
VerDeploy.usp_Util_CheckFolderExists(Stored Procedure)- Check if root folder exists.
VerDeploy.StoredConfigServerData(USER_TABLE) – Save sp_configure state.
VerDeploy.TextFromAFile(USER_TABLE)- Stores texts from SQL file.
VerDeploy.RunScriptLog(USER_TABLE)- Stores log events for each execution.
How to run
Call VerDeploy.usp_Setup_RunScripts.
