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.

Notes:

• MS SQL Server is not supported when the Automation Engine runs on Linux.

• You can integrate an alternative MS SQL database schema and split your database in two. You need administration rights to do it.

  Important! You can create a system with an alternative scheme from scratch or you can convert an existing classic scheme to an alternative one.

• Do not limit resource consumption. Aborting transactions due to limitations specified in the database can impede processing in the AE system. This can also result in inconsistent database contents, which can cause subsequent errors or endless loops.

• When selecting the SQL Server, select local if the SQL Server is on the same computer, otherwise enter the name of the DB computer.

• When creating a new data source to SQL Server, do not select the checkbox Perform translation for character data.

More information:

• Automic Compatibility Matrix
• Automic Automation System Requirements and Sizing
• Recommendations for MS SQL
• Integrating an Alternative MS SQL Server Schema
  • Creating an Automation Engine System with the Alternative Scheme from Scratch
  • Converting a Classic Automation Engine Database Scheme to the Alternative One
• Automation Engine Database
• ILM - Information Lifecycle Management

Disk Space Required

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

• Test systems: 1 GB

• Production systems

  • Small systems: 5 - 20 GB

  • Medium systems: 20 - 50 GB

  • Large systems: > 50 GB

For more information, see Automic Automation System Requirements and Sizing.

Other Requirements

Installation Settings

The following settings for the SQL Server must be configured on the DB computer:

• A 64-bit client must be installed
• Use standard sorting
• Terms are not case-sensitive (in alphabetical order, regardless of case)

Prerequisites for ILM

You can maintain the AE database by using partitioning with ILM (Information Lifecycle Management) and guarantee optimum performance. For more information, see ILM - Information Lifecycle Management.

MS SQL requires additional permissions to work with ILM:

• SQL Server requires the VIEW SERVER STATE permission
• SQL Database requires the VIEW DATABASE STATE permission in the database

Setting Up an MS SQL Database

To set up an MS SQL Database you must first create the database and a new login in the computer in which the database should be installed. Then you have to set up the data source for ODBC access in the admin computer.

Creating a New Database

1. Make sure your SQL Server has been started.

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

Creating a New Login

1. Enter a login name.

2. Select SQL Server Authentication. Keep in mind that it is best to create a separate database user for AE. Do not use the user sa (system administrator).

   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. and select the following roles:

   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 the following command:

   alter database xxxx set read_committed_snapshot on

Setting Up the Data Source for ODBC Access

Important! In the INI file of the Automation Engine (see Automation Engine), set the SQLDRIVERCONNECT= parameter according to the type of ODBC driver you are using:

• Microsoft ODBC Driver

  If Mars_Connection= is set to Y, set the first position of ODBCVAR= to N. If not, set the first position of ODBCVAR= to S, otherwise you get the 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. To do so, follow the ODBC Data Source Administrator wizard.

Potential problems:

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