How to migrate database server for IPS and DS repositories?

[MarcusZwirner]

Hi experts!

One of my Data Services customers has to migrate the existing MS SQL Server database to another machine. That MS SQL Server contains the repositories for Information Platform Services and Data Services (version 14.2.2), i.e. the repository databases and the DB connections have to be switched to the MS SQL Server on the new machine (the remaining DS installation on the existing application server keeps unchanged).

We did the following:

1. We created the (empty) databases and the database users on the new SQL Server with the same names as on the old MS SQL Server.
2. The DBA “copied” the content of the repository databases from the old to the new SQL Server (by a backup / recovery procedure).
3. We created a new ODBC data source on the application server for the CMS and Audit database on the new SQL Server.
4. We copied the CMS database from the old to the new SQL Server according to chapter 11.4 in the IPS Administrator Guide (http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_ips_admin_en.pdf) and we checked the successful execution in the CMC under Settings -> points to the new SQL Server.
5. In the CMC we switched the Audit database to the new ODBC connection (under Auditing -> Configuration ADS Database).
6. In the CMC we changed the Data Services repositories to the new SQL Server (under Data Services -> Repositories -> right-click -> Properties) by changing only the database server name (everything else is the same as before).
7. In the DS Server Manager we replaced the existing repository entries by the repositories on the new SQL Server (since it is not possible there to update only the database server name we associated the new repositories additionally and deleted the old entries afterwards).
8. We checked the entries in the AL_MACHINE_INFO table of the DS repository and updated them to the new database server name.
9. Then we shutdowned the old SQL Server machine and restarted the application server (i.e. we restarted IPS, DS and Tomcat).

After all those steps we were able to login successfully to the CMC and everything looks fine there (the CMS database settings, the Audit database connection and the DS repositories database host).

But when we tried to login to the DS Designer we get the repository list but cannot logon to the repository since it still points to the old and no longer available SQL Server (the error message “cannot open database” contains the old database server name).

Questions:

1. What did we miss?
2. Where are the database connections stored? We checked all of the CMS tables in the IPS repository database but couldn’t find any information about databases.

What’s interesting: When we define a new DS repository in the CMC pointing to the same new database as the existing repository entry we are able to logon to that repository and everything works fine. So it seems that for the existing DS repository entry the CMC shows the new database server name but still uses the old one!?

Thanks in advance for your replies and suggestions!

Marcus

[chethan_lingaraju · Accepted solution]

Hello Marcus,

For Q1)

Your plan should have been

  6. Drop all the repositories & add it again - with same details except for host name.

  7. In DS server manager, flush the contents and add again with new host name

1. Delete jobserver.
2. Execute "DELETE FROM [dbo].[AL_MACHINE_INFO] WHERE PORT = <JobServerPort>" on all DBs. Job server port is usually 3500.
3. Add new jobserver and add all the DB details (somehow command-line usage of AWServerConfig.exe is not working for us)

  8. Not required.

For Q2)

I was also looking for same information. Please see the replies on . Question is still open.

Thanks

Chethan

[MarcusZwirner · Accepted solution]

Hi Chethan,

thanks a lot for your reply.

Do you know why it is required to drop all the repositories in the CMC and add it again even though it is possible to overwrite the properties?

Since we have a lot of repositories and users we would like to avoid dropping all the repositories (as we have to re-implement then the user security for each repository again).

Best regards

Marcus

[chethan_lingaraju · Accepted solution]

Hi Marcus, once when we tried similar activity, it did not work. Since it was for few repositories, we dint bother to investigate further. We just flushed all the CMC & Jobserver details and recreated.