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:
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.
- You can install SQL Server either on the same system where you install
InterChange Server or on any system on the same network. IBM suggests
that you install SQL Server and InterChange Server on different
systems.
- If you choose to install SQL Server on a different system from the one
where InterChange Server resides, you need to install SQL Server Utilities on
the same machine as InterChange Server. The SQL Utilities, which
include DB-Library and administration tools, are necessary for network access
and system troubleshooting.
Select the following options during SQL Server 2000 installation:
- Select SQL Server 2000 Components in the first screen.
- Select Install Database Server in the Install Components screen.
- Click Next in the Welcome screen, select Local Computer, and click Next to
continue.
- Select either Create a new instance of SQL Server or install Client Tools
in the Installation Selection screen, or both, and click Next.
- Enter your user Name and Company in the User Information screen and click
Next.
- After accepting the License terms, select Server and Client Tools in the
Installation Definition screen, click Next.
- 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.
- Select "Use the same account for each
service" and "Use the Local System account" in the Services Account screen and
then click Next.
- 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.
- Click Next in the Start Copying Files
screen.
- Click Finish on the Setup Complete screen.
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:
- Click Start > Programs > IBM WebSphere InterChange Server > IBM
WebSphere InterChange Server > IBM WebSphere InterChange Server
Configuration Wizard.
- On the InterChange Server Configuration window, modify the parameters and
then click Apply.
- Click OK on the Changes Complete window and then click Exit.
To configure SQL Server to work with InterChange Server, follow the steps
outlined in the following sections:
SQL Server is automatically installed as a Windows service. Use the
Services window to verify that it is running.
- Click Start > Settings > Control Panel, and then double-click
Administrative Tools.
- Double-click Services.
- In the Services dialog box, scroll to MSSQLServer.
- Make sure the Status is Started and the Startup Type is Automatic.
- If the Status is Stopped, right-click MSSQLServer, then select
Start.
- If the Setup Type is Manual, right-click MSSQLServer, select Properties,
then select Automatic from the Startup type in the General tab. Click
OK.
The next time you restart your machine, SQL Server will start
automatically.
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:
- Click Start > Programs > Microsoft SQL Server > Enterprise
Manager.
- In the left pane of the SQL Server Enterprise Manager window, expand
Microsoft SQL Servers, then expand SQL Server Group.
- In the right pane of the SQL Server
Enterprise Manager window, register SQL Server with the Enterprise
Manager:
- Select the SQL Server Group to which you want to add your server (for
example, SQL Server Group).
- 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.
- 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.
- 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.)
- 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.
To create the database for InterChange Server repository:
- In the console tree of the SQL Server Enterprise Manager window,
right-click the server that you have registered and select New >
Database.
- In the Database Properties dialog box, type the following
parameters:
- In the General tab:
Type wicsrepos in the Name field.
Type 50 in the Initial Size (MB) field (located in the Data Files tab for
SQL Server 2000).
- In the Transaction Log tab:
Type 50 in the Initial Size (MB) field, and make sure that the
"Automatically grow file" and "Unrestricted file growth" boxes
are checked.
- Accept all of the other defaults, then click OK.
- 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.
- 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.
- SQL Server 2000
- In the console tree of the SQL Server Enterprise Manager window,
right-click the icon for the wicsrepos database, then select
Properties.
- In the Options tab, select Simple in the Recovery Model pull-down menu,
leave all other defaults, and then click OK.
- Make sure the tempdb database has at least 15 MB of disk space
for use by InterChange Server.
- In the console tree of the SQL Server Enterprise Manager window,
right-click the tempdb database, then select Properties.
- 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.
- Accept all other defaults, then click OK.
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:
- Create the relationship database in SQL Server, in the same manner that
you created the ICS repository, but with another name.
- 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.
This section describes how to create the ICS administrator login account
and assign table creation privileges using Microsoft SQL Enterprise
Manager.
- If it is not already open, open the Enterprise Manager by clicking Start
> Programs > Microsoft SQL Server > Enterprise Manager.
- In the left pane, click the icon of the registered server for which you
want to create a login account.
- (SQL Server 2000 only) Click View > Taskpad from the menu bar and click
on the Wizards tab in the right pane.
- 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).
- In the Create Login wizard, click Next in the Welcome screen.
- Click "SQL Server login information..." for
the authentication mode, then click Next.
- 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.
- In the Grant Access to Security Roles screen, clear all check boxes and
then click Next.
- 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.
- In the Completing the Create Login screen, click Finish.
- Click OK when the following message appears: "The login was
successfully created."
- 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.
- In the list of databases in the Microsoft SQL Servers window, right-click
wicsrepos, then select Properties.
- 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.
- Click OK.
- Repeat step 12 for any relationship databases that you are also
creating.
- 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".
- In the left pane of the SQL Server Enterprise Manager, right-click your
server, then select Properties.
- 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.
- Set the default database for the login
account.
- In the left pane of the SQL Server Enterprise Manager, expand the
directory for the server that you have registered.
- 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.
- Right-click the wicsadmin login account name, then select
Properties.
The SQL Server Login Properties dialog box for the wicsadmin
login account appears.
- 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.
To verify that the database server is configured correctly to work with
InterChange Server, use MSQuery:
- Click Start > Programs > Microsoft SQL Server > Query
Analyzer.
- 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.
- Select the "Start SQL Server if stopped" check box, then select
SQL Server authentication.
- 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.
- 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.
- Restart the machine before continuing with the installation.
