Need help in changing connection to new sql server

dataservices administration
1

Hi,

I am new to data integrator. We are having BO XI R3.1 and BODI pointing to sql server 2000. I want to upgrade to sql server 2008.

Can anyone pls. guide me where should i change the connections to point to sql server 2008.

Thanks,

Diablog (BOB member since 2009-08-04)

2

Are you getting a new server which will run SQL Server 2008 or upgrading an existing server to 2008?

Do you have a test system you can run this through on first?

DanDensley :uk: (BOB member since 2009-05-12)

3

Sql server 2008 is already installed on different machine. Its not loaded so new server itself can used as test machine.

Diablog (BOB member since 2009-08-04)

4

Ok so you have a BO Server, a DI Server and an DB Server. You are changing the DB Server and it will now have SQL Server 2008 rather than SQL Server 2000?

I am currently going through a similar but we are going from SQL Server 2005 to a virtual server with SQL Server 2008. It’s reasonably complex but essentially we are doing the following …

Stage 1 - BO Databases

  1. DBA’s restore just BO System and Audit databases to new server - this requires an SQL Backup of the source SQL Server 2000 databases as the
  2. We store input/output file repositories on the DB server too so copy across Input and Output File Repositories
  3. In CMC switch IFRS and OFRS paths to new server
  4. Shutdown SIA on BO Server
  5. Install SQL Server Native Client for 2008 ODBC on BO Server
  6. Switch ODBC’s for BO System and Audit DBs on BO Server
  7. Restart SIA
  8. Double check IFRS and OFRS paths
  9. Run reposcan. Check errors. Repair
  10. Test!!
  11. Let users back onto the system

Stage 2 - DI and Data warehouse

  1. Turn off current DI activities
  2. DBA’s restore DI DW databases and DI repository databases to new server
  3. Install SQL Server Native Client for 2008 ODBC on DI Server
  4. On DI Server (as main DI User) go to DSSM - Delete/Re-add any repositories
  5. In DI switch/check Data Stores
  6. Re-import all Tables to cope with version change 2000/2008
  7. In DSMC remove old Jobs and add back in.
  8. Check run with standard job
  9. Test!!
  10. Once happy with DI changes Switch Universe connections
    DONE!

One point to watch for. Default datetime column in SQL Server 2008 is datetime2. So new tables you create will start using this. You can switch it back once created and re-import.

NB: This is what I’m planning on doing, your situation may be different and pose different problems :). We’ve had a DEV system on SQL Server 2008 for some time and migration of work from there to QA or Live which are still SQL Server 2005 can be troublesome. Particularly where datetimes are involved. Re-importing tables or forcing saves of dataflows by making and undoing changes has worked around this.

DanDensley :uk: (BOB member since 2009-05-12)

5

for DS repo on SQL Server 2000, you can export the repo to ATL and create a new Repo in SQL Server 2008 and import the ATL in that, since it’s anew repo you will have register this in MC/CMC and job server again
or
you can use any other tool provided by SQL Server 2008 to migrate you DS Repo Database from SQL Server 2000 to SQL Server 2008

for Datastores
you can create a new Configuration in the same Datastore and set the version to 2008 and set that configuration as default for the Datastore

for SQL Server 2008 you will have to install the SQL Server 2008 Client on the Designer and DS Job Server Machine

manoj_d (BOB member since 2009-01-02)

6

There are other gotchas - you may well find that when you re-import tables you lose the ‘delete before load’ option on target tables. And you may have to open every single dataflow and force a save by modifying it.

I had a huge migration from SQL Server 2005 to SQL Server 2008 to do last year, and I found that once I had the datastores up and running with a test job the easiest way to migrate the remaining code was to

  1. export to ATL file without the datastores (but including the tables)
  2. change all “Microsoft SQL Server 2005” to “Microsoft SQL Server 2008” in the ATL file
  3. import ATL file to target environment.

99% of the code worked first time.

dastocks (BOB member since 2006-12-11)