BlackBerry Forums Support Community
              

Closed Thread
 
Thread Tools
Old 06-16-2005, 11:23 PM   #1
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default HOWTO: Migrate MSDE2K or SQL2K database to new server

Please Login to Remove!

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
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.

Last edited by jibi; 06-17-2005 at 11:11 AM..
Offline  
Old 06-17-2005, 07:35 AM   #2
dev
CrackBerry Addict
 
dev's Avatar
 
Join Date: Apr 2005
Location: Toronto
Model: 8800
Carrier: Rogers
Posts: 571
Default

I see you had some spare time....
Offline  
Old 06-17-2005, 08:22 AM   #3
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

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.
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.

Last edited by jibi; 06-17-2005 at 08:27 AM..
Offline  
Old 06-17-2005, 08:58 AM   #4
dev
CrackBerry Addict
 
dev's Avatar
 
Join Date: Apr 2005
Location: Toronto
Model: 8800
Carrier: Rogers
Posts: 571
Default

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?
Offline  
Old 06-17-2005, 09:20 AM   #5
pista
Knows Where the Search Button Is
 
Join Date: Apr 2005
Location: SE Michigan
Model: 7100T
Carrier: T-Mobile
Posts: 17
Default

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.
Offline  
Old 06-17-2005, 12:34 PM   #6
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

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).
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 06-17-2005, 01:00 PM   #7
dev
CrackBerry Addict
 
dev's Avatar
 
Join Date: Apr 2005
Location: Toronto
Model: 8800
Carrier: Rogers
Posts: 571
Default

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....
Offline  
Old 06-20-2005, 12:39 AM   #8
subvenki
New Member
 
Join Date: Jun 2005
Location: India
Model: 7100g
Carrier: Airtel
Posts: 9
Default

Hi,

When I follow this process, don't I need to change the Service name and host name oin ServerConfig table ?
Offline  
Old 06-20-2005, 07:54 AM   #9
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

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).
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 09-14-2005, 02:43 PM   #10
boma0021
Talking BlackBerry Encyclopedia
 
boma0021's Avatar
 
Join Date: Jan 2005
Location: LE
Model: Pearl
Carrier: T-Mobile
Posts: 202
Default

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 ???
Offline  
Old 09-14-2005, 02:53 PM   #11
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

http://www.blackberry.com/knowledgec...89044&vernum=0
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 09-14-2005, 03:04 PM   #12
boma0021
Talking BlackBerry Encyclopedia
 
boma0021's Avatar
 
Join Date: Jan 2005
Location: LE
Model: Pearl
Carrier: T-Mobile
Posts: 202
Default

your are great, thanks for the help with the BB Manager - it was the reg entry.
any ideas with the sync service messages?
Offline  
Old 09-14-2005, 03:41 PM   #13
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

not sure. which version of BES are you running w/ service pack level?
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 09-14-2005, 03:53 PM   #14
boma0021
Talking BlackBerry Encyclopedia
 
boma0021's Avatar
 
Join Date: Jan 2005
Location: LE
Model: Pearl
Carrier: T-Mobile
Posts: 202
Default

BES 4 Service pack1 HF3 for Domino 6.5.4 on w2003 with sql2k
Offline  
Old 09-14-2005, 07:30 PM   #15
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

thought about upgrading to SP2?
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 09-14-2005, 08:20 PM   #16
greywolfe
Talking BlackBerry Encyclopedia
 
greywolfe's Avatar
 
Join Date: Mar 2005
Location: NSW, Australia
Model: 8100
Carrier: optus
Posts: 303
Default

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.
__________________
BES Admin, sometime God.
Offline  
Old 09-14-2005, 08:42 PM   #17
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

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.
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 09-14-2005, 08:48 PM   #18
greywolfe
Talking BlackBerry Encyclopedia
 
greywolfe's Avatar
 
Join Date: Mar 2005
Location: NSW, Australia
Model: 8100
Carrier: optus
Posts: 303
Default

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 :(
__________________
BES Admin, sometime God.
Offline  
Old 09-14-2005, 11:20 PM   #19
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

does the SQL give any sort of hint as to what it changes? care to send to me?
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Closed Thread


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


One Datasensor TH-TD-11 Transistor O/P 95B030070 Temperature Controller NEW picture

One Datasensor TH-TD-11 Transistor O/P 95B030070 Temperature Controller NEW

$75.00



BLF188XR RF Power LDMOS Transistor High Frequency Microwave Tube 1Pc picture

BLF188XR RF Power LDMOS Transistor High Frequency Microwave Tube 1Pc

$87.56



5PCS Transistor RCA TO-3 2N5630 picture

5PCS Transistor RCA TO-3 2N5630

$12.17



Mr. Steam Part No. 103538C Level Control Board picture

Mr. Steam Part No. 103538C Level Control Board

$649.99



Portable 600pc 15Value NPN PNP Transistor TO-92 Assortment Kit Set /Box Hot picture

Portable 600pc 15Value NPN PNP Transistor TO-92 Assortment Kit Set /Box Hot

$11.16



TO-92 Assortment NPN PNP DIY kit 15 value 600pcs Transistor  picture

TO-92 Assortment NPN PNP DIY kit 15 value 600pcs Transistor

$12.96







Copyright © 2004-2016 BlackBerryForums.com.
The names RIM © and BlackBerry © are registered Trademarks of BlackBerry Inc.