Version Deploy is a set of scripts by Rimer Sharon. All SQL script that you would like to run on databases, should be a path that the SQL Server can reach 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).
Installation should be a snap, just build this project as-is and deploy it on your SQL Server.
Make sure you have system administrator privilege on the SQL server instance.
CLR Assembly RegEx Functions for SQL Server by Phil Factor (t|b)
Version Deploy is licensed under the MIT License.
Every script should have 2 line of comments. Each line should start and end with triple (-). The first line supposed to be the version mark and look like this
--- Script for version ____________ ---The second line will be for the script remark:
--- ________________________ ---Both 2 lines will stored in user table - [VerDeploy].TextFromAFile, this will be showed in the summery mail at the end of the run.
Hard Coded –“GO”
Each script is disassembled to several parts depends on the “batch separator” count.
Each batch will run separately.
TODO – In the future, the “Batch Separator” will be configured by the user.
This solution is working with 2 assemblies.
- CLR_Util – Assembly that Contains usp_clr_ExecuteByDotNet - CLR Stored Procedure that runs script and catches if there are any errors in a table. 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...
- RegexFunction - Assembly that Contains RegExSplit - CLR table-valued function that can separate text into several rows by regular excretion syntax.
This assembly has been created by Phil Factor ([t] (https://twitter.com/Phil_Factor)| [b] (https://www.simple-talk.com/author/phil-factor)). More info
- VerDeploy.usp_Setup_RunScripts(Stored Procedure)– Main Procedure
@DatabaseName (sysname) -- The Database name that you want to run your scrupt on.
@ScriptPath (NVARCHAR(255)) -- Optional Local Path (Group 1 – At list one).
@MapPath (NVARCHAR(255)) -- Optional Network Path(Group 1 – At list one).
@debug (BIT) -- Print Info massages.
@IsAllFolder (BIT) -- Run all scripts within the specified folder and sub folders.
@MailRecipients (NVARCHAR(255)) -- Mailing addresss to send results.
- 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. Recommendations for Index Maintenance with AlwaysOn Availability Groups
- 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 stored 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_Util_INNER_RunScriptFromFileTable(Stored Procedure)-
- VerDeploy.usp_SendMail(Stored Procedure)-Send summery mail to recipient.
- VerDeploy.usp_Util_MapNetworkDrive(Stored Procedure)- Map network drive in case that the SQL script are located in network shred path.
- VerDeploy.usp_Util_UnMapNetworkDrive(Stored Procedure)- UnMap network drive if a drive have 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)- Stored texts from SQL file.
- VerDeploy.RunScriptLog(USER_TABLE)- Stored log events for etch run.