1. SQL Server Information
1.1. How-To: Enable Full Text Indexing for SQL Server Express
 

Product: All

Version & Build: All (required for 4.5 and above)

 

 

Background:

 

If you use Microsoft SQL Server (2000, 2005 or Express), full text indexing must be installed.  Full text indexing is required when upgrading to modus v4.5.

 

 

Procedure:

 

If SQL Server Express is already installed, proceed with the following instructions:

 

  • From the Control Panel, double-click on Add or Remove Programs
  • Select Microsoft SQL Server 2005 and click on Change
  • From the Component Selection menu, select SQLEXPRESS: Database Engine and click on Next
  • From the Feature Maintenance menu, select Database Engine
  • Click on Next until you arrive at the Change or Remove Instance menu (this may take a few minutes)
 
 
 
  • At the Feature Selection menu, expand Database Services, select Full-Text Search and click on Next until the installation completes

 

Note: 

 

·          If the SQL Server Express installation files are not available (i.e. they were deleted), you will be prompted for the installation files’ directory

·          Go to http://msdn.microsoft.com/vstudio/express/sql/download/ to download the SQL Express 2005 Advanced Services SP2 files

·          From a command prompt, type sqlexpr_adv.exe –x <enter>

·          When prompted, enter a directory into which the files will be extracted (e.g. c:\temp\sqlexpress)

·          Open Windows Explorer and  browse this directory

·          In the directory selection dialog, open the setup directory (e.g.: c:\temp\sqlexpress\setup) and double-click on  sqlrun_sql.msi

·          Upon completion of the installation, go to the SQL Server Configuration Manager where you should see a new SQL Server FullText Search (SQLEXPRESS) service

 


 
 
·          Restart all of the SQL Server services to enable full text indexing

 

 
1.2. Known Issue: SQL Server Express Setup Problems on Windows 2000 Server

 

Product:  All

Version & Build: 4.45+

 

 

 

Modus v4.45 includes SQL Server Express as part of its installation package.  This article provides the necessary steps to follow should you encounter problems when installing SQL Server Express on Windows 2000 Server. 

 

 

1.3. Info: Extended Database Format for ModusMail


Product:
ModusMail

Version & Build: 4.1.361 + up

 
Modusmail Extended Database Schema
 

The ModusMail Extended Database format supports most of the mailbox information and settings at the User level.  If you are using this format for mailbox authentication, please verify prior to installation that your database fields match the attached schema; otherwise mismatch errors can occur. 
 
NOTE: Mailbox aliases are not yet configurable in the database - they must be created in the Modus administration console: User's mailbox properties > Aliases tab.
 
Important Notes:
 
1. The table name must be called VOPMAIL (upper case).
 
2. Index the Username and Domain fields to help reduce processing time.
 

3. The attached database schema has been updated to increase the size of the auto responder / vacation and forwarding address fields.  If you wish to increase the size of these or any other fields without dropping and recreating the table, use the following procedure (instructions are for SQL):

  • Increase the size through the Enterprise Manager
  • Run the following script using the Query Analyzer:

alter table VOPMAIL

alter column auto_message varchar(255)
go
alter table VOPMail

alter column forward_to varchar(255)
go
 
1.4. How-To: Configure the SieveStore Database in SQL Server

 

Product: All

Version & Build: 4.4.568 and below

 

Summary

This document explains how to use a SQL database instead of Access for SieveStore data.  The SieveStore database contains your custom sieve filters and any Whitelist/Blacklist entries.

 

Install Microsoft SQL Server

When installing Microsoft SQL server, specify that SQL authentication will be used (not Windows authentication).  Make sure to note the username and password you choose as you will need them later.  For example, the default user proposed by Microsoft is SA.

 

 

Configuring the Database on a Microsoft SQL Server

 

Creating the SieveStore DB


This Database will contain all custom rules, the Trusted Senders List and the Blocked Senders List:

  • Open the Microsoft SQL Enterprise Manager
  • Expand the tree in the left pane and open Databases
  • Right-click on Databases and select New Database
  • Name the database (e.g. SieveStore) and click on OK
  • Open the Query Analyzer (Menu Tools>Query Analyzer)
  • Select the newly created database (e.g. SieveStore)
  • Execute the scripts found in ...\Vircom\Modus<Mail or Gate>\DBStructures to create the tables (ScriptArchive, ScriptStatistic, ScriptDomainUser and SieveCatalog)
  • It is OK if an error occurs that some tables cannot be removed
  • Close the Query Analyzer and Microsoft SQL Enterprise Manager
  • Check that the  ScriptArchive, ScriptStatistic, ScriptDomainUser and SieveCatalog tables were correctly created in the SieveStore database

  

Importing Your Current Sieve Scripts, Trusted Senders List and Blocked Senders List:

 

  • In MS SQL Enterprise Manager, click on Databases
  • From the menu bar, click on Action > All Tasks > Import Data

o        The DTS Import/Export Wizard should appear  - click on Next

  • At Data Source, use the pull-down menu and select Microsoft Access
  • At File Name, browse for SieveStore.mdb, located in ...Vircom\Modus<Mail or Gate>\SieveData and click on Next
  • At Destination, use the pull-down menu to select a destination as your SQL server

o        The destination will be preset as your SQL server

  • Select Use SQL Server Authentication and click on Next
  • In the Specify Table or Query panel, select Copy table(s) and view(s) from the source database and click on Next
  • In the Select Source Tables and Views, select Select All and click on Next
  • Click on Next twice and Finish to complete the operation
  • The 4 tables should have been exported successfully

 


Configuring the SieveStore DB on the Modus Server

 

  • On your server, go to Administrative Tools > Data Sources (ODBC)
  • Click on System DSN
  • Click on Add
  • Select the driver for which you want to set up a data source (most likely SQL Server)
  • Click on Finish
    • Enter the name of the data source, its description and specify to which SQL Server you want to connect (likely the name of the SQL Server)
  • Click on Next and continue configuring your new DSN
  • Use SQL Authentication and the account created when installing SQL Server
    • Set the database as the default database (i.e. SieveStore)
  • Click on Finish
  • Ensure that the configuration is correct and click on Test Data Source to test the data source
  • If everything is OK, the message TESTS COMPLETED SUCCESSFULLY will appear - your data source is now properly created
  • Open the Modus Console and go to Spam – Properties – Sieve Database
  • Enter the name of the newly created System DSN, its user name and password
  • Click on Apply



 

NOTE:  If you were using another database for the Sieve DB before creating one in SQL Server and perform a spam update, there is a chance that you may get an error stating that there are no new updates.  This indicates that the the database is not being updated.
 
To resolve this issue, proceed with the following:
  • Open the Registry Editor
  • Go to HKEY_LOCAL_MACHINE\System\Modusadm\Parameters
  • Delete the SieveUpdateLUSN and SieveUpdateLUD keys
  • Exit the Registry Editor and perform another spam update

 

1.5. How-To: Use SQL Server Instead of the Built-in MDB Database for Quarantine storage


Product:
ModusMail

Version & Build: 4.1.361 + up



This how-to explains how you can set-up ModusMail or ModusGate to use MS-SQL 7 or MS-SQL 2000 database instead of the built-in Access/Jet-based database to improve the Quarantine performance.
 

There are 3 steps to this process:

1) Create the necessary tables in MS-SQL;
2) Create a system DSN on the machine running modus;
3) Point the modus Quarantine to the new database.


Step #1 - Create the necessary tables in MS-SQL

First, create the tables that Modus needs to store the Quarantine info in MS-SQL. 

Please use the script available in ...\Vircom\ModusMial\DBStructures to automatically create a new database with the tables included.  See New SQL 2000 script for Quarantine Database for details.

 

Open the SQL Script with Notepad:
a) Click on START;
b) Click on Programs -> Microsoft SQL Server -> Enterprise Manager;
c) Click on Tools -> SQL Query Analyzer;
d) Select the SQL server hosting the database you wish to use;
e) Cut and Paste the script to the Query Analyzer window (see screenshot);


f) Execute the script;
g)The script might generate two errors because it will try to delete existing tables of the same name
   (tblMsgCatalog and tblRecepients), ignore those messages;
h) Using SQL Enterprise Manager, browse the database in question and check to see if tblMsgCatalog
   and tblRecepients were created;
i) Note that when you create the system DSN on the next step, it must be an account defined in the SQL security,
   not an NT account. For instance, the default "SA" account would do fine;
j) Ensure that the specified SQL Account has enough rights to read/write information from/to tables
   ("tblMsgCatalog","tblRecipients"), used by the Quarantine mail store.


Step #2 - Create a system DSN on the machine running modus
 
a) Go to START -> Settings -> Control Panel -> Administrative Tools;
b) Double click on Datasources (ODBC);
c) Select System DSN and Put in a Datasource Name;
d) If the SQL server is installed on remote computers the authentication method should be
   "With SQL Server authentication using a login and password entered by the user".
   You cannot use Windows NT authentication with a remote SQL Server
   because the local SYSTEM account has no network credentials;
e) Put in the usual datasource username & password and click on Next;
f) Change the default database to the one containing the two datastore tables we created in step one
   and continue to click on next until you get to test the datasource. Make sure the test is successful.
 

Step #3 - Point the modus Quarantine to the new database

a) Go to the modus console -> system -> Quarantine database tab;
b) Type in the datasource name, username and password and hit apply;
c) At this point, modus should be able to talk to the MS-SQL server for it's Quarantine datastore.

 

 

Step #4 - Restart the MODUSCAN and MODUSADM services to force modus to use the new ODBC Driver

 
1.6. How-to: Prevent Quarantine-Related Problems

Product:
All
Version & Build: All
 
1.7. How-To: Configure SQL TCP stack
 

Product: All

Version & Build: All 

 

 

Symptoms:

 

At times Modus WebAdmin would fail to authenticate due to a connection or TCP stack error with the SQL server. The SQL TCP stack needs to reflect the information of the local server network configuration.  

 

 

Procedure:

 

Here are the steps to ensure the SQL TCP stack is configured correctly:


     1- For Windows server 2008R2 and lower, Open configuration manager under START - PROGRAMS - MICROSOFT SQL SERVER 20xx - CONFIGURATION TOOLS - SQL SERVER CONFIGURATION MANAGER.

  •  For Windows server 2012R2 and higher, On the desktop Right-Click on the start button and select SEARCH, in the search field type "SQL SERVER CONFIGURATION MANAGER"
  • Then from the list Right-Click on your select and choose "Run as Administrator on the list.

     2- In the SQL Configuration Manager expand the following SQL Server Network Configuration menu and Right-Click on TCP/IP and select properties.

     3- In the TCP/IP Properties page select the IP addresses tab and ensure that all the IP addresses listed represent the same local IP addresses of the server and ensure all interfaces are Active and Enabled.



     4- Once that is done restart SQL express agent so that the changes will take effect.



 
1.8. How-To: Move SQL database from one server to another
 

Product: SQL

Version & Build: All

 

 

 

Procedure:

 

It is recommend that both servers  to have the similar version of SQL install or upgrade to a more recent version. Down grade migration requires additional procedures.

 

  1. On the current version of SQL stop all services associated with SQL.

  2. Open windows explorer to locate the database .MDF and .LDF files and copy the files to its new server. normally located under "\MSSQL1X.SQLEXPRESS\MSSQL\DATA"

  3. Paste the .MDF and .LDF files to where the new SQL server will hold its data files. normally the same location under "\MSSQL1X.SQLEXPRESS\MSSQL\DATA"

  4. Open up SQL management studio on the server and right-click on DATABASE and select ATTACH.

  5. Click the ADD button and browser to the file location and select the .MDF file you copied.

  6. Click OK twice to have the databases imported.
  7. Then restart the SQL service.