Documentation forOrion Platform

Migrate the SolarWinds Orion SQL database to Amazon RDS for SQL Server

This topic applies to all Orion Platform products.

Last Updated: April 2, 2020 | Migration Guide

Supports: Orion Platform products using SQL database that support Amazon RDS for SQL Server

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

How do I migrate?

  1. Review the database migration requirements.
  2. Check whether your database uses a memory-optimized group. If so, disable it.
  3. Back up your database.
  4. Import the backed-up Orion SQL database to Amazon RDS for SQL Server via Amazon's S3.

Step 1: Review database migration requirements

Requirement Version / tier
MS SQL Server version to migrate from Any SQL Server version supported by Amazon RDS for SQL Server. See Microsoft SQL Server on Amazon RDS (© 2020, Amazon Web Services, Inc. or its affiliates, obtained from https://docs.aws.amazon.com/AmazonRDS/ on April 6, 2020)
MS SQL Server version to migrate to The same version as your current Orion database version or a later one.
Orion Platform products SolarWinds products with Orion Platform 2018.2 or later
Migration tool Microsoft SQL Management Studio 17.9.1 and later

Step 2: Check whether the current database uses a memory optimized group

If your current database uses a memory-optimized file group, you need to disable it to be able to migrate to an Amazon RDS for SQL Server.

  1. Run the following query:

    Copy

    Does the database use memory-optimized groups

    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.

Step 3: Create a backup to import it to Amazon RDS for SQL Server

  1. 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%'

        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
  2. Run the following script on your Orion DB. The script checks whether there is a filestream and whether it is empty.

    Copy

    Check for filestreams and whether they are empty

    SELECT TOP 1 * FROM (

    SELECT CASE

    WHEN size > 0 THEN 'A - Non-empty filestream'

    WHEN size = 0 THEN 'B - Empty filestream'

    ELSE 'Unexpected result'

    END AS Step

    FROM sys.database_files WHERE type_desc = 'FILESTREAM'

    UNION SELECT 'C - No filestream'

    ) AS A
  3. Clean up the filestream. Complete these steps if the script result is "Non-empty filestream", otherwise skip this step.

    1. In the MS SQL Management Studio, right-click the Orion database and select Tasks > Export Data-tier Application.
    2. In the dialog, select a location where you have at least the same free space as your DB occupies.
    3. Export the database.
    4. In the MS SQL Management Studio, right-click the Orion database and select Import Data-tier Application.
    5. Select the exported database file.
    6. Into the New database name field, type a different name than the original Orion name. For example, you can add a suffix "FromBackup".
  4. Remove the filestream.

    Complete these steps if the script result says "Empty filestream" or "Non-empty filestream".

    1. In the MS SQL Management Studio, right-click the SolarWinds Orion database, and select Properties > Filegroups.

      If your result was Non-Empty filestream, right-click the new database you created in the previous step (2f)

    2. Remove any FILESTREAM filegroup or MEMORY OPTIMIZED DATA.

  5. Create the backup and note the backup file name.

    This is the only step you need to do if the script result is No filestream.

    To do so, open MS SQL Management Studio, click the SolarWinds Orion database, and select Tasks > Backup.

Step 4: Import the backup into the cloud

  1. Import the database to Amazon RDS via Amazon's S3.

    Follow the instructions in the Amazon's Guide "Importing and Exporting SQL Server Databases" (© 2020, Amazon Web Services, Inc. or its affiliates, obtained from https://docs.aws.amazon.com/AmazonRDS/ on April 2, 2020) to import Orion DB to Amazon RDS for SQL Server via Amazon's S3.

  2. Log in to the server hosting the main polling engine and run the Configuration wizard. This connects your Orion Platform deployment to the newly imported Orion database in Amazon RDS for SQL Server.

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.