Predominantly we are in a situation where in certain servers need be upgraded. There will be a number of features we will be wanting to use with the latest versions of SQL Servers. Like the usage of CTEs, ranking functions in 2005 vis-a-vis 2000 or usage of datatime2, datetimeoffset datatypes in 2008 vis-a-vis 2005 or the usage of report builder 3.0, SSAS multiple cubes etc.,
Now one of the dilemma’s is whether to go for in-place upgrade or side-by-side migration. Here are the layman definitions:
In-Place Upgrade: Upgrading both the system and user databases at one go and which can include massive downtime for the databases depending on the size with similar or upgraded hardware as needed.
Side-by-Side migration: It involves creating a new instance on a new hardware and then transferring the databases once the server setup is complete. Further details:
Additional Points
In-place upgrade:
fastest and easiest
Need not change client connections
if the upgrade fails, it takes time to fix it and client apps will be unavailable during that time.
Should be easily handled even if technical know-how is limited in SQL admin area.
All the DBes are either offline or online at a time.
No new hardware needed but might need upgrade based on system requirements for the upgrade
Upgrading from non clustered instance to a clustered instance of 2008 is not possible.
Testing can be limited because it involves directly modifying the existing databases.
Linked server, replication and logshipping scenarios neednt be dealt with and relatively handling the issues rather than setting up all over again
Side-by-Side migration:
Users continue to access old server and databases
Server configurations and server objects like users, logins, SQL Server job agents need be synced with old server
clients are online most of the time and offline only for a brief moment when switching from old server name to new one.
Need more SQL Admin knowledge to configure collation settings, jobs, logins, roles etc.,
Have a choice to upgrade more Dbs and then do later one DB upgrade at a time on a timely basis.
Need new server to install a new instance of SQL Server.
Data transformation can be done using SSIS packages while moving the Databases.
