In this blog post, we will show you how to downgrade Microsoft SQL Server instances from Enterprise edition to Developer edition on Amazon Elastic Compute Cloud (Amazon 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 Amazon 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 Amazon EC2 instances, as illustrated in Figure 1. Once the SQL Server workloads are migrated from on premises to Amazon EC2, this solution can be implemented.

Figure 1: Source and Target Amazon 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 Amazon EC2 instance and install SQL Server Developer edition, referred to as the target Amazon EC2 instance.

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

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

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

Step 1. Launch the new target Amazon EC2 instance

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

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

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

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

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

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

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

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

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

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

Figure 4: Amazon EBS volume configurations

f. Figure 5 illustrates creating the snapshot for the required Amazon EBS volumes using the create multi-volume snapshot process. Amazon 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 Amazon Simple Storage Service (Amazon 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 Amazon EBS volume snapshots ahead of time and create an incremental Amazon EBS volume snapshot when the SQL Server services have been stopped on the source Amazon EC2 instance, as discussed in step 2b.

Figure 5: Create snapshot of Amazon EBS volumes

Figure 6 demonstrates the Amazon EBS snapshot creation in progress:

Figure 6: Amazon 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 Amazon EBS snapshot to an EBS volume

a.     Create new Amazon EBS volumes from the EBS snapshots taken in step 2. The new Amazon 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 Amazon EBS volume from EBS snapshot

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

Figure 9: New Amazon EBS volumes created

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

Figure 10: Attach new Amazon EBS volumes

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

d.     The new Amazon EBS volumes will now show up on the target Amazon 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 Amazon EBS volumes attached to target Amazon EC2 instance

e.     The attached Amazon 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 Amazon EBS volumes in Disk management

Step 4. Post Amazon EBS restore tasks

a.     On the target Amazon 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 Amazon EBS volume to the current location of the SQL Server Developer edition’s system database files on the target Amazon EC2 instance, as shown in Figures 15 and 16.

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

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

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

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

Figure 17: User database files from source Amazon EC2 instance

e. For user databases, you can create new databases using the MDF and LDF files copied from source Amazon 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 Amazon EC2 instance to the target Amazon 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 Amazon EBS snapshots taken from the source Amazon EC2 instance. See Figure 20.

Figure 20: Delete Amazon EBS volume snapshots

Conclusion

In this blog post, we have demonstrated how to manually downgrade SQL Server Enterprise edition hosted on an Amazon 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.