Following feedback that customers would like to make use of GFI MailArchiver with Microsoft SQL Server Express, with GFI MailArchiver 6.3 we included the ability to automatically roll-over from one database to another when the size limit on the database is reached – which will make using SQL Express with GFI MailArchiver much more feasible and manageable (since SQL Express has a 4GB size limit on a single database). This functionality is available for GFI MailArchiver “SQL (Express) + NTFS” archive stores (i.e. archive stores in which email metadata is held on SQL Express and emails are stored on disk).

On this vein, I thought about drawing up some guidelines for using SQL Express with GFI MailArchiver on virtual paper.

Deployment of SQL Express for use with GFI MailArchiver

Some of you might already have SQL Express deployed, so I will give you a couple of hints for things to make sure of before trying to use your SQL Express deployment with GFI MailArchiver. I also realize that some of you might not have SQL Express already deployed, so I will also briefly go through deploying it from scratch for our purposes. I will be using SQL Express 2008.

Before covering deployment itself of SQL Express, however, I have one recommendation for you, whether you’re deploying it from scratch or otherwise. Due to the nature of its task, GFI MailArchiver is a disk I/O intensive piece of software – it is continuously reading/writing emails to/from disk, it is continuously reading/writing data to/from database (which can itself be disk I/O intensive), and it is continuously indexing and performing searches on emails, which necessitates reading/writing to/from index files on disk (which also entails disk I/O). Customers tend to fall into the trap of storing emails, databases and indexes on one disk, sometimes even on the same disk on which the Microsoft Exchange Information Store databases are stored (which bring along with them even more disk I/O) – resulting in disk thrashing and severely degraded performance of the product. In order to avoid this, it is important to plan for a disk which is dedicated for the storage of your SQL Express databases. Should you wish to avoid having to grab another disk for this purpose, you might want to look at whether you have another machine on your network with some spare disk and CPU cycles on which you might want to deploy SQL Express instead. Given the flexibility offered by SQL Express you will probably find that indeed you do have such a machine (damn, I even installed it on a XP machine in my test environment!). That is an equally viable option with the added advantage of also offloading your server of some CPU cycles. But enough about that, let’s dive into what you need to do if you already have SQL Server Express deployed somewhere on your network.

If you already have SQL Server Express deployed on your network, it’s quite simple really:

  1. Make sure mixed mode authentication is enabled.
  • Fire up the SQL Server Management Studio console, right click on the SQL instance you’re going to use, and click ‘Properties’.

  • In the Server Properties dialog box, select ‘Security’ and enable ‘SQL Server and Windows Authentication mode’.

  • Click ‘OK’.

2. Make sure that the ‘SQL Server Browser’ service is started.

  • Fire up the SQL Server Configuration Manager console and select ‘SQL Server Services’. Should the ‘Start Mode’ for the ‘SQL Server Browser’ service not be set to ‘Automatic’, right click on it, click ‘Properties’, and on the ‘Service’ tab set ‘Start Mode’ to ‘Automatic’.
  • If the ‘SQL Server Browser’ service is currently stopped, right click on it and click ‘Start’.

3.    If SQL Express is installed on a different machine to your GFI MailArchiver machine, make sure that TCP/IP is enabled for the SQL Server instance you will use with GFI MailArchiver.

  • In the SQL Server Configuration Manager console, expand the ‘SQL Server Network Configuration’ node, and select ‘Protocols for SQLEXPRESS’.
  • Right click ‘TCP/IP’, and click ‘Enable’.

  • You will need to restart the SQL Server service for this change to come into effect. Select ‘SQL Server Services’, right click on the SQL Server service for the instance you’re working on, which will be ‘SQL Server (SQLEXPRESS)’ if you’re using the default instance, and click ‘Restart’.

4.    If SQL Express is installed on a different machine to your GFI MailArchiver machine, and you have the Windows Firewall enabled on the SQL Express machine, you will need to open access to the SQL Server Browser service, as well as configuring the SQL Server Express database engine service to listen on a specified port and opening access to that port.

To open access to the SQL Browser service, you will need to open access to UDP port 1434.

To set the SQL Server Express database engine service to listen on a specified port, follow the instructions at http://msdn.microsoft.com/en-us/library/ms177440.aspx – ‘How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)’ making sure that you clear ‘Dynamic Ports’ and set ‘TCP Port’ in ‘IPAll’ too like below. You will then need to open access to TCP on the port you configured. More information here: http://msdn.microsoft.com/en-us/library/ms175043(v=SQL.100).aspx – ‘How to: Configure a Windows Firewall for Database Engine Access)’.

You will also need to restart the SQL Server service for these changes to take effect.

5.    That’s it –  your SQL Server Express deployment is ready for use with GFI MailArchiver!

Deploying SQL Express from scratch

I don’t want to make this boring, so I’m not going to post a screen shot of each installation step… but I will give you some useful information and hints on the matter:

1.    To start off with, a comment about prerequisites – if the platform you’re installing on happens not to have them, you’ll need to install the following:

  • Microsoft Windows Installer 4.5
  • Microsoft .NET Framework 3.5 Service Pack 1
  • Microsoft Windows PowerShell

2.    If you’re installing SQL Server Express on an Active Directory Domain Controller machine, or on a machine on which the Windows Firewall is enabled, you might get some warnings in the ‘Setup Support Rules’ dialog as shown below.

  • With respect to installing on a domain controller, I will refer you to SQL Server 2008 Books Online (November 2009) > Hardware and Software Requirements for Installing SQL Server 2008 > Installing SQL Server on a Domain Controller: http://msdn.microsoft.com/en-us/library/ms143506.aspx#DC_Support. In short, however, if you’re installing on a domain controller you will need to run SQL Server services under the SYSTEM account or you’ll need to create an account for the purpose – other than that, SQL Server Express Setup will not block you from installing, and you shouldn’t have problems unless you’re planning to change the machine in question from a domain controller to a domain member, you’re installing to a read-only domain controller, or you’re looking at a SQL Server failover cluster.

The SQL Server 2008 Books Online section on the matter also mentions that Microsoft does not recommend installing SQL Server 2008 on a domain controller “for security reasons” – granted, you don’t want anyone who manages to break into your SQL Server machine to also have access to your domain controller… Furthermore, you will probably find that with a larger number of users (my guess being anything more than a 100 users) the usage patterns of active directory and SQL Server on the same machine will be such that they impact each other performance-wise. If any of this is of concern to you, always remember that you can easily install SQL Server Express on pretty much any machine with some spare disk and CPU cycles.

  • If you’re installing SQL Express on a different machine to your GFI MailArchiver machine, and you have the Windows Firewall enabled on the machine on which you’re installing SQL Server Express, you will need to open access to the SQL Server Browser service, as well as configuring the SQL Server Express database engine service to listen on a specified port and opening access to that port.

You will be able to do this once SQL Express is actually installed.

3.    Service accounts: When you get to the ‘Server Configuration’dialog, you will need to configure a couple of settings related to SQL Server Express services.

  • As per Microsoft recommendation, I chose to run the SQL Server Express services under separate accounts, so I created an account, ‘sqlexpress’, under which to run the ‘SQL Server Database Engine’ service.
  • Make sure you set ‘Startup Type’ for the ‘SQL Server Browser’ service to ‘Automatic’. This service makes it possible to discover a SQL Server instance on the network, so it needs to be started for GFI MailArchiver to be able to discover your SQL Server Express instance.

4.    In the ‘Database Engine Configuration’ dialog, make sure you select the ‘Mixed Mode’ authentication mode. You will also need to select a user to act as administrator for your SQL Server Express instance.


5.    If you’re installing SQL Express on a different machine to your GFI MailArchiver machine, once the SQL Express installer has completed successfully make sure that TCP/IP is enabled for the SQL Server instance you will use with GFI MailArchiver.

  • Fire up the SQL Server Configuration Manager console.
  • In the SQL Server Configuration Manager console, expand the ‘SQL Server Network Configuration’ node, and select ‘Protocols for SQLEXPRESS’.
  • Right click ‘TCP/IP’, and click ‘Enable’.

  • You will need to restart the SQL Server service for this change to come into effect. Select ‘SQL Server Services’, right click on the SQL Server service for the instance you’re working on, which will be ‘SQL Server (SQLEXPRESS)’ if you’re using the default instance, and click ‘Restart’.


6.    Almost forgot – if SQL Express is installed on a different machine to your GFI MailArchiver machine, and you have the Windows Firewall enabled on the SQL Express machine, you will need to open access to the SQL Server Browser service, as well as configuring the SQL Server Express database engine service to listen on a specified port and opening access to that port.

This can be done in exactly the same way as I described earlier in this post for existing installations of SQL Server Express.

7.    That’s it – you’ve deployed SQL Server Express and it’s ready for use with GFI MailArchiver!

In my next post I will run you through configuring GFI MailArchiver to make use of SQL Express and to automatically roll-over from one database to another when the 4GB size limit of the SQL Express database is reached.