17 Dec 2019

Loading SQL Server PowerShell Module tip

As mentioned in previous posts:
I have been working on integrating our SSIS projects into our continuous delivery pipeline using Octopus, PowerShell and the SQL Server PowerShell module. In this post I will cover how I had to resolve the following error: New-Object : Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is load.

I'm going to start explaining the contributing factors which led me to getting the aforementioned error:
  • Where the script is executed
  • What the script does.

Where the script is executed

As part of the deployment pipeline, we decided not to install tentacles on our SQL Servers. To deploy our SQL Server changes we use one of our application servers, to receive Octopus packages like our SSIS projects, SQL Scripts, then the application server runs the deployment PowerShell scripts to update our SQL Servers.

As detail in the image below:
Our Deployment Pipeline
Our Deployment Pipeline

What the script does

The script does the following steps:
  1. Check for the installation of the SQL Server PowerShell module
  2. If the module is not installed then download a temporary copy and load all the module assemblies for the current PowerShell session.
  3. Connect to the SSIS server
  4. Create the SSIS folder if it didn't exist.
  5. Upload the SSIS ISPAC files to the server.
  6. Then linked the environment configuration and SSIS project together
  7. Then create/update the environment configuration
While our script was executing it would download a temporary copy of the module and then deploy our SSIS project. This was happening for all our SSIS projects, which were created as separate deployments.

How I got the error

To optimise our deployment pipeline, I decided to manually install the SQL Server PowerShell module on our application server to stop the several downloads of the module. Afterwards I started getting the error: New-Object : Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is load.

My script used the following example, which is recommended on many sites on the internet:

if ((Get-SqlModuleInstalled -PowerShellModuleName "SqlServer") -ne $true) {
    # Display message
    Write-Output "PowerShell module SqlServer not present, downloading temporary copy ..." 
    # Download and install temporary copy
    Install-SqlServerPowerShellModule -PowerShellModuleName "SqlServer" -LocalModulesPath $LocalModules
    # Dependent assemblies
else {
    # Load the IntegrationServices Assembly
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null # Out-Null suppresses a message that would normally be displayed saying it loaded out of GAC

The problem was with the else block it assumes that if the module is installed then the SQL Server assemblies will be in the Global Assembly Cache (GAC). In my case, they were not as my application server didn’t have any component of SQL Server installed until the module.

How I resolve the error

To resolve the error, I had to update my script to always load the assemblies from the installed location of SQL Server PowerShell module. I managed this by changing my script to always call Get-SqlServerAssemblies but tweak the function to use -ListAvailable when calling the Get-Module for the SQLServer. This enable getting the path to were the module is being loaded from, as shown in code below:

Function Get-SqlServerAssmblies {
    # Declare parameters
    # Get the folder where the SqlServer module ended up in
    $sqlServerModulePath = [System.IO.Path]::GetDirectoryName((Get-Module -ListAvailable -Name "SqlServer").Path)
    # Loop through the assemblies
    foreach ($assemblyFile in (Get-ChildItem -Path $sqlServerModulePath -Exclude msv*.dll | Where-Object { $_.Extension -eq ".dll" })) {
        # Load the assembly
        [Reflection.Assembly]::LoadFile($assemblyFile.FullName) | Out-Null

Once my script was updated the deployment code was working again and now completes in less than 5 minutes.

Here is a PowerShell Script template that will load the SQL Server module and assemblies either by download a temporary copy or using the installed module: Load-SQLServerModule.ps1

10 Nov 2019

Automating deployment of SSIS projects

As mentioned in the previous post: Automating building of SSIS projects, I am working on integrating our SSIS projects into our continuous build/deployment process. In this post, I will cover my implementation of automating the deployment of SSIS projects.

Our continuous delivery pipeline uses the Octopus Deploy platform which is a release management; automated deploy and operation runbook platform that consists of a central server and deployment agents referred to as tentacles.

The tentacles receive the deployment packages, which contains the code being deployed, and runs the deployment scripts.

The server is where all the deployment packages are stored; deployment projects are maintained and the management of the following: infrastructure, users and server.

Octopus deployment projects are defined as steps from one of the following templates types: Built-in, custom or Community Contributed. The template could be defined in one of the following languages: PowerShell, C#, F#, Bash or Python3.

Before I could create my deployment project. I had to extend my automated build by adding another PowerShell script to create deployment packages and publish them to the server.

The sample of the script can be found here: PublishSSIS.ps1

I started my deployment project by using the existing community templates for deploying SSIS ISPACS projects. I found them easy to use to deploy the SSIS project and configure either by directly changing the SSIS parameters or via an environment configuration.

When using the environment configuration, which is how we deploy our SSIS projects, the existing templates copied all the SSIS project and package parameters into the environment configuration and set the values either from SSIS project/package parameter or matching Octopus variable name.

I didn’t like this approach as it made it hard to see and find what configuration was set by Octopus. This was due to a large number of parameters existing in the environment configuration from all the connections defined within our SSIS projects and packages.

I decided to create my own Octopus template, which I publish to the Octopus library called: "SSIS Deploy ispac with Environment", to only create environment references when Octopus variable name, which has to be wrapped with SSIS[], matches the name to a SSIS parameter. For package level parameters the Octopus variable name also has to be prefixed with the package name within []. Any environment references when there are no matching Octopus variable with this pattern are removed.

My template also differs in the following ways:

Will not create the SSIS catalogue if it didn't exist, as I don't want one-time operation included in my template. As well as to reduce the risk of my template not working due to new parameters required for creating SSIS catalogue for future release of SQL Server.

Will always create environment reference for the project being deployed. The name of the environment reference will be the same as the project name being deployed with a defined tag. The default the tag will be the name of the environment that is SSIS package is being deployed to.

I would like to give thanks to the other SSIS templates community contributors as their code helped me to write my own template.

2 Nov 2019

Automating building of SSIS projects

I am currently working for a healthcare solutions company on one of their leading products. Presently we are working on improving our continuous build/deployment processes. I have been looking into how to include all our SQL Server Integration Services (SSIS) projects and would like to share the approach taken for the continuous build in this blog post.

Automating the build of SSIS project(s) is not as straightforward as it is for the .Net projects. One of the main barriers is that for SSIS projects there is no native support in the visual studio standard build tool (MSBuild).

If you wish to use MSBuild you would need to write your own build file with a custom extension library that you would have either created yourself or downloaded from a community/open source code sharing site.My approach was to use PowerShell to do the following:
  1. Read an XML config file which has the solution and projects files paths along with build configuration.
  2. Find the path to visual studio. By using vswhere.exe looking for version 15 and the installation path.
  3. Then automate visual studio to build require projects
  4. Finally copies all the built output into an artefact folder structure.
I develop this script to run on a development computer or the build server. Integrating this script into our build platform, which is currently Jenkins, was an easy process as there was a PowerShell plugging for build projects.

While running the script on the build server, there was a problem with vswhere program not returning the installation path. To resolve this problem I added a parameter for passing in the install path of visual studio and to bypass the vswhere step.

A copy of my script and an example of the config file can be found at this location:SSIS Automate Build Files

The reasons for using power shall were as follows:
  • I needed to have the build process to be easy to maintain and could be ported over to any build server platform that runs on Windows.
  • To reduce the risk of us having to revisit the build process due to changes of how SSIS projects are built or the extension library not able to support the build changes.

24 Jan 2016

Database Projects Topologies

I have been working with Visual Studio Database projects starting with Database Professional to its successor SQL Server Data Tools. Within this post I wish to share some of the project topologies that I have used to manage my project databases requirements

Before starting I would like to cover the 2 ways that database projects can be referenced.  As these methods play an important part of the projects topologies without having to develop code.
  • External Referencing
    When adding a database reference and setting the options of database name or, optional, server name with a literal / variable value will configure the reference as external.  This will mean any SQL objects within the reference will need to be referred by using 3/4 part naming.  Also the referenced DACPAC / project will need be to deployed first, separately, before deploying the current project.
Screen Shoot of setting Database as a External Referenace
  • Composite Referencing
    Creating a database reference but not setting the database name / server name will configure as internal, composite, to the database.  Allowing the use of two part naming.  Another benefit is that the project containing the reference can deployed the reference objects within the same deployment.
Screen Shoot of setting Database as a Composite Referenace

The reason behind how I came to create theses topologies were due to the following reasons:
  • Deployments problems.
  • Reduce repeating code
  • Ensuring consistent model
  • Couldn’t spend time creating, managing and installing extensions for the tools.

Tables and Code splitting topology

I was working on a database warehouse project, which was using DB Pro, was having deployment problems.  The deployments were failing due to circular references as database required objects be created in each of the other databases.  To get a deployment to work I had re-create the deployment script a few times.

To deploy all the projects compiled models in a single execution I restructured the projects in the following manner:
  • storage project: which contain all the, table, indexes and files DDL
  • code project: this project had all the views, functions and store procedures DDL
The code project would use composite referencing to the storage project for the same database. Then external referencing both the code and storage project for other external databases.

There was an added advantage of splitting the projects in this way. It allowed the DBA to check the deployment for table rebuilds.  As he only had to check the output of the storage project.  However there was some extra configuration management caused by this method, this was mitigated within our deployment framework I was using at the time.

Showing Tables and Code Splitting Topology
Showing the Table and Code Referencing Topology

Publication, Subscriber and Publisher topology

Another project I worked on made use of SQL Server replication to replicate a subset of objects. To ensure that the objects being replicated where consistent between the databases I use the following project topology:
  • A publication project: Would contain all the objects which would need to exist on both publisher and subscribers.
  • A publisher project: Which contain objects that would only exists on the publisher database.
  • A subscriber project: This project contain objects only for the subscribing database.
The publication and subscriber project used the composite project reference to this publication project.
This helped ensure that the publisher and subscriber database had a consistent model for the replicated objects.  Also any changes only needed to be made to one project.
Showing the Publication, Subscriber and Publisher Topology

Packaging Standard Objects

Another topology I've used within my database projects is to separate any standard/self-contained code into a separate project. Then apply composite reference if required.

Here are some examples:
Here are the following reasons why I placed the standard code into a separate project:
  • The standard code is maintained in one place.
  • I have a deploying mechanism for the standards
  • Made it easier database projects to get the standards applied by referencing the projects.

6 Feb 2015

Automation database deployment with refactored objects

While automating the deployment for my SQL Server Data Tools (SSDT) projects, as mentioned in a previous post: Automating SQL Server Database Projects Deployment, I found it important to create / maintain the projects refactor logs.  This is because SSDT / DAC framework uses the model driven deployment method, which works by comparing the model with the target database to create / execute a delta script for updating the database to match the model.  A problem with is method is miss identifying objects which have been renamed or transferred to a new schema.

For code objects like: store procedures, functions, views etc. this would be more of a nuisance as the database will contain redundant code. This would happen if the deployment option DropObjectsNotInSource is not set to true.  This would make it harder to maintain the database and may cause future deployments to fail if schema binding is involved.

However for tables or columns, again depending on deployment options, this will cause one or more of the following: data loss; deployment failures or unnecessary steps within the delta script.  A group of the unnecessary steps that would be generated, mainly when columns are renamed, are table rebuilds. These steps could cause deployment failure when copying the data from original table to the temp table if the renamed column is without a default value and doesn’t allow nulls. When the option BlockOnPossibleDataLoss is set to false which allows the framework to drop columns and tables regardless if there is data. Could cause unintentional data lost if drop column / table were actual renamed / transferred because the data wouldn’t be copied to new the column / table.

The SSDT mechanism for managing renames / transfer of database objects is by having the actions recorded in a log file call: <ProjectName>.refactorlog. The refactor log is then used within the deployment to help generate the required steps to update the database.

To create / update the refactor log is by using the SSDT refactoring options by following these steps: Refactor Menu Options
  • Select the object you wish to re-factor in the SQL Server Object Explorer under projects.
  • Right mouse click on the object and hover over the refactor menu item and select the required action:
    • Rename: The rename option will record that an object has been renamed. This will allow the deployment to create the following statement: EXEC sp_rename '<OldNameOfObject>', '<NewNameOfObject>';
    • Move To Schema: The move schema refactor option will record that an object belonging to one schema will be moved to the new schema. This would cause the following statement to be created in deployment: ALTER SCHEMA <NewSchema> TRANSFER <ObjectType>::<OldShema>.<NameOfObject>;
When using one of the above options action will also be applied to all the objects which reference the object being refactored.

Refactor Preview Screen

One annoying problem, this is dependent on the object being renamed / transfered, is that the refactor options will not rename the file within the project. So you could end up with a file called one thing ie: Myschema.csp_MyStoredProc.sql but the object inside could be MyTransfer.csp_MyRenamedProc. This can make the solution/project view impossible to use to find objects.

There are also two other options available which are mainly aimed at code objects like views, stored procedures and functions etc. I have not use these functions so I don't know if they impact the refactor log.
  • Expand Wildcards: The expand wild cards refactoring is there to help remove select * statements by replacing them with all the column names.
  • Full-Qualify NamesThe full qualify refactoring option is about updating the column names with a qualified table name or alias.
Also when adding a new column to already existing table apply them at the end of the table. Otherwise the framework will also do the table rebuild steps as there is no option to force the use of Alter Table Add Column.

further reading:

19 Jan 2015

Building a simple dynamic site map

A while ago I worked on a project to improve a website indexing within search engines. The project was initiated because there was a noticeable usage downward trend for the site. The main reason was the site was not being listed within relevant searches by search engines. This was because our content didn't emphasize the keywords within the search due to our HTML layout; URL structure and crawling errors.

One aspect of the project was to improve the site layout and provide more information publicly.  The layout improvements were: to include the use of headers to highlight search keywords; a simpler layout to reduce HTML errors.  Providing more information was to help increase coverage of keywords.  Another improvement made to the site was the restructuring of the site URLs.  By making the URLs more human readable and converting the site search functionality to use ASP.Net routes.

A site map was introduced to enable to help the search providers index our site of our static and searchable content. More information about site maps, which Google, Bing and Yahoo supports, can be found at the following link: http://www.sitemaps.org/.

I needed to find a method of creating a site map that would only list the valid search URLs available based upon the content within our database.  The approach I took was to get the SQL Server to generate the XML for the site map and to have an ASP.Net page that would stream the XML out.  I would like to start off with the SQL that I use to create the site map XML using the following elements of TSQL:
  • WITH XMLNAMESPACES (DEFAULT 'http://www.sitemaps.org/schemas/sitemap/0.9'): to set the name space of the XML document.
  • FOR XML PATH('url'): for XML operator also setting the parent element name.
  • ROOT('urlset'): for creating the root node for a valid XML document.
Full Code:

   1:  ;WITH XMLNAMESPACES (DEFAULT 'http://www.sitemaps.org/schemas/sitemap/0.9')
   2:  , SiteMapURLS(URL, changefreq, LastUpdated, PriorityWieght) -- Name Query to get the site URL and the last Date when for URL was last Changed
   3:  AS
   4:  (
   5:  SELECT [ta].[URL] -- URL patten like 'http://www.mysite.co.uk/' + [ta].[URLPart] +'/' 
   6:  , 'daily' --or one others: always, hourly, weekly, monthly, yearly, never
   7:  , [ta].[DateField] -- MAX(DateField) to get the last date
   8:  , 0.5 -- Valid values range from 0.0 to 1.0
   9:  FROM TableA AS ta
  10:  -- Group By [ta].[URL] OR URL patten like 'http://www.mysite.co.uk/' + [ta].[URLPart] +'/' 
  11:  ) 
  12:  SELECT [smURL].[URL] AS 'loc' -- creates the following: <loc> 
  13:  , REPLACE(CONVERT(varchar,ISNULL([smURL].[LastUpdated],'2013.01.01'),102),'.','-') AS 'lastmod' -- creates the following: <lastmod> and corrects the date format
  14:  , [smURL].[changefreq]) AS '<lastmod>' -- to create the following: <changefreq>
  15:  , CAST(ROUND([smURL].[PriorityWieght],1) AS DECIMAL(2,1)) AS 'priority' -- to create the following <priority>
  16:  FROM SiteMapURLS AS smURL
  17:  FOR XML PATH('url'), ROOT('urlset');

Within the website I created a generic handler called sitemap.ashx. This page would then stream the content of the stored procedure.

While the site was being updated it was decided to improve the data access layer by using Entity Framework. In order to make sure that the site had a consistent data access I made use of the Entity Framework. The following code shows how I managed to stream the XML content out of the database:

   1:  Public Sub ProcessRequest(context As HttpContext) :IHttpHandler.ProcessRequest
   2:      context.Response.ContentType = "text/xml"
   4:      //Getting the Connection from entity framework
   5:      Using sqlConnection As SqlConnection = DbContext.Database.Connection
   6:          Using sqlCommand As New SqlCommand("website.csp_GetWebSiteMap" , sqlConnection)
   7:              sqlCommand.CommandType = CommandType.StoredProcedure
   8:              sqlCommand.Connection.Open()
   9:              Using xmlReader As XmlReader = sqlCommand.ExecuteXmlReader()
  10:                  Dim xdocument As XDocument = xdocument.Load(xmlReader)
  11:                  context.Response.Write(xdocument)
  12:                  xmlReader.Close()
  13:                  sqlCommand.Connection.Close()
  14:              End Using
  15:          End Using
  16:      End Using
  17:  End Sub

To enable the search crawlers to find our site map I created a robot.txt file. This file also helps to restrict URLs which you don't want crawlers to access like URLs which require authenticated user.

There is one caveat to this approach which is the URLs which are created in SQL Server do not need to be encoded, which was the case for me. If the URLs you have need to be encoded you would need to create a simple SQL CLR function that would make a call the following method(http://msdn.microsoft.com/en-us/library/ee360286(v=vs.110).aspx).

9 Jan 2015

Automating SQL Server Database projects Deployment

Within this post I wish to share the methods I used to automate the deployment of my SQL Server database projects.  I needed to automate the deployment of my database projects within the build process so I could integrate the execution of my unit tests. As mentioned in a previous post: Integrating TSQLT with Team Foundation Build Services continuous build.  I also wanted to automate the deployment into the development, test and production environment for the following reasons:
  • To empower the developers to do database deployment into the required environment and reducing the dependence on me.
  • To reduce the complexity of deploying the database projects.
  • To have an easy deployment process to document i.e. run this script.
  • In a way to document the deployment of the database projects.
Before I start I would like to highlight an invaluable project / solution file(s) that help with the automation.  These are "Profiles" which are used as configuration files for deployment.  They store settings like connection string, database name and other behavioural settings for DAC services (the framework used to deploy SQL Server database projects).  These files can be created when deploying from visual studio.  If the database solution, like mine was, is made of composite projects.  Then profiles, which can be shared between the SQL Server database projects, can ensure that the projects are deployed to the same SQL Server and database.

As mentioned before I needed to automate the deployment of my database project for the unit tests.  I couldn't use the MS-Test deployment functionality as I needed a post deployment script within a composite project to be executed.  I decided to use PowerShell to automate SQLPackage.exe for the following reasons:
  • It would be easier to manage the arguments required for SQLPackage
  • Easier to capture the output of SQLPackage into a log file.
The basic approach was to use Invoke-Expression on a dynamic created command string and redirect the output to file.  Then check the $LASTEXITCODE was 0 after the execution to ensure no errors had happened.  The command string was made up of hardcoded values and parameters passed into the script.  A demo version of this can be downloaded from the following link: http://1drv.ms/1Ks0qq0.

To deploy the successful builds the tool that was used within the company, at the time, was: TFSDeployer ( http://tfsdeployer.codeplex.com/ ).  Unfortunately I need to point out that this is not an active project, more information can be found here: http://tfsdeployer.codeplex.com/discussions/449767.  To get TFSDeployer to deploy the dacpac files, the build output for database projects, I needed to create a PowerShell script.  I wanted to find a way of using PowerShell without using an external process, SQLPackage or MSDeploy, and using $LASTEXITCODE to detect errors.
While searching for an alternative method I came across the DAC Services API (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacservices.aspx. The API supports .Net which allows interaction to all the functionality of the framework.  With this API I was able to create a reusable function to do the following:
  • Generate deployment report and script.
  • Optionally deploy the changes to target server and database.
  • Capture all the events into log files.
I also wrapped this function within a script which will take a parameter to load a DAC Services API library file.  A version of this script can be downloaded from the following link: http://1drv.ms/1Kc5fDP.  Also a demo script of using the DAC function within a PowerShell script can be downloaded from here: http://1drv.ms/1Kc5sad

The scripts can only be executed within PowerShell 3 or above as DAC service API library requires Microsoft .Net 4.0 or above.  Also the following SQL Server redistributable components are required:
further reading:

26 Aug 2014

Integrating tsqlt with Team Foundation Build Services continuous build

As I've mentioned in a previous blog post I have been integrating a SQL Server unit test framework: tsqlt into my project life-cycle (click here to read more).  Within this post I will share how I got my tsqlt unit tests executing within the continuous integration solution and the results influencing the outcome of the build. The continuous integration platform I am using is Team Foundation Build Services 2012 (TFBS). However my approach will work with any continuous integration platform which handles MS-Build and MS-Test, like Team City.

Build Integration

As I had created my database unit tests within SQL Server Data Tools (SSDTs) integrating them into the continuous integration was a straightforward process.  By extending the build definition to include the unit test project into the list of solution / projects which have to be built.  As I had my own solution, which was listed within the build definition, for all the database projects and other related projects my unit tests were already included.

I also added a very useful MS-Build argument to the build definition: /p:GenerateProjectSpecificOutputFolder=true which puts the outputs of the projects in their own separate folders. This was another way of separating the unit test framework and the unit test from the application database.

Unit Test Execution

Integrating the execution of the database unit tests could be achieved by writing a new unit test adapter to execute the tsqlt unit tests and report the results back to TFBS. However I decided to use an easier approach by making use of the SSDTs extension within MS-Test.  I use MS-Test database unit test to execute the tsqlt unit test and to check the execution result. I have reasons why I don't use MS-Tests to unit test my application database. These can be found in a previous blog post (click here to find out more).

I structured my MS-Test project to mirror the approach taken within tsqlt.  By having a MS-Test class for each tsqlt test schema. Then within each test class have one MS-Test database unit test per tsqlt unit test, using the same names minus spaces and the beginning “test”. This will make it easier seeing the results of the tsqlt unit test within result viewer.

Within the MS-Test database unit test the following SQL pattern was used:

   1:  --Execute the unit tests
   2:  EXECUTE [tSQLt].[Run] @TestName = N'[MyUnitTestClass].[test My Unit Test for My database Application]';
   3:  --Then check the results using the following SQL
   4:  SELECT [tr].[Result]
   5:  , [tr].[Msg]
   6:  FROM [tSQLt].[TestResult] AS tr
   7:  WHERE [tr].[Class] = 'MyUnitTestClass'
   8:  AND [tr].[TestCase] = 'test My Unit Test for My database Application';

Then use the single value assert method to check row 1 column 1 contained the following value: Success.

I have been able to write a T4 code generator which connects to a SQL Server database with tsqlt unit tests and does the following:
  • Create a class file for each of the tsqlt test schema
  • Generate the database unit tests for each tsqlt test found within the schema.
You can download my code generator on the following link: https://onedrive.live.com/redir?resid=E6F77D4B3FF8D47C!385&authkey=!AH-1sMQPJIs_B0c&ithint=file%2c.zip.

The code generator requires the following prerequisites:
To use the code generator update the tsqltUnitTest.tt file with the following:
  • SQL Server: With the name of the SQL Server which contains the unit test database.
  • Database name: The name of the database containing the unit tests.
  • Test name space: With the name of your MS-Test project that output files will be copied to.
Then right mouse click on the tsqltUnitTest.tt and select the option of running custom tool. After the execution of the T4 template there will be a directory, with the same name as your name space setting, containing all the files which need to be copied to your MS-Test project.

To create the empty MS-Test, to copy the output of the code generator into, use the following steps:
  1. Add a new C# MS-Test project to the solution

  2. Add a SQL Server unit tests file. This will add all the necessary references and code to convert the MS-Test project into a SQL server test project.

  3. Delete the newly added SQL server unit test file.
Adding MS-Test Project to solution

To get the unit tests to execute successfully I had to alter the standard build process template. The alteration was to insert an activity, after build and before the MS-Test activities, to run a power shell script. The script would do a deployment of my application database. Then a deployment of the unit test project, which would include the framework, to the localdb.

Standard Build Process Template With Powershll Task

The reason for the extra step was to work around the problem of SQL package only executing Pre/Post deployment files of the dacpack of the referenced source parameter. I needed my application database post deployment script to be executed as this sets up all my reference data which my unit test require.

To integrate the unit tests execution I only had to update my build definition to start executing test based on DLL file pattern match which was: *test*.  And change the setting "failed the build if tests fail" to true.

Finally another useful advantage of using MS-Test is that database developers can deploy and test their code to localdb all within Visual Studio.  So now they can follow the same rules as their .Net counterparts by ensuring that no code is check-in before it passes its unit tests on their local machines.

further reading:

9 Jul 2014

Integrating tsqlt with SQL Server Data Tools

As mentioned in a previous post I've recently been working with a SQL Server unit test framework: tsqlt. I would like to share how I managed to integrate the framework and unit test into my project life-cycle.

Unit tests should be treated the same as application code, meaning they should be contained within a project under source control. Being under source control will allow the tests to be maintained and changes tracked when multiple people are helping to write tests.  Also having the unit test in a project means there is a deployment mechanism out-of-the-box.

The tsqlt framework has a few requirement which are as follows
  • CLR is enabled on the SQL Server
  • The framework and the unit test are contained within the same database of the objects which are being tested
  • The database instance is trustworthy
As unit test are required to be contained within the same database as the objects being tested, also due to the way SQL Server Data Tools (SSDT) project validation works, the easiest solution would be to add the unit test and the framework objects into the same project of your application database.

However one question would be raised which is how do you stop the unit test framework and the unit tests getting into the production deployment? The tsqlt framework does offer an approach to this problem by supplying an uninstall store procedure which could be called after deployment to remove the unit tests and the framework. This is more of a workaround rather than solving the problem of not deploying tests and the framework into production.

There is an alternative solution, which is the method I used, to integrate the unit tests into the project life cycle.  The solution is to use SSDT project setup method of composite database project.  The composite database project method, which has been around since visual studio database professionals, is basically having 2 (or more) database projects, where one references the other(s), which both gets deployed into the same database on the same server.

My method to implement composite SSDT database project was as follows:
  • Created an application database project as normal. Which contained all the objects for the application requirements.
  • Create a unit tests database project to contain all my unit tests and supporting objects. This project could also be where tsqlt unit framework code could be stored.  I decided created a separate tsqlt dacpak file by using the following steps:
    • Create an empty database.
    • Execute the tsqlt install script within the empty database.
    • Use SQLPackage with the extract action.
  • Before starting to create my unit test code within my unit to project I had to set up the references.  I created references to the application project and the tsqlt dacpack file but setting database location to be “same database”, which is the same as removing the generated database name and variable.
Adding Database Reference To Application Project From Unit Test ProjectAdd Database References to tsqlt dacpac file From Unit Test Project
Setting up the projects in this method means I can deploy my application project without the unit tests and the framework to all the environments. Then add the unit test to any one of my  environments: development, test or separate unit test server.

The reason for creating a dacpack file was to have a single reusable file for my next projects. And if the unit framework is updated I only have to update a single file.  Also hopefully at some point the SSDTS project will support the use of NuGet. This would mean that I can upload my dacpack to a NuGet server and add the package reference.  Then if I ever updated the server package the project would automatically pick up the new version. (There is a fork within the NuGet project which already allows this: https://nuget.codeplex.com/workitem/2439.)

Here is a link to find out more about composite projects: http://blogs.msdn.com/b/ssdt/archive/2012/06/26/composite-projects-and-schema-compare.aspx.

SQLpackage has a setting which will allow a composite project to deploy its composite references objects at the same time. This allow the unit test project to deploy the unit tests including the application database and the tsqlt framework database objects. However be aware that only one Pre/Post deployment file is executed which is one that is attached to the dacpack listed in the SQL package source file parameter. So the composite references project have post/pre-deployment requirements are not executed.  This can be a problem if your application database has a post-deployment script to load reference data.

A couple of closing notes:

To create a unit test class within SSDTS for tsqlt add the following extended property on the schema which is to become a tsqlt test class: tSQLt.TestClass with a value of 1
   1:  EXECUTE sp_addextendedproperty
   2:  @name = N'tSQLt.TestClass'
   3:  , @value = 1
   4:  , @level0type = N'SCHEMA'
   5:  , @level0name = N'MyUnitSchema';

And creating unit test the store procedure must start with "test "

further reading:

Automated Unit Testing SQL Server Code

I have never been a fan of automated unit testing of SQL code mainly because previous frameworks / tools, which I came across, all seem to miss the point of SQL code which is either retrieving or manipulating data.

Some of the frameworks would only test SQL scalar functions which limited code coverage. Others would test stored procedures but only the output parameter not the data being returned which limited the usefulness of the test.  The methods used to determine if a test pass or fail were also limited. As some would only check the return value; the correct number of rows were returned; a single row of data appeared in the dataset or within a row and column a value is returned. This meant that you could write code that would satisfy the test and could still be functionally incorrect as the rest of the data not checked could be inaccurate.

Another problem that some of the previous frameworks forced upon you to solve was how to make the test repeatable. As data has state which is maintained through the code that you are testing. If you rerun a test it may not be against the same data state as the previous test run.  This could cause test to fail because the expected results were out of date or due to constraints violations as previous run weren’t removed.  Also getting the data into a starting state could be time-consuming and error prone because of identity properties; referential integrity and table constraints.

However I recently came across a unit test framework called tsqlt which has changed my mind about automating unit testing against my SQL code.  The framework supports the testing of any executable SQL code and offers approaches to isolate embedded stored procedures; triggers and functions. There are a range of asserts the test to use to make sure that the code is functionally correct ranging from data compare, structure compare, the existence of objects and calls were made to embedded code.

The framework helps with repeatable unit tests by wrapping the execution of tests within database transactions and then rolling back once the execution has completed. Also the framework offers tools for getting the database into a particular test state by faking the database tables and applying selective constraints, this is similar to .net mocking objects, which makes inserting data easier.  More details of how to use and get the tsqlt framework can be found at the following URL: http://tsqlt.org/.

I would like to share some of my methods I use to make using the framework a little bit easier.

Creating the set up stored procedure within each test class helps reduce the amount of repeatable code that each unit test would have to contain. The set up stored procedure is automatically called before each unit test, within the test class, is executed. The code I put in my Setup procedures was the faking of the required tables and inserting of any initial data required.

To create a set up stored procedure just create a stored procedure call SetUp e.g.

   1:  CREATE Procedure [MyTestClass].[Setup]
   2:  AS 
   3:  EXECUTE [tSQLt].[FakeTable]
   4:  @TableName = N'[MyAppSchema].[MyAppTable]'
   5:  , @Identity = 1
   6:  , @ComputedColumns = 0
   7:  , @Defaults = 1
   8:  Retutn 0

I found by grouping my unit tests based upon C.R.U.D operations help to get the most out of the setup stored procedure. As I found my insert operations needed the tables to retain their identity columns and defaults to ensure that the insert stored procedures worked. Whereas the updates only required the defaults and the reads only required a fake table.

I found putting my test data into table value functions / views reduced repeating code. Also this helped me in creating my actual data as I was able to apply filters and case statements to get the required results for the particular tests.

For timestamp column you can emit them from the expected results so they are not compared with the actual table. However my method for dealing with my timestamp columns was within my setup stored procedures / unit tests and application code. 

Within the setup stored procedures I would alter the defaults to a static date and time, which was completely different to the initial data, before faking the table. Then use the same static value in my expected table within the unit test. Within my application code I change my approach to updating my timestamp columns. Instead of directly updating the column by using a call to the SYSDATETIME() function I would update the column by using the DEFAULT keyword e.g.

   1:  UPDATE [AppSchema].[AppTable]
   2:  SET [AppColumn1] = @pValue1
   3:  , [AppColumn2] = @pValue2
   4:  , [AppColumn3] = @pValue3
   5:  , [LastUpdated] = DEFAULT
   6:  WHERE [KeyColumnId] = @pKeyColumnId

further reading: