Set up SQL Server database

This topic provides configuration information for using SQL Server databases with B2Bi, including:

About the SQL Server database

The B2Bi installer supplies the driver that is required to work with this database. You do not have to supply a database driver. After B2Bi installation, you can find database drivers at
<B2Bi_install_directory>\Interchange\corelib.

Important: You must create the database before B2Bi is started for the first time.

SQL Server clustering support

Microsoft SQL Server provides two variations of high-availability solutions:

  • Failover Clustering
  • AlwaysOn Availability Groups

SQL Server tuning guidelines

Add a database instance

For SQL Server it is not possible to configure the database URL with an instance using either the B2Bi installer or the database configuration tool.

To configure an SQL Server database instance:

  1. After you install B2Bi, go to ../Interchange/conf and open datastoreconfig.xml in a text editor.
  2. In datastoreconfig.xml. replace
  3. <ConnectionURL>jdbc:jtds:sqlserver://{Host}:{Port}/{DatabaseName}
  4. ;SelectMethod=Cursor;sendStringParametersAsUnicode=false;socketTimeout=600</ConnectionURL>
  5. with:
  6. <ConnectionURL>jdbc:jtds:sqlserver://{Host}:{Port}/{DatabaseName}
  7. ;instance=name_of_the_instance;SelectMethod=Cursor;
  8. sendStringParametersAsUnicode=false;socketTimeout=600</ConnectionURL>
  9. Save the file.

Active Directory domain user

User name syntax

If your SQL Server database user is an Active Directory domain user, use the following syntax for the user name:

user@domain

User access permissions

By default, SQL Server grants access only to domain administrators. You must manually add all other users in the SQL Server security configuration.

The following minimum permissions are required:

  • db_datareader
  • db_datawriter
  • db_ddladmin
Note   Neither the system role DBO or the database role Db_owner is required.

Set the isolation level

The SQL Server isolation level setting (SET READ_COMMITTED_SNAPSHOT) controls where locks are taken, when data is read, and what type of locks are requested. It also controls how long the READ locks are held. Incorrect settings can lead to deadlock errors and READ time out errors.

Be sure to set READ_COMITTED_SNAPSHOT to ON. To implement this setting:

  1. Stop B2Bi.
  2. To check the current setting, run the following query on the SQL Server database :
  3. SELECT name, is_read_committed_snapshot_on FROM sys.databases;
  4. To enable the is_read_committed_snapshot setting, run the command:
  5. ALTER DATABASE [<databasename>]

    SET READ_COMMITTED_SNAPSHOT ON;

  6. Restart B2Bi.

Performance issues

If you notice B2Bi performance is slow due to the SQL Server:

  1. Open the SQL Server Database Maintenance Plan Wizard and locate the Update Data Optimization Information window.
  2. Select all three options on the window.
  3. Set up a schedule (for example, every hour).
  4. If you set up a short interval schedule, on the Specify the Database Backup Plan window, turn off the backup option.

Warning: Any database must allow a minimum value of 800 for the “maximum connections” attribute.

SQL Server database setup procedure

To setup the SQL server database:

  1. Create a database for the server in your database application, including a user name and password. Make sure the user has complete administrator rights to read, write, and modify.
  2. Install B2Bi (if it is not already installed).
  3. Check the database connection settings before you start the servers.
  4. See Check database connections.
  5. To make database connection changes, run the B2Bi Server installer in configure mode and make the required changes. See Server installer configure mode.
  6. Start the servers. When the servers start, they connect to the database and generate the required tables.
  7. You can use your database application to confirm the tables are added.

Related Links