SQL Compare command line and /migrationfolder switch

robermar2

My company just purchased a large set of licenses for sql source control and sql compare for our automated build agents that use team city and svn.

We are attempting to include our databases in our continuous integration environment using sql red gate tools.

We have been fairly successful in setting this up, but have come across an issue when comparing databases that only a migration script can solve.

We have successfully committed migration scripts through sql source control and they are part of our svn repository. We are using team city to check out the database script folders as well as the migration scripts.

We have 10.1 of sql compare installed and our attempting to use the /migrationfolder switch.

We immediately receive this error:

Error: The /migrationfolder switch cannot be used without the /scriptsfolderxml
switch. Please use SQLCompare.exe /? or SQLCompare.exe /verbose /? for more
information.

The /? and /verbose /? switches do not offer any additional help/guidance at all.

Your online documentation does not offer any either on this particular feature.

I see documentation on using migration scripts using the sql compare user interface, but that is not what we are attempting to do...we are attempting to automate this process using the command line.

Any help on this would be greatly appreciated as I don't understand why the scriptsfolderxml switch has anything to do with the migrationfolder switch, much less what I would need to put in the xml file to allow this to work.

If the migrationfolder switch is not fully supported, a workaround would be greatly appreciated. Our database change requires us to drop a table, recreate it with an additional column and insert default data into it upon creation. The table exists in the target database and has data in it and a column needs to be added that does not allow nulls and does not have default data specified...we need to populate it with specific data and migration scripts have allowed us to do that in our development environment, but we need to automate these changes to our QA environment. Thanks.

David Atkinson

Here is an example use of the sqlcompare.exe command line:

sqlcompare.exe /sourcecontrol1 /versionusername1:<username> /versionpassword1:<password> /revision1:123 /scriptsfolderxml:<repo_location_xmlfile> /s2:<yourserver> /db2:<CIDatabase>

/versionusername1 and /versionpassword1 are the credentials required to connect to your source control system. You use /migrationfolder if you are adding your own custom migration scripts to source control and wish for these to be pulled into your deployment scripts.

The <repo_location_xmlfile> is a text file that you must create to accompany the command line. It contains the information required to connect to the source control repository. Create a new text file and paste the contents of the SQLSourceControl Scripts Location extended property of your source controlled database. To find this, right click on the database in the Object Explorer, select Properties, Extended Properties page, and copy the Value text from the SQLSourceControl Scripts Location property to your text file. It should look something like this:

<?xml version=”1.0” encoding=”utf-16” standalone=”yes”?>
<ISOCCompareLocation version=”1” type=”SvnLocation”>
<RepositoryUrl>
http://svn.repo/projects/your database scripts folder/
</RepositoryUrl>
</ISOCCompareLocation>

We're aware of the lack of documention and are currently working to resolve this.

I will email you the complete document which I copied the above extract from.

David Atkinson
Product Manager
Red Gate

freecell1

I'm having similar problems with comparing a subversion scripts folder to a live database. I was on 10.1 (which doesn't have the options mentioned above) and was just using /Scripts1 to point to an svn checkout directory. At first it appeared to pick up migration scripts but I have not been able to get it to work again. I wish I knew more about how the two tools interact.

I also am wondering if maybe the scripts are being excluded to a perceived wrong db version .. I don't know how this is stored. Maybe db extended properties? I have a new column this exists in svn and a migration script. Staging server does not have the column. I want my command line SQL compare to grab the script and use it, but I don't see how.

David Atkinson

The repository referenced in the target database's extended properties need to match the repository of the source. Can you check that this is the case?

David

matt_hosking

It seems the 'scriptsfolderxml' must always be provided, even if you don't need it (e.g. if you're using '/scripts1'), so just add '/scriptsfolderxml:"blank"' (without the single quotes) and it seems to work. The 'blank' can be anything (other than nothing, or it errors) - must be a bug.

matt_hosking

Scratch that - it seems that unless you use source control as the source for the database, migration scripts are ignored (even though it actually says it is retrieving them in the output). However, there's good news: I finally got it to work. Here's what I did:

Created a project in the SQL Compare UI, specifying source control as the source (not using scripts), mirroring exactly what I was doing in the command line. Created a deployment script. Saved the project. Opened the project file (in '[documents]\SQL Compare\SharedProjects') in a text editor. Copied the contents of the 'ScriptFolderLocation' and 'MigrationsFolderLocation' elements to separate XML files, replacing '<' and '>' with '<' and '>' respectively and saving in UCS-2 Little Endian (using Notepad++) - this is required due to the UTF-16 in the header. Specified the above XML files as the '/scriptsfolderxml' and '/migrationfolderxml' parameters, along with '/sourcecontrol1' parameter (no value despite what command line help says), '/revision1:Latest' and all my other usual parameters (except '/scripts1' of course).

And it worked (migration script finally included). I'm not sure if registering the source control in SQL Compare (as part of creating the project), actually running the comparison, creating the deployment script, or getting those two XML files exactly right is what fixed it, but I no longer get an 'object reference' error (due to one or more of these).[/list]

freecell1

The repository referenced in the target database's extended properties need to match the repository of the source. Can you check that this is the case?

Ah ha. No, it's not the case. We are testing this as part of a new process. The databases downstream from dev (staging, prod) have never had any changes rolled out from these tools yet, so they don't have these extended properties. How do they get into the database the first time?

David Atkinson

They get these after the first deployment. You might want to copy them across manually from a database linked to source control. You will need to ensure that the revision number is set appropriately though.

freecell1

I got this working eventually. Originally I was doing the compare from

script folder -> database

using the checkout folder on the build server. I used the new options in 10.1 to change this to

svn server -> database

This took a while as I had to install SQL Source Control on the build server, which meant I had to installed SQL Management Studio on the build server, etc. Currently What I have going includes a custom c# console app that wraps calls to SQL Compare and SQL Data Compare. Basically:

1) MSBuild file on build server has a list of databases to compare (17 for us).
2) MSBuild file calls our custom CompareDatabases console app once per database.
3) That app calls SQL Compare and SQL Data Compare. For SQL Compare it dynamically generates the XML config file mentioned above. For Data Compare it takes a stored list of tables to compare (static data) and dynamically builds an XML config file that specifies a destination server and either /sync or a script file name. The script file name comes from a build label set in the build server (happens to be TeamCtiy).
4) The build has a step to check in the generated SQL scripts, if applicable, for a DBA to review before we run them in staging/prod.

Thanks to everyone for the help. I'm happy to share details of how I did all this.

David Atkinson

Congratulations for getting this working! We're well aware that our documentation is a little sparse. Would you consider writing a blog post or even publishing an article describing your specific set up to assist others doing the same thing? I don't know why, but of late we've been inundated with questions about continuous integration.

Having to install SQL Source Control and SSMS is tedious and in fact there is a (hacky) way of avoiding this which I can share with you should it help. The longer term plan is for us to provide a single installer that installs all the command lines and resources required for them to work, so you won't have to install the UI components separately.

Would you be able to share the contents of your xml file. I'm not 100% sure why this is required.

Database continuous integration is very much in its infancy so you may consider yourself a pioneer!

I've recently started blogging about SQL development and plan to focus primarily on continuous integration.

http://geekswithblogs.net/SQLDev/Default.aspx

David

freecell1

Glad you are starting a blog. this'll be really helpful. I'd be happy to write something but we are still running into issues so I will wait a bit.

The latest problem is that when comparing svn (HEAD revision) to a database, SQL Compare is trying to apply all historical changes instead of just those that differ between svn and the target database. This fails due to objects no longer existing, etc.

Command line is like this:

sqlcompare /exclude:role /exclude:user /exclude:a
ssembly /sourcecontrol1 /Server2:MYSERVER /Database2:MyDb /sync /migrationfolder:d:\code\trunk\database\migrationscripts\MyDb /scriptsfolderxml:d:
\code\trunk\Build\Shared\config\SQLCompareSvnConfig_MyDb_temp.xml /revision1
:HEAD /versionusername1:builduser /versionpassword1:mypass /verbose

There are just 3 objects that differ, but the sync is trying to run through earlier changes. If I change to script generation mode, it generates a ton of older stuff. It seemed like this was working, so I'm mystified. The target db shows svn revision 5018 in its extended properties, and the HEAD revision in svn is 5034. But changes scripted go way back before that.

David Atkinson

What happens when you try the same comparison using the SQL Compare UI?

David

freecell1

It's similar actually. I have a support case open but we seem to be stuck. What happens is:

1) I choose to compare subversion HEAD to a shared database server.
2) The comparison results screen correctly shows objects that changed since the last sync with that server. In my case subversion is at 5034 and the dev SQL server is at 5018. The database extended property on the SQL server shows 5018.
3) But ... once I click Deployment Wizard, the "Review Migration Scripts" page shows pre-selected migration scripts from earlier revision. Also, the little circle-graph at the bottom says "The deployment script will be created as follows: 0 --> 4673 --> 4783 --> 5018 --> 5034" and if I click Next, it generates a script with all the old, unneeded changes.

If I uncheck the older migration scripts, it still scripts back to rev 0, just without the scripts.

It seem obvious that it's not "getting" that the target server is on rev 5018. There are no RedGate tools on the target SQL Server, but I'm not aware that they need to be there.

Any clues are appreciated ... I ma y be missing something obvious.

John

jhoerr

matt_hosking wrote:

...unless you use source control as the source for the database, migration scripts are ignored...

Can someone else confirm this? And if it's true, can someone explain why it's necessary? It's not at all clear to me why this would be the case, considering that everything in the scripts folder has already come from source control.

freecell1 wrote:

The target db shows svn revision 5018 in its extended properties

What is the exact form of this extended property? I think I perhaps need to prepopulate it on the production database so that I can start performing migrations.

Thanks!

David Atkinson

freecell1 - does the extended property on the database target correspond exactly to the extended property in a database that is linked to your source control repository? I believe this is case sensitive (rightly or wrongly...).

David

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 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?

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&gt;
</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&gt; </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

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

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&gt;
</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&gt; </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?

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.

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.)

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

vedran

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:

Brian Donahue

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.