Purpose:

To move the CSA MC database from one machine to another
and then BACK again (or just move the remote CSA MC database
from one machine to another).  Because this procedure is solely to
MOVE the SQLServer database, the CSA MC will NOT HAVE TO BE REINSTALLED.
The CSA MC will just point to the NEW SQLServer database.
This procedure can be used for various scenarios such as:

-Moving the CSA MC database to a more powerful machine
-Recovering from a database crash and restoring the saved CSA MC database
 files (raw saved database files that is- mdf, ldf, ndf files)
-Testing a production database on a non-production system
-Temporarily moving the database to another machine while the
 original database machine's OS and database engine are upgraded




BACKGROUND:

There will be ONE CSA MC talking to this REMOTE database for the purposes
of this document.  You may have two CSA MC machines.  In that case, ODBC changes
(outlined later in this document) need to be made on BOTH CSA MC machines. 

Legend:

- csamc  (the CSA MC machine)

- csamc45 (the name of the CSA MC database for this example, your datatbase
           name may be different)

- db1    (The ORIGINAL database machine)


- db1b   (The SECONDARY database machine)


---------------------------------------------------------------------------------

Steps:

- net stop the Ciscoworks Daemon Manager (CRMDmgtd) on the csamc machine

- make sure the FULL SQLServer Enterprise Edition (spk3a) is installed on db1b
  in preparation for the move.

- make sure db1b has the SAME user configured that you used to create the 
  db on the db1 machine

- on the db1 machine, net stop and net start mssqlserver

- Detach the csamc45 database from db1:
   - right click on the csamc45 database - all tasks - detach database
    - make sure you check the update statistics box prior to clicking OK
    - CLEAR the database connections prior to the detach if prompted


- COPY the database files to db1b.  The database files (usually located
  in program files\Microsoft SQL Server\MSSQL\data) are:
   - csamc45.mdf
   - csamc45_log.ldf
   - csamc45analysis_data_data.ndf

NOTE: you may have called your CSA MC databases something else.

NOTE: YOu can re-attach the database back to db1 AFTER you copied the
 database files over to db1b if you want.

- Attach the csamc45 data files that were copied over from db1 to db1b:
   - right click on the databases folder in Enterprise Manager
     on db1b, all tasks-attach database
   - in the MDF field, browse to the csamc45.mdf file that you copied
     over from db1
   - When you select the csamc45.mdf database, it will also 
     automatically find the ccsamc45_log.ldf and the 
     csamc45analysis_data_data.ndf too
   - in the attach to field it should specify the original name
     of your csamc45 database (so, it will be populated with csamc45 in this
     document's example)
   - specify database owner: as 'sa' (Do NOT select the original user of the datbase!)
   - you will get a message that the DB attachment completed successfully
     and you will see the CSAMC45 database under the list of databases
     in Enterprise Manager
     


- Open regedit on the csamc machine(s):

  - HKLM-software-Cisco - csamc45- DSN

change the original string reference to the OLD database server (db1) to the NEW 
database server (db1b)

For example, the string will say:

DSN=csamc45DSN!SERVER=db1!DATABASE=csamc45!NETWORK=DBMSSOCN!

Change that to:

DSN=csamc45DSN!SERVER=db1b!DATABASE=csamc45!NETWORK=DBMSSOCN!

- EDIT the ODBC connection on the csamc machine to POINT to db1b:
  - Open up the control panel - administrative tools - ODBC
  - Go to the System DSN Tab
  - Click on the csamc45DSN and select the Configure button
  - Keep the NAME the same but change the SERVER to db1b (you 
    can just type in the db1b machine name - as long it is resolvable)
  - hit next and select SQLServer Authentication at the next window
  - Login ID is the NAME of the datbase owner (the original owner)
  - enter password
  - Next make sure the change the 'default database to' field has the CORRECT
    name of the csamc45 database (csamc45 for this example)
  - keep the other items in their default states
  - next screen keep the defaults (make sure "Use regional settings . . . " box
    is NOT CHECKED!!)
  - Hit FINISH and then hit the TEST DATA SOURCE button.  It should say
    "TESTS COMPLETED SUCCESSFULLY!"

- Do the SAME for the csaanlaysis45DSN entry


-on the db1b machine, you need to properly map the login to the user of the
 csamc45 database:

- open Query Analyzer:

- choose csamc45 as the database

then execute the following commands (this example uses IRadmin as the 
original owner of the database and 'password' as IRadmin's password):

EXEC sp_addlogin 'IRadmin', 'password'
EXEC sp_change_users_login 'Update_one', 'IRadmin', 'IRadmin'

- net start the Ciscoworks Daemon Manager (CRMDmgtd) on the csamc machine

- log into the cSamc to make sure you have connectivity
----------------------------------------------------------------------------

If you want to MOVE the CSA MC database BACK to db1, follow the same procedure
as specified above with a few minor changes.  An outline of the steps would be this:

- UPGRADE/reghost, etc. db1

- net stop the Ciscoworks Daemon Manager service on csamc

- stop and restart mssqlserver on db1

- DETACH the csamc45 database on db1b

   - csamc45.mdf
   - csamc45_log.ldf
   - csamc45analysis_data_data.ndf

- Copy the csamc45 data files from db1b to db1

- ATTACH the database files you moved from db1b to db1

- EDIT the ODBC connections on csamc (remember, there are TWO - csamc45DSN
  and csaanalysis45DSN) to POINT to db1

- Open regedit on the csamc machine:
  - HKLM-software-Cisco - csamc45- DSN
change the original string reference from the NEW database server (db1b) to 
the ORIGINAL database server (db1)

For example, the string will say:

DSN=csamc45DSN!SERVER=db1b!DATABASE=csamc45!NETWORK=DBMSSOCN!

Change that to:

DSN=csamc45DSN!SERVER=db1!DATABASE=csamc45!NETWORK=DBMSSOCN!

- RUN ONLY this ONE script via Query Analyzer to map the user
  correctly to the correct login (because the login already
  exists for the ORIGINAL db1 system):

EXEC sp_change_users_login 'Update_one', 'IRadmin', 'IRadmin'

- net start csam45 service on csamc

- launch the CSA MC on csamc.  It should now be pointing to and successfully
  connecting to, db1


you also need to update the database table:


mc_config

column:

sysvars.cf 

name: mc.server_cert_cn   = CSAMCservername   (you need to Update this to reflect the name of your NEW csa mc)

name:   mc.server_ip  = csamc_ip_address  (you need to update this to reflect the IP address of your NEW csa mc)

Do this right after you have RESTORED the CSA MC database (or attached it to your other SQLServer).


JOE

END