Beware of upgrading to the Orion 2018.4 platform (especially if you use Rubrik for backing up your Orion DB)

Case # 00226330

This past weekend my team and I upgraded to NPM 12.4 and Orion platform 2018.4. What we didn't realize (and is hardly mentioned in any release notes/documentation), is that this upgrade added In-Memory functionality to the Orion DB (https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization?view=sql-server-2017 ).

We use Rubrik as a backup solution enterprise-wide. Rubrik just so happens to not support this feature...so you can see where this is going.

Support advised me that this feature can absolutely not be removed and that this issue falls on us to find another backup solution. Luckily for us, we found that this In-Mem optimization file group is not even being utilized and is not touching any tables in our Orion DB. Our DBAs were able to remove that file group and then we were able to backup our Orion DB with Rubrik...even though we were told the Orion DB is now leveraging this functionality and there is no way to turn it off. And this is where we got lucky again; there is apparently a known bug in 2018.4 where the Memory Optimization feature is not being implemented. This bug is supposed to be fixed in Orion 2018.4 Hotfix 2, which has not been released yet.

So, if you use Rubrik as a backup solution for your Orion DB, you probably don't want to upgrade to 2018.4 until Rubrik supports this functionality or until SolarWinds allows you to opt out of this feature.

Find more posts tagged with

rubrik

Database

npm

2018.4

Accepted answers

All comments

aLTeReGo

Customers can continue using older backup solutions which do not support memory optimized tables, but this performance feature must be disabled in Orion by following the steps outlined below.

1. Go to: \Program Files (x86)\SolarWinds\Orion

2. Open: ConfigurationWizard.exe.cfg

3. Change the MemoryOptimizedTables value from TRUE to FALSE:

4. Save the file

5. Run query below against Orion database:

UPDATE dbm_TimeSerieProcessingConfig SET IsInMemoryTable = 0 WHERE TableType = 'cur'

6. Re-run the Orion Configuration Wizard.

cmblackburn

Just to add to this discussion, I attempted an upgrade yesterday and wasn't aware of the new in memory tables either, come to find out our DBA found that this was a feature in our DB that wasn't allowed, long story short I had to restore from back and enable in memory on the DB side and then the upgrade went successfully. It seems to me there needs to be some logic put in the configuration wizard to determine if in memory tables are allow and then adjust the config wizard configuration file accordingly.

mfal42

I work for Rubrik as a SQL Server specialist. Rubrik supports In Memory OLTP databases as of 4.2 (generally available as of September of last year). I recommend either engaging Rubrik support or your Rubrik account team to look into upgrade options.

aLTeReGo

cmblackburn, did your DBA somehow disable the ability to create memory optimized tables on SQL? If so, can you share how this was done so we can better detect this scenario? Currently, we're simply checking the version/edition of SQL and utilizing features available in those versions. If there is a way in which those features can be forcibly disabled somehow, we'd certainly like to know how so we can account for that scenario better in future releases.

rschroeder

Very cool techno work & discoveries!

cmblackburn

aLTeReGo DBA stated we had a server level trigger setup to not allow in memory tables.

lynchnigel

ahhhh sounds very similar to what I had with the DBAs, they ended up moving some of the newer tables to another area I think he also mentioned DLLs as well ? (I dont know the full details) So now they are getting the release notes before I do anything and only when I get the all clear (or im in work that day ha) that I will be upgrading again.

foonly

I don't have a backup issue, but apparently the memory optimized tables cause problems for the config wizard when migrating to a W2K16 server.

The NTA wizard throws: "Selected database [SolarWindsFlowStorage] already contains memory optimized filegroup. Each database can contain just one. Please choose another database."

The exact scenario was a comedy of errors - on my part, as well as design factors of the wizard (defaulting to new install, even when specifying an existing database).

I've been through a couple of upgrades to from NPM 12.3 to NPM 12.4, NTA 4.4 to NTA 4.5 now since December. Each has been different, probably due to hotfixes, core, and wizard changes. The 1st upgrade ended up with with the NPM database having an IN_MEM memory optimized file added in addition to the FGNTAFLOWSTORAGE added for NTA. That server has the poorest performance. I want to remove the IN_MEM, but neither me, or our DBA has a clue of the ramifications.

I have opened an urgent, system down support ticket for the above error.

foonly

I ended up uninstalling all SolarWinds modules, rebooted, and installed again.

I suspect the 1st failed install, where I accidentally took the default for the database name instead of specifying our existing DB for NPM and only installed NPM, is what got something unglued. After that install, I only uninstalled NPM. and left the rest. Then I tried to install everything, whereupon I got the above error message.

The new server is now the fastest of our bunch. Today.

Prineel

Came across a similar situation after moving to SQL AG

Running Config Wizard would throw the following error.

2019-07-16 17:14:47,281 [19] ERROR ColumnStoreDriver - Error when checking memory optimized tables possibility. Memory optimized tables are disabled. The user does not have permission to perform this action.
2019-07-16 17:14:48,438 [12] ERROR ConfigurationProgressScene - Exception while configuring plugin Orion Core Services component Orion Database. System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: dbm_DowngradeAllCurTables: dbm_DowngradeCurTable: Violation of PRIMARY KEY constraint 'PK_CPULoad_CS_dupl'. Cannot insert duplicate key in object 'dbo.CPULoad_CS_dupl'. The duplicate key value is (2019-07-15 01:00:18.9829195, 690).

Running the following query returned True

SELECT * FROM dbm_DatabaseProperties WHERE [Key] = 'TimeSeries.MemoryOptimizedTables'

Should i modify ConfigurationWizard.exe.config, with the following <add key="TimeSeries_MemoryOptimizedTables_Enabled" value="false"/>, and run the Configuration wizard?

Thanks

aLTeReGo

Some DBAs go so far as to disable memory-optimized tables. Another possibility is that the SQL Server you're migrating to is SQL 2014, which does not support in-memory tables. My recommendation is to work with your DBA to determine why you are unable to create in-memory tables as this is a performance-enhancing feature which is very beneficial.

lynchnigel

Indeed, our DBA had to move part of the DB elsewhere once he found that memory optimised tables was being used. I dont know the whys or wherefors but everytime we upgrade they now look at the notes beforehand.

mandevil

Does anyone know what features the %_cur% tables support? Was thinking this may be a part of real-time polling in PerfStack, but not sure.

Have a situation where a number of tables are growing large that are memory optimized causing less and less memory for normal buffer cache.

Especially seeing ResponseTime_CS_cur getting quite big.

Has anyone else experienced bloating with regards to memory optimized tables?

aLTeReGo

mandevil wrote:
Does anyone know what features the %_cur% tables support? Was thinking this may be a part of real-time polling in PerfStack, but not sure.
Have a situation where a number of tables are growing large that are memory optimized causing less and less memory for normal buffer cache.
Especially seeing ResponseTime_CS_cur getting quite big.
Has anyone else experienced bloating with regards to memory optimized tables?

%_cur% tables are memory optimized tables, used to improve SQL query and overall website performance. These tables should remain relatively consistant day to day and should not be growing. If you're continuing to see them increasingly grow in size, I would recommend ensuring that database maintnenace is running without issue. If everything appears to be correct, but the tables are continuing to grow, please open a case with support so we can analyze your diagnostics to determine the cause.