SQL errors after moving from Azure SQL to local

Implemented

SQL errors after moving from Azure SQL to local

avatar

Everything seemed fine after migration, but now we found out that we cant create new objects or edit the ones we already have
Sometimes RDM throws this error:

How do we fix this ASAP? We have snapshot and backup of RDM, so we can always roll back.

SqlException - Violation of PRIMARY KEY constraint 'PK_Connections'. Cannot insert duplicate key in object 'dbo.Connections'. The duplicate key value is (xxx-xxx-xxx-xxx-xxx).
The statement has been terminated.

at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.Server.DatabaseManager.<>c__DisplayClass19_0.<ExecuteNonQuery>b__0(DbCommand dbCommand)
at Devolutions.Server.DatabaseManager.ExecuteCommand(DbConnection dbConnection, DbTransaction dbTransaction, String sql, IEnumerable`1 parameters, CommandType commandType, Action`1 action)
at Devolutions.Server.DatabaseManager.ExecuteNonQuery(DbConnection dbConnection, DbTransaction dbTransaction, String sql, IEnumerable`1 parameters, CommandType commandType)
at Devolutions.Server.DatabaseManager.ExecuteNonQuery(String sql, DbTransaction dbTransaction, IEnumerable`1 parameters, CommandType commandType)
at Devolutions.Server.ConnectionManager.InsertConnection(ISessionContext context, ConnectionInfoEntity connection, DbTransaction dbTransaction)
at Devolutions.Server.ConnectionManager.ValidateAndWriteToDb(ISessionContext context, ConnectionInfoEntity connection, DbTransaction dbTransaction, Guid repositoryId, Boolean addLogs, Boolean isRoot, Boolean skipValidation)
at Devolutions.Server.ConnectionManager.SaveConnectionsByTransaction(ISessionContext context, ConnectionInfoEntity[] connections, DbTransaction dbTransaction, Dictionary`2 savedGroups, Boolean addLogs, Boolean isRootCreation, Boolean skipValidation, SecurityManager securityManager)
at Devolutions.Server.ConnectionManager.SaveConnection(ISessionContext context, ConnectionInfoEntity connection, Dictionary`2 savedGroups, Boolean addLogs, Boolean giveNewRootAccess, SecurityManager securityManager)

All Comments (7)

avatar

Hello Nicpa,

Thank you for contacting us on that matter!

Have you followed the steps listed in this knowledge base to perform your migration https://kb.devolutions.net/rdm_sql_server_database_migration.html? If you haven't I would recommend restoring your backup and performing the migration once more using the recommendations listed in this link.

Best regards,

James Lafleur

avatar
Hello Nicpa,

Thank you for contacting us on that matter!

Have you followed the steps listed in this knowledge base to perform your migration https://kb.devolutions.net/rdm_sql_server_database_migration.html? If you haven't I would recommend restoring your backup and performing the migration once more using the recommendations listed in this link.

Best regards,


Yes I followed that exact KB. I have used SSMS to export from Azure SQL and import on MS SQL

avatar

Hello,

Thank you for your quick reply!

I will transfer this information to our engineering department and get back to you shortly.

Best regards

James Lafleur

avatar

Hello,

As this problem is related to Devolutions Server, this thread has been moved to the right section.

It seems that this issue is related to an invalid cache.

Could you please try these steps and let me know if that helps?

1- Go in Administration - Reset Server Cache on the DVLS web UI and reset the first 4 items.

forum image

2- In RDM, go in File - Data Sources - Advanced - Manage Cache and delete the local cache file.

forum image

3- Then connect on the DVLS data source in RDM and try to create a new entry.

Best regards,

Érica Poirier

avatar

Hello,

I just want to add one step I forgot in my previous post.

Please stop and start the DVLS instance using the Stop Server/Start Server button on the DVLS Console before step 1.

forum image

Best regards,

Érica Poirier

avatar
Hello,

As this problem is related to Devolutions Server, this thread has been moved to the right section.

It seems that this issue is related to an invalid cache.

Could you please try these steps and let me know if that helps?

1- Go in Administration - Reset Server Cache on the DVLS web UI and reset the first 4 items.

forum image

2- In RDM, go in File - Data Sources - Advanced - Manage Cache and delete the local cache file.

forum image

3- Then connect on the DVLS data source in RDM and try to create a new entry.

Best regards,


I have done all 3 things, didnt work.
I cant even create new object from the web version. Nothing happens when I click "Add"

Can we please schedule a session to get this fixed ASAP?

avatar

Hello,

@everyone we solved the problem by resetting the timestamps of the ConnectionHistory table in the SQL database.

If you ever have such similar issue, please contact us at service@devolutions.net and someone from our support team will assist you.

Best regards,

Érica Poirier