BlackBerry Forums Support Community

BlackBerry Forums Support Community (http://www.blackberryforums.com/index.php)
-   BES Admin Corner (http://www.blackberryforums.com/forumdisplay.php?f=21)
-   -   HOWTO: Migrate MSDE2K or SQL2K database to new server (http://www.blackberryforums.com/showthread.php?t=10406)

jibi 06-16-2005 11:23 PM

HOWTO: Migrate MSDE2K or SQL2K database to new server
 
This process should cover how to migrate an MSDE 2000 or SQL 2000 database to a new server running SQL 2000 (I'm sure MSDE 2000, as well, but thats not anything I deal with). This would include migrating from a local SQL 2000/MSDE 2000 instance on the BlackBerry server to a remote SQL database server.

The steps can vary from situation to situation, so this will include the "Backup and Restore" procedure. You can also substitute the Backup/Restore with the "Copy MDF/LDF Files; Attach Database" scenario as well (see below at the end).

1 - Backup the database
This can be done through the GUI or through command-line:

Command-Line:
osql -E
1> BACKUP database BESMgmt TO DISK = "C:\BESMgmt_backup.bak"
2> Go

GUI:
- Open SQL Enterprise Manager and expand to Databases.
- Right-click on Databases and click on Backup Database...
- Click on Add and choose a path and filename; click OK.
- Click on the Options tab and select to "Verify backup upon completion".

Copy this .BAK file to the new database server.

2 - Export the user list from the BlackBerry server
- Open BlackBerry Manager and right-click on the server instance and click on Export List. This is optional but suggested.

3 - On the new database server, be sure that the proper SQL permissions have been added for the BES service account (per the Installation Guide).

4 - Change the database information on the BlackBerry server
Prior to this step, be sure to copy down your Client Access License, SRP Identifier, and SRP Authentication Key for the BlackBerry server, as they may be needed for an optional step

- Open BlackBerry Server Configuration.
- Click on the Database Connectivity tab and click on Change Database.
- Select Yes when prompted and change the information to point to the new backend SQL server with the Database name of BESTest.
- At this point, you can either cancel the wizard or continue on through it. If you choose the latter, the SRP Identifier and SRP Authentication Key will be needed. The database has been created on the server and thats the important part of this process.

This step creates the extra system data needed on the new SQL server from a new database installation.

4 - Login to the database server and restore the database
- Open SQL Enterprise Manager and expand to Databases.
- Right-click on Databases and click on Restore Database...
- Select From Device and click on Select Devices...
- Click on Add and navigate to the .BAK file and click OK.
In the event that the drive letters change from where the database was stored on the old server to a new drive letter for the new server (for example, D:\ to E:\), you'll need to click on Options and change the drive letters for the LOG and DATA files under "Restore database files as".
- Click back on General and type in BESMgmt for "Restore database as".
- Click OK and it should restore the database successfully.

5 - Change the database information for the BlackBerry server to point to the correct database
- Open BlackBerry Server Configuration.
- Click on the Database Connectivity tab and click on Change Database. Select Yes when prompted and change the information to point to the new backend SQL server with the Database name of BESMgmt. Go through the rest of the wizard - the SRP Identifier and SRP Authentication Key should not be needed, as it will be pulled from the existing database (if not, just use the same information from step 3).

At this point, the BlackBerry services will startup and the users will populate automatically.

The "Copy MDF/LDF Files; Attach Database" scenario would include the following in place of step 4.

4 - Copy MDF/LDF Files and attach database
- On the original database server, stop the MSSQLSERVER service (and any other SQL services that are running as well). You will also want to disable the MSSQLSERVER service.
- Navigate to your SQL DATA directory and copy the BESMgmt.MDF and BESMgmt.LDF database files to the SQL DATA directory on the new database server.
- On the new database server, open SQL Enterprise Management and expand to Databases.
- Right-click on Databases and click on Attach Database...
- Click on the ... button and navigate to the BESMgmt.MDF file and select it
- All information should be populated, so click OK and it will attach it

You should probably verify permissions as well, but they should populate correctly. I did have an issue with a separate SQL-authenticated account populating for the database but not under the permissions tab under the login under the Security area of SQL (and it wouldn't let me add it there).

If anyone has something to add to this, by all means... This *should* be correct, but please verify prior to taking it as official documentation (although my official documentation from RIM wasn't exactly correct, either... had to add 2 steps after a phone call).

Obviously, for the potential Murphy's Law occuring, leave the MDF/LDF files on the original server as well as leaving SQL/MSDE installed. You never want to leave yourself in the situation where you have to a complicated rollback procedure. Rollback would be to enable and start the MSSQLSERVER service on the old database server then change the database information in the BlackBerry Server Configuration to the old server.

For another method, you can reference the top of bfrye's post here:
http://www.blackberryforums.com/showthread.php?t=8536

dev 06-17-2005 07:35 AM

I see you had some spare time....:-)

jibi 06-17-2005 08:22 AM

just a tad... :-)

But seriously, I was planning on cosolidating our two databases onto a new server, so this was just a generic version of my documentation for our other administrators.

And on that subject, if anyone is wanting to consolidate two databases, then you will simply need to Export the userlist from SERVER2, change the database information in the BlackBerry Server Configuration to the new database, open BlackBerry Manager (it will error) and change the Database information in it, then Import Users from File and select the exported list of users (and choose not to clear existing stats for the users). If you utilize IT Policy, then the users will need to reactivate their handhelds in order for you to be able to push policy out to them.

dev 06-17-2005 08:58 AM

Since you opened that can of worms....here's something I'm working on. Tell me if my logic is ok.
Scenario: approximately 20 BES v3.6 within the same domain across the city, with several Exchange severs ~40. There's a consolidation effort towards that as well.
All have separate MSDE/SQL2000 local dbs
All have different BESAdmin accts - don't ask why, that's how it is
May not have any IT Policies - I'm really hoping it is so
BES users per BES - range from 60->800
Project: Consolidation to 5 BES v4.0.X with clustered SQL and remote attachment servers. All new hardware.
Plan: To make the transition as seamless/transparent as possible
Procedure: in the interim- upgrade users to v4.0 (desktop&device)-preparation for EA
Install v4.0 on new hardware with one of the current SRP IDs, possibly BES
with the least amt of users- isolated of course
Export user list, CALs etc from old BES v3.6 - use for the new BES v4.0 above
Copy/restore method for mdf & ldf files on the new sql cluster
Bring up new BES v4.0 (after testing...etc and shutting down old BES v3.6)
At this point things should be good for these uers - same srp id...etc.
For the other BESs...simply move/add them to the new BESv4.0, and activate via EA.
Eventually freeing up SRP IDs to be used for the other new BES.
My only question here is the BESAdmin accts for the older BESs. They become obsolete after the migration....but can the new BESAdmin acct be used to add the old BES servers to the new BES console. Same domain shouldn't be a problem?
What do you think?

pista 06-17-2005 09:20 AM

Looks cool. I'd add a line to step three to make sure the ID being used has db create rights on the SQL server. I never actually finished the change database wizard, just enough to create the database and update the master tables. I didn't have the SRP info handy and was too lazy to look it up. I knew the data was in the MSDE backup anyway.

There are a few different ways to do this. What's important is that BES must create the db on the SQL server. Then either copy or restore the other db.

jibi 06-17-2005 12:34 PM

k, updated.

@dev, umm. dang, dude. I would imagine you'd want the least amount of user-interaction involved, which would decide which BES to use for the new database. I'd personally migrate the BES servers with the largest user base to new hardware and BES 4.0 prior to the database migration. This would have less impact on them overall not to mention less of a headache for you. So if 800 users existed on one BES, I'd use that one for your base database and first 4.0 BES.

I'd use the BES servers with the less number of users to migrate to the new database and free up the SRP ID/Auth Key (less users to deal with on the EA part, although the large servers would still need to do an EA in order for you to handle their handheld info and whatnot).

As for your question, I'm not really sure. New BES servers cannot be added to the console without sharing the database, no? This is something thats been foreign to me for the Exchange BES servers here (although they both now show up now that we're sharing databases).

dev 06-17-2005 01:00 PM

That option does reduce the overall work.....as well as shows the client significant progress....umm
As well as the upgrade (device OS) could happen whenever, whilist the other BES migrations are being done.
Thanks for the alternative perspective....

subvenki 06-20-2005 12:39 AM

Hi,

When I follow this process, don't I need to change the Service name and host name oin ServerConfig table ?

jibi 06-20-2005 07:54 AM

Why would you? This process simply covers moving the database from one server to the other. The ServiceName column simply shows the BES Instance Name. If you aren't changing the Instance Name, then there's no reason to change it in the database. Same goes for the MachineName column. In bfrye's example, you change this, and that is because you are actually migrating the BES and the Database to new physical servers.

The process outlined above is ONLY for moving the database off the BES to its own remote server, or to move it from one remote server to another (or, i suppose, to move it from a remote server to a BES).

boma0021 09-14-2005 02:43 PM

Hi Jibi,
thanks for the great docu. Moved today a BES 4.0.1 Domino from MSDE tp SQL2k. The only problem I have the Blackberry Manager will not start. Any Ideas?
error Message: AdminServer Client library failed to intializie
Another question, I checked the funktion everything is working fine (mail, calender, search...) but I found in den NT Event viewer serveral(nearly any secound) error messages from the sync service:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'SyncRequest' with unique index 'idx_SyncRequest'.[SqlState 23000][ODBCRecord::DoSetValue]DATA: Locked[2] UserConfigID[98] Locked[2] Operation[3].
any ideas ???

jibi 09-14-2005 02:53 PM

http://www.blackberry.com/knowledgec...89044&vernum=0

boma0021 09-14-2005 03:04 PM

your are great, thanks for the help with the BB Manager - it was the reg entry.
any ideas with the sync service messages?

jibi 09-14-2005 03:41 PM

not sure. which version of BES are you running w/ service pack level?

boma0021 09-14-2005 03:53 PM

BES 4 Service pack1 HF3 for Domino 6.5.4 on w2003 with sql2k

jibi 09-14-2005 07:30 PM

thought about upgrading to SP2?

greywolfe 09-14-2005 08:20 PM

Jibi, have you ever encountered all your users dissapearing/unable to add users when upgrading to BESX 4.0 sp2? I know its a known issue since RIM sent me a fix (a *very* chunky sql file) but I need to narrow down the reasons why it occurs and under what circumstances.

jibi 09-14-2005 08:42 PM

the only issue i've had like that is when i move a user from one BES to another. all of the users then disappear. i have to close blackberry manager and reopen it. i haven't reported it yet, though.

greywolfe 09-14-2005 08:48 PM

Very strange then...I need to find out whether its isolated to remote sql installs only or not. RIM of course wont give me more info on where this problem originates :(

jibi 09-14-2005 11:20 PM

does the SQL give any sort of hint as to what it changes? care to send to me?


All times are GMT -5. The time now is 10:41 AM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.