You sould get a check-list together of the tasks you need to do. The following is suggested as a starting point, but you are the person who knows your site, not me, so there willl be other things to add.
- Document your existing environment
This includes accounts, permissions, connection strings, Agent Jobs, etc. You also need to list any non-Microsoft objects in master and msdb database - tables, views, stored procs, functions, plus their permissions
If you have any connection strings with the server name in them, a good plan is to create a DNS alias for your existing server and change the connection string to use the alias. The Alias can then get changed at cut-over without affecting yourt application (and also quickly be changed back if go-live gets abandoned).
2. Plan how you will implement these in the new environment
This includes deciding if any of the items in your inventory are no longer needed and explicitly marking them as not to be implemented
You can use backup/restore for the user databases, but you must use the master and msdb that come with your new SQL version. SQL Server will most likely not work if you restore a old version master or model and you will certainly be outside of Microsoft support if you have a problem getting it to work
3. Plan your new environment
This includes SQL Version and Edition, Windows version, server size, disk layout, connectivity requirements. As others have said, aim for SQL2019, there is little point in using a 5-year old product for a new server
You also need to plan how you will test the new environment. You should be certain that everything works as expected before cut-over day. You also need a go-live plan and a back-out plan in case go-live has to be abandoned
Your go-live plan should include a checklist that details each step needed and who is responsible for doing it, plus a box to mark completion and another box to contain any notes that may be needed. It should include what monitoring will be done to confirm everything is working ok after the go-live, such as specific checks 2 hours after thhe go-live. The plan should also document any situations that may cause the go-live to be abandoned and how you will revert to the old server.
4. Build your new server
5. Implement all the accounts, etc you listed earlier
Be careful about the Agent jobs, some of these may need to be disabled before you go live if they populate downstream systems
6. Restore your user databases
7. Work through your test plan
Some tests may need to be repeated until they give the required results. Your server is not live at this stage so if the worst comes to the worst go back to step 4
Check if any changes are needed to your go-live plan.
8. Get sign-off for the go-live
9. Work through your go-live plan
If you have DNS aliases in the connection strings, the aliases can be changed to point to the new server
If the go-live is expected to take more than 30 minutes, make sure there is food and drink available - people can make silly decisions when thirsty.
Make sure any jobs disabled during testing are now enabled
10. Verify that everything is working as expected and all post-go live checks have been completed
11. Celebrate the success
Or comiserate if the go-live was abandoned, and then plan how to do it better next time
Hope thi shelps
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara