SQL Database Moving from One Instance to Another: In this case we are moving sql database “Moving_DB_SQL1_To_SQL2” from SQL1 instance to SQL2 Instance.
Step1: Create required SQL logins of the source database on target instance. For “Moving_DB_SQL1_To_SQL2” database on SQL1 is having a sql login “SWA_SQL_LOGIN” .
Created “SWA_SQL_LOGIN” SQL login on SQL2 instance getting pwd from DF/kee pass/ from where we stored tool
Step2: Offline the database source instance. Made “Moving_DB_SQL1_To_SQL2” database offline on SQL1 instance and detached the database.
Step3: Move/copy the MDF and LDF files from SQL1 instance to SQL2 instance.
Step4: Attached the MDF & LDF files of “Moving_DB_SQL1_To_SQL2” database on SQL2 instance.
Step5: Activities on SQL2 instance
Changed DB Owner to SA
Changed the Database to Simple Mode as it is TEST environment. If it is Prod need to check the current mode in Prod on Source instance and make the same.
Run Fix Orphan user stored proc on the new database
Verify the newly attached database once.
Step6: Moving Client Access point and port# (KSRDBTEST,1433) from SQL1 to SQL2 instance.
Remove Dependency from SQL1 instnace. Go to Dependency Tab and remove the client access point IP address and click ok.
Remove the port# under TCP/IP related to KSRDBTEST client access in SQL1 instance.
Make the Client Access name KSRDBTEST offline.
Stop and Start the SQL1 instance in fail over cluster manager.
With the above steps Client access dependency is removed from Source Now add the client access point to SQL2 Instance.
Go to Failover cluster manger, under SQL1 instance assign the Client Access Name KSRDBTEST to SQL2 Instance.
Configure the Port# under TCP/IP on SQL2 instance.
Stop and Start the SQL2 instance in the failover cluster manager.
Connect in SSMS with Client access name and verify.
No comments:
Post a Comment