Downgrade SQL Server 2012 from Standard Edition to Developer Edition

Problem

We have several dev/test servers with SQL Server 2012 Standard edition installed. Since Developer edition is free for development/test environment, so it make sense to downgrade to Developer edition. Unfortunately, Microsoft does not provided a direct method to do it. According to Microsoft online book: https://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx, it is easy to upgrade from cheaper edition to expensive edition, not the other way around.

Solution

We will have to uninstall SQL Server 2012 Standard edition, then install Developer edition. But before doing that, we can backup the system databases, then migrate them later into the new installation.

Steps

  1. Preparation: check the instance properties and system database properties, take a screenshot of the root directory and database default locations.database-setting1
  2. Run  SQL Server 2012 Configuration Manager, take a screenshot of accounts used by SQL Server services, then stop the services.
  3. Copy system databases (master, model, msdb) and ReportServer database to a new folder.
  4. Uninstall SQL Server 2012 Standard edition.
  5. Install SQL Server 2012 Developer edition, set the root directory and database default locations the same as the previous installation. Make sure the patch level is the same as before.
  6. Run SQL Server 2012 Configuration Manager, stop SQL Server services.
  7. Copy the system databases (master, model, msdb) and ReportServer database from the backup folder to the current installation folder.
  8. Restart SQL Server services.

Trouble Shooting for “No global profile is configured”

Problem

A weekly maintenance plan failed this weekend. I checked the history of the maintenance job, and found the following error message:

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,, SQL001, MaintenancePlan.Sundays,,, The job failed.  The Job was invoked by Schedule 39 (! DBA – MaintenancePlan.Sundays).  The last step to run was step 1 (Sundays).,00:00:04,0,0,,,,0

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,1, SQL001, MaintenancePlan.Sundays, Sundays,, Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  9:13:00 PM  Progress: 2017-01-29 21:13:01.80     Source: {06474EBF-18F1-49F5-960B-B11B5686CA69}      Executing query “DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp…”.: 100% complete  End Progress  Error: 2017-01-29 21:13:02.40     Code: 0xC002F210     Source: Notify Operator Task Execute SQL Task     Description: Executing the query “EXECUTE msdb.dbo.sp_notify_operator @name=N’The DB…” failed with the following error: “No global profile is configured. Specify a profile name in the @profile_name parameter.”. Possible failure reasons: Problems with the query “ResultSet” property not set correctly parameters not set correctly or connection not established correctly.  End Error  Warning: 2017-01-29 21:13:02.40     Code: 0x80019002     Source: Sundays      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:13:00 PM  Finished: 9:13:02 PM  Elapsed:  2.464 seconds.  The package execution failed.  The step failed.,00:00:04,0,0,,,,0

Solution

The reason is that the database mail profile we have configured, is not the global/default any more. Change the profile to “Default” will fix the problem.

Steps:

1. Right click Database Mail, select Configure Database Mail from the right-click menu.

configure-db-mail

2. Select Next on the Database Mail Configuration Wizard window. configure-db-mail21

3. Select Manage profile security in the window.

configure-db-mail22

4. Select Yes, then Next in the Default Profile field.

configure db mail41.jpg

5. Select Finish to complete the configuration in the next window