Create SQL Server databases manually 

---

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

Before starting

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

• Ensure you have SQL Server account and Windows Authentication mode enabled

• Create a SQL Server Account; For example: lcuser

• Ensure you have applied your companies password policies

• Provide a password

• Apply sysadmin permissions

Local Account mode

• Create a local account on the server which is running SQL Server, for example: lcuser

• Ensure you have applied you companies password policies

• Provide a password

• Add the local account to SQL Server with the sysadmin permissions.

• Add the local account to the Local Administrators group.

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. 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

• • <servername>\<local_account> and <local_account_password> are the credentials for the user ID that you created in a previous step or an existing ID with administrative privileges.

  • <sql_server_account> and <sql_server_passowrd> are the credentials which have been created within SQL Server. This does not apply for Windows Local Account or Windows Domain Account.

  • <sql_server_data_path> is the directory in which the created database are stored. You created this directory in a previous step or you use the default path which you have given at the setup of SQL Server.

  • <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, also change them in the J2C authentication alias in the WebSphere® Application Server Integrated Solutions Console.
    
    

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

+

Search Tips   |   Advanced Search