SQL mode (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.
During the processing of a campaign, JungleMail uses SharePoint lists to store information about each recipient used and all links used in a campaign. After a campaign is sent, JungleMail records every email open and/or link click to the JungleMail Tracker Actions list. This can result in a big 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.
That is why in JungleMail Enterprise edition the SQL mode was introduced. By saving the recipient history, tracker links and tracker action record to the
Note: this feature is only available in JungleMail Enterprise. To activate SQL mode you will need the following permissions: be a SharePoint Farm admin and Site Collection Admin.
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
- JungleMail uses the current site Application Pool Identity to read data from the database
to displaycampaign 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
1. Enabling Database Storage
To enable Database storage, open your SharePoint Site Settings. In Site Collection Administration, click EnovaPoint JungleMail Settings. Now scroll down. At the bottom of the page, you will find the SQL Database Storage section. Here you can enable or disable data storage in a SQL database.
2. SQL Database Storage configuration
By clicking Configure SQL Database Storage, you are given the following settings.
Once you have enabled the storage, JungleMail will fill in the Database server with the current SQL server name of your SharePoint. If you wish, you can change the server name.
JungleMail will also fill in the database name with the default value JungleMail_ServerName. You can change the default name to one that fits in with your general administration.
Note: default authentication (Windows authentication) makes use of the current IIS application pool authentication.
3. Creating a new database
To create a new database, simply enable the SQL Database Storage. Then enter the correct Database server and Database name. To finish up, check the box for Create new database and click Save:
A new database will now be created:
4. Migrating existing data from default 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 is created or later as described in this step.
Make sure the checkbox Create new database is unchecked and Migrate JungleMail History and Tracking data from SharePoint 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 will depend 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. 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.
Once your data is migrated you can clean the migrated lists mentioned above or even delete them and re-activate JungleMail core feature.
Deleting SQL data
There are several ways to delete the JungleMail data and clear up the SQL database.
- 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 Recycled Bin, Deletedcolumn is set to 0.
Each JungleMail SQL table has Deleted column.
Later you can run SQL query manually to clear up the database and delete these marked records.
- Delete the SQL data manually leaving 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.
- 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).
- There are no foreign keys in the database.
- You can filter Job SQL records by using “JobGuid” SQL column. This SQL column value equals to value stored in the SharePoint “Job Unique ID” column in the “JungleMail Jobs” list (you can add it to the view).
- It is recommended to delete information in this sequence: JungleMailTrackerActions, JungleMailTrackerLinks, JungleMailHistory.
- If you would like to delete “old” jobs, it is easier to use #1 option – filter Jobs by date, delete them, clear SQL data.
- If you think your queries (filtering) are slow, try to create indexes for your queries.
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