SQL Compare command line and /migrationfolder switch

[jhoerr]

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?

[David Atkinson]

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.

David

[jhoerr]

[David Atkinson]

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.

[jhoerr]

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.

[freecell1]

Here are the property values from my local box (linked to svn) and the dev server we push to:

Local:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="SvnLocation">
<RepositoryUrl>http://tco-dev-scm.xxx.internal/svn/code/trunk/Database/MigrationScripts/Intranet/</RepositoryUrl>
</ISOCCompareLocation>

Dev:

<?xml version="1.0" encoding="utf-16" standalone="yes"?> <ISOCCompareLocation version="1" type="SvnLocation"> <RepositoryUrl>http://tco-dev-scm.xxx.internal/svn/code/trunk/Database/MigrationScripts/Intranet/</RepositoryUrl> </ISOCCompareLocation>

They seem the same. Is it possible the way I call the command line, or the exclude filters, is causing a problem? Here are the command line params:

/exclude:role /exclude:user /exclude:assembly /sourcecontrol1 /Server2:TCO-DEV-SQL-02 /Database2:Intranet /sync /migrationfolder:C:\TeamCity\buildAgent\work\946b3977434c5179\database\migrationscripts\Intranet /scriptsfolderxml:C:\TeamCity\buildAgent\work\946b3977434c5179\Build\Shared\config\SQLCompareSvnConfig_Intranet_temp.xml /revision1:HEAD /versionusername1:builduser /versionpassword1:xxx

Thanks,

John

[David Atkinson]

[David Atkinson]

[vincentj]

Hi guys,

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.

[David Atkinson]

Sorry to not have responded earlier. Can I ask whether you're using a CI tool on your build server?

David

[vincentj]

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]

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