Outlook 2007 Business Contact Manager served by SQL Server 2005

My last post dealed with installing BCM 2003 on a remote dedicated server; in this case, the same subject but related to the new Office Outlook 2007 with Business Contact Manager is addressed here. Microsoft has this procedure fully documented on Outlook 2007 Document: Deploying Outlook 2007 with Business Contact Manager in a Remote Database Configuration

Outlook 2007 with Business Contact Manager uses a SQL Server 2005 database for storing its data. By default, configuring Outlook 2007 with Business Contact Manager with any Outlook profile creates a SQL database on the local computer. Instead of creating a database on the local computer, you can choose to connect to an existing database on a remote server.

The main quiz of the document is installing a named instance called MSSMLBIZ and set up to listen to TCP port 5356. The documents talks about moving the database data using a backup and a restore, but if you want to try other procedure, you can simply:

1. Stop MSSQL$MSSMLBIZ service running on the original PC
2. Copy database files (called MSSmallBusiness.ldf and MSSmallBusiness.mdf by default on BCM2007) to the new coputer that will be serving them
3. Attach the database files (using sp_attach_db or the IDE in VS2005)
4. Configure proper permissions, open ports, etc.

The most useful point in the former Microsoft document (IMHO) is the one that explains how to Use a database backup file from a previous version of Business Contact Manager for Outlook on a remote server.

Users may have used a previous version of Business Contact Manager for Outlook 2003, and may have created a backup of their database from that previous version. Specifically, the backup file may have been created by using the user interface and it may be in an .msbcm file or a .sbb file. This section describes how these backup files may be restored to a Business Contact Manager 2007 database on a remote server.

Happy upgrading ;)

Outlook 2003 Business Contact Manager served by SQL Server 2005

When you install Outlook 2003 with Business Contact Manager Update , a named instance of MSDE is installed (under %PROGRAMFILES%\Microsoft SQL Server\MSSQL$MICROSOFTSMLBIZ ). If you are the only one to work with your contact database it will be fine, but if you plan to share the database with your coworkers in a workgroup or domain, either you need to leave your computer permanently online or need to move the database files to a real server. In this document I explain how to move your contacts database to a remote server running SQL Server Express 2005 so that you could benefit from not being overloaded with having to manage and run MSDE on your computer, being able to restart or turn off your computer without advising your collegues, scheduled backups and, in summary, all the benefits that you could achieve having your data served by a dedicated computer instead of your personal one.

1. On the computer that will be used as server, install a named instance of SQL Server Express 2005 called SERVERNAME\microsoftsmlbiz, enable it for network access and make it listen to tcp port 56183 (tcp port 1433 is the default)
2. Stop your local MSDE instance (net stop MSSQL$MICROSOFTSMLBIZ from your command prompt)
3. Copy your MSBusinessContactManager.ldf and MSBusinessContactManager.mdf files along to the server that will be hosting them. The files on your computer are located under: %USERPROFILE%\Local Settings\Program Data\Microsoft\Business Contact Manager\ The destination directory on the server that will be hosting the database depends of your configuration, but it can be something like: %PROGRAMFILES%\Microsoft SQL Server\MSSQL.3\MSSQL\Data
4. Once you have those two file on the destination server, attach the database to the server and grant proper permissions to users.
5. For every client that was connecting to your MSDE instance, open Outlook and configure it to use the remote server instead.
6. Restart Outlook.
7. You can safely set MSSQL$MICROSOFTSMLBIZ and SQLAgent$MICROSOFTSMLBIZ services startup type to Disabled or Manual for all computers that are conecting to a remote database.

Since you can only create new databases on the local computer (Using Create or select a new database menu), if you want to create the database from scratch (no data) using SQL Server 2005, you will need to disable/stop MSDE service, install SQL Server 2005 (or Express) on your local computer (a named instance called MICROSOFTSMLBIZ and listening to tcp port 56183), and then you can use Outlook wizard to create the new database on your local server that will be SQL Server 2005 (or Express) instead of MSDE. You can then move that database file (using sp_detach_db y sp_attach_db to the final server).

Remember that database files created with the newer versions of SQL Server 2005 cannot be attached to older engines (i.e. SQL Server 2000 or MSDE), they are not backwards compatible. If you try to attach a MSBusinessContactManager database created with SQL Server 2005 to a MSDE engine, you will get the following error (in ERRORLOG file):

In spanish:

Error: 602, Severity: 21, State: 50
No se encuentra la fila en sysindexes de la base de datos con Id. 7, Id. de objeto 1, Id. de índice 1. Ejecute DBCC CHECKTABLE en sysindexes..

In english:

Error: 602, Severity: 21, State: 50
Could not find row in sysindexes for database ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes..

This is important because if you ever need to upgrade to Office 2007, you will need a way to move the database back to the local MSDE engine to allow the upgrade procedure reuse your existing data as part of the upgrade.