BizTalk Server: How to (un)deploy T-SQL scripts with BTDF
Introduction
For deployment of BizTalk Applications often the BizTalk Deployment Framework (or BTDF) is used. Besides deploying BizTalk artifacts, it can also be used for deployment of other things such as T-SQL scripts.
In English, the reverse of deploying a script might perhaps be removal - whilst correct that would arguably lose clarity in the context of this article. Therefore the word undeployment will be used.
This article describes the steps to prepare deployment and undeployment of T-SQL scripts with BTDF.
Store T-SQL scripts in your solution
The first step is to store your T-SQL scripts in a solution folder of the Visual Studio solution which contains your BizTalk projects and Deployment project. A convenient approach is to create a solution folder called ‘SQLScripts’ under your Deployment project. As the scripts will be preprocessed before they are executed, we name the scripts following a convention:
Master.<CreateDBLogic | InsertData | RemoveDBLogic>.sql
Examples:
Master.CreateDBLogic.sql
Master.InsertData.sql
Master.RemoveDBLogic.sql
If you follow this naming convention, you always have a maximum of 3 T-SQL scripts per BizTalk solution. Of course you can also store the scripts per object type - Table, View, Stored Procedure, etc. etc. This way you'll have more scripts, but you'll have a better overview of which scripts will be deployed or undeployed.
Perform the following steps:
- Open your BizTalk solution
- Navigate to the folder which contains the Deployment project
- Create a solution folder named ‘SQLScripts’
The screenshot below shows a solution which contains BizTalk projects and a BTDF project for deployment. The BTDF project, called Deployment, contains a solution folder named ‘SQLScripts’. This folder contains 6 T-SQL scripts. Three of those scripts are ‘Master’ T-SQL scripts for deployment, the other ones are placeholders for undeployment.
Create variables in the Settings File
Next we’ll create variables in the SettingsFileGenerator.xml file from the Deployment project. These variables contain the name of the server and the database on which the T-SQL scripts will become (un)deployed; they will be used later in this article.
Perform the following steps:
- Open the SettingsFileGenerator.xml file in Excel
- Create a variable called ‘SQLServer’ (without quotes)
- Create a variable called ‘SQLScripts.Database’ (without quotes)
- Give each variable, per environment, the appropriate value
The screenshot below shows a settings file, which was opened in Excel and contains both variables.
Enabling a variable in the Deployment project
Now we need to make sure that we can use the previously created variable SQLServer in the BTDF project file. Therefore we need to add this variable to an already existing ItemGroup. The steps only need to be performed for variables that will be used in the BTDF project file.
Perform the following steps:
- Open the project file. It is located in the solution folder which was created for the Deployment project and has the extension ‘btdfproj’.
- Search for the ItemGroup which contains an element called PropsFromEnvSettings.
- Add the earlier created variable SQLServer to the semicolon separated list of variables
Afterwards the Item Group will look like below:
1. <ItemGroup>
2. <PropsFromEnvSettings Include="SsoAppUserGroup;SsoAppAdminGroup;SQLServer" />
3. </ItemGroup>
Prepare the 'Master' T-SQL scripts
This step describes how the Master T-SQL scripts will be prepared for preprocessing. This step is important, because it prepares the T-SQL scripts for using the variables from the settings file.
Say we have a script which creates a user in a certain SQL Server database; the script looks like below:
01.USE [NorthWindDB]
02.GO
03./****** Object: User [BizTalk] Script Date: 3/7/2014 11:51:57 AM ******/
04.CREATE USER [BizTalk] FOR LOGIN [NORTHWIND\BizTalk Application Users] WITH DEFAULT_SCHEMA=[dbo]
05.GO
06.
07.SET ANSI_NULLS ON
08.GO
09.SET QUOTED_IDENTIFIER ON
10.GO
As you can see, the script contains a reference to a database called NorthwindDB and to a Windows Group called NORTHWIND\BizTalk Application Users.
As the values (especially for the Windows Group) might be different in each DTAP environment, the BTDF settings file contains variable which can be used here. So for this script, all you need to do is to replace each occurrence of the database and the Windows Group and replace it with the corresponding variable in the settings file.
Perform the following steps:
- Find all occurrences where the database (NorthwindDB) is used and replace them with the variable from the settings file (${SQLScripts.Database})
- Find all occurrences where the Windows Group (NORTHWIND\BizTalk Application Users) is used and replace them with the variable from the settings file (${SsoAppUserGroup})
- Store the script following the earlier mentioned conventions for Master T-SQL scripts
Afterwards the script will look like below:
01.USE [${SQLScripts.Database}]
02.GO
03./****** Object: User [BizTalk] Script Date: 3/7/2014 11:51:57 AM ******/
04.CREATE USER [BizTalk] FOR LOGIN [${SsoAppUserGroup}] WITH DEFAULT_SCHEMA=[dbo]
05.GO
06.
07.SET ANSI_NULLS ON
08.GO
09.SET QUOTED_IDENTIFIER ON
10.GO
Copy T-SQL scripts to File System
To be able to execute the T-SQL scripts during (un)deployment, the scripts need to be copied to the file system of the server on which the (un)deployment becomes executed. Therefore we need to modify an already existing Target called ‘CustomRedist’.
Perform the following steps:
- In the already opened deployment project file, navigate to the Target called ‘CustomRedist’
- Add the following XML to the ItemGroup:
<MakeDir Directories="$(RedistDir)\SQLScripts" />
<!-- Force MSBuild to expand the item spec into physical file specs -->
<CreateItem Include="SQLScripts\*.*">
<Output TaskParameter="Include" ItemName="SQLScripts" />
</CreateItem>
<Copy DestinationFolder="$(RedistDir)\SQLScripts\%(RecursiveDir)" SourceFiles="@(SQLScripts)"/>
Afterwards the Target will look like below:
<Target Name="CustomRedist">
<MakeDir Directories="$(RedistDir)\SQLScripts" />
<!-- Force MSBuild to expand the item spec into physical file specs -->
<CreateItem Include="SQLScripts\*.*">
<Output TaskParameter="Include" ItemName="SQLScripts" />
</CreateItem>
<Copy DestinationFolder="$(RedistDir)\SQLScripts\%(RecursiveDir)" SourceFiles="@(SQLScripts)"/>
</Target>
Preprocess the ‘Master’ T-SQL scripts
We have come to the point where we will preprocess the Master T-SQL scripts. To do so, we need to create a Custom Post Deploy Target to the project file. This Target will contain the commands to preprocess the Master T-SQL scripts. After this step the T-SQL scripts are ready to become deployed.
Perform the following steps:
Navigate to the lower end of the project file, just before the Project closing tag (</Project>) and create a Target called ‘CustomPostDeployTarget’. Therefore you’ll have to add the following XML:
<Target Name="CustomPostDeployTarget"> </Target>Next add a line for each T-SQL file, which you want to deploy. Such a line looks like this:
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives /i:"$(MSBuildProjectDirectory)\SQLScripts\Master.<Name of T-SQL deploy script>" /o:"$(MSBuildProjectDirectory)\SQLScripts\<Name of T-SQL deploy script>" /d:CurDir="$(MSBuildProjectDirectoryParent)" /s:"$(SettingsFilePath)"" ContinueOnError="false" />
Afterwards the XML might look like this:
<Target Name="CustomPostDeployTarget">
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives
/i:"$(MSBuildProjectDirectory)\SQLScripts\Master.CreateDBLogic.sql"
/o:"$(MSBuildProjectDirectory)\SQLScripts\CreateDBLogic.sql"
/d:CurDir="$(MSBuildProjectDirectoryParent)"
/s:"$(SettingsFilePath)"" ContinueOnError="false" />
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives
/i:"$(MSBuildProjectDirectory)\SQLScripts\Master.InsertData.sql"
/o:"$(MSBuildProjectDirectory)\SQLScripts\InsertData.sql"
/d:CurDir="$(MSBuildProjectDirectoryParent)"
/s:"$(SettingsFilePath)"" ContinueOnError="false" />
</Target>
Deployment of the T-SQL scripts
In the previous step we created a Custom Deploy Target to preprocess the Master T-SQL scripts. This Target will become extended with the commands which will execute the T-SQL scripts, which were generated by the preprocess step.
Perform the following steps:
Navigate to the Custom Deploy Target called ‘CustomPostDeployTarget’.
Next add a line to the Custom Deploy Target for each T-SQL file, which you want to deploy. Such a line looks like this:
<Exec Command="sqlcmd -S $(SQLServer) -i"SQLScripts\<Name of T-SQL deploy script>"" />
Afterwards the XML might look like this. For better readability of this article, the preprocess scripts of the T-SQL scripts, which are also part of this Target, are replaced by a placeholder:
<Target Name="CustomPostDeployTarget">
<!-- Placeholder for preprocess scripts (for better readability of this document) -->
<Exec Command="sqlcmd -S $(SQLServer) -i"SQLScripts\CreateDBLogic.sql"" />
<Exec Command="sqlcmd -S $(SQLServer) -i"SQLScripts\InsertData.sql"" />
</Target>
The command ‘sqlcmd’ is already in your path (PATH variable), so you don’t have to enter the entire folder where that command can be found. The name of the server, on which the commands become executed, is defined by that earlier created variable SQLServer.
Undeployment of the T-SQL scripts
The final step is to prepare for undeploying the T-SQL stuff when the BizTalk Application becomes undeployed. Undeployment scripts contain the reverse actions of the deployment scripts like revoking user permissions to execute Stored Procedures, dropping Stored Procedures and dropping (content of) tables. Also here counts that the earlier defined variable SQLServer is used to define on which server the T-SQL scripts will become executed.
To achieve all this we need to add a Custom Post Undeploy Target.
Perform the following steps:
Navigate to the lower end of the project file, just before the Project closing tag (</Project>) and create a Target called ‘CustomPostDeployTarget’. Therefore you’ll have to add the following XML:
<Target Name="CustomPostUnDeployTarget"> </Target>Now add a line to preprocess the Master T-SQL script for undeployment.
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives /i:"$(MSBuildProjectDirectory)\SQLScripts\Master.<Name of T-SQL undeploy script>" /o:"$(MSBuildProjectDirectory)\SQLScripts\<Name of T-SQL undeploy script>" /d:CurDir="$(MSBuildProjectDirectoryParent)" /s:"$(SettingsFilePath)"" ContinueOnError="false" />Next add a line for each T-SQL file, which you want to execute for undeployment the T-SQL stuff. Such a line looks like this:
<Exec Command="sqlcmd -S $(SQLServer) -i"SQLScripts\<Name of T-SQL undeploy script>"" />
Afterwards the custom undeploy Target may look like this:
<Target Name="CustomPostUnDeployTarget">
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives
/i:"$(MSBuildProjectDirectory)\SQLScripts\Master.RemoveDBLogic.sql"
/o:"$(MSBuildProjectDirectory)\SQLScripts\RemoveDBLogic.sql"
/d:CurDir="$(MSBuildProjectDirectoryParent)"
/s:"$(SettingsFilePath)""
ContinueOnError="false" />
<Exec Command="sqlcmd -S $(SQLServer) -i"SQLScripts\RemoveDBLogic.sql"" />
</Target>
Suggestions
Although the solution above works fine, you might consider a couple improvements:
Adding custom databases to the BizTalk backup job
In case you create for example an archive database with your SQL scripts, you might want to have this database added to the BizTalk backup job. Normally this can be done manually by following this article, but it can also be achieved by adding some more scripts and commands to your deployment project.
After the custom database has been created, a couple of objects has to be created for the BizTalk backup to work. The folder in which BizTalk is installed contains scripts which creates those objects. The names of these scripts are:
- Backup_Setup_All_Tables.sql
- Backup_Setup_All_Procs.sql
So we need to retrieve the folder which contains those scripts, but we also need the name of the server where the BizTalk Management database resides and we need to know the name of the BizTalk Management database. This information can be retrieved from the Registry with MSBuild commands.
Create a Property Group like below and add it to your project file.
<PropertyGroup>
<MgmtDBServer>$([MSBuild]::GetRegistryValueFromView('HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration', 'MgmtDBServer', null, RegistryView.Registry64, RegistryView.Registry32))</MgmtDBServer>
<MgmtDBName>$([MSBuild]::GetRegistryValueFromView('HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration', 'MgmtDBName', null, RegistryView.Registry64, RegistryView.Registry32))</MgmtDBName>
<SQLScriptPath>$([MSBuild]::GetRegistryValueFromView('HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration', 'SQLScriptPath', null, RegistryView.Registry64, RegistryView.Registry32))</SQLScriptPath>
</PropertyGroup>
We now have 3 variables named MgmtDBServer, MgmtDBName and SQLScriptPath with the needed information. These variables can be used in the project file by surrounding them with braces and a dollar sign, like this: $(MgmtDBServer). We’ll use them later in this paragraph.
Next add the following scripts to the solution folder which contains the other T-SQL scripts:
- Master.AddToBizTalkBackup.sql
- Master.RemoveFromBizTalkBackup.sql
Add the following command to the Master.AddToBizTalkBackup.sql script:
01.INSERT INTO [dbo].[adm_OtherBackupDatabases]
02. ([DefaultDatabaseName]
03. ,[DatabaseName]
04. ,[ServerName]
05. ,[BTSServerName])
06. VALUES
07. ('${SQLDatabase}'
08. ,'${SQLDatabase}'
09. ,'${SQLServer}'
10. ,'${SQLServer}')
11.GO
Add the following command to the Master.RemoveFromBizTalkBackup.sql script:
1.DELETE [dbo].[adm_OtherBackupDatabases]
2. WHERE [DefaultDatabaseName] = '${SQLDatabase}'
3. AND [DatabaseName] = '${SQLDatabase}'
4. AND [ServerName] = '${SQLServer}'
5. AND [BTSServerName] ='${SQLServer}'
6.GO
As you will have noticed, both scripts contain placeholders which refer to settings in SettingsFileGenerator.xml, so the scripts have to be preprocessed. Therefore you’ll have to add the following XML to the CustomPostDeploy Target:
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives /i:"$(MSBuildProjectDirectory)\SQLScripts\Master.AddToBizTalkBackup.sql" /o:"$(MSBuildProjectDirectory)\SQLScripts\AddToBizTalkBackup.sql" /d:CurDir="$(MSBuildProjectDirectoryParent)"
/s:"$(SettingsFilePath)"" ContinueOnError="false" />
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives /i:"$(MSBuildProjectDirectory)\SQLScripts\Master.RemoveFromBizTalkBackup.sql" /o:"$(MSBuildProjectDirectory)\SQLScripts\RemoveFromBizTalkBackup.sql"
/d:CurDir="$(MSBuildProjectDirectoryParent)"
/s:"$(SettingsFilePath)"" ContinueOnError="false" />
The database becomes added by executing the following from the CustomPostDeploy target, so add this XML to it:
<Exec Command="sqlcmd -S $(SQLServer) -d $(SQLDatabase) -i"$(SQLScriptPath)\Backup_Setup_All_Tables.sql"" />
<Exec Command="sqlcmd -S $(SQLServer) -d $(SQLDatabase) -i"$(SQLScriptPath)\Backup_Setup_All_Procs.sql"" />
<Exec Command="sqlcmd -S $(MgmtDbServer) -d $(MgmtDbName) -i"SQLScripts\AddToBizTalkBackup.sql"" />
As you can see all previously retrieved values are used in these statements.
To remove the database from the BizTalk backup during undeployment, you need to add the following to the CustomPostUndeploy target:
<Exec Command="sqlcmd -S $(MgmtDbServer) -d $(MgmtDbName) -i"SQLScripts\RemoveFromBizTalkBackup.sql"" />
A few more tips on this topic:
- Make sure the database is in Full Recovery Model, read this article
- Force a Full backup after the database has been added to the BizTalk backup, read this article
IMPORTANT: The fact that something is technically possible, does not imply that it's a good idea. Messing around with the BizTalk databases is NOT a good idea. Therefore you might consider to manually insert/remove that record in the adm_OtherBackupDatabases table.
Create a batch file to execute the SQL scripts
In the article is described how to execute the SQL scripts in the Custom Deploy Targets. This led to rather long command lines. By creating a batch file, you reduce the length of the command line, which results in a more simple and better readable command line.
To achieve this you have to perform the following steps:
- Create a template for the batch file and save it in the root folder of your Deployment project. Name it for example ExecuteDbScript.txt
- In the template enter the command line to execute the SQL script
sqlcmd -S ${SQLServer} -i"SQLScripts\%1"
- Preprocess the ExecuteDbscript.txt file with the following command in the Custom Post Deploy Target.
<Exec Command=""$(DeployTools)\xmlpreprocess.exe" /f /c /noDirectives
/i:"$(MSBuildProjectDirectory)\SQLScripts\ExecuteDbScript.txt"
/o:"$(MSBuildProjectDirectory)\SQLScripts\ExecuteDbScript.bat"
/d:CurDir="$(MSBuildProjectDirectoryParent)"
/s:"$(SettingsFilePath)"" ContinueOnError="false" />
- In the Custom Post Deploy Target replace the command line to execute the SQL scripts to
<Exec Command="SQLScripts\ExecuteDbScript.bat SQLScripts\CreateDBLogic.sql" />
<Exec Command="SQLScripts\ExecuteDbScript.bat SQLScripts\InsertData.sql" />
Delete generated SQL scripts
Another step which had to be performed is generating the SQL scripts which will be deployed. These scripts were based on Master templates which contained a placeholder for the database against which the scripts were executed. As these scripts are generated during the deployment of the installation package, the package is not aware of the existence of the files and would not removed them during uninstallation. The result is that the file system is not fully cleaned up after uninstall.
By deleting the generated SQL scripts with the Custom Undeployment Target, the file system will be cleaned up nicely, without having any left overs. Add the following XML to the Custom Post Undeploy Target, to delete the generated SQL scripts:
<Delete Files="SQLScripts\CreateDBLogic.sql" />
<Delete Files="SQLScripts\InsertData.sql" />
<Delete Files="SQLScripts\RemoveDBLogic.sql" />
TIP: By renaming the Master templates of the SQL scripts to the .txt extension, you can simple delete all generated SQL files with only one command:
<Delete Files="SQLScripts\*.sql" />
Use Comments for more information during (un)deployment
To make things easier to understand for users who are less experienced with BTDF you could add informational messages to the Custom Deploy Targets. When you start a message with '%0a' an extra new line is added for better readability. Below is an example of such a message.
<Message Text="%0a*** Create batch file to execute database scripts ***"/>
Downloads
This TechNet Gallery download contains a Visual Studio 2013 solution which you can use for further study or use. Further the solution is based on BizTalk 2013R2 and BTDF v6.
See Also
Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.