My master doesn't start after I migrated its SQL Server database to another server

Knowledge Article

Article Number

000334320

Old Article Number

000133086

Article Type

Solutions to a Product Problem

Title

My master doesn't start after I migrated its SQL Server database to another server

Summary

There are several things to update when you migrate your DB to a new server:
- the DB information on ../master/config/Vision64database.ini or the ODBC Connector
- the SID of the DB owner on the new DB server or the new DB owner if it changes from the original
It can be a mix of all of this or not.

Product

BMC Client Management

Component

Client Management

Applies to

Any version of BMC Client Management

Problem

My master doesn't start after I migrated its database to another server.

What should I should I update when I'm migrating my database to a new server?

How should I recreate the db owner on the new sql server I am migrating my DB to?

Cause

You either did not update the file vision64database.ini to reflect the new database server name, or its new owner name in the same ini or in the ODBC Connector. Or you did not update the DB owner properly on the database, weither it changed or not.

Solution

All these possibilities might need to be combined, depending on your situation.

1- Update DB information on ../master/config/Vision64database.ini:
If the hostname of the new server is different from the old one you will need to either update the vision64database.ini or the ODBC connector on the master to reflect the change:

A- DatabaseType=ODBCDirect
If this is set at the beginning of the file then something similar to "Driver={SQL Server};Server=MyServerName;Database=myDatabaseName" must be set later in the file. "MyServerName" would be the "old" server name I suppose that the name of the database did not change when you migrated the DB (change it as well if it does) but you will have to update "MyServerName" with the new hostname.

B- DatabaseType=ODBC
This means that the master connects to the DB through an ODBC Connector set on the master. The name of the ODBC connector is set in the field "DatabaseName=". Edit the connector configuration and set the new hostname to it.

2- Update the SID of the DB owner on the new DB server:
It is not sufficient to migrate your DB then recreate the same user on the new DB server because their SID will differ. As a result the two users will be considered as different users by SQL Server and the master will not connect.

Connect to the DB (you can use SQL Server Management Studio) then:
- recreate the same user as the owner of the db on the new server
- run the following query:

exec sys.sp_change_users_login 'update_one', 'MyUserName', 'MyUserName';

Replace "MyUserName" by the owner of the DB (you can find this information on the DB properties and in the field "User=" of the file ../master/config/Vision64database.ini .
- start the service of our agent on the master
Warning: the schema assigned to the DB owner might be different than the DB owner, pay attention to this, else you'll end-up with a double schema which will result as broken DB.

3- Set a new DB owner:
If you want to set a different DB owner on the new server you will have to:
- create the new user on the new DB server. If the user will be "sa" then there's obviously no need to try to create it...
- extract the file UpdateDBOwner.sql from the attached UpdateDBOwner.zip. Password is"Bcm123".
- edit the script and replace "OldUserName" in this script by the name of the old DB owner on the old DB server and "NewUserName" by the name of the new DB owner on the new server. If the new owner is going to be "sa" then "NewUserName" must be set to "dbo"
- run the script to update the database and its objects with the new DB owner (you can also open it, copy and paste the script in sql server management studio if you prefer).
- update the parameter "User=" in the file ../master/config/Vision64database.ini. Warning: Pay attention to this step, else you'll end-up with a double schema which will result as broken DB.
- update the parameter "PasswordEnc=" to "Password=" and set the new password as a value to this parameter in the file ../master/config/Vision64database.ini, .e.g "PasswordEnc=x3+*7c" becomes "Password=MyNewPassword". It will be encoded at the first boot of the master. Warning: Sometimes "Password=" can still be in the file, in this case do not create a new entry, simply update this existing "Password=".

Attachment(s):

UpdateDBOwner.zip