Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 22 May 2017

How to Change SQL Services run through Different Service Account on SQL Server other than Default

How to Change SQL Services run through Service Account on SQL Server


service account it should meet below criteria



  • Service account should be password protected



  • Service account should be full control over Data folder which SQL Service is installed on local drive path



if again it is not working follow below for

we can go below


  • it should be local admin of server



  • it should be part of SQL Server sysadmin privileges



Demo:

Here SQL 2012 C O N V TEST is running on default account as

NT Service\M S SQL$SQL 2012 C O N V TEST

we need to change it to USER PC / USER



Here make sure/add  it should be full control on DATA folder on which SQL Server is installed


Now we are going to change SQL Service account as see below


you should make sure password is mandatory other wise you will get error



if you not give password/ account does not have password then will face below error


after we changed service account successfully see below



keep blogging and enjoy!!!!!!!!!!!!!






















Monday, 1 May 2017

How to resolve .bak files does not shown in the folder while restore in SQL Server


How to resolve .bak files does not shown in the folder while restore in SQL Server


you can see now we are going to restore standard edition 2012 from express edition 2012

Source
Express edition 2012



Destination:

SQL 2012 Standard edition 


now we are going to select .bak files from SQL 2012 Express edition

but it did not shown up as see below


but actually bak files existing on specified location



when we go to properties of folder it showed express edition service account which SQL Server running

it causes it did not show while choose file from SQL Standard edition


solution

we should need to give all permission on Restoring server Service account of which SQL server running our cases SQL Standard edition service account


Now we choose standard edition service account and give all permission on backup folder


now give all permission of standard edition service account


Now it shown bak files as see below


so restore database activity is done

















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

Select Name,physical_name from sys.master_files



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:

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