locked
Database restore to different SQL server different domain/forest RRS feed

  • Question

  • We are going to remove several databases from Production SQL server to new domain/forest to new SQL server.
    Trust between two domains will be setup and it will remain permanently.
    Source SQL server is going to be SQL 2008 R2 SP2 CUx.
    Target SQL is 2012 SP1 CUx. 
    Step 1 :
    I will back-up all databases first Full backup then Transaction logs.
    Database will be than put in to read only mode. 
    Step 2 :
    I will restore each database to new server
    Step 3 :
    I will use sp_help_revlogin script to create all logins after I restore database…
    That includes SQL users and Windows users.  It is important to know, the  users from old domain will use the same user name, to access SQL database. We will not use  new domain credentials.
    Step 4 :
    Create new account related  to existing jobs. So there will be new users
    I will recreate all jobs, that exist on old SQL server. I will look and change any domain names, and path, that will point to old server/old domain/old users..
    Step 5 : 
    I use SQL alias to connect to databases. I will have to change name of SQL alias target server in every client, that connect to that server. Alias will be the same, only server name will change, and point to new SQL Server.  I will create Alias-es on new SQL server with the same name as old server.
    DNS for alias in old domain will point to new IP.

    Did I miss anything  ?
    Monday, July 8, 2013 12:55 PM

Answers

  • I have got response from all involved parties, regarding migration to SQL 2012.  I got conformation that all databases are compatible with SQL 2012.

    Only database that is going to be moved by contractor is related to CRM/ERP application, and they will move it and that has some SSIS packages.

    There are other shell/batch jobs. 

    All setups will use the same disk configuration, that I mean the same drive letters. Server will use same collation. Server is going to be Cluster, but I do not believe it is relative in these matter.

    I will have to setup LogShipping. AlwaysOn Availability,  is not an option (not enough HW).


    • Edited by MaliStane Monday, July 8, 2013 1:51 PM mirroring - > log shipping
    • Proposed as answer by Sofiya Li Wednesday, July 10, 2013 6:51 AM
    • Marked as answer by Sofiya Li Wednesday, July 17, 2013 2:15 PM
    Monday, July 8, 2013 1:50 PM

All replies

  • Path of Installed sql binaries are same? I happened  to see a problem with msdb..syssubsystems.

    Do you  have any power shell/batch files to copy over? Are the Server / Database configurations same? Any SSIS packages are there?


    Srinivasan

    Monday, July 8, 2013 1:14 PM
  • I have got response from all involved parties, regarding migration to SQL 2012.  I got conformation that all databases are compatible with SQL 2012.

    Only database that is going to be moved by contractor is related to CRM/ERP application, and they will move it and that has some SSIS packages.

    There are other shell/batch jobs. 

    All setups will use the same disk configuration, that I mean the same drive letters. Server will use same collation. Server is going to be Cluster, but I do not believe it is relative in these matter.

    I will have to setup LogShipping. AlwaysOn Availability,  is not an option (not enough HW).


    • Edited by MaliStane Monday, July 8, 2013 1:51 PM mirroring - > log shipping
    • Proposed as answer by Sofiya Li Wednesday, July 10, 2013 6:51 AM
    • Marked as answer by Sofiya Li Wednesday, July 17, 2013 2:15 PM
    Monday, July 8, 2013 1:50 PM
  • Hi MaliStane,

    Thanks for your sharing. It will be very beneficial for other community members who have similar questions. If the issue had been solved, I like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have?

    Thanks
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Wednesday, July 17, 2013 2:15 PM