Create SQL Server databases manually

Create Microsoft SQL Server databases with SQL scripts instead of using the database wizard.

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

The SQL scripts are located in a compressed file called connections.sql.zip|tar, located in the INSTALL/Connections/connections.sql directory of the Connections setup directory or installation media. Extract this file before proceeding. When extracted, the SQL scripts are located in the Connections/connections.sql/application directory of the Connections set-up directory or installation media, where application is the directory containing the SQL scripts for each application.

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

Before beginning the task, decide whether to use SQL Server with or without an instance name, and with or without an A-Record Alias.

If we installed SQL Server with a default instance, you do not need to supply details of the sql_server_instance_name. For example, in a default instance

• The name of the server is ServerA.

• You configured the default instance when setting up SQL Server.

• Use only the server name.

Alternatively, in an instancename example:

• ServerB is the name of the server

• You configured the instancename as Connections when setting up SQL Server.

• Use the ServerB\Connections naming format.

Finally, where the A-Record is specified as an Alias for SQL Server:

• ServerC is the name of the server

• You configured the default instance when setting up SQL Server.

• We created an A-Record to use as an alias for a new SQL Server called ServerC.

• Use the name of the new A-Record. For example, use A-Record-Name\sqlserver_server_instance_name>

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

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

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 you have write permissions for the directories and log files. To create the application database tables:

1. Configure SQL Server account mode and Windows Authentication mode:

   1. Create a SQL Server Account such as lcuser.

   2. Apply sysadmin permissions.

2. Configure Local Account Mode:

   1. Create a local account, such as lcuser, on the system hosting SQL Server.

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

   3. Add the local account to the Local Administrators group.

   Specify these credentials later as parameters of the U and P flags for the sqlcmd command.

3. Create a directory on the SQL Server system to store the application databases.

   Later on, specify these directories as parameters of the file path flag for the sqlcmd command.

4. Create a SQL Server user ID with system database administrator privileges.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.

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

   1. cd directory to which you copied the database creation scripts for the application.

   2. Enter the following command to create the application database table:

      If the 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 is stored.

      • password_for_application_user is the password for each application database.

      • The database user IDs are named :
        • Activities: OAUSER
        • Blogs: BLOGSUSER
        • Bookmarks: DOGEARUSER

        • Cognos :COGNOSUSER
        • Communities: SNCOMMUSER
        • Files: FILESUSER
        • Forums: DFUSER

        • Global Configuration Database: FNGCDUSER (Connections Content Manager)
        • Home page: HOMEPAGEUSER
        • Metrics: METRICSUSER
        • Mobile: MOBILEUSER

        • Object Store: FNOSUSER (Connections Content Manager)
        • Profiles: PROFUSER
        • Wikis: WIKISUSER

        Specify the password to be associated with this user ID.

      • When you run the installation wizard, we are asked to provide a user ID for the JDBC provider. Specify the user ID created by the database creation script and the password definedd in this step.

      • We can change the passwords for these database users later in SQL Server Management Studio. If we change the passwords there, also change them in the J2C authentication alias in the WAS console.

      • If we plan to install the Metrics application, we can create the database now but the tables are not created until you start the Cognos BI Server for the first time.

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

      • sql_server_account andsql_server_account_password are the credentials for SQL Server. These credentials do not apply for Windows Local Account or Windows Domain Account.

      • servername \local_account are the credentials for the user ID.

      • sql_server_data_path is the directory in which the created database is stored.

6. (Home page only) Perform the following steps for the Home page application:

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

7. (Communities only) Runs:

   sqlcmd -U admin_user -P admin_password -i calendar-createDb.sql

   sqlcmd -U admin_user -P admin_password -i calendar-appGrants.sql

8. To grant access privileges for the applications:

   1. cd 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 For Connection Content Manager:

      libraries.gcd/sqlserver/appGrants.sql -v password="password="password_for_application_user"

      libraries.os/sqlserver/appGrants.sql -v password="password="password_for_application_user" where password_for_application_user is the password for application database (FNGCDUSER and FNOSUSER).

9. (Files only) Create the tables for Push notification by ...

   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 "pns-createDb.sql" -v filepath="sql_server_data_path" password="password_for_application_user

   Followed by:

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

   For example, for Local Account Mode:

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

   Followed by:
   sqlcmd -S sql_server_name\sql_server_instance_name -U servername \local_account -P local_account_password -i "pns-appGrants.sql" -v filepath="sql_server_data_path" password="password_for_application_user"

   Where:

   • sql_server_account and sql_server_account_password are the credentials for SQL Server. These credentials do not apply for Windows Local Account or Windows Domain Account.

   • servername \local_account is the credential for the user ID.

   • sql_server_data_path is the directory where the created database is stored.

What to do next

For more information about Microsoft SQL Server 2005 and 2008, go to the Microsoft SQL Server web site.

Parent topic:
Create databases with SQL scripts

Related reference:

Microsoft SQL Server Web site