Red Gate forums :: View topic - SQL Compare command line and /migrationfolder switch
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

SQL Compare command line and /migrationfolder switch

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Go to page Previous  1, 2
Author Message
jhoerr



Joined: 21 Sep 2011
Posts: 8

PostPosted: Wed May 30, 2012 10:12 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Wed May 30, 2012 10:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
jhoerr



Joined: 21 Sep 2011
Posts: 8

PostPosted: Thu May 31, 2012 3:26 pm    Post subject: Re: Reply with quote

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?
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Thu May 31, 2012 3:34 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
jhoerr



Joined: 21 Sep 2011
Posts: 8

PostPosted: Thu May 31, 2012 4:20 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
freecell1



Joined: 13 Mar 2012
Posts: 20

PostPosted: Fri Jun 01, 2012 2:41 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Fri Jun 01, 2012 7:47 pm    Post subject: Re: Reply with quote

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?
Back to top
View user's profile Send private message Send e-mail
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Fri Jun 01, 2012 7:51 pm    Post subject: Re: Reply with quote

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?
Back to top
View user's profile Send private message Send e-mail
vincentj



Joined: 17 Feb 2011
Posts: 36

PostPosted: Tue Jul 17, 2012 11:40 pm    Post subject: Reply with quote

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.

Quote:
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.)
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Fri Jul 20, 2012 12:18 am    Post subject: Reply with quote

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

David
Back to top
View user's profile Send private message Send e-mail
vincentj



Joined: 17 Feb 2011
Posts: 36

PostPosted: Fri Jul 20, 2012 3:31 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1114
Location: Twitter: @dtabase

PostPosted: Tue Jul 24, 2012 12:02 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
vedran



Joined: 16 Nov 2013
Posts: 1

PostPosted: Sat Nov 16, 2013 1:29 am    Post subject: Re: Reply with quote

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! Shocked
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6580

PostPosted: Tue Nov 19, 2013 5:39 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2
Page 2 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group