We have one SQL Server 2016 Standard instance, and two SQL Server 2017 Standard instances installed on the same server. Now my management wants to install SQL Server 2017 Enterprise edition and upgrade all three instances to SQL Server 2017 Enterprise.
Please suggest the right procedure to completed this whole process.
I'm new to SQL Server DBA tasks, and I need some suggestions on how to upgrade SQL Server 2016/2017 Standard to SQL Server 2017 Enterprise.
Upgrading your two 2017 instances to Enterprise is a simple process. SQL Server Setup has a function called Edition Upgrade, which is a fairly straightforward process.
Your SQL 2016 Standard to SQL 2017 Enterprise is more complicated. You need to assess your environment and determine what best suits your resources, skills and appetite for risk. Microsoft has a good primer on this. You have two high-level options:
An In-Place Upgrade involves upgrading the installed SQL Server instance to a later version/edition in place on the same server. This is a somewhat simpler option but is higher risk. rollback is more difficult and from experience, the potential for things to go wrong are greater.
A Migration upgrade involves building a new SQL Server Instance and migrating your databases and server objects from the current instance to a new one. The advantage of a migration upgrade is that your original instance is left untouched, so rolling back usually involves little more than restoring your pre-upgrade backups and reconfiguring your application connection strings to point to the old server.
You also have the advantage of a 'slow burn' approach, where you can migrate individual databases, or subsets of databases, at a time. This is particularly helpful if your instance is shared by many applications because you can migrate the databases and objects for one application at a time, reducing the overall impact of the upgrade.
The downside to a migration approach is the need for additional hardware and licences for the period that both the old and new instances are running, plus the additional time taken to complete the 'upgrade'. Often this downside is outweighed by the risks involved with an In-Place Upgrade, but that is a business decision you need to determine within your team.
As you've stated in your comments, your goal is to put together a plan for your Senior DBA as part of a training assessment. You've not stated if this needs to be a high-level plan or a detailed plan, but either way, start with the primer from Microsoft and determine your preferred option based on risk appetite, allowable downtime, available resources (people and equipment) and your skillset. From here, you can develop a high-level plan for your Senior DBA to review and, if necessary, develop a detailed plan based on the feedback on your high-level plan.
Don't forget to include a plan for pre-upgrade activities, such as determining application support for the new version and edition, testing the process etc, and post-upgrade activities, such as assessing performance changes, determining if raising the compatibility levels is suitable and potentially enabling newly accessed features. For example, going to Enterprise Edition enabled setting up an Availability Group to take advantage of the advanced HADR capabilities of Enteprise Edition.
