locked
Sql Cluster Migration RRS feed

  • Question

  • Dear Experts,

    I have a requirement for creation of a new SQL 2008 R2 Instance on a windows server 2008 R2 to decommision an existing SQL 2008 R2 Instance. However the specifcation is to retain the same SQL Instance Name and IP.

    Can you please validate the below mentioned steps :

    1. Build the New Windows cluster and Install the SQL Instance with a different name say XYZ wherein the existing name is ABC. The configuration of source and destination SQL Server i.e Version, edition is same.

    2. Replicate the LUN from source to destination and attach the user databases on destination server.

    3. Take backup of MSDB database from source and restore on destination.

    4. Script all objects for Master database including Logins, linked servers and execute on destination.

    5.Create another object in AD say PQS and change the source virtual SQL Instance name from ABC to PQS.

    6.Change the Virtual IP of the source SQL server.

    7.Delete the Object Name ABC from AD.

    8.Create a new object ABC in AD.

    9.Change the destination SQL Server Virtual Name to ABC and assign the same IP as the Original Source Name.

    Wednesday, June 11, 2014 2:51 AM

Answers

  • Yes looks good but bit worried with assigning the same IP (assign the same IP as the Original Source Name.), that may give some confilcts... Have you tested before ?If possible just test it once because if anything goes wrong then you may have to spend some time to fix the issue, even if its production server the its not a good practice..

    Other than this everything looks good for me because everything is in same version of sql.. and SAN and all the configuration


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by tracycai Wednesday, June 18, 2014 11:14 AM
    Wednesday, June 11, 2014 3:04 AM
  • Following approach can be consider too as this was recently applied all went fine.


    Before Migration Day:
    0. For Backup\Note purpose, script out everything be it OS, DB configurations end to end like sp_configure from master db, script out jobs, location of SSIS packages, OS specific configurations e.i. every thing.
    1. Install and configure new separate Failover Sql Server and keep different\separate name and IP. Make sure that you are planning\keeping same IP segment for physical server nodes as this will make sure that IP changes of the new instances will be a lot easier for you.
    2. Make sure whatever you are configuring must be same to that of existing server.


    *** If some testing is required due to some SSIS packages and all,and there are good number of days for actual migration days then follow below path:
    1. Put a schedule job to do full Backup on existing server and restore all databases once a day on new server.
    2. Put all SSIS packages on new servers too. Make sure that jobs and others db objects must not refer existing server through any means or stop the Project Team for this option if they are not sure of.
    3. Scrit out all logins and priviledges, to get an idea, please check below link:
    http://support.microsoft.com/kb/918992/en-us
    3. Script out other rest db objects like linked servers, operators, db email, jobs and for the time being you can put all jobs in disabled state and you enable then on the day or migration and execute these script on new server. If  you doubt that config. is still referring existing server then just disabled them for time being till on day migration activity.

    On Day of migration.
    0. Make sure that there are no sessions on databases. Front End application services must be stopped, any other services configured using dos batch files execution which can still directly access db must be stopped too.If any other means if you think are there, then stop them as well.
    1. Run the backup schedule job on more time with the down time provided and restore the dbs too.
    2. Stop\Bring down the services on existing server or follow the 3rd step to name and ip changes for existing server to something different for any future validations.
    3. To change the name and ip as per our expectation on new server now which you are targeting as replacing your existing\old server, you can follow the information\hint as shared in below link:
    http://msdn.microsoft.com/en-us/library/ms178083.aspx
    4. Once you brought new server in all same to same that of old server, name, ip etc. and this name and ip is avaiable over network then follow enabling the rest of disabled objects if any to bring all db objects in place on new server.
    5. Check for all configuration jobs are of same value to this server from backup note above like memory, cpu, page file, any dbcc configuration, other software if any etc etc too.
    6. Allow application to start application and different component to start testing.
    7. Get final signal off.

    All the best, let me know if you have any issues.

    MSDB restoration is required if you have put all packages over there or some customized db objects otherwise above pointers will take care of so many things.


    Santosh Singh

    • Marked as answer by tracycai Wednesday, June 18, 2014 11:14 AM
    Wednesday, June 11, 2014 7:48 AM

All replies

  • Yes looks good but bit worried with assigning the same IP (assign the same IP as the Original Source Name.), that may give some confilcts... Have you tested before ?If possible just test it once because if anything goes wrong then you may have to spend some time to fix the issue, even if its production server the its not a good practice..

    Other than this everything looks good for me because everything is in same version of sql.. and SAN and all the configuration


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by tracycai Wednesday, June 18, 2014 11:14 AM
    Wednesday, June 11, 2014 3:04 AM
  • Following approach can be consider too as this was recently applied all went fine.


    Before Migration Day:
    0. For Backup\Note purpose, script out everything be it OS, DB configurations end to end like sp_configure from master db, script out jobs, location of SSIS packages, OS specific configurations e.i. every thing.
    1. Install and configure new separate Failover Sql Server and keep different\separate name and IP. Make sure that you are planning\keeping same IP segment for physical server nodes as this will make sure that IP changes of the new instances will be a lot easier for you.
    2. Make sure whatever you are configuring must be same to that of existing server.


    *** If some testing is required due to some SSIS packages and all,and there are good number of days for actual migration days then follow below path:
    1. Put a schedule job to do full Backup on existing server and restore all databases once a day on new server.
    2. Put all SSIS packages on new servers too. Make sure that jobs and others db objects must not refer existing server through any means or stop the Project Team for this option if they are not sure of.
    3. Scrit out all logins and priviledges, to get an idea, please check below link:
    http://support.microsoft.com/kb/918992/en-us
    3. Script out other rest db objects like linked servers, operators, db email, jobs and for the time being you can put all jobs in disabled state and you enable then on the day or migration and execute these script on new server. If  you doubt that config. is still referring existing server then just disabled them for time being till on day migration activity.

    On Day of migration.
    0. Make sure that there are no sessions on databases. Front End application services must be stopped, any other services configured using dos batch files execution which can still directly access db must be stopped too.If any other means if you think are there, then stop them as well.
    1. Run the backup schedule job on more time with the down time provided and restore the dbs too.
    2. Stop\Bring down the services on existing server or follow the 3rd step to name and ip changes for existing server to something different for any future validations.
    3. To change the name and ip as per our expectation on new server now which you are targeting as replacing your existing\old server, you can follow the information\hint as shared in below link:
    http://msdn.microsoft.com/en-us/library/ms178083.aspx
    4. Once you brought new server in all same to same that of old server, name, ip etc. and this name and ip is avaiable over network then follow enabling the rest of disabled objects if any to bring all db objects in place on new server.
    5. Check for all configuration jobs are of same value to this server from backup note above like memory, cpu, page file, any dbcc configuration, other software if any etc etc too.
    6. Allow application to start application and different component to start testing.
    7. Get final signal off.

    All the best, let me know if you have any issues.

    MSDB restoration is required if you have put all packages over there or some customized db objects otherwise above pointers will take care of so many things.


    Santosh Singh

    • Marked as answer by tracycai Wednesday, June 18, 2014 11:14 AM
    Wednesday, June 11, 2014 7:48 AM