How to install Onebeat and SQL Server on different machines?

This article includes the following parts: 

1. Background
2. SQL Server Settings
3. Onebeat Server Settings
4. Testing
5. How to:
   1. Restore Database
   2. Backup Database
   3. How to run Store Procedure
      
   4. Solve error OS Error 3 The system cannot find the path specified - for Support only.

 

Background

In case Onebeat Server and the SQL Server are installed on different machines, follow this article in order to set up the required configurations and settings in each machine.

SQL Server Settings

The following actions should be performed on the SQL Server Machine:

1. Open SQL Server Management Studio > right click on the SQL server name > properties > security > mark the “SQL Server and Windows Authentication mode” and press OK

2. Under the SQL server name > Open the Security folder > Logins:

* Defining Password for an existing User - double click on the name of the user, changes to SQL Server Authentication and add a password.

* Defining a new User - Right-click on Logins > New Login > change to SQL Server Authentication, and add a password. 

3. Open the SQL Server Configuration Manager > Open the SQL Server Network Configuration > choose the correct SQL instance for example Protocols for SQL2012 > make sure that the following is Enabled: 

* Shared Memory

* Named Pipes

* TCP / IP

Onebeat Server Settings

The following actions should be performed on Onebeat server machine:

1. Open Onebeat Server Administrator (OSA) > SQL security tab > change the Authentication to “SQL Server and Windows” and add the user name and password (as you defined in the SQL settings - Step 2). 

2. Open Onebeat Server Administrator (OSA) > Connection tab, change the following settings as follow:

SQL Location

Add the path in which the DB will be stored in the SQL Server Machine. 

For example: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

Server Name

* In the field “Server Name” add the IP address of the SQL Server PC according to the following formula: [internal IP address of the SQL Server PC\SQL Instance (in case there is more than one instance)]¹

* Press the “Add” button

* In the “servers list” choose the SQL server name

* Press “Save Server configuration” => you should receive the following message “Server config file is successfully saved”

¹  Examples for SQL Server names:

Example 1 - IP address / DNS name, one SQL instance:

 

For the above example the server name is:

* “QASB2”

or

* "192.168.1.143” (the IP address of the SQL server PC) 

Example 2 - IP address / DNS name, 2 SQL instances:

For the above example, in case we would like to connect to the SQL2005 instance, the server name is:

* “QASB2\sql2005”

or

* “192.168.1.143\sql2005”

3. Adding OSQL files:

For SQL server version 2008 R2 - download this zip file (which contains 2 files: osql.rll and OSQL.EXE) and follow the instructions below: 

Go to the OSA directory folder (default path is: C:\Program Files\Onebeat\Onebeat Server Administration) and paste the files osql.rll and OSQL.EXE. 

Other SQL server versions:

Please contact the Onebeat support team and specify the OS bit and the SQL Server version you are using. They will provide you the compatible OSQL files that should be installed.

Stop Onebeat server, close and reopen it > restart Onebeat server

4. Adding SQL Components:

4.1. Install the SQL Server Native Client for Windows:

The Microsoft SQL Server Native Client contains the SQL OLE DB provider and SQL ODBC driver in one native dynamic link library (DLL) supporting applications using native-code APIs (ODBC, OLE DB, and ADO) to Microsoft SQL Server.

For SQL 2008 / 2012: open the attached installation file and follow the setup instructions.

For other SQL versions, using IT team, open the "Tools" setup (from the SQL installation) and install only the "Connectivity Components", see the image below:

After the installation, the SQL Native Client driver has been added:

4.2 Install SMO (SQL Management Objects):

Install the SMO files on Onebeat machine; open the SMO zip file - first try to install the "SharedManagementObjectsx64" installation, only if it fails to install the "SQLSysClrTypesx64.msi" file and then "SharedManagementObjectsx64".

*Pay attention that for Onebeat Admin the 'Paths Definition' direct to folders on the Onebeat server machine.

Testing

Open Onebeat Admin, login while the Server IP is: localhost / Onebeat Server IP

In case you successfully log in -> the above-required configurations were set as successfully.

How to... 

Restore Database

In case Onebeat Server and SQL Server are installed on different machines, in order to restore Database follow the below instructions. 

 Preconditions: 

• The backup file that should be restored must be stored on the SQL server.

• The path to the stored backup file must exist on the Onebeat server.

• In the in question path, create a dummy file (doesn’t matter the type/name of the file)

Instructions:

Log in to Onebeat Admin >Settings > Database

• Press the “Backup / Restore” button

• Press the “Restore File” and choose the dummy file >press open

• In the Restore field, change the name of the file according to the real backup file which is stored on the SQL server.

• Press the “Restore Database” button

Backup Database

In case Onebeat Server and SQL Server are installed on different machines, in order to backup a database follow the below instructions: 

Preconditions: 

• The path where we would like to store the Backup file should be existing on both servers; SQL and Onebeat server. This means, you must create an identical path for "saving" the backup file on Onebeat server &  saving the backup file on SQL server.
• The Backup file will be created on the SQL Server machine 

Instructions:

Log in to Onebeat Admin >Settings > Database

• Press the “Backup / Restore” button

• Press the “Backup File” and choose the path which exists on both Servers

• Press the “Backup Database” button
  The command will run from Onebeat server but the backup will create and save only in SQL server in the path that was defined. If you get an error during the backup process check the manual
  Backup Troubleshooting

How to run Store Procedure

1. Download to Onebeat Server sqlcmd Utility from Windows sqlcmd Utility - SQL Server | Microsoft Docs

2. Start the sqlcmd utility and connect to a named instance of SQL Server
   

   Open a Command Prompt window, and type sqlcmd -SmyServer\instanceName. Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.

   Press ENTER.

   The sqlcmd prompt (1>) indicates that you are connected to the specified instance of SQL Server.
   You can follow the steps in section 2:  Start the sqlcmd Utility - SQL Server Management Studio (SSMS) | Microsoft Docs
   
   

3. Connect to SQL Server by following those steps:
   Connect to the Database Engine With sqlcmd - SQL Server Management Studio (SSMS) | Microsoft Docs