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

SQL Compare command line and /migrationfolder switch

Postby robermar2 » Wed Mar 21, 2012 9:40 pm

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.
robermar2
 
Posts: 5
Joined: Wed Mar 21, 2012 9:27 pm

Postby David Atkinson » Wed Mar 21, 2012 11:08 pm

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

Code: Select all
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:

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

Postby freecell1 » Thu Apr 12, 2012 8:32 pm

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

Postby David Atkinson » Thu Apr 12, 2012 11:58 pm

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

Postby matt_hosking » Fri Apr 13, 2012 2:02 am

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
 
Posts: 14
Joined: Wed Sep 07, 2011 2:43 am
Location: Australia

Postby matt_hosking » Fri Apr 13, 2012 2:55 am

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 '&lt;' and '&gt;' 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]
matt_hosking
 
Posts: 14
Joined: Wed Sep 07, 2011 2:43 am
Location: Australia

Postby freecell1 » Fri Apr 13, 2012 4:26 pm

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

Postby David Atkinson » Sat Apr 14, 2012 4:25 pm

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

Postby freecell1 » Mon Apr 16, 2012 10:01 pm

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

Postby David Atkinson » Mon Apr 16, 2012 10:25 pm

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

Postby freecell1 » Wed Apr 25, 2012 7:48 pm

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

Postby David Atkinson » Thu Apr 26, 2012 11:15 pm

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

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

Postby freecell1 » Fri Apr 27, 2012 9:36 pm

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

Re:

Postby jhoerr » Wed May 30, 2012 8:53 pm

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!
Last edited by jhoerr on Wed May 30, 2012 9:52 pm, edited 2 times in total.
jhoerr
 
Posts: 8
Joined: Wed Sep 21, 2011 4:08 pm

Postby David Atkinson » Wed May 30, 2012 9:04 pm

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

Next

Return to SQL Compare 10

Who is online

Users browsing this forum: Bing [Bot] and 0 guests