Migration script problem...

megasoft78

Hi,
I spot an issue with SQL Source Control 3 and SQL Compare 10.
I have a migration script committed into my source control (subversion) and when I use SQL Compare 10 command line to deploy my db changes the migration script is not detected.
I know why is not working and maybe this could be useful to someone else.
SQL Source Control is using SQL Server Extended properties to store revision number and URLs for schema changes and migration script. When we start to use SQL Source control we haven't use migration script then my production database doesn't have migration URL in extended properties.
I solved just adding manually this extended property to production database and magically the migration script get detected.

Best Regards,
Gabriele

megasoft78

Hi,
I did additional tests and my first impression was wrong. Actually the problem is related with URLs in extended properties. I'm using separate branches repository for each version we deploy and because the URL between source database and target database are different (for instance 1.1 in dev and 1.0 in production) the migration script get ignored.
This s a big problem for us because we need to manually update that URLs to make migration script happens.
I think the only workaround could be to use alias for subversion URLs but I'm not sure that exist.

Best Regards,
Gabriele

David Atkinson

You've hit a limitation of our current migrations implementation. It only works within a branch. There are many complexities that we have to consider to support migrations from one branch to another.

For example, if you have a number of releases, v1, v2, v3, v4 etc, and you create a release branch based on v1, which includes v1.1, v1.2, v1.3 etc.

If you need to create a script between v1 and v4, this works fine, and any migrations will be picked up.

However, if you want to go from v1.3 to v4, it gets more complicated. Branching presents an additional challenge when it comes to migrations. The patches made in the 1.x branch, which could of course include migration scripts, will most likely have been merged to the trunk as bug fixes, meaning it isn't possible to simply run the same deployment script as is created by going from v1 to v4 - the script would fail because the "same" migration scripts are being applied twice.

We don't have a built-in solution for this yet, which means you'll need to create your deployment script the old way, but comparing it and making manual modifications. In future we'd like to allow users to create a cross-branch migration script, effectively bridging the branches with a route from 1.3 to an appropriate point on the trunk, in this case maybe v4. This means that SQL Compare would now be able to generate a deployment script from any version in the v1.x branch to the trunk, from v4 onwards.

David Atkinson
Product Manager
Red Gate

megasoft78

Hi David,
thank you for your reply.
I agree with you the complexity of supporting multiple branches but in our case we use branches only as a safe way to deploy small bug fixes in production.
I know that most users use multiple branches and then merge everything back to trunk but we don't have that complexity. We have just one branch at time.
I tried to solve the problem using svn:external from subversion to have always the same URL but it seems not working with SQL Source Control.
Do you have any suggestion?

Best Regards,
Gabriele

David Atkinson

I have emailed you directly to better understand your current branching strategy.

David