Monthly Archives: September 2011

MSSQL Database Server Migration from default instance to named Instance

Task: Migrating ERA DB to DUDB\ERA

Senario: -There were 44 databases and its associated users along with SQL server Agent jobs, It was a tedious and time consuming task which includes following steps

  • Attach/ restore all databases
  • Migrate SQL server logins from ERADB to DUDB which in-turn disturbs the user mapping between Database due to SID issue
  • Migrate SQL agent Jobs

To reduce the repetitive manual work and save time , I choose to Migrate Entire instance with the help of system databases

Issues :-

  1. On ERADB SQL server was using default instance and on DUDB Instance name is ERA
  2. The folder structure of SQL server on ERADB was C:\Program Files\Microsoft SQL Server\
    MSSQL_10_50.MSSQLSERVER\MSSQL
    and on DUDB it was C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\

    The Folder structure is getting used by SYSTEM databases of SQL server

Solution worked to resolve the issues

  1. First I have changed the SQL server instance name on ERADB as below

     

    1. Check the current instance name

select
@@servername

 

  1. Remove the existing instance name


sp_dropserver
‘ERADB’

go

c) change the new Instance name


sp_addserver
‘DUDB\ERA’
,
‘local’


go

 

d) Verify sql
server instance configuration


sp_helpserver


select
@@servername

 

  1. Changing the folder structure of SYSTEM databases ( MASTER, MSDB,TEMPDB,MODEL )
    1. First created the folder structure as of DUDB on ERADB
    2. Stopped SQL server service on ERADB
    3. Copied all System databases to the newly created folder structure
    4. Started SQL server Service
    5. Now the main task to tell SQL server about this folder structure change

       

       

    To do so used below tricks

For msdb Database :- MSDB database contains all the SQL Agent jobs , and its schedules..

Use master
go
Alter database msdb modify file (name=msdbdata,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\msdbdata.mdf’)
go
Alter database msdb modify file (name=msdblog,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\msdblog.ldf’)
go

For tempdb Database :- in tempDB all the temporary variables, tables transactions are carried out

Use master
go
Alter database tempdb modify file (name=tempdev,filename= ‘C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\tempdb.mdf’)
go
Alter database tempdb modify file (name=templog,filename= ‘C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\tempdb.ldf’)
go

For MODEL Database

Use master
go
Alter database model modify file (name=modeldev,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\model.mdf’)
go

Alter database model modify file (name=modellog,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\modellog.ldf’)
go

And the LAST

For master Database :- as Master database is the Core database of SQL server in which all the user databases , and all users information is present.

Open regedit

In HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server \MSSQL_10_50.MSSQLSERVER\MSSQLSERVER\Parameters

Change the vales as below

SQLARG0 = -dC:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\ master.mdf

SQLARG1 = -eC:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\Log \ERRORlog

SQLARG2 = -lC:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\ mastlog.ldf

 

  1. Now restarted SQL server service on ERADB, checked the files path for system databases and found it has been pointing to new folder structure location.

     

  2. Then stopped SQL server on ERADB

     

  3. Stopped SQL Server service on DUDB for ERA instance.

     

  4. Created d:\ERADB and E:\ERADB2 on DUDB server

     

  5. Copied all subfolders of ERADB and ERADB2 from eradb server to dudb server

     

  6. Taken backup of system databases of DUDB for ERA instance

     

  7. Copied system databases from ERADB ( new folder structure ) to DUDB era instance folder

     

  8. Started SQL Server service on DUDB for ERA instance.
Advertisements

Leave a comment

Filed under Uncategorized

[SOLVED] Preparing to configure Windows. Do not turn off your computer….

After uninstalling SMTP server feature on windows 2008 R2 Server, asked for restart, so restarted the Server and after long time ( approx 1 Hour wait) server has not restarted.

Ping was working fine, but RDP was not working.

On the server console below text was getting displayed.

Preparing to configure Windows. Do not turn off your computer.

 

 

So done below tricks to make server operational.

  1. Taken remote of another server of which user name and password is same as of SERVER A
  2. Opened services.msc , connected to server A
  3. Come to know WINDOWS MODULES INSTALLER Service is in Stopping mode

    And was not allowing to stop or start from services.msc of Server B

     

     

  4. If we hardboot server , there was chance of windows OS crash.
  5. Opened properties of WINDOWS MODULES INSTALLER service to check for the command getting executed for this service

     

  1. The executable for the service was

    C:\windows\servicing\trustedinstaller.exe

  2. Now searched for remote task manager and downloaded from

    http://lizardsystems.com/remote-process-explorer/

    this tool allows to connect to remote system task manager

     

     

  3. Right click on the above executable and select option kill
  4. The server was shown below screen

 

10 ) And restarted the server J

 

Thanks

Prashant Deshpande

 

 

 

117 Comments

Filed under Uncategorized