Question Re: Moving Repository Databases from SQL Server 2005 to 2008? in Technology Q&A

Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Wed, 06 Mar 2013 15:00:02 GMT

Does anyone have a reference to documentation that explains the process/best practice for moving Data Services 3.2 (12.2.2.3) Repository Databases from one SQL Server to another? We are moving all of our SQL Server 2005 databases to a new SQL Server 2008 database. I was hoping to find a step by step process for changing connections within my Data Services environment.

Central Repository
Local Repository on Job Server
Local Repositories for individual users

Thanks

Tammy Datri

Applications Manager

Grane Healthcare

Re: Moving Repository Databases from SQL Server 2005 to 2008?

DayaJha — Wed, 06 Mar 2013 15:57:28 GMT

Hi Tammy,

For your Query regarding changing of Database from SQL Server 2005 to SQL Server 2008 following steps need to be taken care:

As you Mentioned you have Local Repository: Currently i assume you have "N"number of Local Repository Created in SAP Business Objects Data Services 3.2 and all database is in SQL Server

Solution For Local Repository:

First take the Backup of All Local Repository (Data Base backup or ATL Backup)
Upgrade all Local repository Using SQL Server 2008 as a database
Then Change the Source/Target Connection in DataStore Using Configuration Tab (If you have "N"Number of Datastore that are Pointing to SQL Server 2005 Create a New Configuration SQL Server 2008 in Each Datastore and save it)
If you Have XML/Excel/Flat-files as a Source then Map it Once Again
Validate the Jobs
Then go to Job Server and change the Database of each Repository. (Previously it was Pointed to SQL Server 2005 now we have Re-Point to SQL Server 2008 User Credential)
Restart the Data Services Job Server and Login and check the Job Server is Configured or not in Local Repository.
Then go to Data Services Management Console and Change all the Repository Details in Repository Tab (Previously it was Pointing to SQL SERVER 2005 now Re-Point to SQL Server 2008)
And Then Finally Execute the Job From Data Services management Console or Directly From Local Repository.

As you Mentioned you have Central Repository: Currently i assume you have "N"number of Central Repository Created in SAP Business Objects Data Services 3.2 and all database is in SQL Server

Solution For Central Repository:

First take the Backup of All Central Repository (Data Base backup or ATL Backup)
Upgrade all Central repository Using SQL Server 2008 as a database
Then go to Data Services Management Console and Change all the Repository Details in Repository Tab (Previously it was Pointing to SQL SERVER 2005 now Re-Point to SQL Server 2008)
Now Login all Local repository and add Central repository again.

Hope this will help you as well.

Thanks,

Daya

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Wed, 06 Mar 2013 16:36:05 GMT

Thanks for the quick reply. I will give this a shot. I appreciate your guidance.

I'm currently moving all of my datastores now. The next step is to move the repositories. You are correct, we have several. I am fairly new to Data Services so we don't have alot of jobs running yet. I am the only developer right now but others sat in on the training and I want to make sure they adjust their connections as well.

I will move the databases to the new server and see what happens!

Tammy

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Wed, 06 Mar 2013 20:31:44 GMT

I have moved the Local Repositories and so foar so good! I will move the rest of them on friday. Thanks for your help!

Tammy

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Mon, 11 Mar 2013 14:21:46 GMT

I was able to move everything but the Central Repository. When I try to update the repository connection in the Data Services Management Console, I have problems. I am able to test the connection and get message that the connection is good. When I click the apply button, I get the message: You cannot add a non secure central repository to the administrator BODI-3016783.

I was able to upgrade the central repository to a secure repository but it does not show up in the Data Services Management Console now. I am also not sure how to modify the central repository for each client when it is a secure repository.

What is the difference between secure and non-secure.

I am restoring the database backup again and will try it again as a non-secure repository.

Tammy

Re: Moving Repository Databases from SQL Server 2005 to 2008?

DayaJha — Mon, 11 Mar 2013 14:59:09 GMT

Hi Tammy,

Issue is you cannot see the Un-Secure Central Repository in Data Services Management Console. Following Steps need to be taken care for Implementing Secure Central Repository. Please follow the steps below:

Step I: Create a Database Central_Repo in SQL Server/Oracle/DB2/MY SQL

Step II: Go to Data Services Management Console and select the following details as per the image.

Note: You have to Tick the Option Enable Security.

Step III: Then go to SAP Business Objects Business Intelligence 4.0/SAP Business Objects Information Platform Services 4.1 Data Services Application to add Central Repository.

Step IV: Now you can see the status in Data Services that you have added Secure Central Repository

Step V: Now you can see in Data Services Management Console Central Repository is Available.

Hope this will hep you as well.

Thanks,

Daya

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Mon, 11 Mar 2013 15:04:02 GMT

Thanks. I was able to connect to the Central Repository in my designer. But now I can't connect to the Profiler Repository. Guessing it is still pointing to the old database server.

I am adding the respositories to the Job Server Config in the Data Services Manager to see if that helps. Seems the repositories that are in there now all point to the old database server.

Tammy

Re: Moving Repository Databases from SQL Server 2005 to 2008?

DayaJha — Mon, 11 Mar 2013 15:16:07 GMT

Can you please post the error regarding Profile Repository.

Thanks,

Daya

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Mon, 11 Mar 2013 15:30:13 GMT

When I submit a COlumn Profile Request, it times out and gives me the status message "Cancelled. C heck log/errorlog.txt under Data SErvices Installation directory on job server for details. This is what I see in the errorlog.txt file.

(12.2) 03-11-13 11:20:30 (E) (4024:3624) CON-120302: Initialization

ODBC call <SQLDriverConnect> for data source <CORP-APPSQL-01\APPDB> failed: <[Microsoft][ODBC SQL Server Driver][SQL

Server]Cannot open database "BUSOBJ_DS_PROFILER_REPOSITORY" requested by the login. The login failed.>. Notify Customer Support.

Re: Moving Repository Databases from SQL Server 2005 to 2008?

DayaJha — Mon, 11 Mar 2013 15:32:52 GMT

Hi Tammy,

Follow the Steps below for Configuration of Profile Repository.

Step I: Create a Database Profile_Repo in SQL Server/Oracle/DB2/MY SQL

Step II: Go to Data Services Management Console and select the following details as per the image.

Step III: Then go to SAP Business Objects Business Intelligence 4.0/SAP Business Objects Information Platform Services 4.1 Data Services Application to add Profile Repository.

Step IV: Now you can see the status in Data Services that you have added Profile Repository

Step V: Then login in Data Services Management Console and create a Profile user.

User Name:admin

Password:admin

Step VI: Now login in Data Services Designer 4.0 and activate the Profile Repository

Go to Tool->Profile Server Login and pass the following Details.

In Server Connection:

Host: Machine Name

Port: 8080

In User Identification:

User Name:admin

Password:admin

Hope this will hep you as well.

Thanks,

Daya

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Mon, 11 Mar 2013 15:45:22 GMT

If I open Data Services Repository Manager, Choose Profiler, Choose my new Server/DB and enter credentials, I am able to get version.

I am able to connect to the Profiler Repository in DS Designer by logging in to Designer, I see the connection to the profiler server is good in the lower right corner. I click on Tools, Profiler Server Login and click on the test button and it tells me the profiler server is running. I click on the connect button and the Profiler Server Connection is successful.

I just tried to run a profile again and got the same message.

I am wondering if I have to delete the old repository connections from the Job Server COnfig? I added the repositories again with the new database server name. But, It won't let me add the Central Repository.

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Mon, 11 Mar 2013 16:02:50 GMT

I brought the old Profiler Database back online and tried to run another Profile Request. Now I get this message.

(12.2) 03-11-13 11:50:42 (E) (2804:3296) REP-100118: Initialization

Repository <PF_TEXT> is missing the language text associated with object id <PF Task Object: 3>. Notify Customer Support.

Re: Moving Repository Databases from SQL Server 2005 to 2008?

DayaJha — Mon, 11 Mar 2013 16:11:41 GMT

Hi Tammy,

Please check the following Points:

Check the Job Server Connection in Job Server Manager For Profile Repository (Better to Create a Seperate Job Server in Job Server Manager to check the Status)
Re-Check the Connection Again in SAP BO/IPS Data Services Tab->Profile Repository (Also Check the Rights & Security in SAP BO/IPS)

Then login Data Services Designer and reconnect the Profile Repository again.

Then Select the Data Store and apply Column Profile Request

Hope this will help you as well. After completion of your query please close the thread.

Thanks,

Daya

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Mon, 11 Mar 2013 16:31:50 GMT

I'm actually running BO EDGE 3.1 so my screens are a little different than yours. I can't get this working. I will submit a support ticket.

Thanks for all your help!

Tammy

Re: Moving Repository Databases from SQL Server 2005 to 2008?

DayaJha — Mon, 11 Mar 2013 18:14:37 GMT

Hi Tammy,

Please see the below steps for Configuration of Profile Server in SAP Business Objects Data Integrator, So the Images are same in SAP Business Objects Data Services Edge Application 3.1.

Step 1: Setup a Data Profile Repository for that Create a new Data Profile Repository

Create the New Data Base for Data_Profile

Step 2: Go to Start->Program->Business Objects 11.5-> Repository Manager

Step 3: After Execute it will give you

Step 4: Then go to Server Manager and setup for Data_Profile for Job Server

Step 5: Then go to Administrator and add that Data_Profile to Repository

Then Test & Apply

Step 6: Then it will show in your Repository add Profile Job Server

Step 7: Then Create User for Login as Profiler in Data Integrator Job Execution Add Data Profile to a User

Then go to User

And Put the Following Details User Name: Daya, Password: ****, Confirm Password: ****, Display Name: Daya, Role: Profile User, Status: Active, Profile_Repository: Data_Profile

Step 8: Then Logout and Login with UserID: Daya Password: ****

Step 9: Here Data Profile repositories is Created

Step 10: Then Go to Development Server and Start the Profile

Step 11: Then Go to Tools Profile Server Login

Step 12: Put the Details Host: BI4SERVER/LocalHost, Port No: Default, UserName: Daya, Password: ****

Step 13: Then Profile Server Is Start

Hope this will help you as well.

Thanks,

Daya

Re: Moving Repository Databases from SQL Server 2005 to 2008?

tammy_datri — Tue, 12 Mar 2013 12:54:34 GMT

Just wanted to follow up to let you know that I talked with someone at SAP support yesterday and we had to update the AL_Machine_Info table in the profiler repository database.to point to the new database server. For some reason The Data Services Server Manager does not always update this table. I see that some of the databases for the local repositories have an entry for the new server and some don't. Might be a bug in the version I am running.

Once I ran this script, I was able to submit the column profile request successfully.

UPDATE [BUSOBJ_DS_PROFILER_REPOSITORY].[dbo].[AL_MACHINE_INFO]

SET [RESERVED2] = '<new servername>_BUSOBJ_DS_PROFILER_REPOSITORY_'

Thanks for all of your help and support on this problem.

Tammy