ron03
Contributor
Contributor

SRM service fails to start after migrating database to another host

The following entry found in SRM.log file

Failed to create database tables. Details: DB does not contain SRM schema: VdbError: Column name does not exist in table dr_product_info


SRM version: 5.0.0
SQL server version (original SQL server): 9.0.5292

SQL server version (new SQL server):9.0.5292

SRM and SQL servers are on different host. Both SQL servers are running in mixed mode.

Here is how the database was migrated

  1. Stopped SRM service
  2. Detached database from original SQL server
  3. Copied database and log file
  4. Attached database on new SQL server
  5. Create SQL user with the same name and password
  6. Ensure SQL user meet requirements (Screenshots attached)

          Database name: SRM (same name on both old and new SQL server)
          user: srm (same user and password on both old and new SQL server)
          schema: srm (same on both server)

         
          Applied following permissions to srm user: db_owner, bulk insert,connect,create table,create view.

  1. Updated ODBC connection in the SRM server. Only change was the server name. ODBC connection test is successful
  2. Started  SRM service. Service did not start. The above error logged in the log file.


I have already tried these steps to solve the issue

  1. Gave srm user access to master and msdb database
  2. Gave sysadmin permission to srm user
  3. Login in as srm user in SSMS. I was able to create a test table in SRM database with out any errors.
  4. Executed sp_tables to find owner of tables in SRM database. All tables are owned by srm user (See screenshots)


As soon as I updated the ODBC connection to point the old SQL server, SRM service starts with out any error. I used SQL profiler to trace the queries executed while the service was started. I noticed the queries included the srm user for old SQL server but not for new SQL server.


SQL Profiler - Old SQL server

exec [sys].sp_columns_90 N'pd_localsite',N'srm',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pd_localsite',N'srm',NULL
exec [sys].sp_statistics N'pd_localsite',N'srm',NULL,N'%',N'N',N'Q'
exec [sys].sp_columns_90 N'pdr_protectiongroup',N'srm',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pdr_protectiongroup',N'srm',NULL
exec [sys].sp_statistics N'pdr_protectiongroup',N'srm',NULL,N'%',N'N',N'Q'


SQL profiler - new SQL server

exec [sys].sp_columns_90 N'pd_localsite',N'',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pd_localsite',N'',NULL
exec [sys].sp_statistics N'pd_localsite',N'',NULL,N'%',N'N',N'Q'
exec [sys].sp_columns_90 N'pdr_protectiongroup',N'',NULL,NULL,@ODBCVer=3,@fUsePattern=1
exec [sys].sp_pkeys N'pdr_protectiongroup',N'',NULL
exec [sys].sp_statistics N'pdr_protectiongroup',N'',NULL,N'%',N'N',N'Q'


Does anyone know how to fix this issues ?

0 Kudos
4 Replies
mal_michael
Commander
Commander

Hi,

I believe you need to run installcreds.exe utility to update user credentials:

Migrating an SRM server to run on a different host (step 7)

Michael.

0 Kudos
ron03
Contributor
Contributor

Hi Michael

I ran the installcreds.exe utility to update user credentials. But I am still getting the same error message.

Ron

0 Kudos
mal_michael
Commander
Commander

Have you restarted SRM service after running the utility?

0 Kudos
ron03
Contributor
Contributor

I have restarted the server  running the utility. The service still won't start. I am getting the same error message in the log.

0 Kudos