Continuous Deployment (CD) with Azure DevOps, SSIS, and SQL Server Database Projects

What are we doing here?

This is the second part of a two-part series covering 1) CI (continuous integration/build) and 2) CD (continuous deployment/release) for Visual Studio SQL Server Database and Integration Services project types using the Azure DevOps) platform.

Some background

The Visual Studio business intelligence (SSIS, SSRS, SSAS) and database projects types have long been the red headed step child of the Visual Studio ecosystem. Huge strides have been made in recent years but there are still some impediments, one of which is creating CI/CD pipelines for these projects in Azure DevOps.

Don't fret

This step-by-step guide will walk you through creating a release pipeline for a solution containing both a SQL Server Database project and an Integration Services project.

Assumptions (you know what they say...)

The following steps assume that you have already have a working Azure DevOps build definition. If not, you can refer to part 1 of this series.

Let's do it

1. Navigate to /Project/Pipelines/Releases and select New Release Pipeline.
   
2. Choose “Start with an Empty job”.
3. Name the Stage. An appropriate name might be the environment you’re deploying to such as “Development”.
4. Click “Add an Artifact”.
5. Select “Build” as the source type and choose the Project and Source (build pipeline) that will be deployed. If you haven’t already created a Build definition, see Continuous Integration (CI) with Visual Studio Team Services (VSTS), SSIS, and SQL Server Database Projects.
   
6. Click the lightning bolt on the newly created Artifact, enable the Continuous deployment trigger and add a branch filter for master or whatever branch will be deployed.
7. Click on the “1 job, 0 task” link in your newly created Stage.
   
8. Click the plus sign to the right of “Agent job” to add a new task.
   
9. Search for “Command Line” and add a new task.
   
10. Click on the new task and add populate the properties as follows.
    
    Keep in mind that this code is using SQL Server 2017. If you’re using another version, you’ll likely have to modify the Tool path.
    
    Argument text: /a:Publish /sf:"replace_with_build_output_root_location\$(Build.DefinitionName)\$(Build.BuildNumber)\replace_with_appropriate_path\replace_with_dacpac_name.dacpac" /tsn:replace_with_server_name /tu:replace_with_user_name /tp:replace_with_password /tdn:replace_with_database_name
11. Add another Command Line task and populate as follows.
    
    Argument text: /S /ModelType:Project /SourcePath:"replace_with_build_output_root_location\$(Build.DefinitionName)\$(Build.BuildNumber)\replace_with_appropriate_path\$(BuildConfiguration)\replace_with_ssis_ispac_name.ispac" /DestinationServer:"localhost" /DestinationPath:"/replace_with_appropriate_ssis_catalog_path"
12. The previous tasks use a variable to tell us which build configuration to use. Add the variable by clicking on the “Variables” tab, then click +Add, enter “BuildConfiguration”, and set a default value that is the name of the build configuration that is appropriate for this Stage of the pipeline. This will depend on the names of the configurations that have been created in your Visual Studio solution.
    
13. Always test! Create a release and make sure it deploys your projects correctly.

As always, if you have any questions feel free to drop me an email at david@elish.net.

Continuous Integration (CI) with Azure DevOps, SSIS, and SQL Server Database Projects

What are we doing here?

This is the first part of a two-part series covering 1) CI (continuous integration/build) and 2) CD (continuous deployment/release) for Visual Studio SQL Server Database and Integration Services project types on the VSTS (Visual Studio Team Services) platform.

Some background

The Visual Studio business intelligence (SSIS, SSRS, SSAS) and database projects types have long been the red headed step child of the Visual Studio ecosystem. Huge strides have been made in recent years but there are still some impediments, one of which is creating CI/CD pipelines in Visual Studio Team Services.

Don't fret

While it's not as straightforward as say a ASP.NET web application, with just a little finessing it's not overly difficult to build and deploy SQL Server Database and Integration Services project types. If you're familiar with creating VSTS build and release definitions it probably wouldn't take you too long to get it figured out. If you are new to this functionality it can definitely be a bit overwhelming. Hence, this step-by-step guide to standing up a basic VSTS build definition that includes both database and SSIS projects.

Assumptions (you know what they say...)

The following steps assume that 1) you have a VSTS account 2) a VSTS collection, 2) a VSTS project within the collection and 3) have a source control repository in VSTS Git, TFVC, GitHub, Subversion, Bitbucket Cloud, or Git (not necessarily hosted by VSTS).

This walk through will use an open source project called SSISProjectWeaver that is hosted on GitHub (using a different repository type requires little, if any changes). It's not necessary to know the details of the SSISProjectWeaver project other than that it contains a SQL Server Database project and two Integration Services projects.

<plug>If you're starting a new SSIS project it's worth checking out SSISProjectWeaver as it's a 100% non-invasive ETL framework that provides metadata-driven parallelism and dependencies between any two packages or groups of packages, extensive logging, reporting and dashboarding, alerting, and a bunch more. It's easy to set up and has been proven in large organizations with hundreds of packages running a day. You don't have to modify your existing code and if you don't like it you just stop using it. Nothing will have to change with your existing packages.</plug>

Let's do it

1. Open you VSTS project and click on the Build and Release tab.
2. Choose your source. For this example we are going to use GitHub.
3. Once you've connected to the repository, select the branch that you want to build and click Continue.
4. In the top right-hand corner under Select a template, click Empty Process. This will start us off with a blank build definition.
   
5. Name your build definition appropriately and then choose the Agent queue. In this example we are using a Hosted VS2017 queue. Depending on your project you might want to use a local/on-premise build agent of VS2015. It all depends on your project types, versions, dependencies, etc.
   
6. Before we define our build tasks let's create some variables. Click on the Variables tab and create the BuildConfiguration and BuildPlatform variables along with their corresponding default values.
   
   
7. Navigate back to the Tasks tab and click the + next to Phase 1. Search for and choose the Visual Studio Build task. This task will use the out-of-the-box msbuild template for building SQL Server database projects with a .dacpac as the build output.
8. Change the Display name to something meaningful.
9. Choose the SQL Server database project or solution to build.
10. Choose the version to build.
11. Use the newly created variables for the Platform and Configuration properties.
12. Save the task.
    
13. Navigate back to the Tasks tab and click the + next to Phase 1. Search for and choose the Command Line task. We will use this task as a workaround to a lack of an msbuild template for SSIS projects by calling invoking devenv to build the projects.
14. Change the version to 2.*(preview). This version allows multi-line commands similar to a .bat file.
15. Change the Display name to something meaningful.
16. Enter the following text into the Script box. The first 8 lines just echo (print line) info that is useful for debugging. The last line is the actual command that is being executed.
    echo BuildShare: $(BuildShare)
    echo Agent.BuildDirectory: $(Agent.BuildDirectory)
    echo Build.SourcesDirectory: $(Build.SourcesDirectory)
    echo Build.ArtifactStagingDirectory: $(Build.ArtifactStagingDirectory)
    echo System.DefaultWorkingDirectory: $(System.DefaultWorkingDirectory)
    echo BuildConfiguration: $(BuildConfiguration)
    echo BuildPlatform: $(BuildPlatform)
    echo Command: "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Devenv.com" "$(Build.SourcesDirectory)\your_project_or_solution_name.(sln|proj)" /build $(BuildConfiguration)
    
    
    "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Devenv.com" "$(Build.SourcesDirectory)\your_project_or_solution_name.(sln|proj)" /build $(BuildConfiguration
17. Save the task.
18. Click on the Triggers tab and enable continuous integration. This will enable VSTS to execute this build definition each time code is committed to your source control repository.
    
19. Test the build definition by clicking on Queue.
20. Assuming your build was successful, you now have the CI half of your CI/CD pipeline and can auto-notify the world when one of your co-workers breaks the build!

Wrap Up

When it comes to technology, nothing is ever as simple as it appears. The SSISProjectWeaver solution is pretty straightforward in terms of code and dependencies. It's T-SQL and SSIS packages with no external dependencies. Building it is is very straightforward as you can see above. However, many projects are much more complex and require more complex configurations to set up a build definition. For example, the first time I created a build definition with an SSIS project it failed because I had references to 3rd party components. Of course Microsoft doesn't know I am using those components so it's not on their hosted build servers. To make it work I had to give up the convenience of using a hosted build server and stood up my own build server with all the necessary components. Food for thought. Don't be discouraged if you hit a few stumbling blocks on your way to automated build bliss!

Stay tuned for part two which will cover CD (continuous deployment) and will walk you through getting your code deployment automated.

As always, if you have any questions feel free to drop me an email at david@elish.net.

Visual Studio SQL Server Project (SSDT) OPENQUERY Warning

Using OPENQUERY in a stored procedure in a Visual Studio SQL Server project will result in the following warning.

 SQL70558: The column definition from this OPENQUERY function cannot be verified, and the operations involved with it might not be understood correctly.       

Depending on how your project and build is configured, this warning it may or may not fail the build. Regardless, if you have to use OPENQUERY and don't want the warning, you can use the Suppress TSql Warnings property on the stored procedure. To view this property in Visual Studio, click on the .sql file in the Solution Explorer window. You should now see this property in the last row of the Properties window. Enter the 70558 warning number. The warning should now be suppressed and will no longer appear in the Error window or the build output.

The Suppress TSql Warnings property can be used to suppress other warnings as well. To suppress multiple warnings just delimit them with a comma.

Visual Studio, TFS, Git, & GitHub

For those unfamiliar with TFVC it is one of the two version control engines shipped with Team Foundation Server, available with Visual Studio Team Services, and available on Microsoft's GitHub "rival", CodePlex. Many developers assume TFVC when they refer to using TFS but this assumption went out the window with TFS 2013 when it began shipping with the option to use either TFVC or Git as a version control repository.

There are many sites such as git-scm.com that do a great job a describing Git and what makes it so powerful so I won't get into specifics here but I will say that it excels over most other version control platforms when it comes to large, distributed projects. One such project is the Linux operating system. In fact, it was Linux's creator, Linus Torvalds, that developed Git to support the large, distributed nature of the development of Linux. Even with smaller projects, developers are embracing Git partially due to applications like GitHub that easily allow sharing and collaboration across the globe.

Git does such a great job that Microsoft itself is moving away from TFVC in favor of Git for many of its own projects. It's even hosting many of its public (open source) projects on GitHub including the .NET Core project.

...which brings us to GitHub. GitHub is not Git and Git is not GitHub. Git is a standalone version control application whereas GitHub is a web-based Git repository hosting service. GitHub allows users to create and share Git repositories and interact with them via the GitHub.com website, APIs, command line tools, IDE plugins, etc.

Back to Microsoft and its integrations with Git and GitHub...

1) CodePlex offers a Git version control option (it is the default):

2) TFS and Visual Studio Team Services offer a Git option (also the default option):

3) Visual Studio 2015 provides local Git repository integration as well as
GitHub integration:


If you landed here it's because you spend at least part of your time developing on the Microsoft stack and are interested in Git. If you know and like Git and didn't know if or how it integrated with Microsoft development tools, now you know enough to find the resources you need to get started. If you've only just heard about Git in passing and work with Microsoft development tools, I would urge you to do some research on Git and GitHub prior to starting your next project so that you can make an informed decision as to which version control system to use and if you want to use hosted repository service like GitHub.

Useful Links

1. Interesting Wired article that gives some history of Git and GitHub
2. The Register article about Microsoft moving projects to Git
3. Visual Studio GitHub extension for all version of Visual Studio prior to Visual Studio 2015 Update 1.
4. posh-git PowerShell Interface Install Overview which is also part of the GitHub Desktop app install
5. Combining TFVC and Git repos in the same TFS project blog post

Multiple Rows of Tabs/Docs in SSMS

For a long time I felt like a crazy person for constantly closing documents in SSMS just because I could not easily access the documents that no longer had a visible tab. I would find myself resizing Object Explorer, maximizing SSMS, etc. to be able to see all the tabs. I'm sure I'm not the only one. In fact, Sergey Vlasov has created a Visual Studio plugin to deal with exactly this. Since SSMS is built on top of Visual Studio, the plugin works for those that also work exclusively in SSMS.

Visual Studio Gallery Link

Thank you Sergey!

Team Foundation Service (TFS) Preview with Visual Studio 2008 (and BIDS)

It is now possible to use the Team Foundation Service Preview (Microsoft hosted TFS) with Visual Studio 2008 and BIDS (VS 2008 Shell).


1) Install Visual Studio Team System 2008 Team Explorer http://www.microsoft.com/en-us/download/details.aspx?id=16338.
2) Install Visual Studio 2008 SP1 even if it is has previously been installed http://www.microsoft.com/en-us/download/details.aspx?id=10986.
3) Install the Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview http://www.microsoft.com/en-us/download/details.aspx?id=29983.
4) Add your server (assuming you've already signed up for TFS preview) using the following URL: https://<yoursite>.visualstudio.com/defaultcollection.

If you receive an error stating:
TF31002 Unable to connect to this Team Foundation Server...
Possible reasons for this failure include:
-The Team Foundation Server name, port number, or protocol is incorrect.
-The Team Foundation Server is offline.
-Password is expired or incorrect.

MAKE SURE YOU ADD /defaultcollection to the end of your URL.

BIDS (Visual Studio 2008 Shell) with TFS 2010

BIDS is not compatible with TFS 2010. It has to do with TFS 2010 having the concept of Project Collections whereas 2005 and 2008 do not. When adding a Team Foundation Server to Visual Studio Team Explorer 2008 (Tools -> Connect to a Team Foundation Server -> Servers -> Add) there is no way to reference a TFS Project Collection. However, it is possible to get your Visual Studio 2008/BIDS environment up to speed.

There are four things you need. 

1) Visual Studio Team System 2008 Team Explorer downloadable here.

2) Visual Studio 2008 SP1 downloadable here.

3)  Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010 downloadable here.

4) Lastly you'll need to create a registry entry for each Server/Collection you want to use. Launch the registry editor (regedit at a command prompt) and create an entry in HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\TeamFoundation\Servers similar to below. 

    Value name: ServerName

    Value data: http://ServerName:PortNumber/tfs/CollectionName

The Future of Visual Studio Database Projects

It is with great sadness that I relay the decision by Microsoft to do away with the Visual Studio Database Project type that I love so much. This project type has given database developers the tools needed to implement high quality Database Development Life Cycle processes. Some highlights can be found here.

However, all is not lost. VSDB will be replaced by what was until yesterday referred to as project Juneau and is now officially named SQL Server Data Tools. It will also be hosted in the Visual Studio environment.

SSDT has many similarities with VSDB but is a complete rewrite from what I understand. Below are some of the features previously unavailable in VSDB.

1. Code navigation (Go to Definition, Find All References)
2. Enhanced code analysis and design time validation
3. Table designer similar to SSMS
4. Connected (live database) and disconnected (declarative) development
5. LocalDB - SQLExpress without the need to explicitly install any SQL Server instances
6. Entity Framework integration for a much more tightly integrated development experience between the data and application layers
7. Support for CLR objects
8. Support for SQL Server 2012 and SQL Azure databases (both support 2005, 2008, and 2008 R2)
9. Integration of SSIS, SSRS, and SSAS projects into the next edition of Visual Studio (currently Visual Studio 2011)

In my opinion, of the biggest shortcomings (as of SSDT CTP3) of SSDT is that there is no longer support for database unit tests. Automated unit tests have a huge impact of quality and I believe are key to robust DDLC.

While I am sad to see VSDB go, I am confident that Microsoft will create an even better tool in SSDT. I look forward to exploring it and sharing my findings in future posts.

VS Studio 2010 Table Designer

Embarrassingly enough this one eluded me for quite some time. I was surprised to stumble onto the fact that there is indeed table designer functionality within Visual Studio. It is very much comparable to the SQL Server Management Studio table designer.

However, as far as I can tell, it is dependent on a database server connection which goes against one of Microsoft's biggest database project selling points - disconnected development.

If you're like me and haven't been able to find this functionality I included a screen shot below to help you find it. The key is the Server Explorer tab.

1. Create a connection if you don't already have one.
2. Expand your connection and right-click on Tables -> Add New Table.

Once you have defined your table you can use the Table Designer - > Generate Change Script . . . menu option to create a DDL script. If you save the definition it will actually save it to the database. 

Visual Studio 2010 Database Projects

I won't go into the details of how to create and use Visual Studio 2010 database projects here (since I am developing a thorough document on the subject) but what I will do is tell you why they are indispensable. In the end it comes down to one thing - PRODUCTIVITY.

If you are thinking about using Visual Studio 2010 database projects but aren't quite sure why you should, read below. If you find these arguments compelling enough than go and give it a try.

1) Source code structure - Visual Studio database projects force the organization of all database source code into an intuitive and somewhat customizable structure that makes all objects easy to find, modify, and version.

2) Dependency checking - The Visual Studio build engine performs dependency checks. If a column name is changed it will immediately identify any dependent database objects such as views and stored procedures that have been adversely affected, saving the time of tracking down bugs after the code has been deployed which eliminates the time necessary to fix the bug(s) and redeploy the database to the affected environments. It can also perform dependency checks between databases. If you have a core application database as well as other system databases such as an audit/logging database, the tool can perform cross-database dependency checks whether or not the other database exist as Visual Studio database project.

3) Deployment scripts - The Visual Studio database project allows for pre and post-deployment scripts that can do things like move files to certain places in preparation for a database deployment, check versions to ensure compatibility prior to deployment and stop deployment if something is not right, populate tables with reference/lookup data after the deployment.

*Keep in mind that deployment includes both the initial creation/installation of the database as well as the upgrade of an existing database that is out of date and even the rollback of a database to a past version.

I have used this tool in conjunction with InstallShield to create install/upgrade processes for a commercial software product. It worked great.

4) Parameterization of any script values - The project allows for the parameterization of just about anything within the source code such as database name, database server, database file locations, etc. so that the same set of source code can be deployed to any environment, without any code modification, by passing in environment specific parameters. Any number of configurations and corresponding sets of variables can be created (e.g. Dev1 Local Dev, Dev2 Local Dev, QA, UAT, PROD) allowing for a two-click (choose the environment and click deploy) deployment to any existing environment.

5) No need for deployment “sub-system” code - The Visual Studio deployment engine removes the need for an abundance of code that would otherwise be necessary in a manual deployment environment. Code for things such as object (table, view, stored procedure, index, function, etc.) existence checks and drops, foreign key drops and recreation, wrapping of scripts in transactions, etc. is automatically generated by the tool. All that is needed is a single “create” script for each object. This dramatically reduces the complexity of source code by eliminating the need to manually create all the deployment scripts necessary to create or upgrade a database.

6) Fast database deployment on any machine - Visual Studio comes with freely distributable executables that provide the ability for non-database developers (ETL, Report, Dashboard developers) to deploy a complete data warehouse (or any other kind of database) with a single command line, .bat, or PowerShell script without the need for a Visual Studio license.

7) Complete build and deployment solutions - Using build automation tools such as NANT or MSBuild we have the ability to package up the above command line deployment capability with tools like the freely distributable SSIS dtutil executable that automates the deployment of SSIS packages and/or RS.exe for report deployment. Taken to this level, we can deploy many if not all SQL Server related software artifacts using a single call to the automated build (NANT, MSBuild) or install (InstallShield, Wise) tool of choice.

I'm sure this doesn't cover everything but it should give you an idea as to how Visual Studio database projects can make all things database source code related much easier and more efficient. Getting started takes about a half hour if you have a pre-existing database and use the import wizard. I strongly urge you to conduct your own experiment so that you can begin to visualize just how powerful a tool this can be.

Visual Studio 2010 Database Projects - MSBuild Issue

I've recently begun using Visual Studio 2010 database projects (DBPro). I think it's a great tool and having been a .NET developer for many years it is nice to see SQL Server development get the respect it deserves in a full-fledged (well almost) Visual Studio project type.

While development productivity has been great with features like object level dependency checking, automated deployment with VSDBCMD, database dependency, data generation, pre and post build and deployment scripts, etc. I've recently encountered a rather glaring shortcoming that keeps Database Projects from having the full functionality of a C#, VB.NET, ASP.NET, etc. Visual Studio project.

The problem is related to the build process. Currently, to build a database project you need to have Visual Studio present on the machine that you are executing the build on. That might not seem like a big deal to many people since you might assume that any developer doing a build would have Visual Studio installed. Not the case. I'm currently working in a Java shop that uses SQL Server as the relational database platform. There are about 12+ developers that run any number of ANT targets to build their development environments on a frequent basis.

ANT can call MSBuild targets. That's not the problem. The problem is that to build a Visual Studio database project you must have certain MSBuild targets and a number of Microsoft assemblies in the GAC that are only installed with Visual Studio 2010. This is not the case for other Visual Studio project types. In most cases all that is needed is .NET 4.0.

While we're not dead in the water we have to rely on a solution that is less than ideal although not altogether terrible. What we have to do is build the project on one of the database developer's machines that has Visual Studio present. We then take the build output (.dbschema, .deploymanifest, etc.) and commit it to source control. From there any developer can run the ANT targets which then call VSDBCMD and deploy the database to their local development environment.

What we have works but it is a departure from how a standard Visual Studio project would work. It also requires the commital of "compiled" code which is never a good idea. The biggest struggle is making sure we the database developers build in the right configuration (Release) and commit the correct build output. It's an easy thing get wrong and can cause a lot of wasted troubleshooting time down stream if it is done incorrectly.

I have spoke to Microsoft about the issue. I am waiting to hear back from a Microsoft support escalation engineer that is in term getting in touch with the DBPro project manager for alternative options. Additionally, I have submitted a request on Microsoft Connect. Feel free to check if any progress has been made on the Microsoft Connect website.