Comments
Sort by recent activity
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. / comments
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
sv...
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. / comments
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 (...
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? / comments
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 t...
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. / comments
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 a...
I figured it out. It looks like the /include:identical switch overrides /exclude switches, because when I took the include out it worked as expected.
This isn't the behavior I'd expect to see. I would think that
/include:identical /exclude:assembly /exclude:role
for example, would first exclude all roles and assemblies, and then produce a report of the remaining object types including identical ones. That's not how it works though ... I tried
/include:identical /exclude:role
to take assembly-related concerns out of the test, and it output a list that included roles ... / comments
I figured it out. It looks like the /include:identical switch overrides /exclude switches, because when I took the include out it worked as expected.
This isn't the behavior I'd expect to see. I ...
I've figured it out. It had to do with having UNSAFE c# CLR assemblies on the server. In that scenario the user must be in the sql admins group for the comparison to work, it seems. THat's why it worked when I ran it but not when the build system did.
We actually ended up revisiting usage of CLR functions and we going to stop making new ones, and also exclude them from sql source control. The actual C# project code is already in svn anyway.
Thanks for the suggestion.
John / comments
I've figured it out. It had to do with having UNSAFE c# CLR assemblies on the server. In that scenario the user must be in the sql admins group for the comparison to work, it seems. THat's why i...