Error in Management Server log saying as follows Violation of PRIMARY KEY constraint 'PK_Primary (4263340)

After fresh installation of vFog server with external SQL Server database the Foglight server fails to start giving an error in Management server log saying “Violation of PRIMARY KEY constraint 'PK_PrimaryKeyName'. Cannot insert duplicate key in object 'dbo.TableName'.. “

Reviewing the Server logs we found that there is some problem with Collate CaseSensitive settings and as the user only had DBO rights we could not drop the database and create it from fresh because it needs SA rights for it.

We performed the following steps to resolve the problem also attached is the Management Server log file.

Due to the customer only having DBO permissions and not SA permissions, we were not able to delete the db and start fresh so we performed the below steps to fix the problem.

We dropped all the tables from the db using the following script:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

This deleted all the tables that did not have any foreign key constraints.

Then we removed the foreign key contraints from the tables that were left and deleted the table.

Next we ensured that the database collation type was set correctly.

After that we executed the create_schema and populate_db SQL scripts.

We then started the FMS from the cmd line to make sure it started successfully, which it did.