Migrate the SolarWinds Platform database to Amazon RDS for SQL Server
This topic applies to all SolarWinds Platform (self-hosted) products.
Last Updated: April 2, 2020 | Migration Guide
Supports: SolarWinds 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 SolarWinds Platform 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 SolarWinds Platform database version or a later one. |
SolarWinds 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:
SELECT * FROM dbm_DatabaseProperties WHERE [Key] = 'TimeSeries.MemoryOptimizedTables'
-
If
TimeSeries.MemoryOptimizedTables = true
, disable memory-optimized tables:- Open C:\Program Files (x86)\SolarWinds\Orion\ConfigurationWizard.exe.config.
- Set
<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 SolarWinds Platform database to 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
-
Run the following script on your SolarWinds Platform database. The script checks whether there is a filestream and whether it is 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
-
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 SolarWinds Platform 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 SolarWinds Platform 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 Platform 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 Platform 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 SolarWinds Platform database 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 SolarWinds Platform deployment to the newly imported SolarWinds Platform 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.