Creating 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, complete the following steps:

Procedure

1. Create a directory on the SQL Server system where you would like to store the application databases.
   
Later on, you need to specify these directories as parameters of the filepath flag for the sqlcmd command.

2. Create a SQL Server user ID with system database administrator privileges that you can use to manage the database tables or use an existing ID that has administrative privileges, such as sa.
   
You will specify these credentials as parameters of the U and P flags for the sqlcmd command later.

3. 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. Enter the following command to create the application database table:
   
Note: If your database server has multiple SQL Server instances, add the following parameter as the first parameter in each command:

-S <sqlserver_server_name>\<sqlserver_server_instance_name>

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

where
• <admin_user> and <admin_password> are the credentials for the user ID that you created in a previous step or an existing ID with administrative privileges.
• <path_to_db> is the directory in which the created database are stored. You created this directory in a previous step.
• <password_for_application_user> is the password for each application database.
• The database user IDs are named as follows:
• Activities: OAUSER
• Blogs: BLOGSUSER
• Bookmarks: DOGEARUSER
• Communities: SNCOMMUSER
• Files: FILESUSER
• Forums: DFUSER
• Home page: HOMEPAGEUSER
• Profiles: PROFUSER
• Wikis: WIKISUSER
  
• Specify the password to be associated with this user ID.
  
Notes:

• When you run the installation wizard, you are asked to provide a user ID for the JDBC provider. Specify the user ID created by the database creation script and the password that you defined in this step.
• You can change the passwords for these database users later in SQL Server Management Studio. If you change the passwords there, you must also change them in the J2C authentication alias in the WebSphere® Application Server Integrated Solutions Console.
  
  
4. (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. Enter the following command to create the application database table:
   
sqlcmd -U <admin_user> -P <admin_password> -i initData.sql

5. 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: Creating databases with SQL scripts

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