In this blog post, we will show you how to downgrade Microsoft SQL Server instances from Enterprise edition to Developer edition on HAQM Elastic Compute Cloud (HAQM EC2) instances. This post is especially useful if there are a single digit number of SQL Server instances to be downgraded. In our next blog post, we will show you how to automate downgrading a large number of SQL Server instances using AWS Systems Manager.

Customers migrating their SQL Server workloads to AWS often ask us how they can optimize licensing to minimize their costs while maintaining functionality and performance. For customers with significant investments in SQL Server licenses, there are many considerations when licensing SQL Server on HAQM EC2 cost effectively.

In on-premises, non-production environments, customers may deploy licensable editions of SQL Server, such as Enterprise or Standard edition, using MSDN subscriptions. However, MSDN subscriptions do not have License Mobility, so when migrating to AWS, customers cannot bring those licenses. They should use SQL Server Developer edition instead because it has all the features of Enterprise edition, but it’s available for free in non-production environments. SQL Server Developer edition is available for versions 2016 and later and can be downloaded from the Microsoft SQL Server downloads page.

Per Microsoft, there is no direct method to do an in-place edition downgrade. The process requires uninstalling SQL Server Enterprise edition and installing SQL Server Developer edition. The steps involve backing up user and system databases, installing SQL Server Developer edition, and then doing a subsequent restore of the databases.

Solution overview

This solution is appropriate if the source and target servers are HAQM EC2 instances, as illustrated in Figure 1. Once the SQL Server workloads are migrated from on premises to HAQM EC2, this solution can be implemented.

Figure 1: Source and Target HAQM EC2 instances

This solution is particularly effective when dealing with very large databases where SQL Server native backup and restore may not be practical due to the time and effort required.

You can also perform a SQL Server downgrade with AWS Database Migration Service (DMS) or SQL Server Import and Export Wizard. However, these options are time-consuming and they only move the data into the target downgraded instance. The logins, agent jobs, and linked servers must be manually migrated into the downgraded target server.

With the approach demonstrated in this blog post, all existing SQL Server objects — including logins, SQL Server Agent jobs, and linked servers —will be propagated to the target downgraded instance. This approach also supports databases with SQL Server Transparent Data Encryption (TDE) enabled. The process of rollback using this method is extremely easy because it does not entail an in-place downgrade.

Walkthrough

Here is a summary of the four primary steps we are going to show you:

Step 1.  Launch a new HAQM EC2 instance and install SQL Server Developer edition, referred to as the target HAQM EC2 instance.

Step 2. On the HAQM EC2 instance hosting SQL Server with Enterprise edition to be downgraded, referred to as the source HAQM EC2 instance, stop SQL Server services and copy the system database’s (master, model, and msdb) MDF and LDF files onto a non-root HAQM Elastic Block Store (HAQM EBS) volume.

Step 3. Take HAQM EBS snapshots of the non-root HAQM EBS volumes hosting the SQL Server Enterprise database’s data and log files on the source HAQM EC2 instance.

Step 4. Attach these HAQM EBS snapshots to the target HAQM EC2 instance configured with SQL Server Developer edition.

Step 1. Launch the new target HAQM EC2 instance

a.     The new target HAQM EC2 instance should be launched with a Windows Server-only HAQM Machine Image (AMI).

b.     Download the SQL Server Developer edition installation media and the necessary cumulative updates to your target HAQM EC2 instance. It has to be the same version (major and minor) as SQL Server on the source HAQM EC2 instance. This can be verified with ‘SELECT @@version’.

Step 2. Create snapshot of the HAQM EBS volumes on source HAQM EC2 instance

a.     Take a snapshot of the source HAQM EC2 instance as a precautionary measure.

b.     Stop the SQL Server services running on the source HAQM EC2 instance.

c.      Copy the system database’s (master, model, and msdb) MDF and LDF files onto a non-root HAQM EBS volume, as illustrated in Figure 2.

Figure 2: System database files copied non-root EBS volume

d. Make a note of the HAQM EBS volumes to take the snapshot of from the HAQM EC2 Console. See Figure 3.

Figure 3: Non-root HAQM EBS Volume IDs from source HAQM EC2

e. Make a note of the volume type, IOPS, and throughput for each of the HAQM EBS volumes and the Availability Zone (AZ), as illustrated in Figure 4.

Figure 4: HAQM EBS volume configurations

f. Figure 5 illustrates creating the snapshot for the required HAQM EBS volumes using the create multi-volume snapshot process. HAQM EBS volume snapshots occur asynchronously. The point-in-time snapshot is created immediately, but the status of the snapshot is pending until the snapshot is complete. A snapshot is considered complete when all of the modified blocks have been transferred to HAQM Simple Storage Service (HAQM S3), which can take several hours for large initial snapshots or subsequent snapshots where many blocks have changed. While it is completing, an in-progress snapshot is not affected by ongoing reads and writes to the volume. However, the incremental snapshots are quick. Therefore, plan accordingly to perform full HAQM EBS volume snapshots ahead of time and create an incremental HAQM EBS volume snapshot when the SQL Server services have been stopped on the source HAQM EC2 instance, as discussed in step 2b.

Figure 5: Create snapshot of HAQM EBS volumes

Figure 6 demonstrates the HAQM EBS snapshot creation in progress:

Figure 6: HAQM EBS snapshots creation in progress

g. Once the snapshots are Available, shown in Figure 7, proceed to the next step.

Figure 7: EBS Snapshots creation completed

Step 3. Restore the HAQM EBS snapshot to an EBS volume

a.     Create new HAQM EBS volumes from the EBS snapshots taken in step 2. The new HAQM EBS volume will be a copy of the EBS volume that created the EBS snapshot. Make sure the AZ is the same as the AZ noted in step 2e. See Figure 8. Refer to the Create a volume from a snapshot documentation if needed.

Figure 8: Create new HAQM EBS volume from EBS snapshot

b.     Once the new HAQM EBS volumes are created and displayed with an Available state, as shown in Figure 9, attach them to the target HAQM EC2 instance, one at a time, and mount the volume inside the HAQM EC2 instance. For more information, refer to the Attach an HAQM EBS volume to an instance documentation.

Figure 9: New HAQM EBS volumes created

c.      Attach the new HAQM EBS volume to the target HAQM EC2 instance, as shown in Figures 10 and 11.

Figure 10: Attach new HAQM EBS volumes

Figure 11: Attach new HAQM EBS volumes to target HAQM EC2 instance

d.     The new HAQM EBS volumes will now show up on the target HAQM EC2 instance with SQL Server Developer edition, as illustrated in Figure 12. In our example, we used a c5 instance type to show that we can also change instance types, but it is recommended to use the same instance type as the source until workload needs are understood.

Figure 12: New HAQM EBS volumes attached to target HAQM EC2 instance

e.     The attached HAQM EBS volumes are shown under the Disk Management Microsoft Management Console (MMC). The disks should be Online by default. If not, bring them online. See Figure 13.

Figure 13: Attached HAQM EBS volumes in Disk management

Step 4. Post HAQM EBS restore tasks

a.     On the target HAQM EC2 instance, stop the SQL Server services and rename the system database’s (master, model, and msdb) MDF and LDF files, as shown in Figure 14:

Figure 14: Old system database files renamed

b. Copy the SQL Server Enterprise edition’s system database’s MDF and LDF files from the newly attached HAQM EBS volume to the current location of the SQL Server Developer edition’s system database files on the target HAQM EC2 instance, as shown in Figures 15 and 16.

Figure 15: System database files on newly attached HAQM EBS volumes

Figure 16: System database files copied to target HAQM EC2 instance

c. Start the SQL Server services on the target HAQM EC2 instance.

d. Attach the user database files that were moved, along with the new HAQM EBS volumes, as shown in Figures 17 and 18.

Figure 17: User database files from source HAQM EC2 instance

e. For user databases, you can create new databases using the MDF and LDF files copied from source HAQM EC2 instance. In this example, we are using the sample Northwind database.

CREATE DATABASE Northwind ON 
( FILENAME = N'D:\MSSQL\Northwnd.mdf' ),
( FILENAME = N'D:\MSSQL\Northwnd.ldf' )
 FOR ATTACH
GO

Figure 18: Attach user databases

f. Figure 19 shows that the SQL Server logins, SQL Server Agent jobs, and user databases were transferred from the source HAQM EC2 instance to the target HAQM EC2 instance with SQL Server Developer edition.

Figure 19: SQL Server Agent jobs, logins and user database

Cleanup

After the Enterprise-to-Developer edition downgrade, to avoid ongoing charges, delete the HAQM EBS snapshots taken from the source HAQM EC2 instance. See Figure 20.

Figure 20: Delete HAQM EBS volume snapshots

Conclusion

In this blog post, we have demonstrated how to manually downgrade SQL Server Enterprise edition hosted on an HAQM EC2 instance to SQL Server Developer edition to help save on costs and reduce downtime—all while keeping the source SQL Server Enterprise database intact. Rollback is extremely easy in this solution because it is not an in-place downgrade.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.