How to move from MSDE to SQL

[BESadmin]

Q: The BlackBerry Enterprise Server in my environment uses the Microsoft Data Engine (MSDE). How do I move the MSDE to a remote Structured Query Language (SQL) or MSDE Server?

A: Please see Support Knowledge Base Article below.

How To - Move the BlackBerry Configuration Database to a new server instance

Doc ID : KB03112
Last Modified : 2007-01-18
Document Type : How To

Environment

• BlackBerry® Enterprise Server software versions 2.1 through 4.1
• Microsoft® Exchange

Procedure

Note: If you are logging in to a named instance of Microsoft SQL Server™, use the following command syntax to log in:
osql -E -S <server_name>\<instance_name>

The BlackBerry Enterprise Server services will be stopped during the following process.

Important: Restarting the BlackBerry Enterprise Server will delay message delivery to BlackBerry devices. For more information, see KB04789.
Perform the following tasks using the BlackBerry service account unless otherwise stated:

1. Back up the BlackBerry Configuration Database.
2. Prepare the new Microsoft SQL Server.
3. Restore the BlackBerry Configuration Database.
4. Configure the BlackBerry Enterprise Server.

Task 1 - Back Up the BlackBerry Configuration Database
If you are using a full Microsoft SQL Server, follow the steps below:

1. Open Enterprise Manager.
2. Right-click the BlackBerry Configuration Database (the default name is BESMgmt).
3. Select Backup Database.
4. Click Add to specify the Directory Name and File Name. Click OK to accept the settings.
5. Click OK to perform the backup.
6. Close Enterprise Manager.

If you are using a Microsoft SQL Server Desktop Engine (MSDE), follow the steps below:

1. Type osql -E at a command prompt. Note: If you are logging in to a named instance of SQL Server, use the following command syntax to log in:
   osql -E -S <server_name>\<instance_name>
2. Type the following commands in the specified order: 1> backup database <database_name> to disk = "C:\<database_name>.bak"
   2> go
   1> quit
3. Close the command prompt.

Task 2 - Prepare the New Microsoft SQL Server
Complete the four steps below.

Step 1 - Assign appropriate permissions

Note: Perform this task using an account with administrative access to the new SQL Server.
If you are using a full SQL Server, complete the following:

1. Open Enterprise Manager.
2. Navigate to SQL Server Group/<SQL_server_name>/Security/.
3. Right-click Logins and select New Login.
4. On the General tab, click the "" button
5. Select the name of your BlackBerry service account from the Global Address List.
6. Click Add, then clickOK.
7. On the Server Roles tab, select Server Administrators and Database Creators. Note: On BlackBerry Enterprise Server software version 4.1, the System Administrator role is needed for Role-Based Administration.
8. Close Enterprise Manager.

If you are using an MSDE, complete the following:

1. Log in to the new SQL Server.
2. Right-click My Computer and click Manage.
3. Expand Local Users and Groups.
4. Go to Groups, then open the Administrators group.
5. Click Add.
6. Type the name of the service account and click Check Name.
7. Click OK, then close the Computer Management window.

Step 2 - Enable the correct Server Protocols

If you are using an MSDE\Microsoft SQL Server 2000, complete the following:

1. Go to Start > Run.
2. Type the command svrnetcn and click OK. The SQL Server Network Configuration Utility opens.
3. Make sure that TCP/IP and Named Pipes are both listed as enabled protocols.
4. Close the SQL Server Network Configuration Utility.
5. If you enabled a protocol in step 3, restart the SQL Services.

or

If you are using Microsoft SQL Server 2005 Express\Microsoft SQL Server 2005, complete the following:

1. Go to Start > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager > SQL Server 2005 Network Configuration.
2. Select the SQL instance that we will be using to house the BlackBerry Configuration Database.
3. Enable the TCP/IP and Named Pipes protocols.
4. If you enabled a protocol in step 3, restart the SQL Services.

Step 3 - Ensure the SQL Agent is running

Note: This service does not exist in SQL Server 2005 Express.

1. Open the Microsoft Windows® Services.
2. To do this, go to the Control Panel and open Administrative Tools > Services.
3. Verify that the SQL Server Agent service is Started and that its startup type is set to Automatic.
4. Close the Services window.

Step 4 - Update the Database Server Schema

Note: If you have already installed the BlackBerry Enterprise Server software to create a blank database on a new server instance, you do not need to perform the following task as the database schema would have already been updated.

1. Download the installation package for the version of the BlackBerry Enterprise Server you are using.
2. Extract the contents of the installation package.
3. Copy the Database folder to the new SQL Server.
4. Navigate into the Database folder and open the BESMgmt.cfg file in Microsoft Notepad.
5. Locate the line DB_NAME=BESMgmt and make sure that the name specified matches the BlackBerry Configuration Database name.
6. If you are using a named instance of SQL Server change the line "SERVER=local" to SERVER=<Server_Name>\<Instance_Name>.
7. Save and close the file.
8. At a command prompt, navigate to the Database directory.
9. Type the command createdb.exe BESMgmt.cfg
10. Once the command has finished executing, close the command prompt.

Task 3 - Restore the BlackBerry Configuration Database

Use one of the following methods to restore the BlackBerry Configuration Database, then proceed to Task 4:

Method 1 - Back up and Restore the BlackBerry Configuration Database (on an SQL Server with the same directory structure)
Complete the following steps:

1. Copy the BlackBerry Configuration Database backup to the C:\ path of the new SQL Server.
2. Type osql -E at a command prompt. Note: If you are logging in to a named instance of SQL Server, use the following command syntax to log in:
   osql -E -S <server_name>\<instance_name>
3. Type the following commands in the specified order: 1> restore database <database_name> from disk = "c:\<database_name>.bak" with recovery,replace
   2> go
   1> quit
4. Close the command prompt.

Method 2 - Backup and Restore the BlackBerry Configuration Database (on an SQL Server with a different directory structure)

Complete the following steps:

1. Copy the BlackBerry Configuration Database backup to the C:\ path of the new SQL Server.
2. Type osql -E at a command prompt. Note: If you are logging in to a named instance of SQL Server, use the following command syntax to log in:
   osql -E -S <server_name>\<instance_name>
3. Type the following commands in the specified order: 1> restore database <database_name> from disk = "c:\<database_name>.bak" with move "<database_name>" to "<new_path>\MSSQL\Data\<database_name>.mdf", move "<database_name>_log" to "<new_path>\MSSQL\Data\<database_name>.ldf"
   2> go
   1> quit
4. Close the command prompt.

Method 3 - Detach and Re-attach the BlackBerry Configuration Database

Perform the following steps:

Step 1 - Stop the BlackBerry Services

1. Open the Windows Services. To do this, go to the Control Panel and open Administrative Tools>Services.
2. Stop each service that has a name that begins with BlackBerry.

Step 2 - Move the BlackBerry Configuration Database files to the new SQL Server

If you are using full SQL Server, complete the following:

1. Log in to the original SQL Server.
2. Open Enterprise Manager.
3. Right-click the BlackBerry Configuration Database.
4. Select All Tasks and click Detach Database.
5. Close Enterprise Manager.
6. Navigate to the SQL Data directory and make a copy of the MDF and LDF files. The default path is C:\Program Files\Microsoft SQL Server\MSSQL\Data\ and the default filenames are BESMgmt.mdf and BESMgmt.ldf.
7. Move the file copies to the new SQL Server.
8. Log in to the new SQL Server.
9. Open Enterprise Manager.
10. Right-click the BlackBerry Configuration Database.
11. Select All Tasks and click Detach Database.
12. Move the files you copied in step 6 into the SQL Data directory. When prompted to overwrite the files, click Yes.
13. In Enterprise Manager, right-click the Database folder and click All Tasks.
14. Select Attach Database.
15. Select the MDF file to attach and click OK.
16. Close Enterprise Manager.

If you are using MSDE, complete the following:

1. Log in to the first MSDE Server.
2. Type osql -E at a command prompt. Note: If you are logging in to a named instance of SQL Server, use the following command syntax to log in:
   osql -E -S <server_name>\<instance_name>
3. Type the following commands in the specified order: 1> exec sp_detach_db @dbname = "<database_name>"
   2> go
   1> quit
4. Navigate to the SQL Data directory and make a copy of the MDF and LDF files. The default path is C:\Program Files\Microsoft SQL Server\MSSQL\Data\ and the default filenames are BESMgmt.mdf and BESMgmt.ldf.
5. Move the file copies to the new SQL Server.
6. Log in to the new MSDE Server.
7. Type osql -E at a command prompt.
   
   Note: If you are logging in to a named instance of SQL Server, use the following command syntax to log in:

   osql -E -S <server_name>\<instance_name>

8. Type the following commands in the specified order: 1> exec sp_detach_db @dbname = "<database_name>"
   2> go
   2> quit
   Move the files you copied in step 4 into the SQL Data directory. When prompted to overwrite the files, click Yes.
9. Type osql -E at a command prompt. Note: If you are logging in to a named instance of SQL Server, use the following command syntax to log in:

   osql -E -S <server_name>\<instance_name>

10. Type the following commands in the specified order: 1> exec sp_attach_db @dbname = "<database_name>",
    2> @filename1 = "C:\Program Files\Microsoft SQL Server\MSSQL\Data\<database_name>.mdf",
    3> @filename2 = "C:\Program Files\Microsoft SQL Server\MSSQL\Data\<database_name>.ldf"
    4> go
    1> quit
11. Close the command prompt.

Task 4 - Configure the BlackBerry Enterprise Server to connect to the new BlackBerry Configuration Database
Complete the following steps:

1. On the BlackBerry Enterprise Server, click Start > Programs > BlackBerry Enterprise Server > BlackBerry Server Configuration.
2. On the Database Connectivity tab, select Change Database.
3. In the Change Database Wizard, specify the new SQL Server name and the original BlackBerry Configuration Database name.
4. Continue with the wizard. Make sure that the Start Services check box is selected, then click Finish.
5. Open the Windows Services and make sure all the BlackBerry services started successfully. The BlackBerry Database Consistency Service may be disabled (this is normal behavior).
6. Open BlackBerry Manager and make sure that all BlackBerry device users display and show a running state.
7. Test message flow from the BlackBerry device. Note: Restarting the BlackBerry Enterprise Server delays message delivery to BlackBerry devices.
8. Close the command prompt.

If you are running BlackBerry Enterprise Server 4.1, install the Database Notification System.

1. Copy the RimEsp.dll file to C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ folder on the new SQL Server.
2. In SQL Query Analyzer, browse to the \Database\DBInstallScripts\SQLServer\4.1\ folder on the installation media.
3. Open the NotifyInstall.sql file and replace <database_name> with the name of the BlackBerry Configuration Database.
4. Run the script.
5. Confirm that the SQL Query Analyzer displays the message Function dbo.xp_RIM_xxxx registered.

[BESadmin]

Move the BlackBerry MDS Services databases to a new SQL Server or server instance

Doc ID : KB12714
Last Modified : 2007-03-28
Document Type : How To

Environment

• BlackBerry® Enterprise Server software version 4.1 Service Pack 2 and later
• Microsoft® Exchange
• IBM® Lotus® Domino®
• Novell® Groupwise®

Procedure

Note: Please use the procedure in this article in conjunction with KB03112, which details the steps to move the BlackBerry Configuration Database to a new Microsoft SQL Server™. The following tasks cannot be used to cut over a BlackBerry Enterprise Server to new hardware.

Perform the following tasks using the account that was used to install the BlackBerry Enterprise Server software unless otherwise stated:

TASK 1 - Prepare the Microsoft SQL Server

Create a SQL user account for BlackBerry MDS Services. This will need to match the BlackBerry MDS Services account created during the installation process. You can check the server.xml file for the user account and password that is currently used.

TASK 2 - Back up and restore the BlackBerry MDS Services databases to the new Microsoft SQL Server.

Important: Restarting the BlackBerry Enterprise Server will delay message delivery to BlackBerry devices. For more information, see KB04789.
Perform the following steps:

1. Stop the BlackBerry Services
   1. Open Microsoft Windows® Services. To do this, go to Control Panel and select Administrative Tools > Services.
   2. Stop each service that has a name that begins with BlackBerry.
2. Back up and restore the BlackBerry MDS Services databases to the new Microsoft SQL Server.
   1. Log in to the original Microsoft SQL Server.
   2. Open Enterprise Manager.
   3. Right-click the BlackBerry MDS Services databases.
   4. Select All Tasks and click Detach Database.
   5. Close Enterprise Manager.
   6. Navigate to the Microsoft SQL Server data directory and make a copy of all files with extensions of MDF and LDF. The default path is C:\Program Files\Microsoft SQL Server\MSSQL\Data\ and the default filenames are mdss.mdf, mdss.ldf, mdss_dis.mdf and mdss_dis.ldf.
   7. Move the file copies to the new Microsoft SQL Server data directory.
3. Attach the BlackBerry MDS Services databases to the new Microsoft SQL Server.
   1. Log in to the new Microsoft SQL Server.
   2. Open Enterprise Manager.
   3. In Enterprise Manager, right-click the Database folder and click All Tasks.
   4. Select Attach Database.
   5. Select the MDF file to attach.
   6. Specify the SQL user account created in Task 1 as the owner.
   7. Click OK.
   8. Close Enterprise Manager.

TASK 3 - Change the Microsoft SQL Server name in the server.xml file.[/b]
Perform the following steps:

1. Edit the server.xml file, found in C:\Program Files\Research In Motion\BlackBerry Enterprise Server\MDSS\jakarta-tomcat-5.5.9\conf\.
2. Add the new Microsoft SQL Server name to all occurrences of url= in the file.
3. Change the following line: url="jdbc:sqlserver://OLDSQLSERVERNAME:1433;databaseName=mdss;forwardRea dOnlyMethod=serverCursor" username="admin412" password="password"

1. to
   url="jdbc:sqlserver://NEWSQLSERVERNAME:1433;databaseName=mdss;forwardRea dOnlyMethod=serverCursor" username="admin412" password="password"

Keywords

BESMgmt, besmgmt.bak, besmgmt.ldf, besmgmt.mdf, move, MSDE, osql, remote, SQL