June 7, 2016 at 9:31 pm
Comments posted to this topic are about the item Continuous Integration with Microsoft SQL Server
June 8, 2016 at 2:01 am
"This scenario has a limitation worth pointing out. It suitable for schema changes only, with no regard for data and, more importantly, constraints over the data."
Unless these points are addressed then we haven't really solved anything.
June 8, 2016 at 2:32 am
Thanks for sharing. And yes, we're also doing a fair bit of CI in the database space. We have a dedicated environment for that, but follow a similar process for standing up an Alpha testing environment using near production data.
We use TFS for version control and builds, with a customised build template that will handle the standard stack (SSDB, SSAS, SSRS, SSIS). We run two templates, one for a build of artefacts and one for deployment + testing of those artefacts, using the same Powershell script + Manifest for deployment to higher environments. We use BDD (Specflow + custom data generation and testing schemas for data generation off features) to exercise the product features; all green on the Test Manager Test suite allows deployment artefacts to get generated for release.
Pickles on the end of the stream for living documentation, although getting business users to read that is still a chore. Still, it shows us per build how our tests are and what has failed and it has saved our bacon a few times when building new features and having to remember how something worked.
Next step is to include the deployment documents in the build and start the next phase for CD.
June 8, 2016 at 2:57 am
Thank you for your comment. I wouldn't go as far as state that it doesn't solve anything. You can automate and track schema changes on individual files, including constraints.
What you can't do is apply new constraints, which are detected by the SSDT, without inserting the reference data previously.
Also, you can compare target tables on two databases and generate DML scripts using Visual Studio 2015 with SQL Server Data Tools.
June 8, 2016 at 3:34 am
I had my hopes up, right up until the end where it's noted that constraints aren't handled.
So, just another half baked solution. Nice approach, but still not there.
----
Jack D. Leach
www.dymeng.com
June 8, 2016 at 4:36 am
As pointed out, this might not be a complete solution for Continuous Integration, but it is a great starting point, and it gave me some ideas of how to proceed.
June 8, 2016 at 7:06 am
Just to quickly answer the final question:
So, is anyone doing CI with SQL server yet?
These days, it seems everyone is doing CI with SQL Server. Redgate (sponsors of this forum) have a suite of products dedicated to just that. Just click the Redgate link at the top right of any page on this forum then click on DLM. (Disclaimer: I do not work for Redgate and we are not using DLM where I work)
Redgate are hardly alone in this space! There are plenty of other players and lots of home-grown solutions.
Gerald Britton, Pluralsight courses
June 9, 2016 at 2:50 pm
Suzy T (6/8/2016)
"This scenario has a limitation worth pointing out. It suitable for schema changes only, with no regard for data and, more importantly, constraints over the data."Unless these points are addressed then we haven't really solved anything.
I don't know that's true. Plenty of people can manage data separately and don't worry about moving it between environments. I've had a number of applications that didn't every do this.
Data and schema are separate, and while it's nice to be able to build and test both, getting at least one done is a good first step.
June 9, 2016 at 2:52 pm
g.britton (6/8/2016)
Just to quickly answer the final question:So, is anyone doing CI with SQL server yet?
These days, it seems everyone is doing CI with SQL Server. Redgate (sponsors of this forum) have a suite of products dedicated to just that. Just click the Redgate link at the top right of any page on this forum then click on DLM. (Disclaimer: I do not work for Redgate and we are not using DLM where I work)
Redgate are hardly alone in this space! There are plenty of other players and lots of home-grown solutions.
I'll say the Redgate solutions for CI/CD are pretty well done. Not perfect, but we handle lots of things, including data and schema changes. We handle constraints. We can manage renames, table/column splits, partitioning, data changes in migrations and more.
There are two ways of doing this. One with with SQL Source Control and the DLM Automation Suite. The other is with ReadyRoll. Both of these lend themselves to good automation and CI/CD processes.
Disclosure: I work for Redgate Software
June 14, 2016 at 2:55 am
Steve Jones - SSC Editor (6/9/2016)
g.britton (6/8/2016)
Just to quickly answer the final question:So, is anyone doing CI with SQL server yet?
These days, it seems everyone is doing CI with SQL Server. Redgate (sponsors of this forum) have a suite of products dedicated to just that. Just click the Redgate link at the top right of any page on this forum then click on DLM. (Disclaimer: I do not work for Redgate and we are not using DLM where I work)
Redgate are hardly alone in this space! There are plenty of other players and lots of home-grown solutions.
I'll say the Redgate solutions for CI/CD are pretty well done. Not perfect, but we handle lots of things, including data and schema changes. We handle constraints. We can manage renames, table/column splits, partitioning, data changes in migrations and more.
There are two ways of doing this. One with with SQL Source Control and the DLM Automation Suite. The other is with ReadyRoll. Both of these lend themselves to good automation and CI/CD processes.
Disclosure: I work for Redgate Software
Thank you for the reply, I'm familiar with these tools but still haven't really tried them out.
For the given constraints limitation, you can address it manually, reordering script execution:
1 - Create the new table
2 - Insert new reference data
3 - Create new column on old table with a default value that exists on new table column
4 - Create FK constraint on old table
The issue here is that we'd like an automated process and not havr to re-order scripts manually.
Does Redgate CI/CD tool perform this re-ordering automatically?
June 14, 2016 at 9:49 am
I'm not sure what you mean by given constraints limitation.
If you want to change add a column to a table that is based on an FK to some new table, that's easy. In fact, you can do that a few ways. Given your list, you can:
1 - Create the new table
2 - Insert new reference data
3 - Create new column on old table with a default value that exists on new table column
4 - Create FK constraint on old table
Or
1 - Create new column on old table with a default value that exists on new table column
2 - Create the new table
3 - Insert new reference data
4 - Create FK constraint on old table
Really there is no precedence other than the FK coming last.
The Redgate tools certainly can facilitate this. If you create this in Dev, with SQL Source Control and the DLM tools, we will track the new table and reference data. Obviously the table create comes before the data. As far as the changes to the existing table with a default, we would track and detect that, but the scripting could put the either before or after the new table, and things wouldn't matter.
Creating a FK would come after both, because SQL Compare would note the FK can't be created until the new table and new column in old table exist.
In ReadyRoll, we would track the changes as they occurred, and they'd be executed in the order you made them in.
June 15, 2016 at 3:46 am
"In ReadyRoll, we would track the changes as they occurred, and they'd be executed in the order you made them in."
In one word: Excellent!
June 15, 2016 at 11:02 am
Yes and no. I'll be doing some content here to explain how this works and how you can use it, but I'd encourage you to play with it in a small db.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply