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.
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.