April 6, 2015 at 8:22 pm
Comments posted to this topic are about the item What is Database Continuous Integration?
April 7, 2015 at 12:48 am
We have evolved in to using this method.
Whilst we use source control for database objects, this is a background automatic process to ensure changes to objects are captured. Though we don't use CI from source control.
Deploying database changes are not just about changing database objects. Often there are other data update/population changes which are required.
So, we have a daily process which restores all key databases to the Dev server, and we have a folder location on our dev server where all scripts placed inside it will then be executed in order. These scripts will modify tables, update functions, update procs, update/clean prepopulate data, and even run procedures/jobs (basically anything a developer needs them to do). These scripts are actually subdivided in to folders based on mini projects.
So in essence, each subfolder ends up with ordered and fully tested deployment scripts, which is perfect for a Dba to then use.
April 7, 2015 at 5:25 am
I think you meant 'tome' rather than 'tomb'!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2015 at 6:17 am
Howdy all
I am curious to know what CI tools and/or processes you all are using on your database and if those tools are working well. We are using MS SQL Server.
Thanks
April 7, 2015 at 7:05 am
Thanks for the note, Phil. Missed that in a review.
April 7, 2015 at 7:10 am
cbowman (4/7/2015)
Howdy allI am curious to know what CI tools and/or processes you all are using on your database and if those tools are working well. We are using MS SQL Server.
Thanks
Disclosure: I work for Red Gate Software.
We have a few tools at Red Gate that have helped us with this and that we sell. We've built SQL Release and SQL CI, part of our bundles, that enable database CI for SQL Server on most CI platforms. We tend to show Team City, but we can work with Jenkins, Bamboo, or TFS.
Microsoft has done some work to TFS to help, but it doesn't always work easily with database code. It requires some work to be able to automatically build the scripts to update your database. Stored procedures/functions are easier than schema changes, and it's not intuitively a hard process, but a somewhat complex one from the automation standpoint.
There are people that follow a change script process, producing and saving .Sql files for all changes in VCS and then using a process to check them out and run them in the order they were developed on another database for testing. That can work, just requires the discipline in scripting.
The testing side of things usually occurs with either a framework like tSQLt (tsqlt.org) or with the Microsoft Unit Testing Framework. Both of these can be driven programmatically and it's a question of just calling the tests against your database. Getting the specific feedback isn't always easy, but it can be done and it's not so bad that I think it's useless.
Ultimately it's a simple idea.
- check out code
- run code in the correct order on a database
- run tests
The key is to do this so often that you don't pile up changes that have to be examined when there's a failure. Any process/tool you use to do this automatically, always, will work.
April 7, 2015 at 10:38 am
I use PowerShell scripts to automate the orderly release of scripts - they are easy to modify when the environment changes.
April 7, 2015 at 10:45 am
John Hick-456673 (4/7/2015)
I use PowerShell scripts to automate the orderly release of scripts - they are easy to modify when the environment changes.
How do these fit within your CI environment?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2015 at 11:21 am
Phil Parkin (4/7/2015)
John Hick-456673 (4/7/2015)
I use PowerShell scripts to automate the orderly release of scripts - they are easy to modify when the environment changes.How do these fit within your CI environment?
Ha! It is a very quasi CI environment; mostly, when I feel updates need to go out, I fire off the batch file that calls the ps1 scripts - Parameters are stored in txt files. The txt files store the order of release and what types of scripts may go to which server(s) (transactional replication is involved); simple to run, maybe not-so-simple to code and set up, now that I think about it.
But, hey, I can share the ps1 scripts, if anyone wants them.
April 8, 2015 at 7:35 am
John Hick-456673 (4/7/2015)
Ha! It is a very quasi CI environment; mostly, when I feel updates need to go out, I fire off the batch file that calls the ps1 scripts - Parameters are stored in txt files. The txt files store the order of release and what types of scripts may go to which server(s) (transactional replication is involved); simple to run, maybe not-so-simple to code and set up, now that I think about it.
But, hey, I can share the ps1 scripts, if anyone wants them.
I suspect it's like many processes I've seen (and built): it's dependent on you. What seems simple to you, might not be simple for others.
The more formal CI systems try to remove reliance on any one person, and ensure there isn't knowledge transfer stuck in someone's head that makes it hard for others to run the system.
April 8, 2015 at 7:40 am
So, is there no one out there using an automatic build/integration system for their database code? Is this why the article simply said we should all be doing this but not giving us any specific examples or advice?
I am not trying to be argumentative or disagreeable, I am just curious. This something we are very interested in.
April 8, 2015 at 7:45 am
There are people doing this with databases. Red Gate has numerous clients doing it, and I've run into others that have built their own systems.
The article was written to introduce people to the concept. There are numerous articles that show how it can be done. Here are a few:
https://www.simple-talk.com/sql/sql-tools/database-build-and-release-with-jenkins/%20/
https://www.simple-talk.com/blogs/2012/04/24/database-continuous-integration-step-by-step/
April 9, 2015 at 12:42 pm
Steve,
Are there any recommendations or best practices for getting started with TSQL Unit tests? That seems like the next logical progression for our processes, but I'm never quite sure what to do to get started. I've seen some people start with the level of "does the DB exist", but if you're using something like SQL Source Control or SSDT that seems like the wrong place to start. I'd appreciate some guidance or examples from people who've been there.
April 13, 2015 at 10:03 am
There's a tSQLt course on Pluralsight that might be worth a look.
April 13, 2015 at 11:21 am
In addition to David's link, I've got a bit on my blog (more coming): https://voiceofthedba.wordpress.com/tag/tsqlt/
There are also some posts from David Green: http://blog.dgta.co.uk/
We've got a few things at SimpleTalk : https://www.simple-talk.com/author/dave-green/
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply