Documentation forOrion Platform

Migrate the SolarWinds Orion database to or from a Microsoft Azure database solution

Last Updated: September 30, 2020| Migration Guide

Supports: Orion Platform products using SQL database that support Azure database solutions (2019.2 and later for Azure SQL, 2019.4 and later for Azure Managed Instance)

This guide details how to migrate your SolarWinds Orion SQL database to a Microsoft Azure database solution (Azure SQL or Azure Managed Instance) 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 or requirements for the database have changed

Recommendations

  • Always use a dedicated server for the SolarWinds Orion SQL database. You should not migrate the SQL database to servers already in use.
  • Use 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.

How do I migrate?

  1. Review the database migration requirements.
  2. Review the before-you-begin recommendations.
  3. Migrate:
  4. Learn more

Task 1: Review the database requirements migration

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 supporting Azure SQL
  • 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.

Azure Managed Instance

Gen5:

  • 4 vCores (small environment)
  • 8 vCores (medium environments)
  • 16 vCores (large environments)
  • 40 vCores (extra large environments
Orion Platform products supporting Azure Managed instance SolarWinds products with Orion Platform 2019.4, such as NPM 2019.4, SAM 2019.4, ...
Migration tool Microsoft SQL Management Studio 17.9.1 and later

Task 2: Review the Before you begin hints

Review the recommendations specific for the migration between the MS SQL Server and an Azure database solution (Azure SQL or Azure Managed Instance) and general migration requirements.

Recommendations specific to migration between MS SQL server and Azure SQL

  • 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 OLTP storage*. 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 it. Run the following query:

      Copy

      Is memory-optimized OLTP storage used?

      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 RTM 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:

    Copy

    Guest account settings do not block migration

     REVOKE CONNECT FROM guest 

Recommendations for the Azure Managed Instance migration

If your current database uses a memory-optimized file group, you need to disable it to be able to migrate to an Azure Managed Instance.

  1. To find out whether it is the case, run the following query:

    Copy

    Does the database use memory-optimized file group?

    SELECT * FROM dbm_DatabaseProperties WHERE [Key] = 'TimeSeries.MemoryOptimizedTables'
  2. If TimeSeries.MemoryOptimizedTables = true, disable memory-optimized tables:
    1. Open C:\Program Files (x86)\SolarWinds\Orion\ConfigurationWizard.exe.config.
    2. Set <add key="TimeSeries_MemoryOptimizedTables_Enabled" value="false"/>.
    3. Run the Configuration wizard.

General migration recommendations

  • 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.

Task 3a: Migrate the Orion database from SQL Server to the Azure database solution (Azure SQL or Managed Instance)

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. Clean redundant spaces in column names

Run the following script on your Orion DB to prevent redundant spaces in column names:

Copy

Prevent redundant spaces in column names

BEGIN TRANSACTION
    DECLARE @definition NVARCHAR(255)
    DECLARE @table NVARCHAR(255)
    DECLARE @constraint NVARCHAR(255)
    DECLARE @getConstraints CURSOR
    DECLARE @sqlCommand NVARCHAR(255)
    DECLARE @correctConstraint NVARCHAR(255)
    DECLARE @errorMsg VARCHAR(MAX)
    DECLARE @errorSeverity INT
    DECLARE @errorState INT
    DECLARE @columnName NVARCHAR(255)

    SET @getConstraints = CURSOR FOR
    SELECT t.name, c.Name, c.definition FROM sys.check_constraints c
    INNER JOIN sys.tables t ON t.object_id = c.parent_object_id
    WHERE c.name LIKE N'CHK_CiscoBuffers_Detail%' or c.name LIKE N'CHK_CiscoBuffers_Hourly%' or c.name LIKE N'CHK_CiscoBuffers_Daily%'
    or c.name LIKE N'CHK_CPULoad_Detail%' or c.name LIKE N'CHK_CPULoad_Hourly%' or c.name LIKE N'chk_CPULoad_Daily%'
    or c.name LIKE N'CHK_LoadAverage_Detail%' or c.name LIKE N'CHK_LoadAverage_Hourly%' or c.name LIKE N'CHK_LoadAverage_Daily%'
    or c.name LIKE N'CHK_ResponseTime_Detail%' or c.name LIKE N'CHK_ResponseTime_Hourly%' or c.name LIKE N'CHK_ResponseTime_Daily%'
    or c.name LIKE N'CHK_VolumeUsage_Detail%' or c.name LIKE N'CHK_VolumeUsage_Hourly%' or c.name LIKE N'CHK_VolumeUsage_Daily%'
     or c.name LIKE N'CHK_InterfaceErrors_Detail%' or c.name LIKE N'CHK_InterfaceErrors_Hourly%' or c.name LIKE N'CHK_InterfaceErrors_Daily%'
     or c.name LIKE N'CHK_InterfaceTraffic_Detail%' or c.name LIKE N'CHK_InterfaceTraffic_Hourly%' or c.name LIKE N'CHK_InterfaceTraffic_Daily%'
    
    BEGIN TRY
        OPEN @getConstraints
        FETCH NEXT
        FROM @getConstraints INTO @table, @constraint, @definition
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (CHARINDEX('LoadAverage',@table) > 0)
        SET @columnName = 'TimeStampUTC'
        ELSE
                SET @columnName = 'DateTime'

            IF (CHARINDEX(@columnName + ' ', @definition) > 0)
            BEGIN
                SET @sqlCommand = 'ALTER TABLE [dbo].['+ @table +'] DROP CONSTRAINT ['+ @constraint +']';
                EXEC (@sqlCommand)

                SET @correctConstraint = REPLACE(@definition, @columnName + ' ', @columnName)
                SET @sqlCommand = 'ALTER TABLE [dbo].['+ @table +'] WITH CHECK ADD CONSTRAINT ['+ @constraint +'] CHECK '+ @correctConstraint;
                EXEC (@sqlCommand)
            END
            FETCH NEXT
            FROM @getConstraints INTO @table, @constraint, @definition
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        SELECT @errorMsg = Error_Message() + ' ' + @sqlCommand, @errorSeverity = Error_Severity(), @errorState = Error_State()
        RAISERROR (@errorMsg, @errorSeverity, @errorState);
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH

    CLOSE @getConstraints
    DEALLOCATE @getConstraints

 

If the following issue occurs when exporting the database to the bacpac format, back up your database and run the following script.

Error SQL71501: Error validating element [dbo].[Cortex_CS_Orion_ApiPoller_ApiPoller_Metrics]: View:
[dbo].[Cortex_CS_Orion_ApiPoller_ApiPoller_Metrics] has an unresolved reference to object 
[dbo].[Cortex_CS_Orion_ApiPoller_ApiPoller_Metrics_Detail_legacy].
Copy

Reorganize Views to fix export issues

 DECLARE @tableName NVARCHAR(255)
 DECLARE @constraint NVARCHAR(255)
 DECLARE @getCortexTables CURSOR
SET @getCortexTables = CURSOR FOR
select distinct TableName from dbm_TimeSeriesConfig where Tablename like 'Cortex_CS_%'


if (select count(1) from dbm_DatabaseProperties where [Key]='TimeSeries.Driver' and Value='LegacyToColumnStore')=1
BEGIN



    BEGIN TRY
        OPEN @getCortexTables
        FETCH NEXT
        FROM @getCortexTables INTO @tablename
        WHILE @@FETCH_STATUS = 0
        BEGIN
         exec dbm_CreateViews @tablename
         print 'Recreating Cortex views'
         FETCH NEXT
        FROM @getCortexTables INTO @tablename
        END
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        print 'Problem with recreating Cortex views'
    END CATCH;

END

 

If you encounter the following issue when exporting to bacpac, create a login for the user or delete the user. See the topic on troubleshooting orphan users in Microsoft help (©2020 Microsoft, obtained from https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver15 on September 29, 2020).

Error SQL71564: Error validating element [Username]: The element [Username] has been orphaned from its login and cannot be deployed.
4. Migrate the database to an Azure database solution

To migrate to Azure SQL:

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.

To migrate to Azure Managed Instance:

  1. Use the Export Data-tier application to save the database file to a drive or to the Azure Storage.
  2. Go to SQL Studio on the Azure Managed Instance server and use the Import Data-tier Application.

5. 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.
6. Start services Start your services on the main and additional polling servers.

Results:

The SolarWinds Orion SQL database is migrated to your Azure database solution (Azure SQL or Azure Managed Instance).

Task 3b: Migrate the Orion database from an Azure database solution to an SQL Server

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

If you migrate the Orion database from an Azure database solution 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 SolarWinds Orion database is migrated from the Azure database solution to the SQL Server on-premise.

After the migration to SQL Server

When migrating your database from an Azure database solution (Azure SQL or Azure Managed Instance) 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:

    Copy

    Set 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:

    Copy

    Set 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.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.