Search Results

SSDT and Pre-Model Scripts

Hello! pre-deploy scripts in SSDT scripts may be executed before the main diff script is executed, but not before the diff script is generated. This is an important fact to understand . If you want scripts to be executed before the diff is generated, then you need to execute pre-model scripts. How you go about this is up to you, but there needs to be a few rules that must be followed: Idempotent : big word, and is used in maths. But don't let that put you off. What it means in this context is that a script can be

SSDT 16.5 Released

Hello! Recently the SQL Tools Team released a new version of both SQL Server Data Tools ( SSDT ) and SQL Server Management Studio (SSMS.)  There’s a range of bug fixes, but two new features that I am particualrly interested in. Firstly, SQLPAckage.exe and the DacFx API can now generate deployment report, deployment script and publish to a datbase all in one action. Neat! This is useful because it’s important to keep track of exactly what has changed on a database. Of course there’s nothing stopping you right now from creating executin these options in three separate actions, but there

Why is SSDT Always Rebuilding My Constraints?

Hello! Let me begin by saying that I’m a big fan of SSDT. It’s free, it works with all flavours of Visual Studio, the team do their very best to keep up-to-date with features that are released with increasing regularity by Microsfot in both Azure and SQl Server on-prem. I’ve met the team a few times, and they’re genuinely keen to engage with the users of SSDT in how it can be better, and how it can be extensible. So, SSDT is a great tool. I said great, but not perfect. It has it's limitations, the same as any tool.

Deploying SSDT Scripts (With My Good Eye Closed)

(If anyone actually gets the reference: kudos. If not, then it's just that you're not a Soundgarden fan) So, there's something that has been troubling me about deploying to databases with SSDT for some time. Actually, it's not a SSDT-exclusive problem per se, it's more of a database/release management process. What it pertains to is that most people like to create the deployment script for review prior to deploying to production. This makes sense if you are new to the Continuous Deployment process, and that maybe sometimes SSDT creates scripts that are not quite expected. You may skip this because

T SQL Tuesday: Shipping Database Changes with SSDT

Hello! Let’s see how this goes: this months subject for T SQL Tuesday is about shipping database changes, something we here are all familiar with. So I thought I’d make some notes about a tool I’m very familiar with, SQL Server Data Tools. The Good It’s free! SSDT works with Visual Studio Community up to Ultimate, and from Visual Studio 2015 onwards it comes with it’s own Visual Studio IDE. SSDT Has a NuGet package available. So you don’t need to install Visual Studio to get builds running, and crucially can control which version is used to compile at a

SSDTPokedex: Migrating a Database Into SSDT

Hello!   If you want to have the best chance of something being successful, you have to be committed to it from the start. That’s a pretty fatuous sounding statement, almost as bad as “to make something better you have to do more of the good stuff and less of the bad stuff”, so let me contextualise: if you desire to have good testing coverage on an application, then you need to be serious about testing form the first day you write code for the application. Be it manual or automated testing, you need to put the effort in terms

SSDT And Pre-Model Scripts Part 2: Execute Pre-Model Scripts Using Octopus Deploy

Hello! Part one of this post concerned the notion of SSDT and Pre-Model Scripts . This post relates to an actual implementation of executing a SQL script using Octopus Deploy. My pull request on Github was approved, so my step template can be exported from the Octopus Library . The only part that I feel requires some explanation is the SQL Scripts and the option to select a previous step. If you've read part one of this post you'll know that you keep the pre-model scripts in a folder in the database solution. These are not compiled (ie not in

SSDT 16.5 Released part 2: Using the DacFx API and Samples!

Hello! Yesterday I posted about the new release of SSDT from the SQL Tools Team at Microsoft. Two of the big changes are the ability to create the deployment report, deployment script and execute the deployment all in one command. The other change is that now for Azure two scripts are generated: one for any changes that need a connection to master, and the other script for changes to the user database. The samples yesterday showed how to execute the new method using SQLPackage, but a lot of people, myself included, have automated the deployment using the DacFx API through

Continuous Integration with Jenkins, SQL Server and Windows Containers

Why use Windows Containers? When creating database applications we need consistency in all our environments to ensure quality releases. Traditionally developers might have their own instance of SQL Server on their workstation to develop against. Database projects would be created in SSDT and pushed to source control when ready for testing. If you’re not using SSDT for database development already, then you should seriously consider it to make your life easier and increase the quality of your releases. Ed Elliot explains why in this blog post . A problem with CI for databases is that databases are a shared resource

Join Us This Wednesday at The Impact Hub King's Cross

…Where I will be presenting on the headless SSDT release from Microsoft. So up front, this does not sound like a topic which requires much discussion: There’s a NuGet package. But it is what underlies the usage of this NuGet package is where the discussion is. When it comes to build servers, the idea behind what software that needs to be installed to support builds is generally “whatever is on the developers box, that also needs to be installed here.” And that is the way I used ot think. However, this idea upfront seems simple, but if you are supporting

SSDTPokedex: Integrating Slack and VSTS Into GitHub Repo - An Infinite Improvement

Hello! One of the home projects I’m currently working on is migrating a database over from SQLite to SQL Server. There’s several tasks that need to be accomplished before we can say that this is successful. Broadly speaking they fit into the key pillars of successful software development: Plan Develop Deploy Measure   So the development part is well under way: there is a repo in GitHub of an SSDT solution that will compile locally (it works on my machine anyway.) Plus I have a couple of releases: one intentionally broken and one fixed. Now there’s plenty of tasks I

Visual Studio Code Extensions and Settings

I primarily work in Visual Studio 2017 and Visual Studio Code, using VS2017 for SSDT work, and VS Code for pretty much everything else. VS code is highly configurable, and as it’s a rainy Sunday, I thought I’d share my settings with you in case you are interested. A few colleagues at work have asked me what extensions and settings I have so, here they are as of Feb 2018. Extensions In a console session within vs code, you can do this to list them: PS C:\> code --list-extensions codezombiech.gitignore DotJoshJohnson.xml eamodio.gitlens gerane.Theme-Blackboard mohsen1.prettify-json ms-mssql.mssql ms-vscode.PowerShell ms-vsts.team PeterJausovec.vscode-docker secanis.jenkinsfile-support yzhang.markdown-all-in-one

Fix For Using Azure Active Directory and DacFX

Hello! As part of an SSDT project we have a contained user that authenticates against an Azure Active Directory group (read more on the CREATE USER page). However the account we are executing deployments with is the SQL Admin account on the Azure SQL Instance. And so we get this error - The executed script: CREATE USER [myUser] FOR EXTERNAL PROVIDER; ' Reason: '' At line:94 char:13 + Throw $toThrow + ~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (Deployment fail... ' Reason: '':String) [], RuntimeException + FullyQualifiedErrorId : Deployment failed: 'Could not deploy package. Error SQL72014: .Net SqlClient Data Provider: Msg 33159,

Keeping The Database Dev Ops Overhead Lightweight

Hello! One very important aspect of Dev Ops that is perhaps over-looked is the overhead that comes with adopting Dev Ops practices. To help explain what I mean, let’s break that sentence down a bit. What Do I Mean By “Dev Ops Practices” I have a strong suspicion that for each of the posts for this T-SQL Tuesday on Database Dev Ops, everyone will have a slightly different take. Or rather, they are going to articulate what Dev Ops means to them. And so here is my take: broadly speaking, Dev Ops is about increasing the cadence of a feature

Feedback requests to Microsoft

If you didn’t know Microsoft has a number of channels to provide feedback. Most historically user connect (connect.microsoft.com), it integrated with their internal bug tracking systems and meant that items flowed from the users to engineering and back. Well supposed to.   The SQL product group still use connect https://connect.microsoft.com/sql with a few teams also using Trello https://trello.com/b/NEerYXUU/powershell-sql-client-tools-sqlps-ssms and or Slack Slack - sqlcommunity.slack.com Visual studio is moving to https://developercommunity.visualstudio.com/spaces/8/index.html from connect and also has https://visualstudio.uservoice.com/forums/121579-visual-studio-ide for ideas VSTS has a great support and also uses MSDN, and takes requests on Uservoice https://visualstudio.uservoice.com/forums/330519-team-services PowerBI has forums and uses user voice

How To Compile SQLProj Files Using Cmdline MSBuild... Errors Included!

I recently needed to build and deploy about 40 small database projects that were in 4 or 5 different database solutions. And I needed to do this several times a day, so compiling via Visual Studio would be a boring and tedious process. So to speed up the process I decided to write the build process in an MSBuild target file and call initiate the build process through PowerShell. The targets file was simple enough to put together. This would be saved in the root location of all the solution folders as "BuildAllDBProjects.targets.xml". Then the PowerShell would be simple enough;

Where To Find Us at SQLBits

Hello! SQLBits is back! This year SQLBits is being hosted in the Grand Hallway at the Olympia which was opened way back in 1886. This marks the return of Bits to London, and in fact the south of England, for the first time since 2015. Back then it was hosted at the Excel Exhibition Centre in the East of London. If you have never heard of SQLBits before, I’d be very surprised as it is the largest SQL Server Conference in Europe and offers world class training. But enough about the brief history lesson, let’s talk about what’s coming up

Migrating SSIS Packages to SSIS Azure part Two – Automating the Deployment

Hello! If you’ve read and followed through my previous post, you will have World Wide Importers Integration Services project running in SSIS Azure. It’s all very interesting, go and have a read . One thing that is missing form that guide, the documentation, and SSIS in general, is how to automate SSIS Deployments. In the WWI SSIS project, there are connection managers that we had to manually update the values of to get it to work post-deploy. This is exactly the opposite of what we want to do. Back when SQL Server 2012 was known as Denali, one of the

Automating SQL Server Performance Testing

You run performance tests as well as functional tests when deploying new code changes to SQL Server, right? Not many people do, I think you should, and this article will show you how to do it by harnessing an existing performance tool, rather than writing your own monitoring infrastructure from scratch. Any good performance monitoring tool that records information to a database will do fine, and we prefer to use Sentry One . Here are the steps to accomplish this. Create a baseline database When you release your database change, you want to have something to compare against as an

Migrating SSIS Packages to SSIS Azure

Hello! In case you missed the announcement (and there were a lot of announcements during MSIgnite), SQL Server Integration Services is in Public Preview on Azure! I’ve written about it elsewhere in greater depth , but here are the headlines: It makes use of SSIS Scale Out , which was released as part of SQL Server 2017 . Although it is based on SSIS Scale Out, you can’t actually configure SSIS Scale Out to run on the instance. If this confuses you then read my in-depth post. SSISDB is installed in either SQL Azure or on a Managed Instance. You