← Active Archive InstallationDownload and Extract scheduler-<version>-archive-win.exe →

Configure the Database for Active Archive

This section tells you how to configure the database for Active Archive.

Active Archive is supported on Oracle 11.2 to 18.3 and Microsoft SQL Server 2012 to 2017.

Active Archive requires a database (schema on Oracle) for each catalog as well as a database/schema for the control database. Although the control database can reside in the same database/schema as a catalog, this is not recommended. The control database is the central database containing global settings, only one is required per Active Archive installation.

note

Redwood recommends you keep the control catalog in its own database on SQL Server.

note

To use one database for multiple catalogs on SQL Server, you need to create a SQL Server Login for each catalog and map it to a schema. Every catalog user must have the db_owner role assigned.

Oracle Database

A database with the default settings is recommended.

You create a user for each catalog as well as an additional one for the control database.

Create users with the following privileges:

  • Unlimited quota on the tablespace (Archiving Server uses the Default tablespace for this connection user)
  • CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE TABLE, CREATE TRIGGER, CREATE VIEW

Example

To create users for the catalog and control database with the required privileges:

$ sqlplus system/mypassword@orcl
SQL> create user example identified by mypassword
  2 default tablespace users
  3 quota unlimited on users;
SQL> grant create procedure, create sequence, create session, create table, create trigger, create view to example;
SQL> create user archive_control identified by mypassword
  2 default tablespace users
  3 quota unlimited on users;
SQL> grant create procedure, create sequence, create session, create table, create trigger, create view to archive_control;

Note that in this example, orcl is the connect string, example is the user for catalog Example, archive_control is the user for the control database, and users is the tablespace. In Oracle 12c and later, Redwood recommends you install Active Archive in a pluggable database.

Note, also, that mypassword is not a very safe password and just used here for illustration purposes.

Oracle Recommendations

As of Oracle 12c, you now have the option to have a container database (CDB) and one or more (up to 252 in Enterprise Edition) pluggable databases (PDB's). The architecture resembles SQL Server instances where you can have multiple independent databases in any given instance.

Oracle recommends you install an application in a pluggable database (PDB) as there are no performance impacts nor functional differences between a PDB and a non-CDB database. Note that you can plug and unplug a PDB with ease, allowing greater database management capabilities.

Also, a single PDB comes at no additional costs in each Standard Edition, Standard Edition One, and Enterprise Edition.

Note that you connect to a PDB via its service name; connecting via instance name actually connects to the CDB. The CDB and all PDB's share the same instance name. Database user/schema names in the CDB must have a specific prefix.

See the Oracle Multitenant White Paper and the Oracle 12c Licensing Information documents which are available on Oracle website for more information.

At the time of the writing, these database capabilities are not available in Oracle Express Edition.

Microsoft SQL Server

Create a control database and the initial catalog database.

The databases must have the following properties:

  • Database instance must have the TCP/IP protocol enabled and listening to a fixed port.
  • Mixed-mode authentication must be enabled
  • SQL Server Recovery Model should be set to Simple
  • An SQL Server Instance installation type requires that the SQL Browser Service is set to Automatic and is running.
  • Database collation order of SQL_Latin1_General_CP1_CS_AS

Configure the Database Server for Active Archive

Enable Mixed-mode authentication

  1. Open and log into the SQL Server instance with SQL Server Management Studio, you must log into the instance where the database will be running.
  2. Choose Properties from the root node, usually named <server>\<instance> (SQL Server <version> <user>, for example EXAMPLE\SQLEXPRESS (SQL Server 10.50.1600 -sa.
  3. In the Server Properties dialog, choose Security.
  4. Make sure SQL Server and Windows Authentication mode is selected.
  5. Choose Ok to close the dialog.

Configure SQL Server to Listen on a Fixed Port

  1. Open Sql Server Configuration Manager.
  2. Expand SQL Server Network Configuration.
  3. Under Protocols for <instance>, choose Enable from the context-menu of TCP/IP if the TCP/IP protocol is disabled.
  4. Choose Properties from the context-menu of TCP/IP.
  5. On the IP Addresses tab, under IPALL, make sure a valid TCP/IP port is specified for TCP Port, for example 1433.
  6. Choose Ok to close the dialog.
  7. Exit Sql Server Configuration.

Creating a Database in SQL Server Management Studio

  1. Open and log into the SQL Server instance with SQL Server Management Studio.
  2. Choose New Database from the context-menu of Databases.
  3. On the General page, fill in a database name. If this database is to become a control database, do not forget the _control suffix which is highly recommended.
  4. On the Options page select SQL_Latin1_General_CP1_CS_AS as the default collation order.
  5. Select the owner for the database.
  6. Make sure Recovery model is set to Simple; this is optional but highly recommended for performance reasons.
  7. Choose Ok to close the dialog and create the database.

Create a user with the following privileges:

  1. Open and log into the SQL Server instance with SQL Server Management Studio.
  2. Expand Security.
  3. Choose New Login.
  4. On the General page, select SQL Server authentication and fill a username into the Login name field. Set a password for this user and confirm it. Do not use your company name or the name of one of your products as a password!.
  5. Uncheck Enforce password expiration (optional but recommended) and User must change password at next login.
  6. Select the default database for this user, it should be one of the Active Archive databases.
  7. On the User Mapping page, for each database the Active Archive will use select its name and select db_owner in the Database role membership for: <username> field.
  8. Note that you can fill in a Default Schema for this user; this allows you to use one database for multiple catalogs, each catalog mapped to a login and a schema.
  9. Choose Ok to create the user.

Example Using sqlcmd

The below example has a very weak password and should not be used verbatim! Note, also, that the below example was used in powershell and that it can be used as such in cmd.exe, provided the password is changed to something safer.

PS C:\Users\example>sqlcmd -S MSQL1\SQLEXPRESS -Usa
password:
1>create login archive with password = 'archive123';
2>create user archive for login archive;
3>grant create database to archive;
4>go
5>exit
PS C:\Users\example>sqlcmd -S MSQL1\SQLEXPRESS -Uarchive
1>create database archive_control collate SQL_Latin1_General_CP1_CS_AS;
2>create database archive collate SQL_Latin1_General_CP1_CS_AS;
3>go

See Also

← Active Archive InstallationDownload and Extract scheduler-<version>-archive-win.exe →