January 10, 2011 at 10:23 pm
Hi All,
I have been asked by my management to provide a roll back plan for a migration of a database in SQL server 2000 to SQL Server 2008. It is a side by side migration. The issue is that the management wants to have a contingency plan for the following situation:
"If we experience any issues after Successfully migration to the SQL Server 2008 lets say after 3 days and then we decide to roll back to the previous SQL server 2K environment, what would be the best possible way to apply all the updates that have gone to the SQL Server 2008 database to the SQL server 2000? (without any loss of data)"
-> Can i set up Replication from SQL Server 2008 database to SQL Server 2000 database for the above situation? If yes, then what would be the draw backs and other problematic scenarios?
-> Is there any other way to do push the changes that had been occurred in the SQL 2K8 database to SQL 2K database?
I need an urgent assistance on this.
Thanks,
Srikant
January 22, 2014 at 1:57 am
Hi,
Can some one share the roll back plan for SQL Server side-by-side migration.
January 22, 2014 at 2:19 am
Srikant
My advice to your management would be to test thoroughly in advance, so that you can be as confident as possible that when you upgrade in live, there won't be any issues. When you do the upgrade, you have a go/no-go decision point, and after that, you fix any issues instead of rolling back.
If management won't buy that, then I don't see any reason why replication wouldn't work, although I've never tried it between 2008 and 2000. Again, test. Issues to look out for are differences in data types. When you do your upgrade, you'll want to ensure that all your text columns are converted to varchar(max), all your datetime columns that only contain date portions are converted to date, and so on. Make sure that you take that into account when setting up replication. I take it that all day-to-day operations on your database are DML? If you have DDL changes as well, that will also complicate things.
John
January 23, 2014 at 6:46 am
well... if you don't have a unique index on any of the tables you cannot setup replication... also... not sure if you can replicate from SQL2008 to 2000.... probably can... but... if that is your fallback plan you had better test that process out and make sure you can do it.
Like a previous poster said.... everything should be tested well and at some point after the upgrade if issues come up you just have to deal with it as there is no way to go backwards....
January 24, 2014 at 3:27 am
Markus (1/23/2014)
well... if you don't have a unique index on any of the tables you cannot setup replication... also... not sure if you can replicate from SQL2008 to 2000.... probably can... but... if that is your fallback plan you had better test that process out and make sure you can do it.
You can providing you aren't using restricted datatypes. This won't be a problem since its an upgrade from 2000.
------------
There are a couple of gotchas with this type of upgrade but they are usually related to 32/64bit upgrades and running older features such as DTS. There are also a couple of refinements in the engine which have made old TSQL "workarounds" no longer work but nothing major.
Its virtually impossible to test 100% of all applications in these sorts of upgrades.
Do you mean SQL 2008R2 or SQL2008? Remember 2008 is also pretty old now and R2 may be the better upgrade path. Rolling back in this scenarios should be only in the event of a critical issue so I would consider using a third party tool to compare data rather than replication as this would be a last resort. Remember that SQL2000 is no longer supported by MS at all so going forward should really be a priority after a couple of days on the new server. Rolling back should only be considered on migration day in my opinion.
I would test as much as possible by upgrading your dev/qa environments. I would ensure you have dev or 3rd party tool support on hand for any unforeseen issues that may need hotfixes and proceed from there. For the most part everything will normally work. Ensure you've backed up all dbs before the migration, you update all stats and consider and index rebuild before ending the maintenance period.
For a speedy migration I like to logship my dbs to the side by side server in advance. This will give you time to prepare the server, logins, jobs, etc. Migration itself is primarily just restoring a tail log on the destination server. Assuming you have a nightly backup job you then have a point in time restore method using that and the logs as well.
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply