Compare between 2008 and 2005 servers?

shadowbob

This might be a dumb question. When SQL Server 2008 comes out, we'll find ourselves in a mixed environment, with some servers running 2008 and some running 2005. Assuming I don't use any 2008-specific features in the database, will I be able to use Compare to synchronize the schema from a 2008 database down to a 2005 database on another server? I'd like to upgrade our dev server to 2008, but some of our production servers will still be running 2005 for a time. Thanks.

Michelle T

This should work most of the time (we've done some testing on downgrade and upgrade between 2005 and 2008 servers) but it may well run into problems if there is 2008-specific syntax in the 2008 database. There ought to be warnings explaining the potential problems when you attempt such a synchronization if SQL Compare spots anything we know it can't handle downgrading gracefully.

Basically, it's very likely to work, but we're not quite as confident of it as within-version comparisons.

jerryhung

I have done a few SQL Compares between SQL 2005 and SQL 2008, with full-text, spatial functions/index, so far so good no major issue

Other than I need to split out the Diff scripts into 2 parts
otherwise it'd return error of "This script needs full-text on the database"

1st one will do the full-text creation & population
2nd one then does the scripts that use full-text

shadowbob

I can keep databases that are running on 2005 in production at compatibility level 90. Would that help to ensure a higher success rate of comparisons?

Michelle T

It should - if you find any problems with comparsions between compatibility level 90 databases on SQL 2005 and SQL 2008, do tell us, because we might be able to fix them.