topsliner.blogg.se - Compare databases using dbschema

For a proof of concept it’s perfectly fine to do this on your local machine. (Not in the same directory that you have pointed SQL Source Control at).

Save your script to a file called buildDatabase.ps1 somewhere in your source control repository.

To learn how to do that check out this post, but I’d worry about that later, after you have something basic working in Jenkins. You may also want to think about parameterising and encrypting your passwords if you have included them (if using WinAuth you do not need passwords, make sure the Jenkins service is running as an appropriate user).

We’ll pass these variables into the script from Jenkins later. Once the script works locally change the values of the $scriptsFolder and $packageVersion parameters to args and args.Optionally uncomment the last two sections to run tSQLt tests and/or deploy to an integration/test database.Note that if you run it multiple times you’ll need to increment the version number or delete old packages from the output directory. Edit the variables at the top appropriately and run the script to ensure it works.Open PowerShell ISE and copy the following script into the top section: Then (optionally) it will run your tSQLt tests against the package and/or deploy the code to a persistent integration or test database. Once validated DLM Automation will create a NuGet package for this version of the database in an output directory. If you are using an alternate SQL Server instance you can carry out your schema validation on that instance instead. We are going to use the Redgate DLM Automation PowerShell cmdlets to call the Redgate SQL Compare engine, NuGet, SQLCMD and various other tools to do the heavy lifting.ĭLM Automation will take our source code, create a new temporary database in localDB and deploy our source code to it to ensure the code compiles (a process called schema validation). You should test this PowerShell script locally before trying to run it from Jenkins. We need to write a PowerShell script to build, test and deploy your database. If you have these bits and pieces in place, you’re ready to follow in my footsteps! Step 1: Create your PowerShell build script

This post assumes that you already have SQL Source Control set up with SVN and that you broadly understand how it works. LocalDB (if you are using it) also needs to be installed on your Jenkins node, otherwise the account Jenkins runs as needs to have access to an alternate SQL Server instance. You need to have DLM Automation installed on your Jenkins node. Your Jenkins node needs to run on a Microsoft OS and have a clear line of communication to your source control repository. In my case I do everything locally (I was preparing a POC) but you don’t have to. And, finally, the latest build of Jenkins CI (open source) which you can get from the Jenkins website.(You can use the free 14-day trial to follow this tutorial.) DLM Automation v2 or later (Redgate tool, comes with SQL Toolbelt) for the Redgate PowerShell cmdlets.If you work in SSMS, SQL Source Control will make versioning your database trivial.) If you don’t source control your DB already you should sort that out first. (Database source control is a prerequisite for database CI. I’m using Tortoise SVN which is open source. SQL Source Control (Redgate tool) to connect your database to a source control repository.Failing that a SQL Server instance that you can connect to. If you use Oracle databases you should go here instead. This tutorial is for a SQL Server databases. I was so impressed that I documented the process in this step by step tutorial.

I got it up and running in about an hour and was impressed how easy Jenkins was to configure, despite the fact I had never used it before. We do this by building, testing and deploying every commit and reporting any errors.Ī little while ago (when I still worked for Redgate) a customer asked for a database CI demo using the Redgate tools and Jenkins. While Continuous Integration (CI) started with application code you should apply the same principles to databases. The point is that CI reduces your feedback loop such that changes that break the build are caught as soon as they are committed to source control. Edit: Originally written in Oct 2013, this post was updated in Nov 2016 due to software updates from Redgate.