I recently had a client using SQL Server 2012 Standard Edition on three of their development servers. However, SQL Server 2012 has a developer edition that is specifically designed for development servers. More importantly, our client has free SQL Server 2012 Developer edition licenses as part of the paid SQL Server Standard Editon purchase.
The problem
Our customer decided to convert their Standard Edition to the Developer Edition so they could save big bucks. However, the conversion process can be a bit tricky. And it’s not as easy as going into setup and changing the SQL Server edition from the Standard Edition to the Developer Edition. The SQL Server setup only goes from Developer edition to the paid versions. Essentially, you need to uninstall the SQL Server Standard Edition and then reinstall Developer Edition for each server instance.
The solution
First, you need to have admin rights to access the virtual machines as well as the servers. Then, you need to take the following steps to complete the conversion process:
- Get the version of the server for the SP/CUs that were applied already.SELECT @@version;
- Identify the file paths for the data, log, and backup locations.
GO
-- Get the location default location for data and log files
SELECT
SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files',
SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files';
-- Get the default backup directory location from the Windows Registry
DECLARE @path NVARCHAR(4000)
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
@path OUTPUT,
'no_output' ;
SELECT @path;
-- Get the actual locations of the data and log files for the server
SELECT name, physical_name AS current_file_location
FROM sys.master_files
ORDER BY name;
-- Retrieve the latest back up information based on backup finish date
SELECT TOP 100 b.server_name, b.database_name, m.physical_device_name, b.backup_start_date,
b.backup_finish_date, b.backup_size / 1024.0 / 1024.0 AS BackupSizeMB
FROM msdb.dbo.backupset AS b INNER JOIN
msdb.dbo.backupmediafamily AS m ON b.media_set_id = m.media_set_id
ORDER BY b.backup_finish_date DESC
- Identify the service accounts for the various SQL Server services, including SSRS if you have a local version of SSRS running.
- Please note, if you are using a domain user account for these services, you will need to have the passwords for the installation of the SQL Server Developer edition.
SELECT servicename, service_account
FROM sys.dm_server_services
- Perform full backups of system and user databases. (You can skip this step if you already have full backups).
- Stop the SQL Server service.
- Copy .mdf and .ldf files of master and msdb databases to the separate location.
- This will ensure all catalog information is preserved in case we need to go back.
- Uninstall the SQL Server Standard edition.
- The user and system database files, agent jobs, SSIS catalogs, etc. will all remain.
- Install the SQL Server Developer edition (check default paths and instance name).
- This process creates new versions of the master and msdb databases without connections to previous databases.
- Configure the service accounts to match the prior environment so the security ACLs match up to the storage locations for the data files and the backup file system.
- Update the SQL Server to the SP/CU level that previously existed.
- Stop the SQL Server service.
- Replace .mdf and .ldf files of master and msdb databases from the previous installation.
- This process re-links the database references, agent jobs, etc.
- Finally, restart SQL Server service.
Now you are back up and running on the SQL Server Developer edition. I if you have multiple instances of SQL Server, you will need to follow these steps for each one.
