How to move from a Microsoft SQL Server Desktop Engine (MSDE) database to a Microsoft SQL Server database

Article ID: KB12247

Type: Support Content

Last Modified: 05-18-2012

 

Product(s) Affected:

  • BlackBerry Enterprise Server for Microsoft Exchange
  • BlackBerry Enterprise Server for IBM Domino
  • BlackBerry Enterprise Server for Novell GroupWise
CollapseEnvironment
  • BlackBerry® Enterprise Server version 3.6 to 5.0 SP3
  • Microsoft® SQL Server® 2000 and 2005
  • Microsoft® SQL Server® Desktop Engine 
CollapseOverview

To move from a Microsoft® SQL Server® Desktop Engine (MSDE) database to a Microsoft SQL Server database, complete the following procedures:

Warning: Changing databases used by the BlackBerry® Enterprise Server can cause irreparable damage, resulting in unexpected server behavior or service failure. Back up the databases prior to implementing any changes.

Procedure 1: Microsoft SQL Server preparations

Task 1: Assign appropriate permissions

Task 2: Update the Microsoft SQL Server so that it is compatible with the BlackBerry Configuration Database

Procedure 2: Moving the BlackBerry® Configuration Database from an MSDE to a Microsoft SQL Server database

Task 1: Back up the BlackBerry Configuration Database

Task 2: Restore the BlackBerry Configuration Database

Task 3: Configure the BlackBerry Enterprise Server to connect to the new BlackBerry Configuration Database

Task 4: Increase the maximum number of BlackBerry Messaging Agents

Task 5: Start the BlackBerry Enterprise Server services

Task 6: Make sure that BlackBerry Manager is pointing to the correct Microsoft SQL Server and BlackBerry Configuration Database

Task 7: Update the Open Database Connectivity (ODBC) Sources for the local computer


Procedure 1: Microsoft SQL Server preparations

This procedure can be performed before moving the database.

Note: Only perform the Microsoft SQL Server preparations when moving to a Microsoft SQL Server that has not hosted a BlackBerry Configuration Database.

Task 1: Assign appropriate permissions

  1. In the SQL Enterprise Manager, go to Microsoft SQL Servers > SQL Server Group > <SQL_server_name>.
  2. Expand the Microsoft SQL Server selection and expand Security.
  3. Right-click Logins and click New Login.
  4. Type the new name.

    For Windows® Authentication

    1. On the General tab, click the button to the right of the Name field.
    2. Select the new service account name from the Name list.
    3. Click Add.
    4. Click OK.

    For Microsoft SQL Server Authentication

    1. On the General tab, type the name of the Microsoft SQL Server account.
    2. Select the SQL Server Authentication option.
    3. Type the password for the Microsoft SQL Server account.
    4. Click Add.
    5. Click OK.
  5. From the Server Roles tab, select Server Administrators and Database Creators from the Server Role list.

    Note: If running BlackBerry Enterprise Server version 4.1, adding the Administrators role allows the addition of the BlackBerry® smartphone users in a role-based administration environment. For instructions, see the BlackBerry Enterprise Server for Microsoft Exchange: System Administration Guide.

  6. On the Database Access tab, perform the following steps:
    1. Select the check box for the BlackBerry Configuration Database (for example, BESMgmt).
    2. In the Database Roles for <BlackBerry_Configuration_Database_name> list, select the db_owner check box.
  7. Close SQL Enterprise Manager.

Task 2:  Update the Microsoft SQL Server so that it is compatible with the BlackBerry Configuration Database

To update the BlackBerry Configuration Database schema, complete the following steps:

  1. Download the installation package for the BlackBerry Enterprise Server version being used. 
  2. Extract the contents.
  3. Copy the Database folder to the new Microsoft SQL Server.
  4. Modify the BlackBerry Configuration Database .cfg file (for example, BESMgmt.cfg).

    Here are the values to be modified within this .cfg file:

    Command Procedure
    Database_name Type the BlackBerry Configuration Database name. The default name is BESMgmt.
    CMD Create a new BlackBerry Configuration Database and verify that the command (CMD) option defined is Install.

    To upgrade a previously existing database, change the CMD option to Migrate for BlackBerry Enterprise Server version 4.0, or change the CMD option to Upgrade for BlackBerry Enterprise Server software version 4.1. Use the Restore option to restore a previously backed up BlackBerry Configuration Database using RESTORE_FILENAME. Use the CatalogDatabase option (IBM® DB2® only) to catalog a remote BlackBerry Configuration Database locally.
    Userid & Password This is the administration account and password used to create or update the BlackBerry Configuration Database. If this is not set, then the Userid will be the administration account using this tool.

    Note: This option is only available with BlackBerry Enterprise Server version 4.1.

    Verbose Set this option to True to have information or error messages displayed on the computer.
    Version Specify the BlackBerry Configuration Database version to create or migrate to using one of the following: 3.5, 3.6, 4.0, or 4.1 (the most recent BlackBerry Enterprise Server software version is the default).
    Create By default, this option is set to True. Change this option to False if no BlackBerry Configuration Database should be created.

    Note: This setting is ignored during a migration or upgrade.
    Backup By default, this is set to False. Change this to True to back up the existing BlackBerry Configuration Database.
    Drop By default, this option is set to False. Change this option to True to drop the existing BlackBerry Configuration Database.

    Note: This setting is ignored during a migration or upgrade.
    DB2NODE This is the local node name of the remote IBM DB2 server. If a BlackBerry Configuration Database is created locally, leave the node name blank. IBM DB2 has an 8 character limit for the node name.

    Note: This applies only to IBM DB2 for creating a new BlackBerry Configuration Database.
    DB2SERVER & DB2PORT This option is used to create the local node name if the DB2NODE value does not exist.
    DB2REMOTE_DB & DB2LOCAL_DB This option is used by the CatalogDatabase command. Leave the DB2LOCAL_DB option blank to create a local alias name that is the same as the remote BlackBerry Configuration Database name.
    Stop By default, this option is set to True. Change this to True if the process stops and an error is displayed.
    Script_root By default, this directory is in the same directory as the CreateDB tool. Specify the path to the DBInstallScripts directory.
    Log_dir By default, this directory is in the same directory as the CreateDB tool. Specify the path to the directory in which to create the log files. The directory should exist and there should be no trailing backward slash ( \ ).
    Timestamp By default, this is set to True. Change this to False to remove time stamps (in HHMMSS format) from the log files.
    Developer By default, this is set to False. If this is set to True, the Developer command creates a smaller BlackBerry Configuration Database.
    DBMS By default, this is set to SQLServer. Specify which database management system is used.
    DB2SCHEMA This is the owner of the IBM DB2 Schema. If DB2SCHEMA is not present, change the default to BESADMIN.
    Db_file_dir Specify a directory in which to save the BlackBerry Configuration Database files. The directory must already exist. For Microsoft® SQL Server®, the default is the same location as the Microsoft SQL Server TempDB database.
    Backup_dir Specify a directory in which to save the BlackBerry Configuration Database backup file. The directory must already exist. The default is the same location as the BlackBerry Configuration Database file.
    Restore_filename By default, this is<database_location>\<database_name>BKUP.dat, where <database_location> is the location of the BlackBerry Configuration Database and <database_name> is the name of the BlackBerry Configuration Database.

    Specify the file to use if a BlackBerry Configuration Database restore is required.

    Note: If more than one backup file exists, the most current version is used.
    GENERATE_EXECUTE By default this option is set to True. If this option is set to False, the Microsoft SQL Server files will be generated but not executed.
    Server By default, this option is set to Local. Specify the computer on which to install the BlackBerry Configuration Database.

  5. Open a command prompt and navigate to the Database directory.
  6. Type the following command for the BlackBerry Enterprise Server version being used:

    For BlackBerry Enterprise Server version 4.0 to 5.0.1

    1. Type createdb.exe besmgmt.cfg

    For BlackBerry Enterprise Server version 3.6

    1. Type createdb.exe -o "C:\program files\microsoft sql server\80\tools\binn\osql.exe" -E -l "c:\program files\microsoft sql server\mssql\data"

      where C:\program files\microsoft sql server\80\tools\binn\ is the location of osql.exe and C:\program files\microsoft sql server\mssql\ is the location of the data directory for the Microsoft SQL Server.

    2. When the command has finished executing, close the command prompt.

    Note: For more information on the createdb.exe command or editing the besmgmt.cfg file, see the BlackBerry Enterprise Server: Installation Guide.


Procedure 2: Moving the BlackBerry Configuration Database from an MSDE to a Microsoft SQL Server database

Stop the BlackBerry Enterprise Server services that use the BlackBerry Configuration Database before performing these tasks.

Important: Restarting certain BlackBerry Enterprise Server services will delay email message delivery to BlackBerry smartphones. For more information, see  KB04789 .

Task 1: Back up the BlackBerry Configuration Database

If a Microsoft SQL Server is being used, complete the following steps:

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

If MSDE is being used, complete the following steps:

  1. Open a command prompt.
  2. Type one of the following:
    • osql -E (logs in to default instance using Windows® Authentication)
    • osql -U <username> -P <password> (logs in to default instance using SQL authentication)
    • osql -E -S <ServerName\InstanceName> (logs in to the named instance using Windows Authentication)
    • osql -U <username> -P <password> -S <ServerName\InstanceName> (logs into a named instance using SQL authentication)
  3. Press ENTER.
  4. Type the following commands in the specified order:

    1> backup database <BlackBerry_Configuration_Database_name> to disk = "C:\backup.bak"

    2> go

    1> quit

  5. Close the command prompt.

Task 2: Restore the BlackBerry Configuration Database

Copy the BlackBerry Configuration Database backup to the new Microsoft SQL Server.

If a Microsoft SQL Server is being used, complete the following steps:

  1. Open SQL Enterprise Manager.
  2. Right-click the name of the BlackBerry Configuration Database (for example, BESMgmt).
  3. Select Restore Database.
  4. In the Restore Database as: field, type the name of the BlackBerry Configuration Database.
  5. Select the From Device option.
  6. Click Select Device.
  7. Click Add.
  8. Click the button beside the Name field and go to the backup.bak file.
  9. Click OK.
  10. Click OK.
  11. If necessary, on the Options tab, change the path for the MDF and LDF files under Move to physical file name.
  12. Click OK.

If using
Microsoft SQL Server Management Studio:

  1. Open Microsoft SQL Server Management Studio (Microsoft SQL Server 2005 or 2008).
  2. Right-click the Databases (for example, BESMgmt).
  3. Select Tasks>Restore Database.
  4. Click From Device and browse for the .bak file.
  5. Select Options and ensure Overwrite the existing database is checked.
  6. Click OK to accept selection.
  7. Close Microsoft SQL Server Management Studio (Microsoft SQL Server 2005 or 2008) when the process is complete.

If MSDE is being used, complete these steps:

  1. Open a command prompt.
  2. Type one of the following:
    • osql -E (logs in to default instance using Windows Authentication)
    • osql -U <username> -P <password> (logs in to default instance using SQL authentication)
    • osql -E -S <ServerName\InstanceName> (logs in to the named instance using Windows Authentication)
    • osql -U <username> -P <password> -S <ServerName\InstanceName> (logs in to the named instance using SQL authentication)
  3. Press Enter.
  4. Type the following commands in the specified order:

    1> restore database <BlackBerry_Configuration_Database_name> from disk = " <path> <BlackBerry_Configuration_Database_name>.bak" with move "<BlackBerry_Configuration_Database_name>_data" to "<new_path>\MSSQL\Data\<BlackBerry_Configuration_Database_name>.mdf", move "<BlackBerry_Configuration_Database_name>_log" to "<new_path>\MSSQL\Data\ <BlackBerry_Configuration_Database_name>.ldf"

    2> go

    1> quit

  5. Close the command prompt.

Note: If the BlackBerry Configuration Database is restored using an account other than the BlackBerry Enterprise Server administration account, grant the account that is used by the database owner permission on the BlackBerry Configuration Database.


Task 3: Configure the BlackBerry Enterprise Server to connect to the new BlackBerry Configuration Database

On the BlackBerry Enterprise Server, complete the following steps:

For BlackBerry Enterprise Server 3.6 through to 4.1:

  1. Click Start > Programs > BlackBerry Enterprise Server > BlackBerry Server Configuration.
  2. On the Database Connectivity tab, click Change Database.
  3. In the Change Database Wizard, specify the new Microsoft SQL Server name and the restored BlackBerry Configuration Database name.
  4. Continue with the Change Database Wizard, making sure that the Start Services check box is not selected.
  5. Click Finish.

For BlackBerry Enterprise Server 5.0 (as discussed in KB18129):

  1. Run Setup.exe from the source installation folder.  By default, this location is <drive>:\Research In Motion\BlackBerry Enterprise Server 5.0.0 or <drive>:\Research In Motion\BlackBerry Enterprise Server 5.0.1
  2. Accept the License Agreement and click Next.
  3. On the Database Information Screen, update the Database server information with the new Microsoft SQL Server name and the restored BlackBerry Configuration Database name.
  4. Reinstall any previously installed Maintenance Releases.
  5. Repeat these steps on any remote nodes of the BlackBerry Administration Server, BlackBerry MDS-CS, and BlackBerry Monitoring Server.
  6. For more information, refer to the BlackBerry Enterprise Server Installation and Configuration Guide.

Task 4 - Increase the maximum number of BlackBerry Messaging Agents

Note: This task is only required for BlackBerry Enterprise Server for Microsoft Exchange, and does not apply when moving between MSDE or SQL Server Express 2005 databases.

To change the maximum number of BlackBerry Messaging Agents, complete the following steps:

Warning: The following procedure involves modifying the computer registry. This can cause substantial damage to the Windows® operating system. Document and back up the registry entries prior to implementing any changes.

  1. To open the Registry Editor, click Start > Run, type regedit and click OK.
  2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Research In Motion\BlackBerry Enterprise Server\Agents.
  3. Double-click NumAgents.
  4. In the Value data field, type 5 and select the Decimal option.
  5. Click OK.
  6. Close the Registry Editor.

Task 5 - Start the BlackBerry Enterprise Server services

  1. Open the Microsoft Windows Services and start all BlackBerry Enterprise Server services. For more information, see  KB13718 .

    Note: The BlackBerry Database Consistency Service may be turned off as a default setting.

  2. In BlackBerry Manager, verify that all BlackBerry smartphone users display a Running status.
  3. Test the email message flow.

Task 6 - Verify that the BlackBerry Manager is pointing to the correct Microsoft SQL Server and BlackBerry Configuration Database

For BlackBerry Manager version 4.0

  1. Open BlackBerry Manager.
  2. Right-click BlackBerry Manager and click Properties.
  3. On the Database tab, verify that the settings are configured for the new Microsoft SQL Server and BlackBerry Configuration Database.

For BlackBerry Manager version 4.1

  1. Open BlackBerry Manager.
  2. Click Tools > Options.
  3. In the left pane, click Database.
  4. Verify that the settings are configured for the new Microsoft SQL Server and BlackBerry Configuration Database.

For BlackBerry Administration Service in BlackBerry Enterprise Server version 5.0 

  1. Open the BlackBerry Administration Service.
  2. In the left column under Servers and Components, expand BlackBerry Solution topology, and click on BlackBerry Domain.
  3. The Database name is listed in the General section of the Domain information.
  4. The database server is not listed in the BlackBerry Administration Service and will need to be verified in the BlackBerry Server Configuration tool.

 


Task 7 - Update the Open Database Connectivity (ODBC) sources for the local computer

Note: This applies to BlackBerry Enterprise Server versions 3.6 and 4.0 using local or remote BlackBerry Manager installations.

To update the ODBC sources, complete the following steps:

  1. Open the Data Sources dialog box in the Administrative Tools folder of the Control Panel.
  2. On the System DSN tab, select the BlackBerry Enterprise Server Administration Database data source.
  3. Click Configure.
  4. On the first tab, make sure the new Microsoft SQL Server address is correct.
  5. On the third tab, make sure that the default database is set to the correct BlackBerry Configuration Database.

    Note: The BlackBerry Configuration Database default name is BESMgmt.

  6. Click Next to continue.
  7. Click Finish to close the dialog box.
CollapseAdditional Information

NOTE: If upgrading to Microsoft SQL Server 2005, please consult the support matrix as outlined in article KB11158.

If the IBM® Lotus® Domino® console fails to load the nBES task and displays the following error after the database is moved from a MSDE database to a Microsoft SQL Server 2000 database, see article KB03543.

Unable to open BESMgmt database

Disclaimer

By downloading, accessing or otherwise using the Knowledge Base documents you agree:

   (a) that the terms of use for the documents found at www.blackberry.com/legal/knowledgebase apply to your use or reference to these documents; and

   (b) not to copy, distribute, disclose or reproduce, in full or in part any of the documents without the express written consent of RIM.


Visit the BlackBerry Technical Solution Center at www.blackberry.com/btsc.