Submit a ticketCall us
Home > Success Center > Network Performance Monitor (NPM) > Best practices for managing your Orion database

Best practices for managing your Orion database

Overview

The most common issues with SolarWinds Orion databases are related to the database size.

Two of the most common symptoms of database issues are degraded Orion performance, and errors related to the inability to connect to the database.

See also Quick Orion database health check guide or SQL Server configuration best practices for NPM.

Best practices for database maintenance and use

  • For production environments, host databases on dedicated servers (physical or VM) including the SolarWinds Orion SQL Database and NTA Flow Storage Database.
  • Create a database backup or snapshot before making any changes to your database. Support may require running queries, deleting tables, and editing table data to resolve issues. We also recommend having a DBA help.
  • Create a backup or snapshot of your DB after completing major configurations and updates to your SolarWinds products.
  • For larger databases, perform data integrity checks, set detection for index fragmentation, and set up a re-indexing routine. Do not use the auto-grow or auto-shrink options as it adds to index fragmentation. Always re-index in your maintenance routines to help with index fragmentation.
  • Create a database backup schedule/job for continuous backups of your data off-peak hours. Also, save these backups to a different system than the database server. NTA includes a backup scheduler you can configure anytime using the NetFlow Storage Database Configurator.
  • You can use VMs for your databases, but you should discuss using them with your DBA and IT staff to ensure the VM is configured correctly. If you have a database VM, you can also create snapshots as a backup.
  • When backing up databases, consider all databases: SolarWinds SQL database, EOC dedicated database, NTA Flow Storage Database, and NCM 7.2.X and earlier (NCM merges into the SQL database with version 7.3.X and later). Schedule maintenance and backups apart from each other to not burden the system or interfere with product performance.
  • If you need to migrate to a new server, consider adding those migration steps to your upgrades. Migrating the database during an upgrade may add time, but it is the best time to tackle the migration. New SolarWinds product versions may require larger systems, changing tables in the database, or need a larger system (CPU, hard drive, RAM, etc).
  • See Reboot the Orion server: for instructions and best practices for rebooting the Orion server. Some users include these steps as part of their database maintenance and backup.
  • The SQL database includes numerous structure changes with NPM 11.5.x. For details on all database changes, check this Excel spreadsheet and this KB article.
  • During an upgrade, we do not purge or remove historical data. The installers retain your historical data. While services are stopped during an upgrade, your polling engines do not capture data to the database. When you restart your services, data resumes capture into the database. If you notice data loss or gaps, you may have an issue with a poller.

Upgrades and Databases

When SolarWinds announces and releases a new version or service release for a product, review the system and SQL requirements. You may need to plan and upgrade your SQL server version or hardware to support the new product.

See the SolarWinds Upgrade Guide.

Migrating the SQL Database

See the SolarWinds Migration Guide.

Steps

 

This section covers only the basics of determining a database issue as the issue pertains to interaction with Orion. It is not intended as an SQL troubleshooting guide.

Troubleshoot database size

Consider changing data retention settings and data roll-up periods for your Orion Platform products.

Orion Platform summarizes detailed data into hourly data increments and then into daily data increments. The shorter the data interval, the greater the effect the setting will have on the database size.

  • Extending the detailed data retention will have the largest potential impact on database size and performance.
  • Extending hourly retention will have a lesser effect.
  • Extending daily retention will have the least effect.

When expanding a data retention period, make small changes and examine the impact on size and performance as you approach the new limit.

To access retention settings:

  1. Log in to the Orion Web Console as an Administrator.
  2. Click Settings > All Settings > Database Settings.

Not all of the data roll-up periods are adjustable. Typically, you can alter one intermediate data roll-up period and the limit for data retention. Available settings depend on the Orion Platform products you have installed.

Troubleshoot degraded performance

Review the disk queue length.

In the Orion database, the single most important SQL server performance measurement is disk queue length. Queue length is a measurement of the SQL writes waiting to be written to disk. When disk queues start lengthening and there is a steady load on the SQL writes, the queues may grow so large that write requests get dropped. This may lead to gaps in data and will affect the overall performance of the SQL server.

The disk queue length should not exceed two times the number of effective spindles in the SQL storage RAID configuration MDF.

The effective spindle count is the number of striped spindles. For a RAID 10 direct attached storage unit with eight total disks, the effective spindle count is four and you're going to get a Read & Write performance bump for every pair of spindles in the set. Four of the spindles in the array are the primary striped array and the other four are a secondary striped mirror of the four primary spindles. Since no performance gain is achieved by mirroring disks, only the primary striped set is used to measure performance.

Troubleshoot lost connection to the database

When errors occur that point to a loss of the connection to the database, complete the following steps to isolate the issue:

  1. Ping the SQL server from the Orion server to check network connectivity.
  2. Open the SQL Server Management Studio or the Orion Database Manager and attempt to connect to the database.
  3. If both steps above are successful, run the Configuration wizard against the database by selecting Database in the first wizard screen. Ensure that you are using the proper database credentials.
  4. Open the Orion Web Console to test connectivity again.
  5. Test opening an ODBC connection from the Orion server using a Microsoft utility such as ODBCPing (© 2017 Microsoft, available at https://technet.microsoft.com, obtained on August 28th, 2017.).

 

If all of this fails, the issue is a failure of the SQL server. You need to go directly to the SQL server and begin troubleshooting. Troubleshooting SQL is version and implementation-specific. Please consult the Microsoft Support site and search for information pertaining to your version.

 

Last modified

Tags

Classifications

Public