LiveCycle for the SQL Server 2005 DBA

If you are a SQL Server 2005 DBA tasked with configuring a database for Adobe LiveCycle ES 8.0.1, the following scripts will help. You can run them using SQL Server Management Studio in the following order as the Instance-wide Administrator (sa). Make appropriate changes for your server envrionment.

Best practice (from a LiveCycle perspective) calls for:
1) the instance login, the database user and the schema to be exactly the same
2) this to be less than or equal to 12 characters in length
3) this to not contain any characters other than letters and numbers (no hyphens)

Also, SQL authentication is preferred over Windows authentication.

Minimum required storage is:
LC ES (8.0.1) : 300 MB, 500 MB is better
LC ES Update 1 (8.2.1) : 800 MB, 1 GB is better

The collation used should not be case-sensitive.

Create Database
Create Instance Login
Create Database Schema
Create Database User
Alter the Schema
Add dbowner role to Database User

Please note: The Database Instance "Login" name and the LiveCycle Database "User" name have to be the same for LCM bootstrapping to work, especially on IBM WebSphere.

If the J2EE appserver is WebSphere, please make sure that the JDBC Data Source is defined with the user-defined data store helper class com.ibm.websphere.rsadapter.GenericDataStoreHelper. If you choose com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelper, you may get an "Illegal attempt to enlist multiple 1PC XAResources" exception during bootstrapping (Process Engine).

The Microsoft SQL Server JDBC Driver v1.2 is available for download here. You have to install the downloaded exe before you can access the single sqljdbc.jar file. Ensure that you do not use the xa version (in the \xa folder)

After LiveCycle has been completely configured (all users in your directory have been synchronized with the LiveCycle database), run the following system stored procedure to update database statistics:
sp_updatestats;

To determine the amount of storage used by the LiveCycle database, run the following system stored procedure:
sp_spaceused;