You have just received the news that your Mainstream support for your SQL Server version is about to expire. Your development bucket list is full, and the headache is coming.
The best news is that your organization has stayed compliant through the years, and you don't need to STEP UP to through multiple versions to get to the most recent release. .
You are a new IT manager and you have not had the privilege to run this type of project before. This is where the advice in this article will help you on your way.
Rule One: Do not assume at any of the steps. Perform each step as completely as you can.
Rule Two: Review each step and validate all the work. There may be mistakes as you go but validation will help alleviate most.
Rule Three: Review the Features and the Secuity Suggestions, you may need to develop work arounds to not interrupt your business or change some of the aspects of your user community's relationship with the application and environment. It will be easier if you let them know about these changes ahead of time.
STEP ONE:
Gather the team, you will need at least a representative from: Security Team (CISO), Client user community, internal user community, (to create your UAT Team) networking, hardware support, Database administration (several team members). developers, Quality Assurance, and architects. Please also invite and keep in close contact any of your third or fourth level third party software vendors that you are running on the SQL Server. Make certain that they are ready for the upgrade as well. This shouldn't be an ask from you this should be you must help us with this conversation.
STEP TWO:
Assign each team member to write up and review their specific business needs.
Look at the Server: analyze the databases, reports, jobs, and data in the environment. With the team review what of each are still in use. You would not believe how many databases have been created for a one-time test that you no longer need or that there are old SSRS or SSIS report/processes that are not necessary any longer as they have been versioned out.
Use a tool like DPA or Spotlight to review the performance on the server. Correct anything that you can now and then take a baseline notation of the performance and issues that may need to linger now so you can compare after the upgrade.
STEP THREE:
Review all of the requirements from hardware, space and memory, software upgrades if needed in either your own home developed application or your third- or fourth-party software packages. Review the list of needs from databases to jobs and reports.
Design a plan to test any of the SSRS SSIS, jobs reports, applications that you need to work within the new SQL server version, preferred a full regression test.
STEP FOUR:
Follow all standard development protocols, get buy in from the entire team. For some this might need to be a firm directive.
STEP FIVE:
Set up a development server, load the new version of SQL Server, Slowly review the application requirement changes, review what could be the aftereffects with each relevant team member. Especially review the new suggested security settings, these can bite you when you try to run jobs, reports are sending data between servers or databases.
STEP SIX: the longest part TEST, TEST, TEST
Use your required reports, jobs SSIS packets and load and test each one for success in the application and check for data integrity.
Data integrity is very important run comparison testing using both server environments. This task should be assigned to UAT team and developers combined.
STEP SEVEN:
if you have the budget set the new development server as your new Production server and keep both running for data comparisons and in case there were items that didn't get passed over.
If you don't have the budget and I should have mentioned this before. BACKUP EVERTHING as it sat in the old version and store it for a few months in case you need it. Archive things you think you don't need DO NOT DELETE, yet.
Later scrub things you don't need to save to save the storage space.
Lastly, pat the team on the back and don't sweat things that you did not realize you need to consider. If you keep your backup and you keep the communications going with the teams and especially the USER COMMUNITY you can fix anything.
