SQL Storage (Enterprise feature)

By default JungleMail stores all data necessary for processing and tracking email campaigns in SharePoint lists. Using the standard configuration for sending big, 5000+ newsletter campaigns, can result in longer processing times and performance degradation of SharePoint and JungleMail in the long run without appropriate maintenance.

SharePoint vs. SQL

After a campaign is sent, JungleMail records every email open and/or link click to the JungleMail Tracker Actions list in SharePoint. This can result in a large number of records created, especially for internal newsletter campaigns. To illustrate: if your campaign was sent to 1000 recipients, contained 5 links, has an 80% open rate and a 60% click rate (when 3 links were clicked on average) then:

• 1000 history items will be created during the sending process to JungleMail History list
• 6 items will be created in JungleMail Tracker Links list (5 for the links + 1 to track email opens)
• 800 open action records will be created in the JungleMail Tracker Actions list
• 1800 click actions will be created in the JungleMail Tracker Actions list, assuming that 3 links were clicked only one time

So in total, one campaign can result in 3606 list items stored throughout these 3 lists. This is why Farm SQL Database Storage was introduced. By saving the recipient history, tracker links and tracker action record to the SQL database you can increase your sending speed and improve the user experience.

How does the database access work?

• JungleMail uses Central Administration Application Pool Identity to create and update the database and to migrate data. It should be called SP Farm account.
• JungleMail uses the current site Application Pool Identity to read data from the database to display campaign analytics information. It should be called SP Content account.
• JungleMail uses SharePoint Timer Service Account to read and write data to the database while it is processing campaigns. It should be called SP Farm account.

Configuration

1. In SharePoint, select Settings > Site settings.

2. Under Site Collection Administration, select EnovaPoint JungleMail Settings.

3. In the Enterprise features section, click Configure Farm SQL Database Storage.

4. In the window that opens, select Enable SQL Database Storage. JungleMail will fill in the Database server field with the current SQL server name of your SharePoint. You can change the server name. JungleMail will also fill in the Database name field with the default value JungleMail_ServerName. You can change the default name to one that fits in with your general administration.

5. Check the Create new database box and click Save.

A new database is now created.

Migrating existing data from JungleMail lists

If you want to migrate data from your existing JungleMail lists, you have the option to do this during the database creation process or later. Make sure the Create new database is unchecked and the Migrate JungleMail History and Tracking data from SharePoint box is checked. Once you click Save, the database migration will start.

JungleMail will now migrate the data from your SharePoint to your SQL database. The duration of the migration depends on the size of the lists. It can take several hours to migrate all your data from SharePoint JungleMail History, JungleMail Tracker links, and JungleMail Tracker Actions lists.

There are no limits to the number of items you can migrate. You can terminate migration at any time and start it again to the same database or a new one. The migration does not delete or change SharePoint items. If you start the migration to the same database again it will start from the beginning, checking each item again: if items are already migrated they will be skipped.

Deleting SQL data

Once your data is migrated you can clean the migrated lists mentioned above or even delete them and re-activate JungleMail core feature. There are several ways to delete the JungleMail data and clear up the SQL database.

1. Delete Job in SharePoint and clear up the database (JungleMail 4.0+ version). When you delete the Job in SharePoint and it is moved to the SharePoint Recycled Bin, JungleMail also marks corresponding database records as Deleted (Deleted column is set to 1). If you restore JungleMail Job from the Recycle Bin, the Deleted column value is set to 0.
   Each JungleMail SQL table has the Deleted column.
   Later you can run SQL query manually to clear up the database and delete these marked records.
2. Delete the SQL data manually, leaving a job in SharePoint. You can delete all history and analytics information (from all 3 tables) from the database manually without deleting the Job. If you do this, analytics information won’t be available anymore, all counts will be 0, but you will be able to see the job in JungleMail.
3. Delete the SQL data manually and delete the job in SharePoint. If you want to completely delete the job, you can optimize #1 option and delete all job records from the database before deleting the job in SharePoint. You will avoid unnecessary database updates (“Deleted” column).

Here is sample query to clear up deleted records from the JungleMail database:

DELETE FROM dbo.JungleMailTrackerActions WHERE Deleted = 1 GO DELETE FROM dbo.JungleMailTrackerLinks WHERE Deleted = 1 GO DELETE FROM dbo.JungleMailHistory WHERE Deleted = 1 GO

Sample query to delete one Job data from the database:

DELETE FROM dbo.JungleMailTrackerActions WHERE JobGuid = 'a0186648-6e49-4896-8fae-c7b33587f7e9'  GO  DELETE FROM dbo.JungleMailTrackerLinks WHERE JobGuid = 'a0186648-6e49-4896-8fae-c7b33587f7e9'  GO  DELETE FROM dbo.JungleMailHistory WHERE JobGuid = 'a0186648-6e49-4896-8fae-c7b33587f7e9'  GO