Versions Supported

Connexion will run on Sql Server 2008R2, 2012, and 2014, from the Express to Data Center versions. Each version of Sql Server has different operating characteristics. Which one you choose will depend on the size, performance and availability requirements of the system. 

Connexion Database Types

Connexion utilizes 2 different types of databases: a single configuration database, for storing the system configuration information; and 1, or more, message storage databases for storing the message and message processing information.

The server and database management screen is shown in Figure 1. The configuration database is the labeled "Active", and the 2 message databases are labeled "Default" and "LongRunning".

Within Sql Management Studio these databases appear as shown in figure below. Here are a few additional things to note:

1. All Connexion databases are prefixed with CXN_.
2. The name of the main Connexion configuration database is set during installation time (see Figure 2)
3. Each message database is prefixed with the name of the main configuration database to which it belongs. This was done to allow users, and scripting tools to easily identify which databases belong to a particular Connexion deployment.
4. Multiple instances of Connexion can be hosted by a single Sql Server instance. As shown in the Sql Management UI below, there are 2 instances of Connexion: CXN_Connexion, and CXN_Connexion2.

Figure 1

Figure 2

Configuring New Message Databases

It can be advantageous to create additional message storage databases beyond the default message database. Here are some reason you might consider creating additional message databases

1. From a backup, index maintenance standpoint, a few smaller databases are easier to maintain than a single large database
2. If you are using Sql Express the maximum size of a database is 10GB. You can create additional databases to store more messages.
3. Better performance - if you have the hardware, you can have different databases located on different spinning disks, or even different Sql Server instances
4. Data separation - In a data center environment it may be advantageous to have different customers messages hosted in different databases
   
   

Additional message databases can be added by clicking on the "Create/Attach Message Repository" as shown below.

Database Maintenance

Connexion has been designed to automatically perform the necessary database maintenance tasks to maintain the performance and integrity of the system. This includes database backup, purging of old records from the database, index maintenance, and database integrity checks.

The database maintenance settings can be found by clicking on the main Connexion configuration database, or one of the message databases (see Figure 1).

Figure 3

Connexion Database Maintenance User

Connexion uses a separate database account for performing database maintenance. This is separate from the account that Connexion uses for normal operation. Due to the nature of the tasks that need to be performed, this account needs a higher level access. The tasks and required perfmissions are as follows"

1. Clean up: read/write
2. DatabaseBackup: sysadmin
3. DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

4. IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

    

The the settings for configuring elevated user credentials for performing database maintenance can be found in the Server and Database Management screen shown below.

Default Maintenance Schedule

The database back up routine has the following schedule:

1. Nightly at the "Backup Time" of day
   1. the Cleanup_CXN script is run - this script deletes old messages from the database and performs other nightly clean up tasks
   2. a differential back up is performed
   3. index maintenance is performed is performed on all tables (details can be found here https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html)
2. Weekly at the "Backup Time" of day, on the "Full Back Day" of the week
   1. the Cleanup_CXN script is run
   2. an index maintenance is performed on all tables (details can be found here https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html)
   3. a full backup is performed