Migrate the SolarWinds Orion SQL database from Microsoft SQL Server on premise to Microsoft Azure SQL Database and the other way round

This topic applies to all Orion Platform products.

Last Updated: June 2019 | Migration Guide

Supported: SolarWinds products with Orion Platform 2019.2 and later that use a SQL database.

This guide details how to migrate your SolarWinds Orion SQL database to Microsoft Azure SQL Database and the other way round.

Migrating allows you to keep all relevant data and history without having to start with a fresh database on a new server.

You may need to migrate if...

  • The database shares a server with a poller or other products
  • The database is encountering performance and disk space issues
  • The supported version and requirements for the database have changed

SolarWinds recommends always using a dedicated server for the SolarWinds Orion SQL database. You should not migrate the SQL database to servers already in use.

SolarWinds recommends using SQL Server Authentication with the sa login and password to ensure that Orion can access the SolarWinds Orion database, even if it is hosted remotely on a separate server.

Requirements

Requirement Version / tier
MS SQL Server version to migrate from MS SQL Server 2014 and later
MS SQL Server version to migrate to MS SQL Server 2016 SP1 and later
Azure SQL tiers

DTU Tier S3 or later

vCore Tiers

Orion Platform products
  • DPAIM 11.2
  • EOC 2.2
  • IPAM 4.9
  • Log Analyzer 2.1
  • NCM 8.0
  • NPM 12.5
  • NTA 4.6
  • PAM 2.1.7
  • SAM 6.9
  • SCM 1.2
  • SRM 6.9
  • UDT 3.4.0
  • VMAN 8.5
  • VNQM 4.6
  • WPM 3.0

Not sure what version your products are? Log in to the Orion Web Console and check the footer.

Migration tool Microsoft SQL Management Studio 17.9.1 and later

Before you begin

  • Make sure you upgraded all deployed SolarWinds products to a version that supports Azure SQL. If there is but one that does not support Azure SQL, you cannot migrate the database to the Azure SQL cloud.
  • If you are migrating from SQL 2016.1 SP1 or later, complete the following steps:
    1. Check whether the Azure database tier you're migrating to supports memory-optimized tables. Use the following links:
      • vCore based model (© 2019 Microsoft, obtained from https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vcore-resource-limits-single-databases on April 21, 2019)
      • DTU-based model (© 2019 Microsoft, obtained from https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vcore-resource-limits-single-databases on April 21, 2019)
    2. If your tier does not support memory-optimized OLTP storage*, find out whether you are using them. Run the following query:

      SELECT * FROM dbm_DatabaseProperties WHERE [Key] = 'TimeSeries.MemoryOptimizedTables'
    3. If TimeSeries.MemoryOptimizedTables = true, open C:\Program Files (x86)\SolarWinds\Orion\ConfigurationWizard.exe.config, set <add key="TimeSeries_MemoryOptimizedTables_Enabled" value="false"/>, and run the Configuration wizard.

  • If you are migrating from Microsoft SQL 2016 SP1 or earlier you won't be able to migrate back. This is because after the migration to Azure SQL, the database will use Column Store that is not supported in the earlier SQL Server versions.
  • Make sure the server hosting your Orion server is using Central European Time (UTC). Azure SQL uses UTC and if your settings are different, you might experience issues with polled data.
  • Run the following query on the Orion database to ensure that guest account settings do not block the migration:
    REVOKE CONNECT FROM guest 

Migrate your SQL database to the new server

Recommendations what to do before running the migration

  • Discuss database migrations with a DBA.
  • Schedule a maintenance window to migrate, during non-peak hours (recommended), and notify your company when taking services offline to migrate.
  • Be aware that during the database migration, your services are down and not collecting polling data.
  • Do not uninstall the Orion SQL database instance on the original Orion SolarWinds server because this causes issues with the Orion server and settings.

Migrate the Orion database from SQL Server to Azure SQL

1. Stop Orion services Open the Orion Service Manager, and stop all services except the SQL service on all Orion servers. The SQL Service (which is located in Services, not in the Orion Service Manager) needs to be running to make the necessary changes to the database.
2. Back up the SQL database

Back up your SolarWinds Orion SQL database. If you need help, please check your vendor's site for documentation and instructions.

If you have your database on a VM, create a snapshot or copy of your VM.

3. Migrate the database to Azure SQL DB

Run the Deploy Database to Microsoft Azure SQL Database task.

You can also use the Export Data-tier Application to save the database file to a drive or to the Azure storage and then go to SQL Studio on the Azure SQL server and Import Data-tier Application.

4. Run the Configuration Wizard on all servers

Perform the following instructions on all servers in your Orion Platform environment to update the location for the Orion SQL database:

  • Main Orion server (main polling engine)
  • Every Additional Polling Engine
  • Additional web server

If you are using a custom port for your SQL server, see Using Orion with a MS SQL server listening on a custom port.

  1. Run the Configuration wizard to update the database location.
  2. Select Database, click Next. Specify your new database server in the SQL Server field.
  3. To use SQL authentication, select Use SQL Server Authentication, provide the SA account credentials, and click Next.
  4. Select Use an Existing Database and select or type the existing database name, and click Next. If you are prompted to use the existing database, click Yes.
  5. Select Create a New Account, and provide a New Account name. Creating a new account ensures that Orion has required access to your migrated database. The New Account must be a member of the securityadmin server role. The sysadmin role and the sa user account are always members of securityadmin.
  6. Provide and confirm an account Password, and click Next.

    Make sure your password meets the Azure password requirements so that the wizard doesn't fail because of an invalid password.

  7. Click Finish to complete the update.
5. Start services Start your services on the main and additional polling servers.

Results:

The SolarWinds Orion SQL database is migrated to Azure SQLdatabase.

Migrate the Orion database from Azure SQL to SQL Server

Migrating the Log Analyzer database or the NTA Flow Storage database from the Azure SQL database to an on-premise database is not supported, and the LA- or NTA-specific data is lost.

If you migrate the Orion database from Azure SQL DB to an on-premise SQL Server, all settings, configurations, and CBQoS data specific for Log Analyzer or NTA are migrated, because they are stored in the Orion database.

1. Stop Orion services Open the Orion Service Manager, and stop all services except the SQL service on all Orion servers. The SQL Service (which is located in Services, not in the Orion Service Manager) needs to be running to make the necessary changes to the database.
2. Back up the SQL database

Back up your SolarWinds Orion SQL database. If you need help, please check your vendor's site for documentation and instructions.

If you have your database on a VM, create a snapshot or copy of your VM.

3. Migrate the database to SQL Server on premise
  1. Use the Export Data-tier Application to save the database file to a drive or to the Azure storage.
  2. In the SQL Server Management Studio, Import Data-tier Application.

4. Run the Configuration Wizard on all servers

Perform the following instructions on all servers in your Orion Platform environment to update the location for the Orion SQL database:

  • Main Orion server (main polling engine)
  • Every Additional Polling Engine
  • Additional web server

If you are using a custom port for your SQL server, see Using Orion with a MS SQL server listening on a custom port.

  1. Run the Configuration wizard to update the database location.
  2. Select Database, click Next. Specify your new database server in the SQL Server field.
  3. To use SQL authentication, select Use SQL Server Authentication, provide the SA account credentials, and click Next.
  4. Select Use an Existing Database and select or type the existing database name, and click Next. If you are prompted to use the existing database, click Yes.
  5. Select Create a New Account, and provide a New Account name. Creating a new account ensures that Orion has required access to your migrated database. The New Account must be a member of the securityadmin server role. The sysadmin role and the sa user account are always members of securityadmin.
  6. Provide and confirm an account Password, and click Next.

    Make sure you password meets the Azure password requirements so that the wizard doesn't fail because of an invalid password.

  7. Click Finish to complete the update.
5. Start services Start your services on the main and additional polling servers.

Results:

The Azure SQL database is migrated to SQL Server.

After the migration

When migrating your database from Azure SQL to Microsoft SQL Server, SolarWinds suggests that you change the options back to defaults for SQL Server:

  1. Run this query to turn the "Is Read committed snapshot" to false:
    ALTER DATABASE dbname  SET READ_COMMITTED_SNAPSHOT OFF
  2. Run this query to turn the recovery mode to simple:
    ALTER DATABASE dbname SET RECOVERY simple

Learn more...

*Memory-optimized tables, also known as In-Memory OLTP: a feature available in MS SQL Server 2016 and 2019 that improves the performance of transaction processing, data ingestion, data load, and transient data scenarios. Memory-optimized tables are only used for nodes and volumes.