If you are a SQL Server 2005/2008 DBA tasked with configuring a database for Adobe LiveCycle ES/ES2, 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) or newer : 800 MB, 1 GB is better
The collation used should not be case-sensitive. If you get this error:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name ‘inctime’
make sure that the collation is set to ‘SQL_Latin1_General_CP1_CI_AS‘ (Code Page 1252, Case Insensitive, Accent Sensitive). See here for more details.
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 IBM 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). For this to work, you need to use version 1.2 of Microsoft’s JDBC driver for SQL Server. It 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).
If the J2EE appserver is Oracle WebLogic, make sure that you use version 1.2 (non-xa) of Microsoft’s JDBC driver for SQL Server. Also, the default values filled in by WebLogic for URL and Driver Class Name might not be correct. If you get errors such as follows, please make sure that the URL is in the following format: jdbc:sqlserver://serverName:portNumber.
weblogic.common.resourcepool.ResourceSystemException: The driver com.microsoft.sqlserver.jdbc.SQLServerDriver does not accept URL jdbc:microsoft:sqlserver://
Also, make sure that the Driver Class Name is com.microsoft.sqlserver.jdbc.SQLServerDriver (you can verify this by looking inside the sqljdbc.jar file using WinZip, WinRAR or 7-Zip).
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:
To determine the amount of storage used by the LiveCycle database, run the following system stored procedure: