Microsoft SQL Server

IBM has certified Microsoft SQL Server 2000 with service pack 3 for use as a database server. The following sections describe how to install and configure SQL Server for use in the InterChange Server system:

Installation Recommendations

If you have previously installed InterChange Server and SQL Server 6.5, and you are now upgrading to SQL Server 2000, the upgrade does not require that you again perform the steps described under Configuring SQL Server. However, you must modify or create a new InterchangeSystem.cfg file for use with Microsoft SQL Server 2000, as described under Modifying the InterchangeSystem.cfg file.

If you are installing SQL Server for the first time, the following installation recommendations may be helpful. Your environment may have slightly different requirements.

Installation procedure

Select the following options during SQL Server 2000 installation:

SQL Server 2000

  1. Select SQL Server 2000 Components in the first screen.
  2. Select Install Database Server in the Install Components screen.
  3. Click Next in the Welcome screen, select Local Computer, and click Next to continue.
  4. Select either Create a new instance of SQL Server or install Client Tools in the Installation Selection screen, or both, and click Next.
  5. Enter your user Name and Company in the User Information screen and click Next.
  6. After accepting the License terms, select Server and Client Tools in the Installation Definition screen, click Next.
  7. Accept the Default Instance Name and click Next. Then, select Typical in the Setup Type screen and click Next.
    Note:
    To modify the destination location for the installation, click the Browse buttons in the Destination Folder section of the Setup Type screen and specify a new location.
  8. Select "Use the same account for each service" and "Use the Local System account" in the Services Account screen and then click Next.
  9. Select Mixed Mode and mark in the Blank Password box on the Authentication Mode screen, then click Next.
    Note:
    InterChange Server does not support Windows Authentication.
  10. Click Next in the Start Copying Files screen.
  11. Click Finish on the Setup Complete screen.

Modifying the InterchangeSystem.cfg file

If you are planning to use Installer to install the InterChange Server software, you may skip toConfiguring SQL Server. Installer generates the correct InterchangeSystem.cfg file automatically. The ICS system uses a Type 4 JDBC driver for SQL. This driver communicates directly with the database management system (DBMS) without using a client DLL.

To set the appropriate driver, follow these steps:

  1. Click Start > Programs > IBM WebSphere InterChange Server > IBM WebSphere InterChange Server > IBM WebSphere InterChange Server Configuration Wizard.
  2. On the InterChange Server Configuration window, modify the parameters and then click Apply.
  3. Click OK on the Changes Complete window and then click Exit.

Configuring SQL Server

To configure SQL Server to work with InterChange Server, follow the steps outlined in the following sections:

Verifying server startup

SQL Server is automatically installed as a Windows service. Use the Services window to verify that it is running.

  1. Click Start > Settings > Control Panel, and then double-click Administrative Tools.
  2. Double-click Services.
  3. In the Services dialog box, scroll to MSSQLServer.
  4. Make sure the Status is Started and the Startup Type is Automatic.

The next time you restart your machine, SQL Server will start automatically.

Verifying server registration

Your local server is registered automatically during installation. You need to register your server only if you do not see it in the Microsoft SQL Server Enterprise Manager. To verify that the server is registered:

  1. Click Start > Programs > Microsoft SQL Server > Enterprise Manager.
  2. In the left pane of the SQL Server Enterprise Manager window, expand Microsoft SQL Servers, then expand SQL Server Group.
  3. In the right pane of the SQL Server Enterprise Manager window, register SQL Server with the Enterprise Manager:
    1. Select the SQL Server Group to which you want to add your server (for example, SQL Server Group).
    2. Select New SQL Server Registration from the Action drop-down menu.

      Either the Registered SQL Server Properties dialog box or the Register Server Wizard appears.

      Note:
      The rest of this procedure assumes that you are using the Registered SQL Server Properties dialog box, but the values that you enter are the same if you use the wizard. To disable the Wizard, check the "From now on, I want to perform this task without using a wizard" box, then click Next. The Wizard disappears, and the Register SQL Server Properties dialog box appears.
    3. In the Server field, enter the name of the server. If you are registering your local server, select "local" from the Server drop-down list.
    4. Select "Use SQL Server authentication" as the Connection option, set the login name to sa, and leave the password blank. (The SQL Server system administrator, sa, has all privileges to create and modify databases and database users in SQL Server. After logging in to SQL Server, change the system administrator password.)
    5. Select a server group from the Server Group drop-down list, then click OK. If the server group you need does not exist, create and select a new server group, following the instructions in the online help for SQL 7.0.

Creating the InterChange Server repository

To create the database for InterChange Server repository:

  1. In the console tree of the SQL Server Enterprise Manager window, right-click the server that you have registered and select New > Database.
  2. In the Database Properties dialog box, type the following parameters:
  3. Accept all of the other defaults, then click OK.
  4. To verify that the database has been created, expand the console tree folder for the appropriate server in the left pane of the SQL Server Enterprise Manager. The new database should be listed under the Databases folder.
  5. Configure the repository database so that the transaction log is truncated each time a checkpoint occurs. Truncating the log ensures that it does not use all available space.
    1. SQL Server 2000
      1. In the console tree of the SQL Server Enterprise Manager window, right-click the icon for the wicsrepos database, then select Properties.
      2. In the Options tab, select Simple in the Recovery Model pull-down menu, leave all other defaults, and then click OK.
  6. Make sure the tempdb database has at least 15 MB of disk space for use by InterChange Server.
    1. In the console tree of the SQL Server Enterprise Manager window, right-click the tempdb database, then select Properties.
    2. In the General tab (located in the Data Files tab for SQL Server 2000), set Space Allocated to 15 MB, and make sure that you select the "Automatically grow file" and "Unrestricted filegrowth" check boxes.
    3. Accept all other defaults, then click OK.

Creating a database for relationship tables

By default, all the relationship tables for InterChange Server mapping are contained in the ICS repository. You can choose to create one or more separate databases for the relationship tables. This involves two general steps:

  1. Create the relationship database in SQL Server, in the same manner that you created the ICS repository, but with another name.
  2. Use the Relationship Designer tool to either specify the database as the global default for all the relationship tables, or specify individual relationship tables that use the database. For more information, see the Map Development Guide.

Creating a login account

This section describes how to create the ICS administrator login account and assign table creation privileges using Microsoft SQL Enterprise Manager.

  1. If it is not already open, open the Enterprise Manager by clicking Start > Programs > Microsoft SQL Server > Enterprise Manager.
  2. In the left pane, click the icon of the registered server for which you want to create a login account.
  3. (SQL Server 2000 only) Click View > Taskpad from the menu bar and click on the Wizards tab in the right pane.
  4. On the Getting Started Taskpad in the right pane, click "set up your database solution," then click "create a login."(For SQL Server 2000, select Create a Login in the Setup a Database section).
  5. In the Create Login wizard, click Next in the Welcome screen.
  6. Click "SQL Server login information..." for the authentication mode, then click Next.
  7. In the Authentication with SQL Server screen, type wicsadmin for the Login ID and wicsadmin for the password. You will also need this information later in the InterChange Server installation process. Click Next.
    Note:
    You may use whatever login and password you want, but be aware that these instructions use wicsadmin as both the login and password.
  8. In the Grant Access to Security Roles screen, clear all check boxes and then click Next.
  9. In the Grant Access to Databases screen, select the check box for wicsrepos and for any database that you have created for relationship tables, then click Next.
  10. In the Completing the Create Login screen, click Finish.
  11. Click OK when the following message appears: "The login was successfully created."
  12. Assign the account table creation privileges in the wicsrepos database. If you are using a separate (other than the default) relationships database for InterChange Server mapping, assign privileges for that database as well.
    1. In the list of databases in the Microsoft SQL Servers window, right-click wicsrepos, then select Properties.
    2. In the Permissions tab, check each box for the wicsadmin user. Be sure to scroll all the way to the right to select all of the options.
    3. Click OK.
    4. Repeat step 12 for any relationship databases that you are also creating.
  13. Increase the number of SQL Server user connections to at least 40. InterChange Server requires at least 15 user connections. If you run other applications that use the same SQL Server, set up at least 40 user connections. For more information, see "Setting up database connections".
    1. In the left pane of the SQL Server Enterprise Manager, right-click your server, then select Properties.
    2. In the Connections tab, set the Maximum concurrent user connections box to 40 or to the number of allowable user connections, then click OK.

      If a dialog box appears inquiring if you want to restart your server, click No. You will restart your server after step 14.

  14. Set the default database for the login account.
    1. In the left pane of the SQL Server Enterprise Manager, expand the directory for the server that you have registered.
    2. Expand the Security folder, then select Logins. The right pane of the Enterprise Manager displays login account names and their properties, including the default database for each login name.
    3. Right-click the wicsadmin login account name, then select Properties.

      The SQL Server Login Properties dialog box for the wicsadmin login account appears.

    4. In the General tab, select wicsrepos from the Database drop-down list, click Apply, then click OK.

You must restart SQL Server for these changes to take effect. However, you might want to perform the following steps in Verifying the Configuration before restarting.

Verifying the configuration

To verify that the database server is configured correctly to work with InterChange Server, use MSQuery:

  1. Click Start > Programs > Microsoft SQL Server > Query Analyzer.
  2. In the Connect to SQL Server dialog box, type the name of the server you are using, or select the server from the SQL Server drop-down list.
  3. Select the "Start SQL Server if stopped" check box, then select SQL Server authentication.
  4. Type the name of the ICS administrator login account (ics by default) for the Login Name and the password (ics by default), then click OK.

    If you cannot log in to SQL Server, check your login name and password, and your Path environment variable setting.

  5. In the Query window, check to see that the wicsrepos database and all relationship databases are in the DB drop-down list. Exit Query Analyzer.
  6. Restart the machine before continuing with the installation.

Copyright IBM Corp. 1997, 2004