Setting up a Microsoft SQL Server database

Set up the connections and create the necessary tables in the Microsoft SQL Server database to work with the Rational® Connector for SAP Solution Manager.

Before you begin

Make sure to meet the following prerequisites:

About this task

Set up the SQL Server database by using the sqlcmd command-line tool this is provided with the SQL Server installation.

Procedure

  1. Activate the sqlcmd tool for Microsoft SQL Server 2008 Express® edition. Click Start > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager.
  2. In the SQL Server Configuration Manager window, in the left pane, click SQL > Server Network Configuration > Protocols for SQLEXPRESS.
  3. Double-click the Named Pipes protocol.
  4. In the Name Pipes Properties dialog box, set the value of the Enabled field to Yes.
  5. Change the Pipe Name to \\.\pipe\sql\query.
  6. Click Apply and restart the SQL Server Services.
    Tip: You can also use a visual tool, such as SQL Server Studio Management, which is also available for the Express version as SQL Server Studio Management Express. For more information, see the SQL Server documentation, or an SQL Server database administrator (DBA).
    Important: If you install the Rational Connector on the same computer or on the same distributed operating systems, a separate database and a database user who is associated with that database must be created.
  7. Create a database for each instance of the Rational Connector. Use the following example, substituting sap with your database name:
    CREATE DATABASE sapdb
    GO
  8. Create a user and password, and change the ownership of the database by running these commands:
    CREATE LOGIN sapDBuser
    WITH PASSWORD = 'sapDBpswd';
    USE sapdb;
    exec sp_changedbowner 'sapDBuser'
    GO 
  9. Change the row version system for the database. From the command-line tool, run this command:
    Important: Before you run this command, ensure that the connection to the database is open.
    ALTER DATABASE sapdb SET READ_COMMITTED_SNAPSHOT ON
    GO
  10. If you are connecting to your SQL Server database through WebSphere® Application Server with JRE 6.0, set the SQLSERVER_JDBC_DRIVER_FILE variable to point to the JRE 6.0 version of the JDBC driver named sqljdbc4.jar. This driver can be installed in a directory of your choice on the application server. For Tomcat servers, put sqljdbc4.jar into the SAPCInstallDir/server/Tomcat/lib directory. For WebSphere Application Servers, follow these steps:
    1. Open the administrative console by pointing a browser to http://yoursystem:9060/ibm/console
    2. Click Resources > JDBC > JDBC Providers.
    3. On the first page of the wizard, select All Scopes, then selectNode=YourNodeName, Server=YourServerSetup. Click New. Set the Database type to SQL Server, Provider type to SQL Server Using IBM JCC Driver and Implementation type to XA data source.
    4. On the second page of the wizard, set the driver directory path to the directory where you placed the sqljdbc4.jar file.
    5. On the last page of the wizard, click Finish.
    6. If you are using WebSphere Application Server, you must also add a custom property that points to the sqljdbc4.jar JDBC driver. For more information, see Setting up WebSphere Application Server.
  11. To configure the database connections and create database tables, use the SQL command tool to run the SAPCInstallDir/MSSQL_Tables.ddl DDL script.
  12. Open the SAPCInstallDir/server/conf/sapconnector.properties file in a text editor and do these steps:
    1. Comment out the default Derby DB lines.
    2. Uncomment the SQL Server lines, and change the host name, instance, user, and password fields to match the credentials of your environment.
  13. Save the file and close.

Feedback