5.4 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) on JBoss, WebLogic, and WebSphere.

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.1 Set up SQL Server for LiveCycle

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

    5.4.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.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.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.1.4 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.

    5.4.2 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 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.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] and click OK.

      Note: The schema name should be the same as the [database_username].

    11. (SQL Server 2008 R2) In the Schemas Owned By This User area, select the schema [database_username].

    12. Click Server > Databases > [database_name] > Security, right-click the [database_username] schema, and select Properties > Permissions.

      1. Click Search in Users or Roles and type [database_username]and click OK.

      2. In the Explicit tab, grant the following permissions:

        • Alter

        • Insert

        • Reference

        • Select

        • Update

        • Delete

      3. Click OK.

    13. Right-click Server > Databases > [database_name], right-click the [database_username] schema, and select Properties > Permissions.

      1. In the Explicit tab, grant Create Table, Create View, and Connect permissions.

      2. Click OK.

    5.4.3.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 public is selected in the Database Role Membership For[database_name] table and then click OK.

    5.4.3.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