Sql Server Upgrade Plan

there are two types of Upgrade strategy

In-Place Upgrading.

Side-by-side Upgrading.

In-Place Upgrade:  An in-place approach allows to upgrade existing SQL Server instance to a higher version. the main benfit of this approach is you can still use existing hardware,instance name. there is no worry on application connection string configuration, logins,users,SQL agent jobs creation.

This approach requires downtime as SQL server instance is offline during upgrade, in place upgrade is not supported for all components and if any issue occur complex and manual rollback strategy .


Side-by-Side Upgrade : This approach quite simple as name suggest, a new sql server instance can be installed on the same server or different new sever. which means server can have SQL2005,2008,2008R2,2012,2014 on same box. one instance is default and others are named instance.


This approach has more granular control over upgrade component-level.

you can run sql server side by side for testing.

you can have rollback strategy  because the the original system is still intact.



 Pre-Upgrade Steps:

performing an upgrade can be a complex and critical process .to mitigating the risk of a failed upgrade or unexpected post-upgrade behavior, a proper research can be perform on instance using available tools.

SQL Server Upgrade Tools:  SQL server 2014 Upgrade Advisory is a free download available as part of MS 2014 and also available in install media.

the purpose of this tool is to identify known upgrade issues and provide workarounds for fixes.(https://msdn.microsoft.com/en-us/library/ee210480(v=sql.120).aspx)

identify backward compatibility and discontinued features.

perform impact analysis on applications and inform to the application owners, this is very critical steps. some vendor applications may not be compatible , this is required further analysis on workarounds

Upgrade Execution Process:

Document each step of upgrade process, This  includes upgrade steps,document locations,server IP,Resources information,Application owners.

Attache tests results on Dress rehearsals and analyze the ares of issues identified during upgrading on test servers.

communicate with the vendors if any alarms are raised.

Post-Upgrade Process: 

Document all post upgrade tasks, user creation,user access, connection string configurations with third party applications,backups and maintenance plans,job scheduling etc.

sanity checks with QA before system handover to business for validations.


once the above process is completed and tested on development/QA/UAT servers and time to go for Production upgrade in a smooth way.

i did not touch on hardware selection inthis article as name implies it is only for upgrade steps.











Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s