Migrating from a database we quite like to one we do not provides as much fun as surgery.

After the DBA has promoted the database into QA we find our application is spouting Primary Key violations because the sequence.nextval values are below those used within their respective tables. Identity columns. Yet another reason to prefer SQL Server. There's a clear pattern here.

Sure, there are some advantages but we'd take portability over the possibility of having more than one auto-generated sequence any day.

It's interesting catching the impressions of an Oracle expert going the other way. Oracle to SQL Server: Crossing the Great Divide, Part 2. The comments in Part 1 make for a fun read. Especially those that bemoan the developer tools. Toad's okay.

Actually, it's not especially so having spent ten days in Palo Alto this month not doing anything remotely tech-related. The next time I'm there though I'll be sure to pop by the William Gates Center in Stanford at the very least. Well, I finally got round to playing with NServiceBus but that's pretty much it.

Back in Blighty meanwhile there's been a steadily growing realisation that I much prefer SQL Server 2008 to Oracle 11g. What ought to be simple, isn't necessarily so, productivity is near zero and success is being defined in terms of not doing anything too damaging to production systems during migration. The plan is to port the Bloomberg ETL process from SQL Server to Oracle and replace the shambolic FTP interface with what seems to be a more robust web services equivalent over HTTPS. Authentication via X509 certificate. Nothing too radical.

We're talking to Oracle via ODP.NET - having known for a while that Microsoft was deprecating System.Data.OracleClient - and using the OracleXmlCommandType. For inserts and updates I lost a crazy amount of time by not using the System.Xml.XmlConvert.ToDateTime method. Oracle's quite particular about date format so "2010-04-01" is an Unparseable Date OracleException, which seems more than a little ridiculous.

Also surprised to find that there are no DATEDIFF and DATEADD functions out of the box. Luckily, they're here. Perhaps I'm used to having better developer tools. Or I need my hand to be held. Probably a bit of both. There's certainly no contest in deciding between which database I'd rather work with given the partition enhancements in SQL Server 2008. Apparently, I'm not alone with regards to views about Oracle being an obstacle.