Forward and reverse integration?
allmhuran
Posts: 19 Bronze 2
I am trying to bring my dev team into modern times by setting up a revision control architecture across all of development, including database development.
We are not a software shop, we are a development team supporting a business. As a result of this the best branching strategy for us is, in my opinion, a branch by release architecture.
I envisage having a TFS branch structure something like so:
And I envisage having a development process like so:
1) Developer works in either the next version branch or the BAU branch as appropriate.
2) Forward integrate (merge) from production regularly to resolve incompatabilities that may be coming from the opposite side of the trunk. This is particularly important for "future release" branches. At minimum, forward integration from the trunk is *mandatory* prior to reverse integrating your changes back into the trunk.
3) Prepare UAT/RC releases from your branch (?) and hand off to the DBA (me) and/or the infrastructure team for deployment.
4) Reverse integrate your changes into the trunk in preparation for the production release. Build and deploy from the trunk for the production release.
One of the problems I am facing isn't really a SQL Source Control problem, but I will mention it anyway: I'm not certain about the origin of UAT or RC builds. The advantage of having them come from a dev branch is that the trunk is always clean, current "production" code. Nobody has to worry about changesets or labels. On the other hand, creating releases from somewhere other than the trunk seems slightly icky. Any ideas here would be appreciated.
In any case, moving on to my actual SQL source control issue...
My sticking point is coming from the forward and reverse integration steps. This is where a developer must merge the trunk into their branch prior to building a deployable release candidate, or from their branch back into the trunk prior to a production release. I'm not sure how to accomplish this function using SQL Source Control + SQL Compare.
What I (or rather the developers) need to be able to do during, say, forward integration, is set the production trunk as the source of a diff, and the development branch they are working in as a target. The output generated by the comparison should highlight incompatibilities or inconstencies if they exist. The diff is then resolved and applied to their dev branch / the development database (yes, we use a single shared dev database approach. I don't want to have to go into why or how we do this, although I can if required). This is the forwad integration step.
To do this, I need to be able to set one source control branch as the "source", and a different branch as the "target" of a sql compare operation. Note that I would prefer not to force the devs to use an actual database as the source or target. They should be able to work using the revision control system, that's where merging takes place.
But I don't see a way to do this by right clicking on a database in object explorer and selecting "set as source" (or target). Yes, I can select the "source control" radio button, but I don't see any way of selecting a different branch.
As such, I don't see any way for my developers to be able to perform the required merges using SQLSS and SQL Compare.
[/code]
We are not a software shop, we are a development team supporting a business. As a result of this the best branching strategy for us is, in my opinion, a branch by release architecture.
I envisage having a TFS branch structure something like so:
. next major version
.
production
.
. BAU maintenance
And I envisage having a development process like so:
1) Developer works in either the next version branch or the BAU branch as appropriate.
2) Forward integrate (merge) from production regularly to resolve incompatabilities that may be coming from the opposite side of the trunk. This is particularly important for "future release" branches. At minimum, forward integration from the trunk is *mandatory* prior to reverse integrating your changes back into the trunk.
3) Prepare UAT/RC releases from your branch (?) and hand off to the DBA (me) and/or the infrastructure team for deployment.
4) Reverse integrate your changes into the trunk in preparation for the production release. Build and deploy from the trunk for the production release.
One of the problems I am facing isn't really a SQL Source Control problem, but I will mention it anyway: I'm not certain about the origin of UAT or RC builds. The advantage of having them come from a dev branch is that the trunk is always clean, current "production" code. Nobody has to worry about changesets or labels. On the other hand, creating releases from somewhere other than the trunk seems slightly icky. Any ideas here would be appreciated.
In any case, moving on to my actual SQL source control issue...
My sticking point is coming from the forward and reverse integration steps. This is where a developer must merge the trunk into their branch prior to building a deployable release candidate, or from their branch back into the trunk prior to a production release. I'm not sure how to accomplish this function using SQL Source Control + SQL Compare.
What I (or rather the developers) need to be able to do during, say, forward integration, is set the production trunk as the source of a diff, and the development branch they are working in as a target. The output generated by the comparison should highlight incompatibilities or inconstencies if they exist. The diff is then resolved and applied to their dev branch / the development database (yes, we use a single shared dev database approach. I don't want to have to go into why or how we do this, although I can if required). This is the forwad integration step.
To do this, I need to be able to set one source control branch as the "source", and a different branch as the "target" of a sql compare operation. Note that I would prefer not to force the devs to use an actual database as the source or target. They should be able to work using the revision control system, that's where merging takes place.
But I don't see a way to do this by right clicking on a database in object explorer and selecting "set as source" (or target). Yes, I can select the "source control" radio button, but I don't see any way of selecting a different branch.
As such, I don't see any way for my developers to be able to perform the required merges using SQLSS and SQL Compare.
[/code]
Comments
Exploring this idea a little further, right now it seems that if I want to use SQL Compare to perform a merge of the database side of a project (leaving TFS to deal with application code only) during, for example, a reverse integration step, I would indeed have to run a comparison between:
source: Dev branch OR dev database
target: UAT/RC/Production database (not branch)
This has a few implications:
1) The UAT/RC environment must exist as an actual database. It can't just be code in source control. This is true even if there is no actual testing happening, just a reverse integration process. So I have to provision environments and resources to house a database that nobody may even be connecting to, just in order to do a revision control operation.
2) If there is no UAT branch, then the comparison must necessarily be between the dev database and the production database. This means either:
a) Developers must be granted the permissions required to perform comparisons against the producton database, OR
b) The DBA must take on the responsibility of resolving conflicts in the developers' code, OR
c) A third database be provisioned that is an exact, up to date replica of production, with the addition of the required developer permissions.
I guess I'm struggling to understand how to use the tools to solve the problems that need to be solved.
Instead of talking from a branching structure pont of view, let me try a different approach, from a developer process point of view.
The developer makes changes and commits them to source control. This part is obvious and simple enough.
We now need to create a "build" that is deployed to a UAT environment. This build may incliude custom migration scripts or data creation scripts. Let's assume that these scripts are sitting in source control and tied to the object definitions that are being looked after by SQL SC. Let's say we use SQL Compare to help us create this build script, and it's passed off to the DBA and infraustructure guys for execution.
Now, assume the build is good and we pass UAT. Time to go to production. As long as the prod environment is in synch with what the UAT environment was prior to release, we're all good so far. We take the build script and apply it to production.
But what does the developer do with the revision control system at this point? Let's say they now merge their development branch into the production branch in order to make sure the same changes from the build are reflected in the "production" code line. But wait... they're NOT the same changes. The changes made to the real environment were made by applying a build generated via SQL compare. But the changes made to the code under revision control was made by merging via TFS. I certainly cannot, or will not, trust that these two independent processes are going to result in identical changes, which means that what is in revision control as "current production code" may not reflect what is actually running in the enviornment. This is unacceptable.
How do we resolve this?
On the application side this problem doesn't exist. People merge using TFS and then build FROM the merged code. There is only one merge process. But, even with the redgate tools, we can't seem to do that on the database side. How do people reconcile these two processes?
I guess that essentially, the release that you have in production is going to be a SPECIFIC version number.
My inclination would be, forgetting branching etc., to have your trunk named accordingly. You would then have separate trunks for each version that you then release.
I'm really not a database developer though, so I don't have the breadth of knowledge that a professional would have about source control practices. The SQL tools that we produce are intended to allow you to perform updates to and easy deployments from your source control system, but as to how you actually go about versioning and the checks and balances that you have in place to ensure that the version you have in production is the same as that in source control, is down to whatever strategy you employ.
If you're having trouble defining what this could / should be, I'd maybe suggest posting on something like Stack Overflow, as the community there might be able to help you a little more than I can.
HTH!
Pete
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
As far as the general branching structure, sure, I agree that it's not the role of SQL SC to define this. I included that part of the question just in case anyone had successfully constructed a similar pattern with the inclusion of SQL SC. Let's pass over that one.
But I think the second part of the question is a canonical requirement of a source control system. Particularly the part where we need to think about how code under revision control is synched to code that has been built and is running in an environment. With SQL, deployment *is* building. SQL Compare must run against an actual environment, which leaves different change paths for what is in source control vs what is deployed.
To put it another way: Even if it was possible to use SQL SC and SQL Compare to apply a cross-branch merge style change script within revision control (which it currently is not), a deployment would still require a second use of sql compare (this time between source and deployed environment).
An alternative approach might be to change the pattern: Instead of going from source branch 1 to source branch 2 to deployment in environment 2, one could go from source branch 1 to enviornment 2 via sql compare, and then check in changes in environment 2 to synch source branch 2. But this significantly increases the amount of work when a developer has to do a merge... indeed, it means a lot of the time developers cannot do merges, since developers don't have the authority to deploy code outside their own environment.