Create SQL Server databases manually 




Create Microsoft™ SQL Server databases with SQL scripts instead of using the IBM® Connections database wizard.

Before you begin

Follow this procedure if you do not want to use the database wizard to create your databases.

The SQL scripts are located in a compressed file called connections.sql.zip|tar, located in the Lotus_Connections_install\LotusConnections\connections.sql directory of the IBM Connections set-up directory or installation media. Extract this file before proceeding. When extracted, the SQL scripts are located in the LotusConnections\connections.sql\<application_subdirectory> directory of the IBM Connections set-up directory or installation media, where <application_subdirectory> is the directory that contains the SQL scripts for each application.

If the database server and IBM Connections are installed on different systems, copy the SQL scripts to the system that hosts the database server.

About this task


This task describes how to use SQL scripts to create SQL Server databases for IBM Connections applications.

Download the SQL Server JDBC 2 driver from the Microsoft web site and follow the instructions to extract the driver files. IBM Connections uses the sqljdbc4.jar file.

IBM recommends that you obtain this Microsoft hotfix for the JDBC 2 driver for production deployments.

Note: To capture the output of each command to a log file, append the following parameter to each command: >> \<file_path>\db_<application>.log

where <file_path> is the full path to the log file and <application> is the name of the log file. For example: <sqlcmd> >> \home\<admin_user>\lc_logs\db_activities.log

where <sqlcmd> is a command with parameters and <admin_user> is the logged-in user. Ensure that you have write permissions for the directories and log files.

To create the application database tables...

Procedure

Account mode:

SQL Server Account mode

Local Account mode

  1. You will specify these credentials as parameters of the U and P flags for the sqlcmd command later.


  2. SQL Server with\without instance, and using an A-Record Alias or not

    If you have installed SQL Server with a default instance you do not need to configure and supply information regarding an sql_server_instance_name .

    Example default instance:

    • ServerA is the name of the server

    • You configured the default instance while using the setup of SQL Server

    • Only the name of the server should be used

    Example with instance:

    • ServerB is the name of the server

    • You configured the instancename as 'Connections' while using the setup of SQL Server

    • The format should be ServerB\Connections

    Example with the following A-Record specified as an Alias for SQL Server to provide future upgrades.

    • ServerC is the name of the server

    • You configured the default instance while using the setup of SQL Server

    • You have created an A-Record which is used as an alias for the new used SQL Server called ServerC

    • The name of the new A-Record should be used

    • When using a named instanced for example connections you should use <A-Record-Name>\<sqlserver_server_instance_name>

 

  1. Create a directory on the SQL Server system where you would like to store the application databases or use the path supplied at the installation of SQL Server.

    Later on, you need to specify these directories as parameters of the filepath flag for the sqlcmd command.

Perform the following steps once per application to create each database:

    1. Open a command prompt and change to the directory to which you copied the database creation scripts for the application.

    2. Create the application database table:

sqlcmd -U <admin_user> -P <admin_password> -i "createDb.sql" -v filepath="<path_to_db>" password="<password_for_application_user>"

Example for SQL Server Account Mode:

sqlcmd -S <sql_server_name>\<sql_server_instance_name> -U <sql_server_account> -P <sql_server_account_password> -i "createDb.sql" -v filepath="<sql_server_data_path>" password="<password_for_application_user"

Example: Local Account Mode:

sqlcmd -S <sql_server_name>\<sql_server_instance_name> -U <servername>\<local_account> -P <local_account_password> -i "createDb.sql" -v filepath="<sql_server_data_path>" password="<password_for_application_user"

where

  1. (Home page only.) Perform the following steps if you are installing the Home page application:

    1. Open a command prompt and change to the directory to which you copied the database creation scripts for this application.

    2. Create the application database table:

        sqlcmd -U <admin_user> -P <admin_password> -i initData.sql

  2. Perform the following steps to grant access privileges for the applications:

    1. Open a command prompt and change to the directory to which you copied the database creation scripts for each application.

    2. Enter the following command:

        sqlcmd -U <admin_user> -P <admin_password> -i appGrants.sql


What to do next


For more information about Microsoft SQL Server 2005 Enterprise Edition, go to the Microsoft SQL Server web site.


Parent topic

Create databases with SQL scripts

Related reference
Microsoft SQL Server Web site
SQL Server 2005 JDBC 2 driver
Microsoft hotfix for the JDBC 2 driver


February 1, 2012 by Andre Krijnen  
   

 

Feb 1, 2012 7:41:38 PM Andre Krijnen   7 Feb 1, 2012 7:38:30 PM Andre Krijnen   6 Feb 1, 2012 7:33:29 PM Andre Krijnen   5 Feb 1, 2012 7:32:03 PM Andre Krijnen   4 Feb 1, 2012 7:28:35 PM Andre Krijnen   Better defined and to conform the information with allready supplied d... 3 Feb 1, 2012 7:09:28 PM Andre Krijnen   SQL Server with\without instance, and using an A-Record Alias or not 2 Feb 1, 2012 7:00:01 PM Andre Krijnen   Added SQL Server account mode, and which options you should enable to ... 1 Submitted by Andre Krijnen on Feb 1, 2012 7:46:51 PM

Re: Creating SQL Server databases manually

- Changed the layout and appearance.

- Added additional info regarding local account mode.

- How to use SQL Server Account mode

- How to provide correct information in the command using the parameters.

- Which permissions is needed on the local server for Local Account Mode

- Updated information regarding the path where the databases should be created

Submitted by Andre Krijnen on Feb 1, 2012 7:01:24 PM

Re: Creating SQL Server databases manually

Added an example for SQL Server account mode. Added additional info in header 'Before you begin'

});