Using Microsoft SQL Server for Print Release database

The Print Release database tables in Microsoft SQL Server are automatically created during installation. During installation, specify the Microsoft SQL Server database server information on the database screen. This process populates the database.properties file with the correct information automatically. In the same directory as the database.properties file, a backup copy of the database_mssql.properties file is stored. The database_mssql.properties file contains variable names that can be used as a template when formatting the database.properties file for Microsoft SQL Server. If you edit the database_mssql.properties for use, then rename it to database.properties.

Notes:

Only the database.properties file is used with the solution.
Before saving the database.properties file, stop the Lexmark Solutions Application Server service.

When switching from Firebird to Microsoft SQL Server after installation, create the Print Release database in Microsoft SQL Server manually. Delete all the backup files, and then run the LPM installer. Specify the Microsoft SQL Server database information on the database screen.

Note: The LPM data is not migrated to the new Microsoft SQL Server database.

Microsoft SQL Server supported versions

Microsoft SQL Server 2022
Microsoft SQL Server 2019
Microsoft SQL Server 2017
Microsoft SQL Server 2016
Microsoft SQL Server 2014

Before installing Microsoft SQL Server 2014 or 2012, from the Server Manager, add the following:

.NET Framework 3.5 features for Microsoft Server 2012
.NET Framework 3.5.1 features for Microsoft Server 2008

Adding Unicode to Print Release tables

From your computer, update the Print Release tables. For example, %ProgramFiles%\Lexmark\Solutions\lpm\ms-sql-script\migrate-ascii-column-data-to-unicode.sql.
Notes:
- Errors for the MP_PRINTERS table may occur when the table does not exist. If you are not using Email Watcher with Printer Nicknames, then comment out its lines in the script.
- When you use Print Release version 2.3.15 or later, errors for the dbo.SCHEMA_ELEMENTS table may occur when running the script on Print Release version 2.3.14. If you are using Print Release version 2.3.14, then comment out its lines in the script.
From Management Studio, connect to the database server as a database owner.
Right-click the Print Release database, and then click New Query.
In the new query window, paste the contents of the migrate-ascii-column-data-to-unicode.sql file.
Make sure that the Print Release database is selected, and then click Execute.

The varchar datatype is now updated to nvarchar in all Print Release tables, such as PR_JOBS.

Updating the database.properties file for Microsoft SQL Server default instances

During installation, LPM version 2.4 or later creates connection strings in the properties files. Create the Print Release database in Microsoft SQL Server manually, and then run the LPM standalone installer. This process populates the database.properties file with the correct information, and then creates the Print Release tables in Microsoft SQL Server automatically.

From Lexmark Management Console, set the application server offline.
From the application server, browse to the <install_path>\apps\printrelease\WEB-INF\classes folder, where <install_path> is the installation path of LPM.
Rename database.properties to database_backup.properties.
Using a text editor, open the database_mssql.properties file.
Do the following:
1. Search for ${SQLSERVER}, and then replace all instances with the network address of Microsoft SQL Server.
2. Search for ${SQLDB}, and then replace all instances with the Microsoft SQL Server database name that contains the Print Release database tables.
3. Search for ${SQLUSER}, and then replace all instances with the Microsoft SQL Server named user that has read-write-delete access to the Print Release database tables.
4. Search for ${SQLPW}, and then replace all instances with the password for the Microsoft SQL Server named user.
Name the file as database.properties.
Save the file.
Restart the Lexmark Solutions Application Server service.
After the Lexmark Solutions Application Server process (tomcat7.exe) has fully initialized, set the server online.
Repeat this process for all application servers.

Note: When only subsets of the Print Release tables are stored in Microsoft SQL Server, copy sections from the database_mssql.properties file to the database.properties file. For example, if only the Print Release statistics data in Microsoft SQL Server is necessary, then from the database_mssql.properties file, copy the database.STATS section. From the database.properties file, overwrite the same information.

Updating datasources for multiple databases

Changes in the database.properties file require updates in the LPM portal application server. By default, the LPM portal is configured with datasources that are called the default and secondary datasources. Database information in the datasource varies on the LPM setup. For example, LPM installed in a non-serverless setup points the default and secondary datasources to the same database. In a serverless setup, the default datasource points to the LPM Microsoft SQL Server database, and the secondary datasource points to the LDD Firebird database.

From Lexmark Management Console, set the application server offline.
From the application server, browse to the <install_path>\apps\lpm\WEB-INF\classes folder, where <install_path> is the installation path of LPM.
Using a text editor, open the database-production-config.properties file.

Update the database information pointed by the default or secondary datasource.

Sample code

dataSource.url = jdbc:sqlserver://<ip address>;databasename=LPMPremise;?lc_ctype=UTF-8
dataSource.driverClassName = com.microsoft.sqlserver.jdbc.SQLServerDriver
dataSource.dialect = org.hibernate.dialect.SQLServer2008Dialect
dataSource.username = lpmadmin
dataSource.password = tiger123
dataSource.properties.validationQuery = select 1
dataSource.pooled = true
dataSource.properties.maxActive = 15
dataSource.properties.maxIdle = 1
dataSource.properties.minIdle = 1
dataSource.properties.minEvictableIdleTimeMillis=5000
dataSource.properties.timeBetweenEvictionRunsMillis=60000
dataSource.properties.testOnBorrow=true
dataSource.properties.testWhileIdle=true
dataSource.properties.testOnReturn=true
dataSource.removeAbandoned=true
dataSource.removeAbandonedTimeout=180000

dataSource_secondary.url = jdbc:firebirdsql://<ip address>/3050:SOLUTIONINFO?lc_ctype=UTF-8
dataSource_secondary.driverClassName = org.firebirdsql.jdbc.FBDriver
dataSource_secondary.dialect = org.hibernate.dialect.FirebirdDialect
dataSource_secondary.username = framework
dataSource_secondary.password = ENC (mna6C4NkloGNVSx4ry08RA==)
dataSource_secondary.properties.validationQuery = select 1 from RDB$DATABASE
dataSource_secondary.pooled = true
dataSource_secondary.properties.maxActive = 15
dataSource_secondary.properties.maxIdle = 1
dataSource_secondary.properties.minIdle = 1
dataSource_secondary.properties.minEvictableIdleTimeMillis=5000
dataSource_secondary.properties.timeBetweenEvictionRunsMillis=60000
dataSource_secondary.properties.testOnBorrow=true
dataSource_secondary.properties.testWhileIdle=true
dataSource_secondary.properties.testOnReturn=true
dataSource_secondary.removeAbandoned=true
dataSource_secondary.removeAbandonedTimeout=180000

To add another datasource, do the following:

Copy the secondary datasource.
Replace secondary with tertiary or any suffix that is appropriate and unique.
Update the database information for the added datasource.

Add the password encryption codec for the added datasource.

Sample code

dataSource_tertiary.url = jdbc:firebirdsql://<ip address>/3050:SOLUTIONINFO?lc_ctype=UTF-8
dataSource_tertiary.driverClassName = org.firebirdsql.jdbc.FBDriver
dataSource_tertiary.dialect = org.hibernate.dialect.FirebirdDialect
dataSource_tertiary.username = framework
dataSource_tertiary.password = ENC (mna6C4NkloGNVSx4ry08RA==)
dataSource_tertiary.properties.validationQuery = select 1 from RDB$DATABASE
dataSource_tertiary.pooled = true
dataSource_tertiary.properties.maxActive = 15
dataSource_tertiary.properties.maxIdle = 1
dataSource_tertiary.properties.minIdle = 1
dataSource_tertiary.properties.minEvictableIdleTimeMillis=5000
dataSource_tertiary.properties.timeBetweenEvictionRunsMillis=60000
dataSource_tertiary.properties.testOnBorrow=true
dataSource_tertiary.properties.testWhileIdle=true
dataSource_tertiary.properties.testOnReturn=true
dataSource_tertiary.removeAbandoned=true
dataSource_tertiary.removeAbandonedTimeout=180000
dataSource_tertiary.passwordEncryptionCodec=com.lexmark.utils.PBEWithMD5AndDESCodec

If a new datasource is added, then from the application server, browse to the <install_path>\apps\lpm\WEB-INF\classes folder, where <install_path> is the installation path of LPM.
Using a text editor, open the app-production-config.properties file.

Update the database information that must point to the tertiary datasource.

Sample code

datasource.webapp = secondary
datasource.badge = DEFAULT
datasource.pin = tertiary
datasource.stats = DEFAULT
datasource.printernicknames = secondary
datasource.printtrackdevices = DEFAULT

Make sure that the updates in the LPM portal are the same as the values in the database.properties file.
Save the file.
Restart the Lexmark Solutions Application Server service.