Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Alter SQL Collation of Solarwinds Database

Hi all,

my collaegues were migrating the Solarwinds database to a new SQL Server with a different collation a long while ago, now we would like to adjust the collation of the database to match the one of the server, we think this might be beneficial to the performance.

I do have a script which served well on other products with SQL databases.

The script is basically doing the following:

- Back up all constraints in the database

- Drop all constraints in the database

- Alters collation

- Recreates constraints

However if I let it run against the Solarwinds database I get the error that it can't recreate the constraints:

Msg 1776, Level 16, State 0, Line 1

There are no primary or candidate keys in the referenced table 'dbo.StatusCalculators' that match the referencing column list in the foreign key 'FK_Containers_StatusCalculators'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

When I check the constraint in the "old" database it is there, and I do not see a reason why it should not be possible to recreate it.

I will open a Case with Support, maybe they can help, but if anyone has an idea, I'll be more than glad to hear those.

Thanks

Holger

Find more posts tagged with

Accepted answers

HerrDoktor

We ended up setting up a new system and copied over the Data by an API Script.

it seems the known way of converting the DB is not supported with the solarwinds DB

All comments

HerrDoktor

We ended up setting up a new system and copied over the Data by an API Script.

it seems the known way of converting the DB is not supported with the solarwinds DB

stek0

Hi Doktor,

I had the same issue and finally changed the collation of our Orion SolarWinds database from Latin1_General_CI_AS

to SQL_Latin1_General_CP1_CI_AS. (After a few unsuccessful attempts ...)

I ended up solving this similar like you, with some additions:

Back up all constraints, indices, stored procedures and views in the database
Drop all constraints, indices, stored procedures and views in the database.
Alters collation (on database and columns via T-SQL)
Be careful at this point: Tables which are named like "NetPath_Performances*" have a "hardcoded collation" in column ObjectId (SQL_Latin1_General_CP1_CS_AS) <- don't change the collation of this column in this tables.
Recreates constraints, indices, stored procedures and views

For the steps 1 . and 4. I used "RedGate SQL Compare". With this tool you can recreate the database objects from a database fullbackup and check if every object has realy beeing recreated.

On my first try I got a tool from the SolarWinds Support called "FixCollation". This tool didn't work for me due to it had problems managing the dependencies of primary and foreign keys. Also it overwrites the hardcoded collation on the NetPath_Performances* tables.

Let me know if you need further information.

Sjef de Kort

blue0o0

Hi HerrDoktor,

I have the same issue. Can you share me the experience about copy over data by API script? Thanks.