Frequently Bought Together

Just remember kids: no-one likes a smart arse.

RedGate SqlCompare wins again

...in my highly subjective 'which tool are we going to use for database schema synchronisation?' challenge that is.

RedGate's prices seem to have gone up again ($595 USD for the comparison bundle[1], but if like me you want to use command line interface you're looking at 2x $595 USD, or 3x if you want the API too). And support and maintenance is on top of that.


So I had a good look around and considered the options:

Visual Studio for Database Professionals - easy option since it's now included in our Team Suite SKU. However even the 2008 version is still pretty crude, with very little option to change the generated delta SQL, and as a result scripting out unnecessary changes (like rebuilding your tables via a temp table just to get the column order 'right') and doubtless doing things with role memberships that I didn't want. So that didn't last long.

SqlDelta I like a lot. It does schema and data compare, and it's got a command line interface all for $330 USD - cheaper than RedGate's most basic non-pro compare bundle. And it's from down under. But it choked on my instead-of triggers on a view (either scripted them as CREATE when they needed to be ALTER or vice-versa). So I had to faff about to get a sync to work. That's an immediate fail.


Then there's ApexSQL Diff. But I didn't really get round to using that. Which is where the 'highly subjective' bit of this review comes in, not to mention the 'use the first product that works, stop playing around and get some work done' voice-of-conscience.

So RedGate it is.

[1] There's an option, not available on their website, to get a Sql Compare Pro bundle, which if you need the pro editions + the API basically means you get them for 2/3 price.

Remember to enable MARS when using Snapshot Isolation from SSAS

We started getting this error when processing our cube:

OLE DB error: OLE DB or ODBC error: Cannot create new connection because in manual or distributed transaction mode

It went away when:

• We changed to using ReadCommitted isolation, rather than snapshot
• We processed the cube using Maximum Parallel Tasks = 1

Reading knowlege base article PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction lead me to think that SSAS was trying to open multiple connections within a transaction, which isn't allowed.

Which got me thinking about MARS. Not quite sure why it wasn't on to start with, but I enabled it, and then everything was fine again.

Turns out this is actually a RTFM, if you pay attention when reading How to enable the snapshot transaction isolation level in SQL Server 2005 Analysis Services . Of course it's only in Snapshot mode that SSAS attempts to ensure database consistency for the duration of the entire Process operation, which is why it's not a problem using ReadCommitted isolation.