Configuring a Microsoft SQL Server database for the IBM UrbanCode Deploy server
To use an SQL Server database with the IBM® UrbanCode® Deploy server, install the SQL Server database, install the IBM UrbanCode Deployserver, and then provide the driver file for the server installation process.
- You must specify case-sensitive collation for the database.
- If you are evaluating IBM UrbanCode Deploy, you can install the database on the same system as the IBM UrbanCode Deploy server.
- When you install IBM UrbanCode Deploy, you need the
SQL Server connection information, and a user account with table-creation privileges.
Note: The user account cannot have the sysadmin role.For example, the commands to create a database, create an SQL server user, and configure the database, might look like the following example:
CREATE DATABASE db_name; USE db_name; CREATE LOGIN db_name WITH PASSWORD = 'password'; CREATE USER db_user FOR LOGIN db_user WITH DEFAULT_SCHEMA = schema_name; CREATE SCHEMA schema_name AUTHORIZATION db_user; GRANT ALL TO db_user;Note: It is recommended that you change the database to a case-sensitive collation.
- Before starting the IBM UrbanCode Deploy server, the
Microsoft SQL Server must have the READ_COMMITTED_SNAPSHOT setting ON. To enable
snapshot, execute the statement below while there are no clients connected to the named
ALTER DATABASE db_name SET READ_COMMITTED_SNAPSHOT ON
- The SQL Server compatibility should be set to 110.
- Enable the Always ON availability group feature to use high-availability for recovery or failover. Refer to the SQL Docs for a detailed procedure.
Configure a Microsoft SQL Server database:
Obtain the SQL Server JDBC driver files from the Microsoft™ site.
When multiple drivers are available, you might need to consult with the database provider to determine which driver to use. The JDBC driver version that you must use might depend on the version of the database and the version of Java™ used by the IBM UrbanCode Deploy server.
- Put the JDBC JAR file to installer_directory\lib\ext.
- Begin server installation. See Installing the server. When you are prompted for the database type, enter sqlserver.
- Provide the JDBC driver class IBM UrbanCode Deploy uses
to connect to the database.The default value is
- Next, provide the JDBC connection string.The format depends on the JDBC driver. Typically, it is similar to the following code:
jdbc:sqlserver://DB_URL:DB_PORT;databaseName=DB_NAMEFor example, the connection string to database that is on the same computer as the IBM UrbanCode Deploy server and that uses the default port resembles the following code:
jdbc:sqlserver://localhost:1433;databaseName=ucdIf your database server uses integrated security, include the integratedSecurity parameter, as in the following example:
jdbc:sqlserver://database_server:1433;databaseName=ucd;integratedSecurity=true;Note: Before using the
integratedSecurity=trueoption, place the
sqljdbc_auth.dllfile in the
\bindirectory of your JRE. The file can be found in the
\auth\x64directory of the SQL Server installation files.
- Finish by entering the database user name and password.
Before you start the server, log into the database and run the following command:
Use the name of the database for
ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON
- Start the server.