Create Microsoft™ SQL
Server databases with SQL scripts instead of using the HCL 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, located in the
HCL_Connections_Install\HCLConnections\native\connections.sql.zip\connections.sql
directory of the HCL Connections setup directory or installation media. Extract this file
before proceeding. When extracted, the SQL scripts are located in the
HCL_Connections_Install/HCLConnections/native/connections.sql/application_subdirectory
directory of the HCL 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 HCL Connections are installed on different systems, copy the SQL scripts
to the system that hosts 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
you 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.
- You 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>
Attention: The HCL Connections 7.0 database wizard does not create the
database for the Community Highlights / Connections Engagement Center (CEC) or
Feature Foundation | Export entries to PDF and provides support for Tailored
Experience. The Community Highlights database scripts are located in the folder -
/Wizards/connections.sql/icec. The Feature Foundation database scripts are located
in the folder - /Wizards/connections.sql/ic360. Refer to the procedure in this
section to create the databases. You will need to manually run the SQL scripts that
are provided with
HCL
Connections™. See
Creating databases for more
information.
About this task
This task describes how to use SQL scripts to create SQL
Server databases for HCL 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. HCL Connections uses the sqljdbc4.jar file.
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
- Configure SQL Server account mode and Windows™ Authentication mode:
- Create a SQL Server Account such as lcuser.
- Apply sysadmin permissions.
- Configure Local Account Mode:
- Create a local account, such as lcuser, on the system
that is hosting SQL Server.
- Add the local account to SQL Server with sysadmin permissions.
- Add the local account to the Local Administrators group.
Note: You must specify these credentials later as parameters
of the U and P flags for the sqlcmd command.
- Create a directory on the SQL Server system where you can
store the application databases.
Later on, you need
to specify these directories as parameters of the file path flag for
the sqlcmd command.
- 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.
- Perform the following steps once per application to create
each database:
- Open a command prompt and change to the directory to
which you copied the database creation scripts for the application.
- 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 is stored.
- 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
- Community Highlights / Connections Engagement Center: ICEC
- Files: FILESUSER
- Forums: DFUSER
- Global Configuration Database: FNGCDUSER (Connections Content
Manager)
- Home page: HOMEPAGEUSER
- IC360: ESSUSER
- Metrics: METRICSUSER
- Mobile: MOBILEUSER
- Object Store: FNOSUSER (Connections Content Manager)
- Profiles: PROFUSER
- Push Notifications: PNSUSER
- Wikis: WIKISUSER
Specify the password to be associated with this user ID.
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.
- (Home page only) Perform the following steps for the Home
page application:
- Open a command prompt and change to the directory to
which you copied the database creation scripts for this application.
- Enter the following command to create the application
database table:
sqlcmd -U admin_user -P admin_password -i
initData.sql
- (Communities only) Run the following commands:
sqlcmd
-U admin_user -P admin_password -i
calendar-createDb.sql
sqlcmd -U admin_user -P admin_password -i
calendar-appGrants.sql
- Perform the following steps to grant access privileges
for the applications:
- Open a command prompt and change to the directory to
which you copied the database creation scripts for each application.
- Enter the following command:
sqlcmd -U admin_user -P admin_password -i
appGrants.sql
For Connection Content Manager, enter the following
commands:
library.gcd/sqlserver/appGrants.sql -v
password="password="password_for_application_user"
library.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).