SQL Compare command line and /migrationfolder switch

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Postby jhoerr » Wed May 30, 2012 10:12 pm

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?
jhoerr
 
Posts: 8
Joined: Wed Sep 21, 2011 4:08 pm

Postby David Atkinson » Wed May 30, 2012 10:57 pm

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
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Re:

Postby jhoerr » Thu May 31, 2012 3:26 pm

David Atkinson wrote:If you generate a script where the source database is a source controlled database...


Ok, I think this piece is key. Do you draw a distinction here between a scripts folder that was generated from a source controlled database, and pointing to the URL of the repository itself?

I see the extended property in my local source-controlled DB -- thanks for that tip. What does SQL Compare do with that 'SQLSourceControl Scripts Location' value?
jhoerr
 
Posts: 8
Joined: Wed Sep 21, 2011 4:08 pm

Postby David Atkinson » Thu May 31, 2012 3:34 pm

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.
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby jhoerr » Thu May 31, 2012 4:20 pm

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
 
Posts: 8
Joined: Wed Sep 21, 2011 4:08 pm

Postby freecell1 » Fri Jun 01, 2012 2:41 pm

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
freecell1
 
Posts: 21
Joined: Tue Mar 13, 2012 8:21 pm

Re:

Postby David Atkinson » Fri Jun 01, 2012 7:47 pm

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?
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Re:

Postby David Atkinson » Fri Jun 01, 2012 7:51 pm

freecell1 wrote: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


Can you experimentally try putting an explicity revision after /revision1: instead of HEAD to see if that's causing issues?
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby vincentj » Tue Jul 17, 2012 11:40 pm

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.

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.)
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby David Atkinson » Fri Jul 20, 2012 12:18 am

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

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby vincentj » Fri Jul 20, 2012 3:31 pm

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.
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby David Atkinson » Tue Jul 24, 2012 12:02 am

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
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Re:

Postby vedran » Sat Nov 16, 2013 1:29 am

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! :shock:
vedran
 
Posts: 1
Joined: Sat Nov 16, 2013 1:23 am

Postby Brian Donahue » Tue Nov 19, 2013 5:39 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Previous

Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests