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
- Deploy MS SQL Server 2014 hosting node according to the Windows Shared Hosting Deployment guide, Installing MS SQL Server section.
- 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
- Open SQL Server Management Studio and connect to the instance of SQL Server 2005.
- Detach all databases by executing the detatch.sql script.
- 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.
- Open SQL Server Management Studio and connect to the instance of SQL Server 2014.
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
- Open SQL Server Management Studio and connect to the instance of SQL Server 2005.
Execute the sq_help_revlogin.sql script to create a store procedure and then execute it:
EXEC sp_help_revlogin
- Copy the generated script.
- Open SQL Server Management Studio and connect to instance of SQL Server 2014.
- 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 : swap all network interface configurations and hostnames.
Reconfigure the SQL Server 2005 and SQL Server 2014 nodes:
Rename the following registry node to match a new hostname:
HKEY_LOCAL_MACHINE\SOFTWARE[\Wow6432Node]\SWsoft\PEM\<HOSTNAME>
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
- Restart the SQL Server nodes.
Odin Automation DB modifications
- Copy the
migration.py
file to Odin Automation Management Node. Stop Operations Automation services
service pemui stop service pem stop service pau stop
Run the migration process:
python migrate.py --migrate-from-host=<SQL Server 2005 old hostname> --migrate-to-host=<SQL Server 2014 new hostname>
NOTE: in case of any failure of
migrate.py
script, contact Odin Technical Support to analyze the situation, some actions may required to be rolled back manually.Start Operations Automation services
service pau start service pem start service pemui start
After migration
- Reconfigure resource types based on the Shared hosting MS SQL databases class: add the mssql2014 provisioning attribute, and remove the mssql2005 one.
- 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.
- Now one may switch off the node with the SQL Server 2005 installed.