2007/01/31

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.

12 comments:

Honza Šebelík (B8) said...

I followed the scenario (copied MSDNE database and attached to remote SQL2005 Express), however I get the following message when trying to choose the new server: "Cannot find any Business Contact Manager databases on computer...". :-( Connection from the client PC to the SQL server through DSN or Management Studio with Windows authentication works fine. The db user is db_owner so there should not be any rights problem.

Honza Šebelík (B8) said...

The default instance name and port number can be comfortably changed under "HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\Business Solutions eCRM".

J.A. said...

If you change the instance name and port for the server, be sure to do that change in registry for every client that will connect to that server.

Regarding the problem on your first post... are you on a domain or a workgroup?

If your scenario includes domain accounts and the domain user that created the original MSDE database is the same that now is trying to connect to the same database, but on the new SQL instance, I think there should be no problem at all. If you can actually connect to that instance and see the database using Management Studio, it is not a permissions issue at all.

Be sure that you can access to SQL server from the client computer using Management Studio installed from the client computer to discard firewall issues. Of course you can connect with that user locally to SQL Server 2005 Express on the machine serving the data, but you need to be able to access the same data remotely. By default, SQL Server 2005 Express installs itself in local-access-mode only (shared memory) and TCP/IP protocol disabled. Check that you have TCP/IP protocol enabled using SQL Server Configuration Manager.

Another thing that you can try is using SQL Server 2005 Surface Area Configuration for characteristics, to Enable OLE Automation and see if that solves the problem.

If everything fails, try to run the server with the default instance name and port (SERVERNAME\microsoftsmlbiz and 56183) and reset those values using regedit on the clients. Maybe that registry trick does not work as you expect after all.

Honza Šebelík (B8) said...

Thank you for prompt reply! We don't have a domain so the credentials are shared using same local usernames and passwords. Most of the testing I did with the default instance and port, and finally I tried the registry trick. I was able to connect to the SQL server from the BCM client computer using Management Studio and TCP/IP. I will check the OLE Automation issue. And I am not sure how does the BCM client recognize the database...

Luther Blissett said...

"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."

You can export the server db, switch to the local db, and import (w/merge) the file.

Daniel Schaeffer said...

Olá José Antônio,

Primeiramente parabéns pelo tutorial, pois o mesmo me ajudou muito. Infelizmente segui os passos e não consegui fechar a conexão do Contact Manager com o banco de dados. Inclusive postei um tópico no TechNet relatando o problema. Se o amigo puder me auxiliar ficaria muito grato, segue o endereço: http://forums.microsoft.com/Technet-BR/ShowPost.aspx?PostID=4225221&SiteID=29.

J.A. said...

I'm sorry but we don't use Outlook 2003 & BCM anymore. We only used it for a while to evaluate its convenience but discarded it after some weeks of testing.

Anonymous said...

Thank you very much for this brilliant article!

I followed you guide and it worked like a charm.

I changed:

Note this is the regpath on Vista x64!
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Business Solutions eCRM

SQLInstanceName: MySQLInstanceName
SQLPort: 56183 (was 5613 or similar before)

Unknown said...

I followed these instructions, including changing the registry values on the client to match the values in the MSSQL server, but am still unable to connect from BCM to the server.
Any suggestions on wha I can test/take a look at to try to figure out where the problem is.

J.A. said...

I'm sorry but as I said, we do not use it anymore. Besides, without knowing what type of error (if any) do you receive it is quite difficult to suggest you anything.

Even without receiving any error, you could take into account the time it passes since you start BCM addon and the 'error'. If it is shown quite fast, might indicate a connection denied (check your credenials in SQL Server) if it takes longer, maybe a timeout.

Check the EventLog in your machine and also on the server and pursue anything (even in security log) that might be raising within the timespan of your tests. You could also use a packet sniffer on the server to check if it is receiving anything at all from your client machine...

I can suggest you lots and lots of things to try, but whithout further details I cannot guide you anymore.

Unknown said...

Thanks. I understand that you are not using it anymore, but since you posted, I am assuming you are willing to give some advice? :)
I get the regular error that BCM could not connect to the database on my server and to make sure that the BCM is installed and running there.
I am able to connect with the studio from the client to the server no problems.
I will take a look at the logs and post back if there is anything related to SQL there.

Jose M. Sanchez said...

After making the registry change on the local machine, reboot the local (client) computer.

Also check connectivity as indicated to the SQL server but substitute the actual PORT used by SQL.

Also note that you must tick off the correct ( or at least SOME ) permissions granted uses, or else nothing will "take".