| sitelink1 | https://www.mssqltips.com/sqlservertip/3...d-edition/ |
|---|---|
| sitelink2 |
Enterprise Edition 만이 제공하는 특정한 기능을 이용하지 않은 경우에만 활용 가능한 Downgrade 방법임
그렇게 설치한 시스템에서 고대로.... 데이터만 파일로 백업하고 복원하여 SQL Server 를 Downgrade 할 수 있음
<Problem>
I have a SQL Server that was installed as Enterprise Edition. I don't need the Enterprise features and I don't want to pay for the licensing. What is the best way to downgrade to Standard Edition?
<Solution>
I've came across a lot of SQL Server's in my career that were installed using the Enterprise Edition and never utilized the Enterprise features. Downgrading to a lower edition cannot be done in place. This tip will describe the steps needed to downgrade.
Create Database Backups
As always, before doing anything drastic (or non-drastic), BACKUP all the databases (system and user)! Also, if you have a development environment, please test on it first.
Check for Enterprise features
After backing up the databases run the following query on each database:
SELECT * FROM sys.dm_db_persisted_sku_features
This DMV will tell you whether or not the database is utilizing any of the Enterprise features. You can find more information regarding this DMV from this tip.
Check Version and Build Number
Because you never know when an install may fail, check the version and build number of the current SQL Server. After the downgrade, you will need to bring the new SQL Server back to the patch level. You can view this information by executing the following command:
SELECT @@VERSION
Save the results to a text editor.
Decision...
There are two different directions we can go from here.
- We can do the traditional uninstall Enterprise, install Standard, patch, restore databases
- Or we can do what I call "The Jonathan Kehayias" approach. (I saw this method awhile back from a forum post by Jonathan):
Copy System Databases
Shutdown the SQL Server service and copy the master, model and msdb database files (.mdf and .ldf) to another location. We will need these later:
Uninstall SQL Server
Uninstall SQL Server from Control Panel:
You only need to uninstall the Instance (includes SSIS, SSAS, SSRS, SSDT). There is no need to uninstall the Shared Components (SSMS, Client Tools, and Connectivity).
Reboot
Reboot the server. After rebooting, browse to the location of the data files and you will notice that the user databases weren't removed with the uninstall, but the system databases were. This is why we copied them in the step above:
Install New SQL Server Edition
Enterprise Edition should be completely removed from the system now. Insert/Mount the media for SQL Server Standard and install. The install should be pretty straight forward. To make things easier in the end, make sure the name of the instance remains the same and the Data Directories point to the correct location.
Once SQL Server is finished installing, open SQL Management Studio and connect to the newly installed instance.
If you expand Databases, Security/Logins, SQL Server Agent/Jobs, etc. you will see it looks like a fresh copy of SQL Server.
Also, if you run the following command you will see that the version has changed to Standard Edition:
SELECT @@VERSION
Install SQL Server Patches
In this example, you will also notice that the build number has changed from 11.0.3000.0 to 11.0.2100.60 so I will need to install the correct patches to bring this server back to 11.0.3000.0.
(11.0.3000.0 is SQL Server 2012 SP1)
Download the correct patches and install on the server before doing anything else.
Once the patches are installed check the server to make sure it's at the same build number as it was before.
Copy System Databases
Shutdown the SQL Server service and copy the master, model and msdb database files (.mdf and .ldf) from the location you saved them in to the new location for the new install.
Start SQL Server
After copying the system files to the new location you can start SQL Server again:
Once SQL Server comes back online, you should see all the databases, logins, jobs, etc. back to normal:
