How to migrate the reporting database from Microsoft Access / Firebird to Microsoft SQL

Answer

This article explains how to migrate the GFI MailEssentials reporting database from Microsoft Access / Firebird to Microsoft SQL.

IMPORTANT: This procedure is not applicable on a reporting database of GFI MailEssentials 2012 or earlier due to different database schema.

NOTE: The same procedure can also be applied to migrate to Microsoft SQL Express ( download: here ).

The procedure to import the reporting data from a Microsoft Access database into a Microsoft SQL database requires the following steps:

1. Create a new database in Microsoft SQL server
2. Import the reporting data from the Microsoft Access / Firebird database into the Microsoft SQL database
3. Configure GFI MailEssentials to start using the new database

NOTE: In order to ensure that all new reporting data is stored in the new above mentioned Microsoft SQL database, please stop the following services before proceeding with the steps below:

• IIS Admin Service (, if platform is Microsoft IIS or Microsoft Exchange 2003)
• Microsoft Exchange Transport Service (, if platform is Microsoft Exchange 2007 or 2010)
• GFI MailEssentials Legacy Attendant Service (for GFI MailEssentials 2010 and older)
• GFI MailEssentials Antispam Attendant Service (GFI MailEssentials 2012)

By stopping the services above, this will temporarily stop your mail flow. The services will be started once again at the end of the procedure.

Configure a new database in Microosft SQL server

You can create a new database from the Microsoft SQL Server Management Studio. This can be done by performing the following:

1. Open the Microsoft SQL Server Management Studio
2. Enter the server name and the credentials to connect to the Microsoft SQL Server and then click on Connect
3. Right click on the Databases node and select New Database
4. Enter the name of the database such as MailEssentials_Reports and click OK to create the database

NOTE: Should you be using Microsoft SQL Server Express, the Microsoft SQL Management Studio Express is not installed by default. You can download this separately at the following links:

• Microsoft SQL Server Express 2005
• Microsoft SQL Server Express 2008

Import the reporting data from the Microsoft Access database into the Microsoft SQL database

The procedure to import the Microsoft Access database to a Microsoft SQL Database differs depending if you are using Microsoft SQL Server or Microsoft SQL Server Express Edition. Use the appropriate procedure.
 

Microsoft SQL Server

1. Right click on the newly created database in Microsoft SQL Server Management Studio and select Tasks > Import Data
2. Click Next button to proceed with the wizard
3. In the Choose a Data Source dialogue box, select Microsoft Access as the Data Source type enter the path to the Microsoft Access file used as the current GFI MailEssentials Reporter database as seen in the screen shot below:
   
   
   
   Click Next to proceed.
    
4. Enter the appropriate information to access the SQL Database created above, as shown below:
   
   
   
   Click Next to continue.
    
5. Select the option Copy data from one or more tables or views and click Next to continue
6. Select all the tables as follows:
   
   
   
   Click Next to continue.
    
7. Select to Run immediately and click Next to proceed
8. Click on Finish to import all tables into the database
9. The Import process will now run and complete as shown in the screen shot below:
   
   
    
10. Click Close and the import process is completed

Microsoft SQL Server Express Edition

1. Open the GFI MailEssentials reporting database you wish to export using Microsoft Access
   
   NOTE: By default the reporting databases are stored in the following path <GFI\MailEssentials\Data>
    
2. The Database tables are displayed once the mdb file is opened as seen in the screen shot below:
   
   
   Microsoft Access 2003
   
   
   Microsoft Access 2007
    
3. Right click on the first table gfi_dle_db_verion and select Export
4. Set the Save as type to ODBC Databases ()
5. A dialogue box will appear to confirm the name of the Table. Do not make any changes to the name and click OK to continue
   
   
    
6. In the Select Data Source Wizard, click New to create a new Data Source
   
   
    
7. From the list, select SQL Native Client as the driver and click on Next to proceed
8. Enter the name for the ODBC connection and click Next and then select Finish
9. A new wizard will appear which will allow you to define the information for the SQL Server Express Instance.
10. Enter the name of the SQL Server instance. For SQL Express this is normally <MACHINE_NAME\SQLEXPRESS>. Click on Next to continue.
    
     
11. Enter the credentials required to connect and update the database table. It is recommended to us the SA account. Click Next to continue
12. Check the Change the default database to: option and select the GFI MailEssentials Reporter Database created previously. Click Next to proceed
    
    
     
13. Click on Next once again click on the Test Data Source button. You should receive TESTS COMPLETED SUCCESSFULLY! and then click OK
14. In the Select Data Source window, select the Data Source you have just created and click on OK
15. You might be prompted to enter the password for the SQL Server. Enter the appropriate password and click OK to export the data into the SQL Server
    
    NOTE: This procedure needs to be done for every single table in the reporter database. You are able to use the same Database Source created previously when importing the remaining tables.

Configure GFI MailEssentials 2012 to start using the new database

1. Open the GFI MailEssentials Configuration
2. Open the Reporting > Settings node
3. Select the "SQL Server" option and specify the SQL Server settings
4. Configure GFI MailEssentials to use the SQL Server and SQL database created above:
   
   
    
5. Once GFI MailEssentials is properly configured with the new SQL database, start the following services once again:
   • IIS Admin Service (If platform is Microsoft IIS or Microsoft Exchange 2003)
   • Microsoft Exchange Transport Service (If platform is Microsoft Exchange 2007 or 2010)
   • GFI MailEssentials AV Attendant Service

Configure GFI MailEssentials 2010 or older to start using the new database

1. Open the GFI MailEssentials Configuration
2. Expand Email Management and right click on the Reporting node and select Properties
3. Click on the Configure button
4. Configure GFI MailEssentials to use the SQL Server and SQL Database configured above:
   
   
    
5. Once GFI MailEssentials is properly configured with the new SQL database, you can start the following services once again:
   • IIS Admin Service (If platform is Microsoft IIS or Microsoft Exchange 2003)
   • Microsoft Exchange Transport Service (If platform is Microsoft Exchange 2007 or 2010)
   • GFI MailEssentials Legacy Attendant Service

NOTES:

• You are now able to view any previous data stored in the old reporting database.
• When using Microsoft Access as reporting database, GFI MailEssentials will automatically recreate a new database should the MDB file reach a size of 1.7GB. This is done to ensure the database does not get corrupt due to the size limitation for Microsoft Access Databases of 2GB. Should you wish to view information which is stored in old Microsoft Access databases, you are able to import this data into an SQL Server database.
• If you have a number of GFI MailEssentials Reporter databases, the procedure discussed above can be done to import all your data into one single SQL database.
• Once the procedure above is complete, GFI MailEssentials will start storing new data in the Microsoft SQL Server database configured.