Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday 28 April 2017

Step by Step Downgrade Enterprise Edition to Standard Edition in SQL Server 2012

Step by Step Downgrade Enterprise Edition to Standard Edition in SQL Server 2012

Pre check


Step1 

take the edition info from enterprise edition with screen shot



Step2 

kindly create sp_help_revlogin script in source (enterprise edition)  if not exists







Step3

Need to take login script out using sp_help_revlogin logins from source







Step 4: 

Need to take logins info (server level and database level with privileges) and save it in notepad









Step 5:

Need to take script out Linked servers,Jobs, Full Text Index,login screen shot etc













Step 6:

Need to take logical name and physical name and save it to excel/screenshot using this query

Exec SP_HELPDB dbname

Select sd.name as DatabaseName,sm.Name as logicalname,physical_name from sys.master_files sm
join sys.databases  sd on sm.database_id=sd.database_id
WHERE sd.name='dbname'








Step 7:

Need to take sp_configure output to excel and save it in excel from source



Step 8:

Need to take services running info in screenshot with db owner info along with default path of data

also take it from configuration manager ipaddress and service status





Step9:

Take the full backup of the all database from Source



Step 10: 

Need to take user db and system db with temp db in screen shot for refer

user db info



temp db info


system db info



Process


Step 1:

Stop the SQL Services


SQL Services stopped status as see below



Step 2:

Need to copy all system source(enterprise) of  system db of  master,model,m s db,temp db to new location




Step 3:

Need to uninstall Enterprise Edition (we should need to restart system after this uninstall)




You only need to uninstall the Instance (includes S S I S, S S A S, S S R S, S S D T). There is no need to uninstall the Shared Components (S S M S, Client Tools, and Connectivity)





Contd from Picture above as see below 


SQL Enterprise Edition Removal succeeded


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

Step 4:

After System restart

Need to Install SQL Standard Edition


you can some of features are already selected as we did not remove in enterprise edition removal


We should need to give same Instance name ( if it should be named instance) for default instance it should be MS SQL SERVER  only


Step 5:

Need to give user database directory from old path while installation


You can see system data files are removed(that is why we backed up system db) after uninstall of enterprise edition also DATA folder is missing in Drive. it will automatically create that folder

User database will not be removed from this uninstall method



now we need to remove existing temp db (enterprise)  from location (we already copied to new

location)


if you choose Install and configure option in reporting services you will get error as see below



to solve this issue kindly use install only option



now you can see errors are passed by see below


SQL Standard Edition Installed Successfully



Step 6:


Before start we should need to change in SQL startup parameter for master it will avoid error to start 

SQL Server due to we change master,model location from system default path

after complete installation login to server verify server and edition info






Step 7:

Stopping SQL Services and copy system db to other location



Step 7: 

Apply patches to latest Service Pack S P 3 for SQL 2012


it provides updates of SQL 2012


Now you can option to apply service pack all existing server at a time or particular server


It is updating one by one as see below




Step 8:

after complete installation patches login to server verify server and edition info



Step 9:

stop SQL Services from configuration manager

Step 10:

after applied patch we have to copy standard edition system db to new path



Step 11:

Now replace model, ms db (enterprise from old path) to new Standard original model, ms db






Step 12:

we need to verify user database m d f / l d f point to new standard original location if not we have to move



Step 13:

Start the services



Step 14:

apply login scripts,linked servers,jobs we took out from source





Step 15:

solve the orphan users and create system level permissions from script(sysadmin privileges)



user db  bring method

Method 1

attach method





Method 2

we can create dummy database and then modify query to replace it

create dummy db on same name


we should consider logical and physical name should be same as per original db with dummy path


now we alter with our original db path


we took offline of dummy db


now we remove dummy db  data and log file from drive

now we move original data  and log file to original path


now we took db online


that is it now we original user db online


Method 3:

backup/restore method

Method 4:

copy and replace existing enterprise master, model ,ms db to new system data file to get all system and user databases

Note 
we should need to take current system db file to backup to other location as safety purpose

i replaced all system db from enterprise to standard as see below


I put all user database M D F / L D F to user path as see below


now you can see all system and user database come online




Post check:

Step 1:

Need to configure memory as per 80% of TOTAL RAM



Step 2:

configure temp db secondary files as per core

There is a special feature in MS SQL from SQL 2005 version on wards,

 Configure no. of Temp DB database data files as per available C.P.U. Cores. If no of cores =< 8 then use the same number of data files as logical processors.

select @@servername ServerName,scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'-------------core usage

select cpu_count from sys.dm_os_sys_info-------total CPU count



Step 3:

configure error log files



Step 4:

set the history from SQL Agent, db mail configuration



Step 5:


Need to check application connectivity



Post Installation check list