I was talking to a friend few days back about upgrading an SQL Server to new version, we discussed about what are the things that we should take care while upgrading an SQL Server. This blog is an out come of that discussion, there could be various and specific SQL Server Upgrade Action Plan depending on RTO, RPO and what is the existing configuration and business needs. However, there could be a simple and generic action plan for upgrading an SQL Server.
There are two types of upgrade – 1) In-Place Upgrade and 2) Side-by-side upgrade, let’s understand both types of upgrade in brief.
In-place upgrade: In this type of upgrade, we do run the upgrade process/setup on the same server. This type of upgrade need more downtime then side-by-side method. The disadvantage of this method is it is little difficult to roll back the process in case we need to.
Side-by-side upgrade: In this type of upgrade, we keep our legacy system up until the upgrade process is not completed, and there will be a new or another server where we will run the upgrade/setup of new version, following we do restore master, msdb and users database along with SSIS packages etc. according to configuration. And once we are sure that application is working as expected we can permanently shut off the old server.
The important point is that we need to use SQL Server Upgrade Advisor (SSUA) and follow the support version and Edition Upgrades document from Microsoft before we begin with SQL Server Upgrade. In this post, I will give you a high level SQL Server Upgrade Action plan which you can modify as per the business needs.
1) Decide the type of Upgrade (Side-by-side or In-place), Depending on type of Upgrade the plan will very, a little though in my opinion.
In-place upgrade:
2.00) Review MS documents and confirm that the upgrade path is supported and feasible
2.01) Install SSUA
2.02) Run Upgrade Advisor and mitigate the path blocking
2.03) Once confident that all path blocking are cleared, run SSUA again
2.04) should be clean now and you are ready to go
2.05) Back up everything (system+user databases)
2.06) Run the setup and select upgrade
2.07) Sit back and relax, should be all good
2.08) Do remember to Rebuild Indexes and Update Statistics with Full Scan
Side-by-side upgrade:
2.00) Review MS documents and confirm that the upgrade path is supported and feasible
2.01) Install SSUA
2.02) Run Upgrade Advisor and mitigate the path blocking
2.03) Once confident that all path blocking are cleared, run SSUA again
2.04) should be clean now and you are ready to go
2.05) Setup a new server with the desired SQL Server version and edition
2.06) apply all the latest SP & CU
2.07) Do configure one of the following
a) DB Mirroring
b) Log Shipping
2.08) Sync Login
2.09) Sync Jobs
2.10) Sync or Migrate SSIS packes if any
2.11) Break/pause 2.7 sub item
2.09) Your dbs are live now on new server
2.10) Do the application regression testing thoroughly (add manual test transaction)
2.11) Once testing team sign off, re-enable LS or DBM
2.12) Decide on the cut-off time
2.13) Stop all the connection to live application
2.14) Do the final/manual sync on LS/DBM
2.15) Verify the Manual transaction is there on other side
2.16) Ask your SA team to point the DNS to new server
2.17) Break the LS or DBM again
2.18) shutdown /disconnect network to old server
2.19) Check the application
2.20) All good ? Cool!!
I have tried my best to include all the steps in SQL Server Upgrade Action plan above, in case I missed anything, do let me know in comment section via email and I will modify my post.
Image courtesy: http://gph.is/2fKBdNx
