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:
- Please note that MS SQL Server is not supported when the Automation Engine is running on Linux. Take a look at the Automic Compatibility Matrix for more information about compatible applications, platforms & databases.
- You can integrate an alternative MS SQL database schema and split your database in two. For more information, see Integrating an Alternative MS SQL Server Schema.
- Database administration rights are necessary to perform this step
- Refer to the reference section of the AE database in order to optimize performance. For more information, see: Recommendations for MS SQL and the CDA System Requirements and Sizing.
- Do no activate the option "autoshrink" in the database. This can occasionally cause an Automation Engine standstill.
- Do not limit resource consumption. Aborting transactions due to limitations specified in the database can impede processing in the AE system. Additionally, inconsistent database contents can result, which can cause subsequent errors or endless loops.
- It is important to 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 the time which results in the following error:
U0003590 DB error: 'SQLExecDirect', 'ERROR ', 'HY000', 'Connection is busy with results for another command' - You can maintain the AE database by using partitioning with ILM (Information Lifecycle Management). For more information read Automation Engine Database before you set up the database.
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 CDA 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):
- 64-bit client must be installed.
- Use standard Sorting.
- Terms are case-insensitive (alphabetical order, regardless of case).
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:
- 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
-
Create a new database (DB computer).
-
Start SQL Server if it has not already been started, see ServiceManager.
-
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
-
-
Create a new login (DB computer).
-
Enter a login name.
-
Select SQL Server Authentication.
Important! The database password cannot include the special characters [ ] { } ( ) , ; ? * = ! @ \. Otherwise, the components cannot access the database.
-
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.
-
On the Extended Properties page, use either Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS (CP 1252).
-
Make sure the database is running with versioning. If not run:
alter database xxxx set read_committed_snapshot on
-
-
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: