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?
- Review the database migration requirements.
- Check whether your database uses a memory-optimized group. If so, disable it.
- Back up your database.
- 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.
Run the following query:Copy
Does the database use memory-optimized groups
SELECT * FROM dbm_DatabaseProperties WHERE [Key] = 'TimeSeries.MemoryOptimizedTables'
TimeSeries.MemoryOptimizedTables = true, disable memory-optimized tables:
- Open C:\Program Files (x86)\SolarWinds\Orion\ConfigurationWizard.exe.config.
<add key="TimeSeries_MemoryOptimizedTables_Enabled" value="false"/>.
- Run the Configuration wizard.
Step 3: Create a backup to import it to Amazon RDS for SQL Server
Run the following script on your Orion DB to prevent redundant spaces in column names:Copy
Prevent redundant spaces in column names
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%'
FROM @getConstraints INTO @table, @constraint, @definition
WHILE @@FETCH_STATUS = 0
IF (CHARINDEX('LoadAverage',@table) > 0)
SET @columnName = 'TimeStampUTC'
SET @columnName = 'DateTime'
IF (CHARINDEX(@columnName + ' ', @definition) > 0)
SET @sqlCommand = 'ALTER TABLE [dbo].['+ @table +'] DROP CONSTRAINT ['+ @constraint +']';
SET @correctConstraint = REPLACE(@definition, @columnName + ' ', @columnName)
SET @sqlCommand = 'ALTER TABLE [dbo].['+ @table +'] WITH CHECK ADD CONSTRAINT ['+ @constraint +'] CHECK '+ @correctConstraint;
FROM @getConstraints INTO @table, @constraint, @definition
SELECT @errorMsg = Error_Message() + ' ' + @sqlCommand, @errorSeverity = Error_Severity(), @errorState = Error_State()
RAISERROR (@errorMsg, @errorSeverity, @errorState);
IF @@TRANCOUNT > 0
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 (
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
Clean up the filestream. Complete these steps if the script result is "Non-empty filestream", otherwise skip this step.
- In the MS SQL Management Studio, right-click the Orion database and select Tasks > Export Data-tier Application.
- In the dialog, select a location where you have at least the same free space as your DB occupies.
- Export the database.
- In the MS SQL Management Studio, right-click the Orion database and select Import Data-tier Application.
- Select the exported database file.
- Into the New database name field, type a different name than the original Orion name. For example, you can add a suffix "FromBackup".
Remove the filestream.
Complete these steps if the script result says "Empty filestream" or "Non-empty filestream".
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)
Remove any FILESTREAM filegroup or MEMORY OPTIMIZED DATA.
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
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.
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.