How do I configure my SQL Server instance to use a different Edition with minimal side effects?

Question

I'm in the process of migrating an old server running Windows 2008 R2 with SQL 2012 Express to a new server running SQL Server 2017 Standard and Windows 2016. I would like to mirror databases between them, but the Express Edition doesn't support it. I tried to upgrade it to SQL 2012 Standard Evaluation, but I received the following error:

Failed The selected SQL Server instance does not meet upgrade matrix requirements.

I discovered this isn't possible: Upgrade SQL Server 2012 Express to Evaluation Edition

I was forced to install SQL Server 2012 Evaluation as a new side-by-side instance and named it ./SQLSERVER2. In order to use this instance, however, it would require moving a ton of data files and changing the instance names in all my configuration files. This will also require server downtime as well. This will be a major PITA without the humous and I want to avoid it if at all possible.

Is there a way of reconfiguring ./SQLSERVER to use the Standard Evaluation Edition with minimal downtime and side-effects?

Answer 1

You could possibly follow these steps to "migrate" the databases to a "new" SQL Server 20xx instance.

1. detach the databases in the running SQL Server Express instance and leave them where they are.
2. Script out any logins that you require.
3. Don't delete/drop any databases.
4. De-install the SQL Server 2008 R2 Express Edition.
5. Install the SQL Server 20xx Standard Edition. (you can even use the instance name SQLEXPRESS; how clever is that?)
6. Add the SQL Server Logins to your "new" SQL Server 20xx Standard Edition instance.
7. Once the "new" SQL Server 20xx Standard Edition is running attach the "old" databases to your new SQL Server 20xx instance.
8. Change the database compatibility level to 110 (if required).

Enjoy a "new" SQL Server 20xx Standard Edition instance hosting your "old" databases.