Article ID: 128894, created on May 31, 2016, last review on Jun 17, 2016

  • Applies to:
  • Operations Automation

Additional info

Starting from Odin Automation 7.0, SQL Server 2000 and SQL Server 2005 are not supported. Existing databases must be migrated to the SQL Server 2014 instance.

Obtaining

Download mssql2014migration-1.0-script.zip.

Before migration

  1. Deploy MS SQL Server 2014 hosting node according to the Windows Shared Hosting Deployment guide, Installing MS SQL Server section.
  2. If you need to migrate from SQL Server 2000, migrate to SQL Server 2005 first. For instructions, refer to the Windows Shared Hosting Deployment (Legacy Components) guide, Upgrading to MS SQL Server 2005 section.

Migration

Databases migration

  1. Open SQL Server Management Studio and connect to the instance of SQL Server 2005.
  2. Detach all databases by executing the detatch.sql script.
  3. Move all .mdf and .ldf files of customers' databases to the host with the SQL Server 2014 instance. Usually, customers' databases are located in the C:\CustomerData\Databases\MSSQL\Data directory.
  4. Open SQL Server Management Studio and connect to the instance of SQL Server 2014.
  5. Attach moved databases by executing the attach.ps1 script and passing the directory with .mdf and .ldf files as argument. Example:

    attach.ps1 -path "C:\CustomerData\Databases\MSSQL\Data"
    

    If one gets an error that "attach.ps1 cannot be loaded because the execution of scripts is disabled on this system...", execute the following command first:

    Set-ExecutionPolicy RemoteSigned
    

    Please note, that there may be some errors during the attach.ps1 script execution. In that case, open Event Viewer and check MS SQL Server Application error entries. If they contain a text like "The transaction log for database is full" then ignore them.

Customer logins migration

  1. Open SQL Server Management Studio and connect to the instance of SQL Server 2005.
  2. Execute the sq_help_revlogin.sql script to create a store procedure and then execute it:

    EXEC sp_help_revlogin
    
  3. Copy the generated script.
  4. Open SQL Server Management Studio and connect to instance of SQL Server 2014.
  5. Execute the copied script.

There may be some errors regarding system logins ("BUILTIN\Administrators", "NT AUTHORITY\SYSTEM") and regarding SQL Server 2005 Odin Automation related logins ("SQLServer2005MSSQLUser", "SQLServer2005SQLAgentUser", "SQLServer2005MSFTEUser"). Please ignore them.

SQL Server nodes reconfiguration

  1. Reconfigure the SQL Server 2005 and SQL Server 2014 nodes: swap all network interface configurations and hostnames.
  2. Rename the following registry node to match a new hostname: HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>
  3. Reconfigure following registry keys at both nodes to fit new IP addresses and hostnames: HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>\communication.ip HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>\host_id HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>\orb.endpoint.addresses HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>\windows.computer.name HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>\windows.computer.name.netbios HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>\windows.computer.name.short
  4. Restart the SQL Server nodes.

Odin Automation DB modifications

  1. Copy the migration.py file to Odin Automation Management Node.
  2. Stop Operations Automation services

     service pemui stop
     service pem stop
     service pau stop
    
  3. Run the migration process:

     python migrate.py --migrate-from-host=<SQL Server 2005 old hostname> --migrate-to-host=<SQL Server 2014 old hostname>
    
  4. Start Operations Automation services

     service pau start
     service pem start
     service pemui start
    

After migration

  1. Reconfigure resource types based on the Shared hosting MS SQL databases class: add the mssql2014 provisioning attribute, and remove the mssql2005 one.
  2. In PCP, go to Infrastructure > Service Nodes > the node deployed for 2014 server, and on the Packages tab, remove the MsSQL2005 package of the service type.
  3. Now one may switch off the node with the SQL Server 2005 installed.

5356b422f65bdad1c3e9edca5d74a1ae caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07

Email subscription for changes to this article
Save as PDF