David - I've performed many DB migrations with Sql Compare, yet the DB 'extended property' has never been set and that's apparently necessary for migrations scripts to work properly. How does this get initially set?

Also, the solutions suggested here for enabling command-line support for the migration scripts are very clever but seem to require a considerable amount of hoop-jumping. Are these the best way to approach the problem, or do you know of any tutorials or blog posts that demonstrate how this kind of thing can/should be done?

The idea is that it should 'just work' and you shouldn't need to jump hoops.

In practice it seems that it's not straightforward to troubleshoot when it goes wrong. We'll have to improve that.

The extended property gets set in SQL Compare's deployment script. If you generate a script where the source database is a source controlled database, you'll see some SQL statements at the bottom of the script that sets the extended properties. If you database hasn't had a SQL Compare script run against it recently (since we implemented the feature), you won't have the extended property. The solution is to add it manually by copying it from a source controlled database to the target.

A scripts folde that has been pulled from source control loses its source control context, so if this is the data source in your SQL Compare comparison, it won't work.

Can you try again using 'Direct from source control' rather than 'scripts folder' as the source?

It needs the scripts location as it builds up the deployment script from the revisions in source control. For example, if you go from revision 1 to revision 10 and there is a migration script from 5 to 6 it will:

Fetch revision 1 and revision 5 from source control and generate the diff script. Then it does the same for 5 -> 6 and 6->10, and attaches them together.

Ah, ok -- and that's the piece that can be supplied via the /scriptsfolderxml argument, correct? This is making sense now. We follow a pretty traditional trunk/feature branch pattern and release from tags, so being able to dynamically provide that repository URL is helpful.

jhoerr wrote:Ah, ok -- and that's the piece that can be supplied via the /scriptsfolderxml argument, correct? This is making sense now. We follow a pretty traditional trunk/feature branch pattern and release from tags, so being able to dynamically provide that repository URL is helpful.

I'm thinking that we should ideally expose the repo using a command line switch rather than relying on the xml file. I take it that the xml file isn't ideal for you?

I've been using SQL Compare and SQL Data Compare to do our continuous integration for a little over a year now, and we too have been finding an increasing need for migration scripts so I'd love to get them working.

I tried following the steps outlined in this thread, and I'm getting an error saying "Using source controlled databases requires SQL Source Control to be installed." I'd be interested in hearing the "hacky" way of running this without SQL Source Control since I'd prefer not to install that and SSMS on our build server.

I'm thinking that we should ideally expose the repo using a command line switch rather than relying on the xml file.

I'd like to cast a vote for the command line feature. We use the command line for all the other parameters so that would work nicely for us. (The XML file is workable, though slightly less convenient.)

We're using CruiseControl.Net and NAnt scripts to do our deployments. We have a couple CI environments that are set up to deploy whenever someone commits to Subversion.

Our staging environment is the last step before production and deployments are done a little differently. First we baseline it to match Production (by deploying Trunk; we keep trunk in sync with production) and then we deploy the entire release branch to the staging environment. Sometimes if a deployment fails or we find bugs, we need to redeploy, which involves first deploying Trunk and then deploying the branch again. I'm curious if the migration scripts will get deployed again in this scenario (we would like them to be).

Update: I tested the above scenario, and it seems that the migration scripts do not get run the second time. I could probably get around this by changing the property so it reflects the first revision of the branch before we do the sync.

David Atkinson wrote:The extended property needs to be there. Once you've deployed once, it will be there subsequently and hopefully you won't have issues.

For now, just add the extended property as an extra step and see if it works.

David

When used like this SQLCompare.exe generates a drop of the migrationscript extended property. After the first synch or execution of the generated script, every following run of SQLCompare will ignore migration scripts and fail unless you re-add the extended property manually.

This surely looks as a BUG, and its working like this in the last version of SqlCompare & Automation Pack!

It should work the way David says, unless possibly you're using the option /Options:DisableSocForLiveDbs. I can't get it to drop the RG extended properties in normal use. Please log a support ticket at redgatesupport.red-gate.com and tell us exactly how you are using this software and we'll try to help.