SQL Server Code Deployment from the File System with a Custom Stored Procedure

By: Prashant Shekhar   |   Comments (5)   |   Related: > DevOps

Problem

Our team wants to develop a Source Control process to apply SQL Server database code changes in various environments. As you can imagine the changes can relate to any of the following: creating new objects (tables, views, functions, stored procedures, etc.), altering objects, etc. Moreover, it was also important to define a Folder Structure to have the scripts run in a particular order, validating the dependencies are met. So how can we achieve these results?

Solution

Before we go into the details, let me layout the folder structure we used that basically mimicked SQL Server Management Studio.

Here's how if looks like

We are using TFS in our company for the source control and for each release, we'll export the T-SQL scripts (*.sql files) into this working folder structure.

As you might imagine, in the current exhibit, the sample is for Release 1.1 which is depicted by the Folder "Release_1_1".

Now let's look at what goes in each of the Folders:

• 0_TABLES is numbered as such to be on top of the hierarchy so that it serves as the first folder to be executed. It has several sub-folders and we'll discuss what they should contain.
  • 1_Columns - CREATE TABLE, ALTER TABLE, etc. scripts.
  • 2_Keys - Definition for Primary and Foreign Keys.
  • 3_Constraints - Table constraints.
  • 4_Triggers - Table triggers.
  • 5_Indexes - Index definitions.
  • 6_Statistics - User defined statistics.
• 1_VIEWS - Views on the base table(s) created in the above step.
• 2_PROCS - CREATE PROCEDURE, ALTER PROCEDURE, etc. scripts.
• 3_FUNCS - User defined functions.
• 4_DBTRIGS - Database Triggers.
• 5_TYPES - User Defined Data Types.
• 6_RULES - User Defined Rules on columns for tables.
• 7_DEFAULTS - Defaults bound to columns for tables.
• 8_DML - DML statements to populate any Reference Table(s).
• 9_JOBS - SQL Server Agent Job definitions.

Once the above folders contain the required *.sql files for each database object definition, the following stored procedure can then be put into place to execute the scripts in sequence. For our testing, I created a dummy database [DBATools] and created the stored procedure there.

The stored procedure takes these three parameters:

• @server - the Fully Qualified Server Name
• @database - the Database name for the deployment
• @release - the version number of the release

I want to quickly help understand these flags used in the dynamic-sql for the DIR command

• /B - Uses bare format (no heading information or summary).
• /S - Displays files in specified directory and all subdirectories.

I would recommend running the shell command beforehand to see the output.

-- START of the procedure
USE [DBATools]
GO

/*######################################################################################

Author: Prashant Shekhar
Create date: Year 2015

Description: Stored Procedure to serve as Deployment Manager for a given Release 
in TFS sprints

Sample Run: EXEC sp_DeploymentManager 'SRVR_name'
,'DB_name'
,'Release_1_1'

Revision History:
================ 
Date Author Comments
---------- -------- --------
MM/DD/YYYY PSHEKHAR Initial Creation

#######################################################################################*/

CREATE PROCEDURE sp_DeploymentManager @server VARCHAR(100)
,@database VARCHAR(100)
,@release VARCHAR(100)
AS

SET NOCOUNT ON

EXEC sp_configure 'xp_cmdshell'
,1;

RECONFIGURE;

DECLARE @root VARCHAR(100)
DECLARE @statement VARCHAR(300)
DECLARE @StagingFilePath TABLE (fpath VARCHAR(300))
DECLARE @fpath VARCHAR(300)
DECLARE @sqlcmd VARCHAR(800)

SET @root = 'C:\SourceControl\'
SET @statement = 'EXEC master.sys.xp_cmdshell ''DIR "' + @root + @release + '" 
/B /S'''

INSERT INTO @StagingFilePath
EXEC (@statement)

DECLARE DeploymentManager CURSOR
FOR
SELECT fpath
FROM @StagingFilePath
WHERE fpath LIKE '%sql'
ORDER BY fpath

OPEN DeploymentManager

FETCH DeploymentManager
INTO @fpath

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlcmd = 'EXEC master.sys.xp_cmdshell ''SQLCMD -S"' + @server + '" -d"' + 
@database + '" -E -i"' + @fpath + '" >> "' + @root + @release + 
'\DeployLog.txt"'''

EXEC (@sqlcmd)

FETCH DeploymentManager
INTO @fpath
END

CLOSE DeploymentManager

DEALLOCATE DeploymentManager

EXEC sp_configure 'xp_cmdshell'
,0;
RECONFIGURE;

GO
-- END of the procedure

Note that the output from each script execution is captured in the log file - "DeployLog.txt". This is helpful to troubleshoot any exceptions during deployment.

Next Steps

• Setup a directory structure for your code deployments to match the image above.
• Create some files to work through a test deployment in one of your Sandbox, Development or Test environments.
• Review the output and results in your database post deployment.
• Once you are comfortable with the process consider options to automate your deployments with SQL Server Agent Jobs.
• Check out all the SQL Server Developer Tips.

About the author

Prashant Shekhar is employed as a DBA with BioTelemetry; specializing in HA solutions comprised of SQL Clustering, Replication and Log Shipping.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips