Synchronizing Outlook Data between Exchange Server and SQL Server

This article looks at synchronization of Outlook data from Exchange Server Public Folders to a SQL Server database and vice versa, namely SQL Server to Outlook/Exchange synchronization.

We look at typical scenarios where you might want to synchronize your Outlook data to and from a database like SQL Server, and the associated benefits.

We also discuss the pros (and cons) of utilising Outlook as a front-end client versus your SQL application client, as well as the advantages of using SQL Server as a back-end database rather than Exchange Server Public Folders.

Download the Article in PDF Format

1. The Advantages of Maintaining your Contacts in Microsoft Outlook

There are many advantages to using Outlook for managing and maintaining your customer/contact database and tracking interactions with these contacts.

  1. Contact Management Functionality

    Outlook already has the basic components that make up any contact management system. Trying to reproduce this functionality inside your SQL system may not be a simple task:

    1. A mechanism to store and profile Contact information: The “Contacts” folder in Outlook already allows a comprehensive profile of any personal or business contact to be maintained.

    2. A means to plan and organise appointments for those contacts: Outlook’s calendaring facilities provide these very effectively and when coupled with Exchange Server incorporate a huge number of collaborative features that are extremely difficult for any other stand-alone CRM system to emulate or reproduce.

    3. A means to schedule tasks and to-do’s for those contacts: Outlook’s task management facility is excellent for this.

    4. A mechanism to record any kind of interaction with a contact: The “Journal” facility of Outlook contains the standard fields necessary to record phone calls, meeting, etc. with clients, and can even time such activities.

    5. A way to send and track e-mail communication: The Inbox and Sent Items stores inward and outward e-mails.

     

  2. Familiarity/Ease of Use

    If users are already utilising Outlook for at least e-mail and calendaring then they are already familiar with the interface and how to add new items, edit existing items, etc. Thus there is usually very little requirement for additional training on how to maintain the customer/contact database.

  3. Customisability

    Outlook forms can be very easily customised to contain additional data fields that are required. The Outlook Forms Designer is a very powerful tool. For more details on how to use the Outlook Forms Designer, see the following article:

    Profiling your Outlook Contacts using the Outlook Forms Designer

  4. Easy Synchronization with PDA's

    Given that all PDA’s synchronise with Outlook as a standard, this means that you have automatic access to your Contact Management data if it is kept in Outlook rather than being in another application.

go to top of page

2. The Disadvantages of Outlook as a Database

Hierarchical, non-Relational Database

The primary disadvantage of Outlook as a database is that it is a hierarchical rather than a relational database. Most users tend to use their Outlook folders as discrete elements, i.e. because it is fairly cumbersome for users to link one item to another, (e.g. a contact to an appointment) they seldom do this. Thus it is difficult for users in a company to get an overall picture of all the activity occurring within the organisation against any particular company or contact. The universal objective of any CRM system however, is to provide a “single-view of all customer-related information to everyone in the organisation". As will be seen later, this limitation can be overcome automatically by synchronizing your Outlook Contact and E-Mail data to a relational database like SQL Server.

Inferior Indexing

While Exchange Public Folders seem to handle large volumes of items within a folder, Exchange Server is not geared to indexing these items efficiently (when compared to SQL Server). So for example the speed with which one can find all the items in one folder that are related to an item in another folder via a key field dramatically slows down when there are say more than 25,000 items in the folder being searched.

Unfamiliar Data Structure

Outlook/Exchange folders are an unfamiliar data structure for more databases, report writers, etc. Even Microsoft’s own low-end database product Access has difficulty linking to an Outlook or Exchange folder in such a way that all fields (including custom fields) can be viewed/manipulated.

go to top of page

3. The Advantages of SQL Server as a Database

There are many advantages to having a synchronized copy of your Outlook Public Folder data in in Microsoft SQL Server database.

  1. Relational Database

    SQL Server was designed as a robust, scalable relational database.

  2. IT Standard

    In certain companies SQL Server has become a declared standard for all database applications. And typically a CRM application in this context would be considered a database application, and so may IT shops simply would prefer to have their data in SQL. Often this is simply because there is more familiarity/expertise around SQL.

  3. Easier Data Manipulation

    It is easier to do mass updates of your data using SQL Query Analyser than it is with an Outlook database, which typically has to be manipulated using Visual Basic for Applications and the Outlook Object Model, which are not as familiar to the IT Professional as SQL Query Analyser.

  4. Easier Reporting

    It is easier to connect Report Writers and Business Intelligence tools to a SQL database than to an Exchange Public Folder store.

go to top of page

4. Synchronizing Outlook Data - Typical Scenarios

There are many situations where one might want to synchronize data between Exchange Public Folders and a SQL database. On the other hand you may have data in a SQL Server database that you want to interact with or update using Outlook. On the other hand Outlook users may be updating Public Folder information which must be constantly synchronized back to a SQL database. We look at several typical scenarios where this requirement might exist.

  1. Synchronizing your Web Site Database to Outlook

    A common scenario is a web site SQL Server database where customers are registering, logging on, etc. that needs to be kept in sync with an Outlook/Exchange Contacts folder that is used for profiling these customers in more detail and e-mailing them information relating to requests logged on the web site.

    Most Internet Service Providers offer a SQL database on a rented basis for the purposes of recording web registrations, etc. Usually one would have some kind of mechanism for the customer to log on and update their profile, which may include changing their preferences for receiving newsletters etc. Ideally you should have this information constantly up to date in-house, if you are using an Outlook database to send mailing, follow-up e-mails etc. to these customers and prospects.

  2. Synchronizing your ERP Database to Outlook

    Another example is where you want to keep Outlook Companies and Contacts folders synchronized with a SQL Server-based ERP or accounting system such as Great Plains, so that again the sales and marketing personnel can easily interact with these customers using standard Outlook functionality.

    Examples of SQL Server-based accounting systems include the following:

    • Microsoft Great Plains
    • MAS 90® / MAS 200®
    • Everest Advanced
    • SAP Business One
    • ACCPAC Pro / Advantage
    • Made2Manage
    • Cougar Mountain
    • Solomon
    • Microsoft Axapta
    • Epicor Enterprise
    • Navision
    • STFB
    • Quickbooks Enterprise

  3. Synchronizing your CRM Database to Outlook

    Many customers will have a SQL Server-based CRM system, but will not want to have to incur the expense of buying a full CRM client for each user in the company, particularly those users who just want to view or update company and/or contact information, and do not need the advanced functionality that many CRM programs offer (along with the associated complexity). Plus they would rather be able to do this in the familiar environment of Outlook, than have to learn how to use a new CRM client.

    Examples of SQL Server-based CRM systems include the following:

    • Microsoft CRM
    • SalesLogix
    • Siebel
    • e-Synergy
    • SalesForce.com
    • ADAPTcrm
    • Epicor Clientele
    • SAP Business One CRM


  4. Synchronizing Outlook Contacts to SQL

    If you have your primary contact database in Outlook there are also several reasons why you might want to synchronize your Outlook data to a SQL Server database.

    Easier Reporting
    Reporting in Outlook is difficult, particular cross folder reporting. For example you might want to link the e-mail address of your contacts to the e-mail address of the sender and/or recipient of e-mails in the E-Mail folder in order to get a listing of e-mails by contacts.

    Integration with other systems
    It is easier to integrate one database with another. So having the data in SQL make this integration easier.

go to top of page

5. Outlook Synchronization between Exchange Server Public Folders and SQL Server using MX-Sync

MX-Sync is an Outlook synchronization utility which synchronizes data bi-directionally (i.e. two-ways) between standard Microsoft Exchange Server Public Folders and a Microsoft SQL Server database.

It is useful in situations as described above where Outlook users are updating Public Folder information which must be constantly synchronized back to a SQL database, and/or want to use Outlook to interact with data that is stored in a SQL Server back-end ERP, accounting or web database system.

MX-Sync can be used in conjunction with MX-Contact, which is a fully comprehensive Outlook-based CRM system. However, MX-Sync does not require MX-Contact, and can synchronize to standard Exchange Server Public Folders, either discrete (i.e. non-related folders), or linked folders that form part of any Outlook-based Contact Management or CRM system.

MX-Sync can synchronize all 5 types of Outlook folders to and from a SQL Server database, namely Contact-type folders, Calendar, Task, E-Mail and Journals folders. It supports Outlook custom fields, so key ID fields that exist in the SQL tables can be synchronized to custom fields in the corresponding Outlook folders so that the relationships between the tables in SQL can also be maintained in the Outlook folders. That is, these same ID fields can be used as linking fields between the Outlook items, or used to build standard Outlook object links between items.

go to top of page

6. Managing Outlook Contacts with MX-Contact

If you’re looking for an application to assist you with managing a contact list of some kind, whether it be customers, prospects, members, suppliers or whatever, check out MX-Contact. MX-Contact is a CRM, Contact Management and Sales Automation package that runs inside Microsoft Outlook 2000, 2002 or 2003. The system utilises all the standard functionality of Outlook but provides many additional features that transform Outlook into a powerful Contact Management and CRM system.

MX-Contact has 7 different versions available catering for a single user through to an enterprise with thousands of users, with data storage in an Outlook Data File, Exchange Server Public Folders, or Microsoft SQL Server.

MX-Contact comprises a Base System, with optional Sales, Marketing and Support modules that can be added at any time. So you can use MX-Contact just for managing the contacts and/or companies you deal with, and the interactions (phone calls, e-mails, appointments, etc.) you have with them, plus use it to manage your sales opportunities, events and/or customer support incidents, by adding any of the available modules.

go to top of page

7. Summary

As we have seen there are both advantages and disadvantages to utilizing Outlook for maintaining your contact database. But synchronizing this data to and from a SQL Server database overcomes these disadvantages and maximizes the strengths of both platforms.

go to top of page

8. About the Author

This article was written by Brian Drury, founder of ExchangeWise (www.exchangewise.com), and the architect of MX-Contact (www.mxcontact.com), a CRM, Contact Management and Sales Automation System for Microsoft Outlook. Brian has over 20 years experience in the IT industry and has focused on Contact Management, CRM and Collaboration systems for the last 14 years. During this time Brian and his staff have been involved in over 200 direct CRM project implementations covering 7 different products.

go to top of page