In my article "Introduction To Change Management" I wrote about the fundamental flaw in the deployment process:
Developers have the first-hand knowledge about the deployment artifacts, but they rarely have security privileges on servers and databases outside of the development environment. DBA's and operations, on the other hand, have permissions and are usually in charge of performing deployments, but they rarely have a good understanding of the deployment artifacts and their relationships.
Suppose we need to deploy a new build of assembly Foo.dll. A method inside it relies on a specific version of stored procedure dbo.Bar, so updated SQL script must be deployed together with the assembly. This is where our process becomes vulnerable to human errors, since IT, DBA, build engineer, and release coordinator all have an opportunity to break the deployment. Take into consideration the sheer number of components and database objects, and you can understand why it usually takes several iterations to make a successful push.
The standard two-track deployment process has other drawbacks, too. For example, it turns DBAs into very expensive clerks as they mindlessly combine scripts together and press "F5" button. Another problem is that it creates a false illusion that database scripts can be safely deployed as a hotfix (while binary code requires regression testing). In reality, both application and database code must be treated as a single entity.
2. Solution
My solution is to bundle all SQL scripts that are required by an assembly with the assembly itself using embedded resources. During deployment, IT engineer will need to invoke standard .NET installation utility:
Installutil.exe <assemblyname>.dll
This invokes a custom installer class which will enumerate script resources in the assembly and execute them in a predefined order.
Although it sounds pretty simple, the above approach has a potential to eliminate a lot of deployment issues, because it gives the developer - someone intimately familiar with implementation details - full control over database deployment process.
It also encourages a modular approach to software design by reducing external dependencies of the assembly.
3. How To Bundle Scripts With Assembly
3.1 Adding resources.
Adding SQL scripts to assembly is a very straightforward procedure. In Visual Studio Solution Explorer, right-click project name and choose "Add Existing Item" from the context menu (it is even better to create a subfolder for these files). After a file was added, open its properties and change Build Action from "None" to "Embedded Resource".
3.2 Marking resources as SQL scripts.
When deploying SQL scripts, order is very important. For example, if you have a stored procedure which selects records from table dbo.Customers, you have to create the table before you can create the stored procedure. So, in order to maintain the order, I created a new custom attribute SqlScriptResourceAttribute:
[AttributeUsage(AttributeTargets.Assembly, Inherited = false, AllowMultiple = true)]
public sealed class SqlScriptResourceAttribute : Attribute
{
public SqlScriptResourceAttribute(string scriptName, int scriptSequence)
{
this.ScriptName = scriptName;
this.ScriptSequence = scriptSequence;
}
}
When using the attribute, it is important to specify fully qualified name of the script, which is assembly name + subfolder name (if any) + file name. In the example below, table creation script t_Customer.sql will be executed before other scripts.
[assembly: SqlScriptResource("MyAssembly.pr_Customer_s.sql", 2)]
[assembly: SqlScriptResource("MyAssembly.pr_Customer_i.sql", 3)]
[assembly: SqlScriptResource("MyAssembly.t_Customer.sql", 1)]
3.3 Adding Custom Installer
In order to be able to run installutil.exe against your assembly, you need to add a custom installer to it. This is done using a few lines of code:
using System.ComponentModel;
using System.Configuration.Install;
namespace MyAssembly
{
[RunInstaller(true)]
public class MyAssemblyInstaller : Installer
{
public MyAssemblyInstaller()
{
ScriptInstaller installer = new ScriptInstaller(this.GetType().Assembly);
this.Installers.Add(installer);
}
}
}
All we need to do is to initialize an instance of ScriptInstaller class with the reference to current assembly and add it to the Installers collection.
4. How To Implement ScriptInstaller
ScriptInstaller is a subclass of System.Configuration.Install.Installer. It overrides standard Install(IDictionary stateSaver) method and implements SQL script deployment logic.
4.1 Enumerating Scripts.
I use Reflection to retrieve embedded resource names and list of SqlScriptResourceAttribute instances, then cross-check them to ensure integrity. Finally, I add all script resource names to a SortedList, ordering them by execution sequence.
private SortedList<int, string> BuildScriptList()
{
SortedList<int, string> scripts = new SortedList<int, string>();
List<string> resources = new List<string>(_currentAssembly.GetManifestResourceNames());
object[] attributes = _currentAssembly.GetCustomAttributes(typeof(SqlScriptResourceAttribute), false);
if (attributes != null)
{
foreach (object item in attributes)
{
SqlScriptResourceAttribute attrib = (SqlScriptResourceAttribute)item;
if (resources.Contains(attrib.ScriptName))
{
scripts.Add(attrib.ScriptSequence, attrib.ScriptName);
}
else
{
Context.LogMessage(string.Format("## Script {0} not found in the current assembly.", attrib.ScriptName));
}
}
}
return scripts;
}
4.2 Loading Scripts.
Loading scripts from the assembly is accomplished using a GetManifestResourceStream method of the Assembly class:
foreach (string scriptName in BuildScriptList().Values)
{
Context.LogMessage(string.Format("## Installing script: {0}", scriptName));
Stream resourceStream = _currentAssembly.GetManifestResourceStream(scriptName);
string sqlScript;
using (StreamReader sr = new StreamReader(resourceStream))
{
sqlScript = sr.ReadToEnd();
}
DeployScript(sqlScript);
}
4.3 Deploying Scripts.
Although this is essentially a standard ADO.NET ExecuteNonQuery() operation, there are two potential caveats. First is database security. Whichever way your code normally builds database connection string, it is very unlikely you will be able to use it with DDL scripts. Commonly, you only get "execute" (and possibly "select") permissions at runtime. So, in order to successfully execute deployment scripts, you need to tweak the connection string to establish a more privileged security context. The best approach, in my opinion, is to use SQL Server Integrated Security and assume that the person executing the installutil command has relevant SQL Server permissions.
Second potential pitfall is related to the fact that most deployment scripts contain multiple batches of TSQL separated by the "GO" command, e.g.:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].pr_Customer_s') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].pr_Customer_s
GO
CREATE PROCEDURE dbo.pr_Customer_s
Keyword "GO" is not part of TSQL language definition, so if you try to call ExecuteNonQuery on the above script directly, you will get an exception. What you need to do is split the script into individual batches using "GO" keyword as a guide, and then execute each batch individually:
string[] batches = sqlScript.Split(new string[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string sqlBatch in batches)
{
DbCommand cmd = db.GetSqlStringCommand(sqlBatch);
db.ExecuteNonQuery(cmd);
}
5. Wrapping Up
There are situations where the solution described in this article is either not applicable (for instance, when company ships their software on a CD with a special setup application) or doesn't add value (for example, when database engineers - not .NET programmers - are developing database scripts). But if your change management process separates database scripts from binary code deployment, you may have an opportunity to streamline it. Automated scripts installation will reduce the number of deployment issues, and DBAs will surely feel relieved that they no longer need to perform this mundane task.
