Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

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. Properly managing size can help you avoid issues with storage capacity and database performance. A primary factor in database size is the data retention settings available in SolarWinds Orion. Each SolarWinds Orion Product allows you to manage the data rollup periods and the data retention limit. The impact of adjusting any of these data retention and rollup windows will be roughly proportional to the effect the Orion Product has on the database size. When considering expanding a data retention period, you may be able to make small changes and examine the impact on size and performance as you approach the desired new limit.

 

Not all of the data rollup periods are adjustable. Typically, you can alter one intermediate data rollup period and the limit for data retention.

 

The impact of altering data retention can be summarized by this rule: 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.

This is due to the summarization of detailed data into hourly data increments and then into daily data increments. Each SolarWinds Orion Product allows similar data retention options and the above guidelines should be followed for each product.

Recommendations

Solarwinds provides the following recommendations and best practices in general for your database maintenance and usage.

  • For production environments, we recommend hosting 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.
  • We also recommend creating a backup or snapshot of your DB after completing major configurations and updates to your SolarWinds products.
  • Database maintenance: For larger databases, you should perform data integrity checks, set detection for index fragmentation, and set up a re-indexing routine. We recommend not using 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, you should consider all database: 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). You should 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 during an upgrade may add time, but it is the best time to tackle the migration. New <variable name="Core.CompanyName"> product versions may require larger systems, change tables in the database, or need a larger system (CPU, hard drive, RAM, etc).</variable>
  • Rebooting the Orion server: Detailed instructions for rebooting the Orion server and information on setting the reboot as a scheduled job. Some users include these steps as part of their database maintenance and backup.
  • The SQL database includes numerous structure changes with NPM 11.5 and 11.5.2. 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 pollers will 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.

Steps

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

 

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.

 

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 that are 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. A good rule of thumb is that disk queue length should not exceed two times the number of effective spindles in the SQL storage. 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. Four of the spindles in this 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.

 

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

  1. Ping the SQL server from the Orion server to check network connectivity.
  2. Open SQL Server Management Studio or the Orion Database Manager and attempt to connect to the database.
  3. If both of the above are successful, run the Orion 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 UI to test connectivity again.
  5. Test opening an ODBC connection from the Orion server using a Microsoft utility such as ODBCPing.

 

If all of this fails, then the issue is a failure with the SQL server. At this point, you will need to go directly to the SQL server and begin troubleshooting. Troubleshooting SQL is very specific for each version and implementation, and it is recommended that you consult the Microsoft Support site and search for information pertaining to your version.

Upgrades and Databases

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

 

For the latest requirements, check the SolarWinds Upgrade Guide.

Migrating the SQL Database

If you need to migrate your SQL Database, see the SolarWinds Migration Guide. We provide a specific guide for the SQL database.

If you only need to upgrade the SQL server, see the Upgrade the Orion SQL server without migration.

 

Last modified
01:49, 29 May 2017

Tags

Classifications

Public