How do I migrate my users' SQM Safe Senders and Blocked Senders lists to a new database?

This article applies to:

• Trustwave MailMarshal (SEG)

Question:

How do I migrate my users' Safe Senders and Blocked Senders lists to a new database?

Background:

The SQM (Spam Quarantine Management) system in MailMarshal stores user Safe Senders and Blocked Senders lists in the SQL database. In order to migrate your users' Safe Senders and Blocked Senders lists to a clean database, you will need to export the data from the existing database, import it into the new database, and force the Controller services to re-read the users' lists. This article assumes you are using SQL Express. The same steps can be followed for Microsoft SQL Server. The article also uses the terms "Array Manager server", "SQL server", and "mail processing nodes". These may be hosted on the same server or separate servers.

Note: Ideally you will create the clean database and immediately migrate the Safe Senders and Blocked Senders lists before rejoining nodes to the array. In this case the copy of user data on the processing servers will not be altered. If you already joined the nodes to the array and data was synchronized from the new database before migration of the user information, you will need to re-synchronize it as described below.

Be aware of the following limitations:

• You can only perform this procedure with two MailMarshal databases that are of the identical version.
  • Both databases must have been created (or upgraded) by the identical version of SEG/MailMarshal SMTP, for instance, 6.8.4.9558.
• The four SQM user tables in the destination database will be cleared and the data replaced.
  
  • This procedure should be done as soon as possible after changing the database, ideally before any SQM or digest release activity is logged.
  • If safe or blocked senders have already been logged to the new database these changes will be lost.
• You must ensure that the identity field on the User table is preserved. The information in the other tables keys off this field. If you simply copy the data into the table, you will lose the links between users and their lists.
• The following procedure uses the SQL Import and Export Data wizard. This Wizard is available in SQL Server and SQL Express 2008 and above.

Procedure:

1. Stop the Array Manager service on the Array Manager computer, and the Controller service on all mail processing nodes within the array.
   • Note: Stopping the Controller also stops the Engine, Receiver, and Sender. Messages will not be accepted or processed.
2. If you need to create a new database, do so now using the Server Tool. Do not rejoin nodes to the array.
3. On the old SQL Express server, start the SQL Import and Export Data wizard from the Start menu (or from SQL Management Studio, right click the MailMarshal database and select Tasks > Export Data).
4. On the Choose a Data Source page, if necessary select the source SQL server/instance name (such as localhost\sqlexpress) and the database. Click Next.
5. On the Choose a Destination page, select the destination SQL server/instance and database.
   
   • This could be the new MailMarshal database. If you cannot connect to the new MailMarshal database, create a new temporary database that you will use to transfer data to the new server.
   • Click Next.
6. Select Copy data from one or more tables. Click Next.
7. On the Select Source Tables and Views page, choose (check) the tables User, UserAlias, UserDelegate, and UserList.
8. For each of the above tables:
   • Select the table in the list, and click Edit Mappings
   • If the destination is a temporary database, choose Create destination table
   • If the destination is a MailMarshal database, choose Delete rows in destination table
   • IMPORTANT: For the User table only, also check the box Enable identity insert
   • Click OK.
9. Click Finish. Verify the choices, and then click Finish again.
10. If you copied directly to the new MailMarshal database, this completes the SQL export and import. If you copied to a temporary database, now copy or move the temporary database to the new SQL server and run the wizard on this server. The source is the temporary database and the destination is the new MailMarshal database.
11. On the Array Manager server, start the Array Manager service. If you do not need to re-synchronize the lists to the mail processing nodes, migration is complete; rejoin the nodes to the array, and start the services on the nodes.
12. To force a re-synchronization of the lists to the mail processing nodes, follow these steps on each mail processing node in the array:
    • For version 6.8 and above, navigate to the NodeConfig folder and delete the file WhitelistManager.db
    • For earlier versions, open regedit and change the value of HKEY_LOCAL_MACHINE\Software\Marshal\MailMarshal\Node\WhitelistLastUpdate to "0".
      • For full details of the folder and Registry locations for each version, see article Q10832.
13. On each mail processing node in the array, start the Controller service. You can also start the Sender.
14. The update of the lists will begin within a few minutes. This activity is logged to the Controller log file as "Updating WL/BL" for each user. For very large sites this update can take a considerable time to complete.
15. Once the updating activity is complete, start the Engine, Receiver, and Sender services.

Notes:

• If you do not apply user Safe Sender lists to the Receiver based SpamProfiler detection, you can start the Receiver with the Controller.
• If email flow is more important than applying users' safe and blocked senders, you can start all services with the Controller.
• It is always best practice to back up MailMarshal configuration and databases before making changes.
• Before altering the Registry, back up the Registry.

This article was previously published as:

NETIQKB50958