Preparing the AE Database - MS SQL

This topic guides you through the steps necessary to set up a MS SQL database for the Automation Engine.

This page includes the following:

Overview

Notes:

Disk Space Required

The initial database space required for an Automation Engine installation depends on the type and size of the system:

For more information, see AWA System Requirements and Sizing.

Other Requirements

Installation settings for SQL Server (to be configured on the DB computer, i.e. the computer where the database is installed):

Prerequisites for ILM

You can partition your database with ILM to guarantee the optimum performance of your Automation Engine system. MS SQL requires additional permissions to work with ILM:

Setting Up an MS SQL database

  1. Create a new database (DB computer).

    1. Start SQL Server if it has not already been started, see ServiceManager.

    2. In the SQL Server Management Studio, create a new database.

      Name the database "AE" to better identify it at a later stage.

      The size of the transaction log should be about 25% of the data-file size (for test systems with truncate log). To change the size go to Database Properties > Files.

      Important! Do not activate the Auto Shrink option in the database as it may occasionally cause an Automation Engine standstill

  2. Create a new login (DB computer).

    1. Enter a login name.

    2. Select SQL Server Authentication.

      Important! The database password cannot include the special characters [ ] { } ( ) , ; ? * = ! @ \. Otherwise, the components cannot access the database.

    3. On the User Mapping page, select the AE database.

      Select the roles below:

        db_backupoperator

        db_datareader

        db_datawriter

        db_ddladmin

        db_owner

        public

      A new user is created automatically and is available in the Security > Users folder of the database.

    4. On the Extended Properties page, use either Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS (CP 1252).

    5. Make sure the database is running with versioning. If not run:

      alter database xxxx set read_committed_snapshot on

  3. Set up the data source on the Admin computer (computer used by an administrator) for ODBC access:

    Important! Set the SQL cursor (1st digit=S) or to activate MarsConnection in the INI file parameter for the ODBC access (SQLDRIVERCONNECT= ) in the particular component (Automation Engine, utility). Otherwise, the SQL Server database can only process one command at at time, which results in the following error:
    U0003590 DB error: 'SQLExecDirect', 'ERROR ', 'HY000', 'Connection is busy with results for another command'

    It is possible to create the data source as a User DSN (user specific) or as a System DSN (once for all users of this computer). However, it is best to create it as System DSN.

    Follow the ODBC Data Source Administrator wizard to create the data source as System DSN.

    Notes:

    • Note that the SQL native client is required in order to use the AE database with MARS. You can download it from the Microsoft homepage if it is not yet installed on your computer.
    • When selecting the SQL Server, select local if the SQL Server is on the same computer, otherwise enter the name of the DB computer.
    • Select SQL Server Authentication keeping in mind that it is best to create a separate database user for AE. Do not use the user "sa" (system administrator).
    • When creating a new data source to SQL Server, do not select the checkbox Perform translation for character data.

    Potential problems:

    • 32-Bit ODBC is used instead of 64-Bit ODBC.
    • The check box "Use ANSI nulls, paddings and warnings" is not activated.

See also: