Pre-installing the metadata repository database in a Microsoft SQL Server 2014 database system

You can use an existing installation of a Microsoft SQL Server 2014 database to create a metadata repository database for InfoSphere® Information Server.

Before you begin

If the SQL statement does not run, then you must add the path to the sqlcmd.exe application to your Path system variable. This application is typically located in one of the Microsoft SQL Server Tools\Binn directories. For example,

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn

About this task

Complete this procedure to enable XA transactions and to create the required database and tables before you run the InfoSphere Information Server installation program.

Procedure

Configure the Microsoft Distributed Transaction Coordinator to enable XA transactions:
1. Click Start > Run. (For Windows 8, right-click Start > Run.)
2. In the Run dialog, enter dcomcnfg and click OK.
3. Click Component Services > Computers > My Computer > Distributed Transaction Coordinator.
4. Right-click on Local DTC and click Properties.
5. Click the Security tab on the Local DTC Properties window.
6. Select the Enable XA Transactions check box and then click OK. A message pops up that says, The MS DTC service will be stopped and restarted. All dependent services will be stopped.
7. Click Yes to proceed.
8. Close the remaining open windows, then stop and restart SQL Server to ensure that the MSDTC changes take effect.

Install the XA stored procedures from Microsoft:

Download the Microsoft SQL Server JDBC Driver 4.2: https://www.microsoft.com/en-us/download/details.aspx?id=54671.
Run the executable file and follow the instructions to extract the contents to a temporary directory.

Locate the sqljdbc_xa.dll file. The version that you need to install depends on the version of SQL Server that you installed:

Microsoft SQL Server version	Directory
Microsoft SQL Server X86 (32-bit)	`temp_dir`\Microsoft SQL Server JDBC Driver 4.2\sqljdbc_4.2\enu\xa\x86
Microsoft SQL Server X64 (64-bit)	`temp_dir`\Microsoft SQL Server JDBC Driver 4.2\sqljdbc_4.2\enu\xa\x64
Microsoft SQL Server IA64 (64-bit Intel Itanium architecture)	`temp_dir`\Microsoft SQL Server JDBC Driver 4.2\sqljdbc_4.2\enu\xa\IA64

temp_dir is the temporary directory where you extracted the Microsoft SQL Server JDBC Driver 4.2.

Copy the sqljdbc_xa.dll file from the JDBC installation directory to the SQLServer_install_dir\Binn directory.
For example,
```
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
```
SQLServer_install_dir is the directory where you installed Microsoft SQL Server.
Open a command prompt and change to the following directory:
```
cd temp_dir\Microsoft SQL Server JDBC Driver 4.2\sqljdbc_4.2\enu\xa
```
where temp_dir is the temporary directory where you extracted the Microsoft SQL Server JDBC Driver 4.2.
Run the following command to start the xa_install.sql script:
```
SQLCMD -Usa -Ppassword -Slocalhost -ixa_install.sql
```
password is the password for the SQL Server system administrator user.
Note: You can safely ignore the following error that might display as a result of running this script:
- Cannot drop the procedure 'xp_sqljdbc_xa_init' because it does not exist or you do not have permission.

Install the XA stored procedures for the JDBC drivers:

Navigate to the following directory. (The ⇒ character indicates a line continuation.):
```
payload_dir\is-suite\DatabaseSupport\Windows\MetadataRepository\SQLServer2014
```
payload_dir is the directory where you extracted the InfoSphere Information Server installation media (is-suite.zip). For example, C:\Documents and Settings\Administrator\My Documents\Downloads\is-suite_build_number\is-suite\DatabaseSupport\Windows\MetadataRepository\SQLServer2014.
Extract the contents of SQLServer_JTA.zip to a temporary directory.

Locate the sqljdbc.dll file. The version that you need to install depends on the version of SQL Server that you installed:

Microsoft SQL Server version	Directory
Microsoft SQL Server X86 (32-bit)	`temp_dir`\SQLServer JTA\x86
Microsoft SQL Server X64 (64-bit)	`temp_dir`\SQLServer JTA\x64
Microsoft SQL Server IA64 (64-bit Intel Itanium architecture)	`temp_dir`\SQLServer JTA\IA64

temp_dir is the temporary directory where you extracted the SQLServer_JTA.zip file.

Copy the sqljdbc.dll file to the SQLServer_install_dir\Binn directory of every SQL Server computer that you want to participate in distributed transactions.
For example,
```
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
```
where SQLServer_install_dir is the directory where you installed Microsoft SQL Server.
Open a command prompt and change to the following directory:
```
cd temp_dir\SQLServer_JTA\SQLServer JTA\platform_dir
```
temp_dir is the temporary directory where you extracted the Microsoft SQL Server JDBC Driver 4.2.
platform_dir is the directory from step 3c where your platform is installed. For example, temp_dir\SQLServer JTA\x64\sqljdbc.dll is the directory for the AMD and Intel (not Itanium) 64-bit version of Microsoft SQL Server.
Run the following command to start the instjdbc.sql script:
```
SQLCMD -Usa -Ppassword -Slocalhost -iinstjdbc.sql
```
password is the password for the SQL Server system administrator user.
Note: You can ignore the following error message:
```
Msg 156, Level 15, State 1, Server HOSTNAME, Line n
Incorrect syntax near the keyword 'tran'.
```

Create the xmeta database:
1. Open a command prompt and change to the following directory. (The ⇒ character indicates a line continuation.):
```
payload_dir\is-suite\DatabaseSupport\Windows\⇒
  MetadataRepository\SQLServer2014
```
  payload_dir is the directory where you extracted the InfoSphere Information Server installation media (is-suite.zip).
2. Run the following command to create the xmeta database:
```
create_xmeta_db.cmd ServerName DbName UserName Password
```
  For example:
```
create_xmeta_db.cmd localhost xmeta xmeta myPa$$w0rd
```
  The create_xmeta_db.sql script creates data and saves log files in the default SQL Server installation location, C:\Program Files\Microsoft SQL Server. This directory might not provide optimal throughput and performance. To create the data and log files in a different directory, modify the create_xmeta_db.sql script to specify a different location.
  
  ServerName
  
  The name of the server where you are creating the database.
  
  DbName
  
  The name of the database that you are creating.
  
  UserName
  
  The database user name that you are using to create the database.
  
  Password
  
  The password for the database user.
  
  Important: Do not assign the sysadmin SQL Server role to the user name that you specified for UserName. Assigning this role to the database user name will cause the InfoSphere Information Server installation program to fail.
Create the schema for the staging area within the xmeta database:
1. Open a command prompt and change to the following directory:
```
cd payload_dir\is-suite\DatabaseSupport\Windows\MetadataRepository\SQLServer2014
```
  payload_dir is the directory where you extracted the InfoSphere Information Server installation media (is-suite.zip).
2. Run the following command to create the schema for the staging area. All arguments are required and must be specified in the order that is shown in the following example.
```
create_staging_schema.cmd localhost XmetaDbName SAUser SAPassword
```
  For example,
```
create_staging_schema.cmd localhost xmeta xmetasr password
```
  XmetaDbName
  
  The name of the xmeta database that you created.
  
  SAUser
  
  The user name that you are using to connect to the staging area schema. This name must be different from the user name that you use to connect to the xmeta database.
  
  SAPassword
  
  The password for the user that is connecting to the staging area schema.
  
  Important: Do not assign the sysadmin SQL Server role to the user name that you specified for SAUser. Assigning this role to the user name that you are using to connect to the staging area schema will cause the InfoSphere Information Server installation program to fail.
If you are using IBM® DataStage® Flow Designer, you must create the user preference table to store the user settings. For information on creating the user preference table, see the README document in the directory install_dir\DatabaseSupport\windows\CognitiveDesignerRepository\SQLServer2014.
Create the necessary IBM Information Server Enterprise Search schemas by running the create_ug_schemas.bat script from the command line. This step is required if during installation you choose to install IBM Information Server Enterprise Search.
1. Open a command prompt and change to the following directory:
```
cd payload_dir\is-suite\DatabaseSupport\Windows\InformationAnalyzer\SQLServer\ug
```
  payload_dir is the directory where you extracted the InfoSphere Information Server installation media (is-suite.zip).
2. Run the following command to create the IBM Information Server Enterprise Search schema. All arguments are required and must be specified in the order that is shown in the following example.
```
create_ug_schemas.bat HostServerName DatabaseName UserName Password
```
For example:
```
create_ug_schemas.bat hostname xmeta xmeta_user secret_password
```
HostServerName

Name of the server.

DatabaseName

Name of the database to use for the new schemas.

UserName

Database user used to log in to the sqlcmd utility to create the schema.

Password

Password for the database user.