Store Exchange 2010 Emails in SQL

Question

I have an application which will start a program flow with the arrival of an email. I will need to link the email to my application, which I'm going to do via a custom property on the message.

I will then need to store the email for reference forever. As Exchange 2010 supports only 10Gb and 100,000 items in a mailbox (without using PST files), I need to get the message into a more permanent store, SQL.

I'm running SQL 2005 and I want to store the email in there but not as an image, so I can search it if I need to. As I am using Exchange Web Services to get the email I already have the full XML for the message. I figure storing it in a XML field which has the Message XML schema associated to it (to help with performance) should give me the best solution.

My issue is getting the XML schema for a Message. I can't seem to find it anywhere and there doesn't seem to be much online content for putting messages into SQL.

Am I going about this all wrong or is there a better solution for me? The mailbox is forecast to receive over 600k emails a year.

Any help or assistance will be happily received.

Thanks, Mike

Answer 1

you can store it in an XML column, index that column and search through it.

as for the message xml schema, there probably is one but i can't find it.

Answer 2

I used XMLSpy to generate a schema based on the XML file. It won't be perfect but it works for me.

XMLSpy generated more than the below but it was too restrictive and also SQL didn't validate it. So I took out some of the extras and kept it nice and simple.

Here is the MessageType XML Schema it generated;

<?xml version="1.0" encoding="UTF-8"?>
<!--W3C Schema generated by XMLSpy v2010 rel. 3 (x64) (http://www.altova.com)--&gt;
<xs:schema xmlns:n1="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
    <xs:import namespace="http://schemas.microsoft.com/exchange/services/2006/types" schemaLocation="messagetype1.xsd"/>
    <xs:element name="MessageType">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="n1:ItemId"/>
                <xs:element ref="n1:ParentFolderId"/>
                <xs:element ref="n1:ItemClass"/>
                <xs:element ref="n1:Subject"/>
                <xs:element ref="n1:Sensitivity"/>
                <xs:element ref="n1:DateTimeReceived"/>
                <xs:element ref="n1:Size"/>
                <xs:element ref="n1:Importance"/>
                <xs:element ref="n1:IsSubmitted"/>
                <xs:element ref="n1:IsDraft"/>
                <xs:element ref="n1:IsFromMe"/>
                <xs:element ref="n1:IsResend"/>
                <xs:element ref="n1:IsUnmodified"/>
                <xs:element ref="n1:DateTimeSent"/>
                <xs:element ref="n1:DateTimeCreated"/>
                <xs:element ref="n1:DisplayCc"/>
                <xs:element ref="n1:DisplayTo"/>
                <xs:element ref="n1:HasAttachments"/>
                <xs:element ref="n1:Culture"/>
                <xs:element ref="n1:EffectiveRights"/>
                <xs:element ref="n1:LastModifiedName"/>
                <xs:element ref="n1:LastModifiedTime"/>
                <xs:element ref="n1:IsAssociated"/>
                <xs:element ref="n1:WebClientReadFormQueryString"/>
                <xs:element ref="n1:ConversationId"/>
                <xs:element ref="n1:Sender"/>
                <xs:element ref="n1:IsReadReceiptRequested"/>
                <xs:element ref="n1:ConversationIndex"/>
                <xs:element ref="n1:ConversationTopic"/>
                <xs:element ref="n1:From"/>
                <xs:element ref="n1:InternetMessageId"/>
                <xs:element ref="n1:IsRead"/>
                <xs:element ref="n1:ReceivedBy"/>
                <xs:element ref="n1:ReceivedRepresenting"/>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>