1. Connect to an External Database

Owned by Helen Kosova (Deactivated)
Aug 31, 2020
9 min read

  • You must have a connector JAR file for your MySQL, Microsoft SQL Server or Oracle database.

  • We explain how to install a primary database, however, we recommend that you have one or several database replicas as well to ensure better availability and performance.

You can install one of the following database management systems on any computer where Zephyr is not installed and configure it so that Zephyr can connect to it:

Supported versions

MySQL Community Server versions 5.6.x and 5.7.x.

Prerequisites

  • Download MySQL Community Server v. 5.6.x or 5.7.x and install it on any computer where Zephyr is not installed.

  • Download the JDBC driver. Make sure the driver is stored on the node where Zephyr will be installed.

Configure the MySQL Database

1. Log in to your MySQL Server.

Open the Command Prompt on the computer where MySQL Community Server is installed and navigate to the MySQL installation folder (for example, C:\Program Files\MySQL\MySQL Server 5.6\bin)

Connect to your MySQL server using the following command:

mysql.exe -uroot

In this example, the username is root. In a new MySQL instance, root is the default user and can be logged in without a password. If you are using an existing MySQL Server instance, the username and password may be different.

2. Create a new user for Zephyr.

Create a new user whose credentials Zephyr will use to connect to the database and set a user password. To do that, use the following command:

CREATE USER 'zephyrDBuser@'localhost' IDENTIFIED BY 'zephyrDBuserPass';

3. Set permissions for remote access.

Type the command below to make the database accessible remotely:

grant all on *.* to zephyrDBuser@'127.0.0.1' identified by 'zephyrDBUserPass';

where zephyrDBuser is the username and ZephyrDBuserPass is the password of the user created at step 2.
The asterisks in the command above grant permissions to all databases. Replace 127.0.01 with the IP address of the node where Zephyr will be installed. This command will grant access to all databases for the given IP addresses. The minimum permission requirement is the privilege to create and populate tables.

When installing a database for a Data Center cluster, you need to grant permissions for each node in the cluster.

Checking permissions

To check permissions for all users, run the following command:
SELECT * from information_schema.user_privileges;

Now you an access your MySQL database remotely from the designated IPs.

4. Set the amount of memory to be used and the maximum number of connections.

Open the my.ini file (Windows) or my.cnf (Linux). The default location is the MySQL root folder.
Set the following values for the parameters:

Parameter

Minimum value

Parameter

Minimum value

innodb_buffer_pool_size=

4096M

query_cache_size=

1M

key_buffer_size=

8M

max_connections=

500

If no lines contain these parameters, then create new lines for them.

To check parameter values, use the following SQL queries:
SELECT @@innodb_buffer_pool_size;
SHOW VARIABLES LIKE 'query_cache_size';
SHOW VARIABLES LIKE ' key_buffer_size ';
SHOW VARIABLES LIKE 'max_connections';

Supported Databases

  • SQL Server 2012

  • SQL Server 2014

  • SQL Server 2016

Prerequisites

  • Install Microsoft SQL Server on any computer where Zephyr is not installed.

  • Download the JDBC driver and place it on the nodes where Zephyr will be installed.

Configure the Microsoft SQL Server Database

Step 1. Create a user account with relevant permissions.

In Object Explorer, expand the folder of the server instance in which you want to create a new login.
Right-click Security > Logins and select New Login…:

 

In the Login – New dialog box, on the General page, enter the name of a user in the Login name box.

To create a login that is saved on a SQL Server database, select SQL Server authentication. In the Password box, enter a password for the new user. Enter that password again in the Confirm password box:

 

From the Default database list, select a default database for the login. Master is the default for this option.
From the Default language list, select a default language for the login.
Click OK.
View the newly created login in the Security > Logins folder:

Next, you need to set permissions for this login. Right click the user and select Properties:

In the Properties window, grant permissions for the user to Alter any database:

 

The Effective tab will show the minimum permissions that must be provided:

You have created a new Login user with the appropriate permissions.

Step 2. Set permissions for remote access.

In this step, we will enable remote connections on the SQL Server instance that you want to connect to from a remote computer.

Staying within SQL Server Management Studio, right-click the server name in the left pane and select Properties:


Select Connections in the left pane and make sure that check-box Allow remote connections to this server is selected:

Now, you need to enable the TCP/IP protocol. To do that, open SQL Server Configuration Manager, expand SQL Server Network Configuration and click Protocols for SQLEXPRESS. Check the status to ensure that the TCP/IP protocol is enabled. If it is disabled right-click on TCP/IP and select Enable:

 

Select Properties from the same pop-up menu.
On the Protocol tab, make sure that Listen All has a value of Yes:

Now, click the IP Addresses tab, scroll-down to the APAll section, and enter the port 1433 for TCP Port:

Now you can start the SQL Server service.

Step 3. Turn on the SQL Server Browser service.

Open SQL Server Configuration Manager, click SQL Server Services in the left pane, right-click SQL Server Browser service and select Properties.

In the subsequent window, switch to the Service tab, click Start Mode, and select Automatic from the drop-down menu:

Switch to the Log On tab and click Start to start the SQL Browser service.


Make sure the state of the SQL Server Browser service is Running:

Step 4. Configure the firewall to allow network traffic.

Configure the firewall to allow network traffic related to the SQL Server and to the SQL Server Browser service.

The following port exceptions should be set:

A port exception for TCP port 1433:

In the New Inbound Rule Wizard of your firewall, do the following to create a port exception:

  1. Select Port.

  2. Select TCP and specify port 1433.

  3. Select Allow the connection.

  4. Choose all three profiles (Domain, Private & Public).

  5. Name the rule SQL – TCP 1433:

 

A port exception for UDP port 1434:

Click New Inbound Rule again and do the following:

  1. Select Port.

  2. Select UDP and specify port 1434.

  3. Select Allow the connection.

  4. Choose all three profiles (Domain, Private & Public).

  5. Name the rule SQL – UDP 1434:

Step 5. Set up authentication

In SQL Server Management Studio, right-click the server name in the left pane and select Properties.

 

In the subsequent window, select Security in the pane on the left, then select SQL Server and Windows Authentication mode in the Server authentication section, and click OK:

Your database is ready to be used.


Step 6. Specify the amount of memory to be used.

In SQL Server Management Studio, right-click on the server name in the left pane and select Properties.

 

In the subsequent dialog, select Memory in the pane on the left and specify the desired values in the Minimum server memory and Maximum server memory fields. The recommended minimum is 8 GB:

Logs

Log files may help you troubleshoot issues. To view the logs in SQL Server, expand Management > SQL Server Logs, right-click any log file, and click View SQL Server Log:

Supported Databases

  • Oracle Database 11g.

  • Oracle Database 12c.

Prerequisites

  • Download and install Oracle Database on any computer where Zephyr is not installed.

  • Download the JDBC driver. Make sure the JDBC driver is stored on the node where Zephyr will be installed.

    • Oracle Database 11g - 10.2.0.2 (ojdbc14.jar)

    • Oracle Database 12c - 12.1.0.2 (ojdbc7.jar)

Configure the Oracle Database

Step 1: After you install Oracle Database, set collation and DB parameters:

The default collation is binary for Oracle, and the one that is supported. The following query can be used to validate the collation for the user (database):

SELECT COALESCE(value, (SELECT value            FROM nls_database_parameters            WHERE parameter = 'NLS_SORT')) FROM nls_session_parameters WHERE parameter = 'NLS_SORT'

To configure the database, connect through the command console:

Type : sqlplus
Enter USER Name : sys/sysdba
Enter Password : *******

Run the commands below to set up Oracle database parameters:
Oracle must be set up with the AL32UTF8 parameter to support UTF-8 characters. To check your charset setting:

SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';

Step 2: Set up your database charset:

  1. shutdown immediate;

  2. startup restrict;

  3. select name from v$database;

  4. view code below:

    ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

  5. view code below:

  6. shutdown immediate;

  7. startup;

Step 3: Connect to the database in Oracle SQL Developer:

User creation

Create a table space:

Create an Oracle user:

If you face issues with the table space, try doing the following: for data files below, find the path of data files and use path value and replace the value "<example>/u01/app/oracle/oradata/XE/system.dbf<example>" below.

You can check the connection to your database when installing Zephyr on your cluster nodes. To do that, click Test Connection on the Configure Database screen of the installation program (see Install Zephyr Enterprise on Nodes). If Zephyr fails to connect to it, the installation will be interrupted.

Next Step >

See Also

Set Up Zephyr Data Center
Support and Troubleshooting

Â