Preparing a Microsoft SQL Server database

Set up a Microsoft SQL Server database before beginning an installation of InfoSphere® MDM.

About this task

To create the MDM database, you must be logged in to Microsoft SQL Server either with Windows Authentication or with the administrative user account that you created when you installed Microsoft SQL Server.

The user name that you use to log in will be used as the schema name during the InfoSphere MDM installation. The user name is case sensitive because SQL Server uses case sensitive collation. Accordingly, the user name that you provide in the IBM® Installation Manager panels during installation must exactly match the original case of the user name.

If you plan to use Windows authentication, your DBA must set the default schema of the login user to the schema that will be used by IBM Installation Manager.

The default schema assigned to a user who logs in using Windows Authentication is dbo. When Windows Authentication is used during the InfoSphere MDM installation, the database objects will be created in the schema named dbo.

Tip: When your SQL Server implementation uses Windows Authentication, do not enter the database user credentials during installation on Database Configuration panel in IBM Installation Manager.

Procedure

  1. Log in to Microsoft SQL Server either with Windows Authentication or with the administrative user account that you created when you installed Microsoft SQL Server.
  2. Modify the CreateDB.sql script that is provided in the Installation Startup Kit.
    Tip: You can extract the Installation Startup Kit from the downloaded package file disk1.zip.
    1. Go to the STARTUPKIT_INSTALL_HOME/CoreData/Full/SQLServer/ddl/ directory (where STARTUPKIT_INSTALL_HOME is the location of the extracted kit).
    2. Open the CreateDB.sql file in a text editor.
    3. Replace the variables in the script with values as described at the beginning of the script. Variables are enclosed in <>, such as <DBNAME>.
  3. Run the CreateDB.sql script to create the database.
    Note: When you use Windows Authentication rather than SQL authentication to access database, you must take one of the following actions:
    • If the client and SQL Server are in same domain, the login user that the client uses must be added into the SQL Server Security logins.
    • If the client and SQL Server are in different domains, then the two domains must be trusted.
  4. Copy the sqljdbc.dll file to the /Binn directory for the instance of SQL Server that is running.
    • If you are using SQL Server 32-bit, the sqljdbc.dll file is in STARTUPKIT_INSTALL_HOME/SQLServer JTA/win32
    • If you are using SQL Server 64-bit, the file is in STARTUPKIT_INSTALL_HOME/SQLServer JTA/win64_amd64
  5. Install the XA stored procedures that are used by the JDBC driver. From the STARTUPKIT_INSTALL_HOME/SQLServer JTA directory, run the instjdbc.sql script as the sa user.
  6. Enable MS DTC for XA transactions.

    For Windows 7 and Windows 2008

    1. From the desktop, click the Start icon and open Component Services using one of these options.
      • Type dcomcnfg in the Start Search box.
      • Type %windir%/system32/comexp.msc in the Start Search box.
    2. Go to Computers > My Computer > Distributed Transaction Cooridinator.
    3. Right-click on Local DTC and select Properties.
    4. On the Local DTC Properties dialog, open the Security tab.
    5. Select Enable XA Transactions and click OK. This step restarts the MS DTC service.
    6. Click OK on the Local DTC Properties dialog and close Component Services.
    7. Restart Microsoft SQL Server to ensure that it syncs up with the MS DTC changes. Verify that XATransactions=1 in Microsoft operating system registry.
  7. Run the following SQL statements to enable snapshot isolation for SQL Server: 
    ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE <database_name> SET MULTI_USER
    Note: These commands are critical if you intend to use LDAP security with WebSphere® Application Server.