In a previous blog I describe in twenty one easy steps how to set up continuous integration using a combination of TeamCity and Red Gate tools.
Okay, it’s easy only in a relative sense, giving that until recently database continuous integration was almost impossible to set up. One would always suspect that a twenty-one-step process is a long way from the optimal simplicity. Also, having to negotiate the command lines and learning the intricacies of their respective switches and exit codes is tedious at best.
We’ve therefore taken the implementation to the next level and removed the need to use the command line to get database continuous integration up and running. We achieved this by developing a TeamCity plugin. This means that once the plugin is installed, Red Gate (SQL Server) will appear in the TeamCity dropdown as a custom build step in its own right.
All you need to do is to specify your database connection details and the plugin will do the rest. Assuming you’ve configured the VCS root to point to your SQL Source Control or SQL Connect database in source control, and a VCS trigger to fire on each check-in, your specified database will always be kept up to date with the latest incarnation in your version control system.
We’ve released the plugin as early as we could to get community feedback, as we’re fully aware that there’s scope to improve it further. We just need to know what’s crucial and what’s just a nice-to-have.
You can try out the plugin for yourself by visiting the following webpage, which also includes step by step instructions.
There is a feedback link on the plugin page that we’d encourage you to use if you’d like anything added or changed, or indeed what we can do to make it even more simple!
Production databases can get very large.
This in itself is to be expected, but when a copy of the database is needed the database must be restored, requiring additional and costly storage. For example, if you want to give each developer a full copy of your production server, you’ll need n times the storage cost for your n-developer team. The same is true for any test databases that are created during the course of your project lifecycle.
If you’ve read my previous blog posts, you’ll be aware that I’ve been focusing on the database continuous integration theme. In my CI setup I create a “production”-equivalent database directly from its source control representation, and use this to test my upgrade scripts. Despite this being a perfectly valid and practical thing to do as part of a CI setup, it’s not the exact equivalent to running the upgrade script on a copy of the actual production database.
So why shouldn’t I instead simply restore the most recent production backup as part of my CI process?
There are two reasons why this would be impractical.
1. My CI environment isn’t an exact copy of my production environment. Indeed, this would be the case in a perfect world, and it is strongly recommended as a good practice if you follow Jez Humble and David Farley’s “Continuous Delivery” teachings, but in practical terms this might not always be possible, especially where storage is concerned. It may just not be possible to restore a huge production database on the environment you’ve been allotted.
2. It’s not just about the storage requirements, it’s also the time it takes to do the restore. The whole point of continuous integration is that you are alerted as early as possible whether the build (yes, the database upgrade script counts!) is broken. If I have to run an hour-long restore each time I commit a change to source control I’m just not going to get the feedback quickly enough to react.
So what’s the solution?
Red Gate has a technology, SQL Virtual Restore, that is able to restore a database without using up additional storage.
Although this sounds too good to be true, the explanation is quite simple (although I’m sure the technical implementation details under the hood are quite complex!) Instead of restoring the backup in the conventional sense, SQL Virtual Restore will effectively mount the backup using its HyperBac technology. It creates a data and log file, .vmdf, and .vldf, that becomes the delta between the .bak file and the virtual database. This means that both read and write operations are permitted on a virtual database as from SQL Server’s point of view it is no different from a conventional database. Instead of doubling the storage requirements upon a restore, there is no ‘duplicate’ storage requirements, other than the trivially small virtual log and data files (see illustration below). The benefit is magnified the more databases you mount to the same backup file. This technique could be used to provide a large development team a full development instance of a large production database.
It is also incredibly easy to set up. Once SQL Virtual Restore is installed, you simply run a conventional RESTORE command to create the virtual database. This is what I have running as part of a nightly “release test” process triggered by my CI tool.
RESTORE DATABASE WidgetProduction_Virtual
NORECOVERY, STATS=1, REPLACE
RESTORE DATABASE WidgetProduction_Virtual WITH RECOVERY
Note the only change from what you would do normally is the naming of the .vmdf and .vldf files. SQL Virtual Restore intercepts this by monitoring the extension and applies its magic, ensuring the ‘virtual’ restore happens rather than the conventional storage-heavy restore.
My automated release test then applies the upgrade scripts to the virtual production database and runs some validation tests, giving me confidence that were I to run this on production for real, all would go smoothly.
For illustration, here is my 8Gb production database:
And its corresponding backup file:
Here are the .vldf and .vmdf files, which represent the only additional used storage for the new database following the virtual restore.
The beauty of this product is its simplicity. Once it is installed, the interaction with the backup and virtual database is exactly the same as before, as the clever stuff is being done at a lower level.
SQL Virtual Restore can be downloaded as a fully functional 14-day trial.
Technorati Tags: SQL Server
The rise in demand for database continuous integration has forced me to skill-up in various new tools and technologies, particularly build servers. We have been using JetBrain’s TeamCity here at Red Gate for a couple of years now, having replaced the ageing CruiseControl.NET, so it was a natural choice for us to use this for our database CI demos.
Most of our early adopter customers have also transitioned away from CruiseControl, the majority to TeamCity and Microsoft’s TeamBuild. However, more recently, for reasons we’ve yet to fully comprehend, we’ve observed a significant surge in the number of evaluators for Atlassian’s Bamboo.
I installed this a couple of weeks back to satisfy myself that it works seamlessly with Red Gate tools. As you would expect Bamboo’s UI has the same clean feel found in any Atlassian tool (we use JIRA extensively here at Red Gate). In the coming weeks I will post a short step-by-step guide to setting up SQL Server continuous integration using the Red Gate command lines. To help us further optimize the integration between these tools I’d be very keen to hear from any Bamboo users who also use Red Gate tools who might be willing to participate in usability tests and other similar research in exchange for Amazon vouchers. If you are interested in helping out please contact me at David dot Atkinson at red-gate.com
I recently spoke with Sarah, the product marketing manager for Bamboo, and we ended up having a detailed conversation about database CI, which has been meticulously documented in the form of a blog post on Atlassian’s website:
We’ve also managed to persuade Red Gate marketing to provide a great free-tool offer, provide a free SQL Source Control or SQL Connect license to Atlassian users provided it is claimed before the end of June! Full details are at the bottom of the post.
Technorati Tags: sql server
Because there is so little information on database continuous integration in the wild, I’ve taken it upon myself to aggregate as much as possible and post the links to this blog. Because it’s my area of expertise, this will focus on SQL Server and Red Gate tooling, although I am keen to include any quality articles that discuss the topic in general terms.
Please let me know if you find a resource that I haven’t listed!
General database Continuous Integration
· What is Database Continuous Integration? (David Atkinson)
· Continuous Integration for SQL Server Databases (Troy Hunt)
· Installing NAnt to drive database continuous integration (David Atkinson)
· Continuous Integration Tip #3 – Version your Databases as part of your automated build (Doug Rathbone)
· How the "migrations" approach makes database continuous integration possible (David Atkinson)
· Continuous Integration for the Database (Keith Bloom)
Setting up Continuous Integration with Red Gate tools
· Continuous integration for databases using Red Gate tools - A technical overview (White Paper, Roger Hart and David Atkinson)
· Continuous integration for databases using Red Gate SQL tools (Product pages)
· Database continuous integration step by step (David Atkinson)
· Database Continuous Integration with Red Gate Tools (video, David Atkinson)
· Database schema synchronisation with RedGate (Vincent Brouillet)
· Database continuous integration and deployment with Red Gate tools (David Duffett)
· Automated database releases with TeamCity and Red Gate (Troy Hunt)
· How to build a database from source control (David Atkinson)
· Continuous Integration Automated Database Update Process (Lance Lyons)
· Evolutionary Database Design (Martin Fowler)
· Recipes for Continuous Database Integration: Evolutionary Database Development (book, Pramod J Sadalage)
· Recipes for Continuous Database Integration (book, Pramod Sadalage)
· The Red Gate Guide to SQL Server Team-based Development (book, Phil Factor, Grant Fritchey, Alex Kuznetsov, Mladen Prajdic)
· Using SQL Test Database Unit Testing with TeamCity Continuous Integration (Dave Green)
· Continuous Database Integration (covers MySQL, Perason Education)
Developers have long since had to context switch between two IDEs, Visual Studio for application code development and SQL Server Management Studio for database development. While this is accepted, especially given the richness of the database development feature set in SSMS, loading a separate tool can seem a little overkill.
This is where SQL Connect comes in. This is an add-in to Visual Studio that provides a connected development experience for the SQL Server developer. Connected database development involves modifying a development sandbox database, as opposed to offline development, where SQL text files are modified independently of the database. One of the main complaints of Data Dude (VS DBPro) is that it enforces the offline approach. This gripe is what SQL Connect addresses.
If you don’t already use SQL Source Control, you can get up and running with SQL Connect by adding a new project to your Visual Studio solution as follows:
Then choose your existing development database and you’re ready to go.
If you already use SQL Source Control, you will need to link SQL Connect to your existing database scripts folder repository, so SQL Connect and SQL Source Control can be used collaboratively (note that SQL Source Control 126.96.36.199 or later is required).
Locate the repository (this can be found in the Setup tab in SQL Source Control)…
…and create a working folder for it (here I’m using TortoiseSVN).
Back in Visual Studio, locate the SQL Connect panel (in the View menu if it hasn’t auto loaded) and select Import SQL Source Control project
Locate your working folder and click Import.
This creates a Red Gate database project under your solution:
From here you can modify your development database, and manage your changes in source control. To associate your development database with the project, right click on the project node, select Properties, set the database and Save.
Now you’re ready to make some changes. Locate the object you’d like to modify in the Solution Explorer, and double click it to invoke a query window or table designer. You also have the option to edit the creation SQL directly using Edit SQL File in Project.
Keeping the development database and Visual Studio project in sync is as easy as clicking on a button.
One you’ve made your change, you can use whichever mechanism you choose to commit to source control. Here I’m using the free open-source AnkhSVN to integrate Subversion with Visual Studio.
Maintaining your database in a Visual Studio solution means that you can commit database changes and application code changes in the same changeset. This is desirable if you have continuous integration set up as you want to ensure that all files related to a change are committed atomically, so you avoid an interim “broken build”.
More discussion on SQL Connect and its benefits can be found in the following article on Simple Talk: No More Disconnected SQL Development in Visual Studio
The SQL Connect project team is currently assessing the backlog for the next development effort, and they’d appreciate your feature suggestions, as well as your votes on their suggestions site:
A 28-day free trial of SQL Connect is available from http://www.red-gate.com/products/sql-development/sql-connect/
Technorati Tags: SQL Server
Testing a database upgrade script as part of a continuous integration process will only work if there is an easy way to automate the generation of the upgrade scripts.
There are two common approaches to managing upgrade scripts.
The first is to maintain a set of scripts as-you-go-along. Many SQL developers I’ve encountered will store these in a folder prefixed numerically to ensure they are ordered as they are intended to be run. Occasionally there is an accompanying document or a batch file that ensures that the scripts are run in the defined order.
Writing these scripts during the course of development requires discipline. It’s all too easy to load up the table designer and to make a change directly to the development database, rather than to save off the ALTER statement that is required when the same change is made to production. This discipline can add considerable overhead to the development process. However, come the end of the project, everything is ready for final testing and deployment.
The second development paradigm is to not do the above. Changes are made to the development database without considering the incremental update scripts required to effect the changes. At the end of the project, the SQL developer or DBA, is tasked to work out what changes have been made, and to hand-craft the upgrade scripts retrospectively.
The end of the project is the wrong time to be doing this, as the pressure is mounting to ship the product. And where data deployment is involved, it is prudent not to feel rushed.
Schema comparison tools such as SQL Compare have made this latter technique more bearable. These tools work by analyzing the before and after states of a database schema, and calculating the SQL required to transition the database.
Schema comparison tools are huge time savers, but they have their limitations. There are certain changes that can be made to a database that can’t be determined purely from observing the static schema states. If a column is split, how do we determine the algorithm required to copy the data into the new columns? If a NOT NULL column is added without a default, how do we populate the new field for existing records in the target? If we rename a table, how do we know we’ve done a rename, as we could equally have dropped a table and created a new one?
All the above are examples of situations where developer intent is required to supplement the script generation engine.
SQL Source Control 3 and SQL Compare 10 introduced a new feature, migration scripts, allowing developers to add custom scripts to replace the default script generation behavior. These scripts are committed to source control alongside the schema changes, and are associated with one or more changesets.
Before this capability was introduced, any schema change that required additional developer intent would break any attempt at auto-generation of the upgrade script, rendering deployment testing as part of continuous integration useless.
SQL Compare will now generate upgrade scripts not only using its diffing engine, but also using the knowledge supplied by developers in the guise of migration scripts.
In future posts I will describe the necessary command line syntax to leverage this feature as part of an automated build process such as continuous integration.
Technorati Tags: sql server
This post will describe how to set up basic database continuous integration using TeamCity to initiate the build process, SQL Source Control to put your database under source control, and the SQL Compare command line to keep a test database up to date.
In my example I will be using Subversion as my source control repository. If you wish to follow my steps verbatim, please make sure you have TortoiseSVN, SQL Compare and SQL Source Control installed.
Downloading and Installing TeamCity
TeamCity (http://www.jetbrains.com/teamcity/index.html) is free for up to three agents, so it a great no-risk tool you can use to experiment with.
1. Download the latest version from the JetBrains website. For some reason the TeamCity executable didn’t download properly for me, stalling frustratingly at 99%, so I tried again with the zip file download option (see screenshot below), which worked flawlessly.
2. Run the installer using the defaults. This results in a set-up with the server component and agent installed on the same machine, which is ideal for getting started with ease.
3. Check that the build agent is pointing to the server correctly. This has caught me out a few times before. This setting is in C:\TeamCity\buildAgent\conf\buildAgent.properties and for my installation is serverUrl=http\://localhost\:80 . If you need to change this value, if for example you’ve had to install the Server console to a different port number, the TeamCity Build Agent Service will need to be restarted for the change to take effect.
4. Open the TeamCity admin console on http://localhost , and specify your own designated username and password at first startup.
Putting your database in source control using SQL Source Control
5. Assuming you’ve got SQL Source Control installed, select a development database in the SQL Server Management Studio Object Explorer and select Link Database to Source Control.
6. For the Link step you can either create your own empty folder in source control, or you can select Just Evaluating, which just creates a local subversion repository for you behind the scenes.
7. Once linked, note that your database turns green in the Object Explorer. Visit the Commit tab to do an initial commit of your database objects by typing in an appropriate comment and clicking Commit.
8. There is a hidden feature in SQL Source Control that opens up TortoiseSVN (provided it is installed) pointing to the linked repository. Keep Shift depressed and right click on the text to the right of ‘Linked to’, in the example below, it’s the red Evaluation Repository text. Select Open TortoiseSVN Repo Browser.
This screen should give you an idea of how SQL Source Control manages the object files behind the scenes.
Back in the TeamCity admin console, we’ll now create a new project to monitor the above repository location and to trigger a ‘build’ each time the repository changes.
9. In TeamCity Adminstration, select Create Project and give it a name, such as “My first database CI”, and click Create.
10. Click on Create Build Configuration, and name it something like “Integration build”.
11. Click VCS settings and then Create And Attach new VCS root. This is where you will tell TeamCity about the repository it should monitor.
12. In my case since I’m using the Just Evaluating option in SQL Source Control, I should select Subversion.
13. In the URL field paste your repository location. In my case this is
file:///C:/Users/David.Atkinson/AppData/Local/Red Gate/SQL Source Control 3/EvaluationRepositories/WidgetDevelopment/WidgetDevelopment
14. Click on Test Connection to ensure that you can communicate with your source control system. Click Save.
15. Click Add Build Step, and Runner Type: Command Line. Should you be familiar with the other runner types, such as NAnt, MSBuild or Powershell, you can opt for these, but for the same of keeping it simple I will pick the simplest option.
16. If you have installed SQL Compare in the default location, set the Command Executable field to:
C:\Program Files (x86)\Red Gate\SQL Compare 10\sqlcompare.exe
17. Flip back to SSMS briefly and add a new database to your server. This will be the database used for continuous integration testing.
18. Set the command parameters according to your server and the name of the database you have created. In my case I created database RedGateCI on server .\sql2008r2
/scripts1:. /server2:.\sql2008r2 /db2:RedGateCI /sync /verbose
Note that if you pick a server instance that isn’t on your local machine, you’ll need the TCP/IP protocol enabled in SQL Server Configuration Manager otherwise the SQL Compare command line will not be able to connect.
19. Save and select Build Triggering / Add New Trigger / VCS Trigger. This is where you tell TeamCity when it should initiate a build. Click Save.
20. Now return to SQL Server Management Studio and make a schema change (eg add a new object) to your linked development database. A blue indicator will appear in the Object Explorer. Commit this change, typing in an appropriate check-in comment. All being good, within 60 seconds (a TeamCity default that can be changed) a build will be triggered.
21. Click on Projects in TeamCity to get back to the overview screen:
The build log will show you the console output, which is useful for troubleshooting any issues:
That’s it! You now have continuous integration on your database. In future posts I’ll cover how you can generate and test the database creation script, the database upgrade script, and run database unit tests as part of your continuous integration script.
If you have any trouble getting this up and running please let me know, either by commenting on this post, or email me directly using the email address below.
Technorati Tags: SQL Server
Typically, when I install SQL Server Management Studio and run it for the first time, the first thing I do is to close the Object Explorer Details window. After all, it doesn’t seem to serve a useful purpose, duplicating information that already exists in the Object Explorer.
However, I have discovered that it has one important redeeming capability, the ability to allow multi-selection by holding the ctrl or shift key depressed during the selection.
Why is this useful?
If I want to rename an object, or indeed make any textual modification to a number of stored procedures, I can simply script as drop/create, do a search/replace, and click execute to apply the changes.
If I need to use SQL Prompt’s Format SQL feature to format all the stored procedures in my database, instead of scripting out each of them individually and applying the changes, which would be impractical for all by the smallest databases, using the Object Explorer Details pane I can script them all to a single query window, apply my formatting changes to my entire stash of procs, and execute the entire script in one fell swoop.
No doubt there are other benefits to multi-selection, or indeed the Object Explorer Details pane. If you know of any, please post a comment on this blog post!
On April 24th (Europe) and May 1st (North America), Red Gate is hosting a free educational webinar covering more SQL Prompt tips and tricks. Sign up here: http://www.red-gate.com/products/sql-development/sql-prompt/webinars
Technorati Tags: SQL Server
There are a number of free T-SQL prettifiers out there, covering both code layout and syntax highlighting, but Format SQL is different in that it leverages the engine that powers SQL Prompt, the popular add-in that extends SQL Server Management Studio for SQL developers and DBAs.
Simply type or paste T-SQL into the top text area, and it formats your SQL on the fly in the lower text area.
This was developed by a small team at Red Gate during Down Tools Week, a quarterly occurrence, where everyone in the company is free to choose to work on anything they like, so long as it’s beneficial to Red Gate.
SQL Data Masker, SQL Index Manager and SQL Test are success stories borne out of previous Down Tools Weeks.
I know the Format SQL team is keen to get community feedback, especially if there are good ideas on how this technology could be put to good use elsewhere. You can provide your comments by clicking on Tell us about your experience on the http://www.format-sql.com/ web page itself.
Technorati Tags: SQL Server
If your organization has continuous integration in place, it will most likely contain a build step in which your latest application source code gets pulled from source control, compiled, and optionally subjected to a various tests.
If you’re a Microsoft shop, chances are that your application works alongside an instance of SQL Server.
SQL Source Control allows you to put your schema objects in source control by maintaining a set of per-object creation scripts representing in your existing version control system, such as Team Foundation Server, Subversion, SourceGear Vault, etc.
Here I will describe how to use the appropriate Red Gate command line interface to recreate the database from these source creation scripts, which is essential in order to achieve database continuous integration.
In SQL Compare Pro’s Program Files folder, you will find the command line interface executable, sqlcompare.exe. Below I will provide examples of how this is called from a DOS batch file and NAnt.
You can either deploy a new database or deploy to an existing one. If doing the former, use sqlcmd.exe to drop and recreate the database as follows.
Sqlcmd.exe -E -SYourCIServer -Q "ALTER DATABASE YourCIDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE YourCIDatabase "
Sqlcmd.exe -E -SYourCIServer -Q " CREATE DATABASE YourCIDatabase"
<exec program= "sqlcmd.exe">
<arg value="-SYourCIServer "/>
<arg value="-Q "ALTER DATABASE YourCIDatabase SET
SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE
<target name="create_CI_DB" depends="drop_CI_DB">
<exec program= "sqlcmd.exe" failonerror="true">
<arg value="-Q "CREATE DATABASE
The -b switch ensures that an exit code of 1 will be return if the command fails, aborting the script.
We use sqlcompare.exe to apply the database changes by setting the source as the creation files scripts folder, and the target as the database (YourCIDatabase).
Sqlcompare.exe /scripts1:YourScriptsFolder /server2:YourCIServer /db2:YourCIDatabase /sync
<target name="DeployToCIDatabase" depends="create_CI_DB">
<exec program="sqlcompare.exe" failonerror="true">
The /sync switch ensures that the changes are applied. It is possible to use /ScriptFile:YourDeploymentScript.sql to generate the script.
You may have noticed the absence of a command to pull the latest scripts folder from source control. This is a task that is generally taken care of by the continuous integration tool, rather than the build script itself.
In an upcoming blog post I will describe how to configure JetBrains TeamCity with a NAnt script to monitor source control for schema changes, and trigger a process to keep our continuous integration database up to date.
<div style="margin: 0px; padding: 0px; float: none; display: inline;" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:66811b12-0a44-4aa7-b7b6-749356782948" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/SQL+Server" rel="tag">SQL Server</a></div>v