T-SQL Version Deploy

March 1, 2017

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.

 

Download the solution

 

Prerequisite

  1. Database Mail

  2. CLR Integration

 

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.

  1. 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.

This assembly has been created by Phil Factor (t|b). More info

 

Code Information

  1. 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 ;).

 

 

  1. 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

 

  1. VerDeploy.usp_Util_SetAGToSync(Stored Procedure)- Set Always on availability groups to Synchronous.

  2. VerDeploy.usp_Util_GetVersionRemarks(Stored Procedure)- Get 2 First lines (comments) from etch SQL script and stores it in VerDeploy.TextFromAFile(USER_TABLE).

  3. VerDeploy.usp_Util_RunScript(Stored Procedure)-Run Script Within transaction

    1. VerDeploy.usp_Util_INNER_RunScriptFromFileTable(Stored Procedure)-

      1. VerDeploy.usp_clr_ExecuteByDotNet(CLR Stored Procedure)

      2. RegExSplit(CLR – Function)

  4. VerDeploy.usp_SendMail(Stored Procedure)-Sends summary mail to recipient(s).

  5. VerDeploy.usp_Util_MapNetworkDrive(Stored Procedure)- Map network drive in case that the SQL script are located in network shared path.

  6. VerDeploy.usp_Util_UnMapNetworkDrive(Stored Procedure)- UnMap network drive if a drive has been mapped before.

  7. VerDeploy.usp_Util_CheckFolderExists(Stored Procedure)- Check if root folder exists.

  8. VerDeploy.StoredConfigServerData(USER_TABLE) – Save sp_configure state.

  9. VerDeploy.TextFromAFile(USER_TABLE)- Stores texts from SQL file.

  10. VerDeploy.RunScriptLog(USER_TABLE)- Stores log events for each execution.

How to run

Call VerDeploy.usp_Setup_RunScripts.

 

 

Please reload

Featured Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Recent Posts

October 31, 2017

October 29, 2017

Please reload

Archive
Please reload

Search By Tags