5.4.3 Creating a SQL Server database

You can create a SQL Server database that LiveCycle will use to store run-time and configuration data. For information about creating a SQL Server database, refer to the SQL Server documentation. LiveCycle supports SQL Server 2005 SP2 and 2008 R2 (English and Japanese).

Create a SQL Server database, and create a user account and assign it DB_OWNER privileges for use when configuring the data source on the application server. For information about creating the database and user, see the SQL Server documentation.

You need the following information when you configure the data source on the application server:

  • Database name

  • User name and password of the SQL Server user account

  • Host name or IP address of database server

  • SQL Server port number

5.4.3.1 Set up SQL Server for LiveCycle

Before you create the LiveCycle database, optimize SQL Server by changing these settings.

5.4.3.1.1 Increase memory

The default SQL Server settings do not aggressively allocate memory. This situation significantly affects performance on most deployments of a SQL Server database.

Note: This section is recommended but optional.
  1. Using Microsoft SQL Server Management Studio, connect to the database server where you will host the LiveCycle database.

  2. Right-click the database server connection and select Properties.

  3. Select the Memory page and enter a size in the Minimum Server Memory (in MB) box that is equal to the size of the free memory on the server.

  4. Restart the SQL Server database.

5.4.3.1.2 Set the processor priority

On dedicated database servers, which are recommended for production installations of LiveCycle, the SQL Server process is configured so that it does not consume too much of the system CPU resources.

Note: This section is recommended but optional.
  1. Using Microsoft SQL Server Management Studio, connect to the database server where you will host the LiveCycle database.

  2. Right-click the database server connection and select Properties.

  3. Select the Processors page and select Boost SQL Server Priority.

  4. Restart the SQL Server database.

5.4.3.1.3 Increase the recovery interval

This setting specifies the amount of time the deployment waits for recovery after a crash. The SQL Server default setting is one minute. Increasing this setting to a larger value improves performance because it causes the server to write changes from the database log to the database files less frequently. This setting does not compromise the transactional behavior; however, it does affect the size of the log file that is replayed on startup.

Note: This section is recommended but optional.
  1. Using Microsoft SQL Server Management Studio, connect to the database server where you will host the LiveCycle database.

  2. Right-click the database connection and select Properties.

  3. Select the Database Settings page and type 5 in the Recovery Interval (Minutes) box.

  4. Restart the SQL Server database.

5.4.3.2 Integrated security

Note: This is an optional configuration.

If you are using SQL Server integrated security, you can set your SQL Server database to Mixed Mode or Windows Authentication Mode. However, if you are using Windows Authentication Mode, you must configure integrated security on Windows to establish a trusted connection with SQL Server (see 5.4.3.2.1 Map the Windows login to the LiveCycle database user).

Note: Configuration Manager fails to validate the database connection if you use Windows Authentication Mode. Therefore, for partial turnkey installation, the authentication type must be set to SQL Server. You can turn on the Windows Authentication Mode once Configuration Manager completes validating the database connection.

5.4.3.2.1 Map the Windows login to the LiveCycle database user

  1. Using Microsoft SQL Server Management Studio, connect to the database server that hosts the LiveCycle database.

  2. Set the Authentication mode to Windows authentication.

  3. Under Security > Logins, create a new account for the Windows domain user and select Windows authentication.

  4. Click User Mapping on the Login - New screen and set the database and default schema for the new user.

  5. Select db_owner as the Database role and click OK.

To verify that you created the user, expand the LiveCycle database in the tree and open Security > Users. The new user appears in the list of users.

5.4.3.2.2 Install the SQL Server database driver

Note: If you have not done so already, download the SQL Server JDBC Driver 3.0 (for all platforms) from the Microsoft Download Center location on the Microsoft website. Ensure that you install the SQL Server database driver on the server where you plan to install LiveCycle.
Follow the instructions on the website for downloading and installing the driver. Make a note of the directory location where you install the driver on your system.
Note: Use SQL Server JDBC Driver 3.0 for both Microsoft SQL Server 2005 SP2 and Microsoft SQL Server 2008.

5.4.3.2.3 Configure integrated security on Windows

Note: You must run the JBoss service as the Windows user configured in 5.4.3.2.1 Map the Windows login to the LiveCycle database user. If JBoss is not running as a service, you must log in to Windows as that particular user before you start JBoss from a command prompt.
  1. Modify the adobe-ds.xml and mssql-ds.xml files, located in [appserverroot]\server\lc_sqlserver\deploy, to add integratedSecurity=true to the connection URL as shown in this example: jdbc:sqlserver://<hostname>:<port>;databaseName=<db-name>;integratedecurity=true

  2. Replace the values shown in bold in the above example with values appropriate to your database server.

  3. Add the sqljdbc_auth.dll file to the Windows systems path (C:\Windows) on the computer that is running JBoss. The sqljdbc_auth.dll file is located within the Microsoft SQL JDBC 3.0 driver installation The default location is [SQL_root]/sqljdbc_3.0/enu/auth/x86 for 32-bit operating systems and [SQL_root]/sqljdbc_3.0/enu/auth/x64 for 64-bit operating systems.
    Note: Use SQL Server JDBC Driver 3.0 for Microsoft SQL Server 2008.
  4. Open the properties for the JBoss for Adobe LiveCycle ES3 service or the JBoss service that you configured, and click the Log On tab.

  5. Select ThisAccount and type the value of the user account that you mapped in 5.4.3.2.1 Map the Windows login to the LiveCycle database user. If you are running JBoss from the command line, you must be logged in as this user.

5.4.3.3 Sizing your SQL Server database

The default database sizes that SQL Server provides are too small for LiveCycle. Even if the database is set to auto-grow, unintended effects can occur, such as reduced performance when the database grows or the growth begins to fragment the disk. It is best to preallocate the database size at creation to reflect your deployment requirements:

Medium size deployments: Environments where the LDAP directory has approximately 100,000 users and 10,000 groups. Set Database Data Initial Size to 1 GB, and set autogrowth to 250 MB.

Large size deployments: Environments where the LDAP directory has approximately 350,000 users and more than 10,000 groups. Set Database Data Initial Size to 2 GB, and set autogrowth to 1 GB.

Note: Database growth is always restricted to a certain size. Administrators should monitor the resource usage of the LiveCycle database to ensure that it does not lose its restricted space or the space available on the disks where the database resides.

5.4.3.4 Creating the LiveCycle database user, schema, and login

You are now ready to create the LiveCycle database user, schema, and login.

Important: Ensure that you use the SQL_Latin1_General_CP1_CI_AS collation (or the Japanese_CI_AS collation if your database will run in a Japanese environment) when you create the database instance for LiveCycle. Any other collation may cause your database initialization to fail. The collation for your LiveCycle database instance can be different from the collation used when creating the SQL Server database.
  1. Using Microsoft SQL Server Management Studio, click Server, and then right-click Database and select New Database.

  2. Enter the database name of your choice.

    Note: The database name is very important, and the name chosen must be consistently used in the following procedures where a reference to database_name exists.
  3. (SQL Server 2005 Only) Ensure that Use Full-Text Indexing is deselected.

  4. In the Database Data Initial Size MB box, enter the appropriate value:

  5. In the Database DataAutogrowth box, enter 50%.

  6. In the Database LogInitial Size box, enter the appropriate value:

  7. In the Database LogAutogrowth box, enter 50%.

  8. Click OK to create the database.

5.4.3.4.1 Create the LiveCycle user in SQL Server

In the following procedure, [database_name] represents the name you specified when you created your database, and [database_username] represents the name you must specify for the new user.

  1. Using Microsoft SQL Server Management Studio, connect to the database server where you created the LiveCycle database.

  2. Click Server > Security, and then right-click Logins and select New Login.

  3. Enter the login name [database_username], and then select SQL Server Authentication and type a new password.

  4. Ensure that Enforce Password Expiration, User must change password on next login is also deselected.

  5. Leave the default database as Master, and click OK.

  6. Click Server > Databases > [database_name] > Security, and then right-click Schemas and select New Schema.

  7. In the Schema Name box, type [database_username], and click OK.

  8. Click Server > Databases > [database_name] > Security, and then right-click Users and select New User.

  9. In the New User dialog box, type the login name and user name [database_username].

  10. Set the default schema to [database_username].

    Note: The schema name should be the same as the [database_username].
  11. In the Schemas Owned By This User area, select the schema [database_username].

  12. In the Database Role Membership area, select db_owner.

5.4.3.4.2 Associate the LiveCycle user with the database

After you create the LiveCycle user, associate it with the LiveCycle database.

  1. Click Security > Logins, and then right-click [database_username] and select Properties.

  2. In Login Properties, on the General page, set the user’s default database to [database_name].

  3. Select the User Mapping page and, in the Users Mapped To This Login section, verify that [database_name] is selected, User is set to [database_username], and Default Schema is set to [database_username].

  4. Ensure that [database_name] is selected in the Users Mapped To This Login table, and ensure that db_owner and public are selected in the Database Role Membership For[database_name] table and then click OK.

5.4.3.4.3 Set the isolation level for the LiveCycle database

LiveCycle requires a specific isolation level to manage deadlocking when long-running transactions occur at the same time as numerous shorter reads.

Important: You must set the isolation level for MS SQL Server to avoid deadlocking issues.
  1. Click Databases, and then right-click [database_name] and select New Query.

    Note: [database_name] represents the name you specified when you created your database.
  2. In the Query panel, type the following text:

    ALTER DATABASE [database_name] SET READ_COMMITTED_SNAPSHOT ON GO

  3. Click Execute. A response is displayed in the messages panel.

// Ethnio survey code removed