Microsoft SQL#

Warning

When using SQL Server in a VM, snapshots and migration are not permitted during operation. Snapshots may only be created when VMs are completely shut down. SQL servers in clusters are an exception to this rule, as they are specifically designed for this purpose.

Attention

From 500 XPhone Connect users (or from 250 users when using TeamDesk and Direct Calls) we strongly recommend the use of a Microsoft SQL Standard/Enterprise Edition.

By default, the XPhone Connect Server is operated with a local Microsoft SQL Server Express Server. The XPhone Connect Server installation offers this Microsoft SQL Express Server for installation. All Microsoft SQL Servers can be operated locally on the XPhone Server, or with remote connection.

System requirements#

Note

A central overview of all system requirements can be found at System Requirements.

Use customer-owned SQL Server#

Hints#

Important

When using customer-owned Microsoft SQL Server instances, responsibility for use and configuration lies with the respective database system administrator and not with the XPhone Connect software manufacturer C4B or the XPhone Connect partner.

Caution

To ensure smooth operation when using your own SQL server instances or databases, please observe the following instructions:

Ensure that there is sufficient free disk space available on the drive where the SQL Server instances or databases are installed. The space required depends, among other things, on the number of users, the volume of calls and chats (journal tables), and the number of images sent and statistical data for analytics.
Regular backups should generally be configured for the XPDATA, XPSTATS and XPSTATSRaw databases. It is not necessary to create backups for the XPVDIR database, as the index tables contained there are regenerated cyclically from the connected XPhone Connect Directory data sources anyway.
It is recommended to use the Simple recovery model for all databases used. For more information, see the online help for Microsoft SQL Server.
If the XPhone Connect Server is run on the same system as the SQL Server, the use of the SQL Server’s RAM should be limited. You can configure this using SQL Management Studio, for example.

The persistent data of the XPhone Connect Server is stored in four databases on two Microsoft SQL Server Express instances. These instances are installed by the setup program and are named XPCONNECT and XPANALYTICS. These instances are independent of other SQL Server installations and can also be operated in parallel with existing SQL Server installations of other editions.

The two XPDATA and XPVDIR databases are created in the XPCONNECT instance. All configuration information and the users’ journals are stored in the former while the other database exclusively contains the indices required for Connect Directory and, if necessary, the replicated contact data.

The two XPSTATS and XPSTATSRaw databases are created in the XPANALYTICS instance. The data collected for Analytics is stored in these databases.

Note

Please also note the information and workaround in the event that the internal databases have not been installed and, consequently, it may not be possible to convert the service account in the XPhone Connect Server Manager.

The database configuration can be found in the atlas.xml file (accessible via the XPhone Connect Server Manager) and has the following default content:

<Databases databaseMigrationSQLTimeOut="900">

<Main type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPDATA;Data Source=(local)\XPCONNECT" autoLockDown="5" organisationDN="ou=Organisation" configurationDN="ou=Configuration" />

<Stats type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPSTATS;Data Source=(local)\XPANALYTICS" autoLockDown="5" />

<VDir type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPVDIR;Data Source=(local)\XPCONNECT" autoLockDown="5" />

</Databases>

Description of the parameter:

Name	Default value	Format	Description
`type`	MSSQL	Text	Currently only MSSQL is supported
`connection`	(see above)	special	This is the ConnectionString for the connection to the database. For valid parameters, see the documentation on the Class SQLConnection for .NET in the SQL Server documentation. If you wish to connect to an existing SQL server, you only need to modify the “Data Source” parameter.
`autolockDown`	5 (1, if no entry exists)	Number	(see below)
`organisationDN`		special	Required for Active Directory integration
`configurationDN`		special	Required for Active Directory integration
`useLogin`	All		Optional setting indicating the credentials with which the login attempt is to be made. “All” - all users “Internal” - only internal SQL users, no service Windows account “Windows” - only the service’s Windows account
`databaseMigrationSQLTimeOut`			This indicates the timeout in seconds for SQL queries which must be run in order to carry out the modifications necessary to databases after updates.

The databases can also be connected on an existing SQL server instance. To use an existing SQL server, the connection parameter must be adapted. The connection string contains the Data Source parameter, which must correspond to this format:

Data Source=<DNS | Hostname>\<SQL instance>,<Port>

(local) stands for the local computer; for the default instance, the first part without \ is sufficient. The port is optional and, unlike in other notations, is not specified with a colon but separated by a comma.

Procedure#

For example, to use the existing SQL Server instance sqlserver\connect, it is recommended to proceed as follows:

Check the requirements of the existing SQL Server:
- The TCP/IP protocol must be activated under Server Network Configuration.
- Remote access must be activated on the existing SQL Server.
- The SQL browser service must be running on the existing SQL Server.
Create a user account (aduserxphoneserver) in the Active Directory under which the XPhone Connect Server service is to run.
Create a database (xphoneserverdata) in the desired SQL instance which is to be used by the XPhone Connect Server.
Create a database (xphoneserverdata) in the desired SQL instance which is to be used by the XPhone Connect Directory.
Create a login for the user aduserxphoneserver in the SQL instance.
In the SQL instance, assign the role db_owner to the login to the xphoneserverdata database.
In the SQL instance, assign the role db_owner to the login to the xphoneservervdir database
End the XPhone Connect Server.
Change the database settings in atlas.xml to the SQL instance.
In atlas.xml, replace the database XPDATA with xphoneserverdata and XPVDIR with xphoneservervdir.
Restrict the login attempts for both databases to integrated Windows login in atlas.xml (uselogin="Windows") and Autolockdown to 0.
Change the XPhone Connect Server service account to “aduserxphoneserver” using the XPhone Connect Server Manager.

Start the XPhone Connect Server:

<Databases databaseMigrationSQLTimeOut="900">

<Main type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xphoneserverdata;Data Source=sqlserver\connect" autoLockDown="0" uselogin="Windows" organisationDN="ou=Organisation" configurationDN="ou=Configuration" />

<Stats type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPSTATS;Data Source=(local)\XPANALYTICS" autoLockDown="5" />

<VDir type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xphoneservervdir;Data Source=sqlserver\connect" autoLockDown="0" uselogin="Windows" />

</Databases>

Procedure for Analytics#

For example, to use the existing SQL Server instance MySqlServer\MyInstance for analytics, proceed as follows:

Check the requirements of the existing SQL Server instance MySqlServer\MyInstance:
- The TCP/IP protocol must be activated under Server Network Configuration.
- Remote access must be activated on the existing SQL Server.
- The SQL browser service must be running on the existing SQL Server.
- After changing the SQL configuration, it may be necessary to restart the SQL Server to activate the changes.
- It may be necessary to set up a firewall rule allowing access to the SQL Server instance.
Access by the XPhone Connect Server to the Analytics databases (e.g. for scheme updates).

(recommended procedure for a web server cluster)
1. Create a user account (aduserxphoneserver) in the Active Directory under which the XPhone Connect Server service is to run.
2. In the desired SQL instance MySqlServer\MyInstancezwei create two databases (XPAnalytics und XPAnalyticsRaw) to be used by Analytics.
  
  Hint
  
  The name of the second database must match that of the first, plus the suffix “Raw.”
3. Create a login for the user aduserxphoneserveranlegen in the SQL instance.
4. In the SQL instance, assign the role db_owner to the login to the XPAnalytics and XPAnalyticsRaw databases.
5. End the XPhone Connect Server.
6. In atlas.xml change the database settings for Analytics (Connection String Name Stats) to the SQL instance:
  - In atlas.xml, replace the database XPSTATS with XPAnalytics.
  - In atlas.xml restrict the login attempts to integrated Windows login (uselogin="Windows") and set autolockdown to 0.
7. Change the XPhone Connect Server service account to “aduserxphoneserver” using the XPhone Connect Server Manager.
Access by Analytics web application to the Analytics databases (for evaluations):
1. In Active Directory, create a user account (aduseranalyticsapppool) under which the Analytics application pool is to run in the IIS.
2. In the IIS, assign the XPhoneConnectAnalytics application pool the domain account you have just created.
3. Create a login for the user aduseranalyticsapppool in the SQL instance.
4. In the SQL instance, assign the roles db_datareader and db_datawriter to the login just created in the XPAnalytics and XPAnalyticsRaw databases.
5. Start the XPhone Connect Server.

Example of the atlas.xml:

<Databases databaseMigrationSQLTimeOut="900">
  ...
<Stats type="MSSQL" connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPAnalytics;Data Source=MySqlServer\MyInstance" autoLockDown="0" />
  ...
</Databases>

Data base security#

Basically, the XPhone Connect Server must be able to access the database with the SQL Server role db_owner. In order to protect the partially personal data from unauthorized access (e.g. by viruses and spy programs), database security should restrict access by other persons as far as possible. These adjustments are made completely automatically for the Microsoft SQL Server instances installed by the setup. The parameter autoLockDown controls this automatic adjustment of database security.

Caution

If existing SQL servers are used, you must be very careful here, as access to the data base for administrative purposes or by other applications is then prevented.

The SQL Server installed by the setup runs in mixed mode, i.e., authentication takes place both via Windows login and via user accounts created in SQL Server. SQL login enables clients to access the central address book, for example, even if they cannot be authenticated via Windows (e.g., for mobile computers without domain login).

Hint

If an existing SQL server does not run in mixed mode, you should deactivate the automatic customisation. You must also ensure manually that the service account of the XPhone Connect Server receives the required access rights (SQL server role db_owner) for the SQL Server.

Flag	Description
`0`	Security is not customised or changed
`1`	A data base administration account is added for emergencies. Only the server knows the password for the admin account.
`2`	All accounts that were not explicitly activated (SQL and Windows) are removed
`4`	The sa account, which always exists, is given a password that only the server knows.
`8`	The local administrator is set up as the data base administrator.

The flags add up, which is to say that 1+4=5 results in the aforementioned setting for the SQL Server data base that is installed by the set-up. With the exception of mode 0, the account for the XPhone Connect Server service is always set up. This is only possible if the XPhone Connect Server service manager was used to change the service account.

Attachments in chat messages#

Activate this option to allow attachments such as screenshots in chat messages.

Attention

The prerequisite for sending attachments is the activation of the FILE TABLE function of the SQL server. This is done automatically for the internal database both when installing XPhone Connect for the first time and when updating. For reasons of data integrity, customer (external) databases are not changed with regard to this setting; the sending of attachments in chat messages is initially not available in these cases.

Checking the internal database#

If there are problems with the activation or use of the attachments in chat messages function after installation or update, you can check the activation of the FILE TABLE function of the internal database:

Run the DBFileTableActivator application as administrator in the XPhone Connect server directory.
Then check the corresponding log to see whether the activation was successful:
- C:\Windows\SysWOW64\DBFileTableActivator.log -> FileStream configuration is OK.

Alternative:

Run the SQL Server Configuration Manager on the XPhone Connect server (sqlservermanagerXY.msc, where XY = version of the SQL Server Manager used; in the current version of XPhone Connect, for example, sqlservermanager12.msc).
Open the properties of the XPCONNECT database under SQL Server Services by right-clicking.
In the FILESTREAM tab, check the Enable FILESTREAM for Transact-SQL access option.
If the option is activated, the internal database is configured correctly.

Manual activation of the FILE TABLE function of the customer’s own SQL server

The XPhone Connect setup does NOT change this option on customer databases!

In order to activate attachments in chat messages, the database administrator must activate the FILESTREAM option at instance and database level for his XPhone Connect database itself.

Important

Hints on Microsoft Docs

Activation FILESTREAM for customer-owned SQL servers#

Initial situation of the configuration example:

The XPhone Connect Server (version 7 or higher) is connected to a customer-owned database instance CONNECT (external SQL Server). The database within the instance is called XPCONNECT. (See also Using_an_existing_SQL_Server)

The FileTable or FILESTREAM options required for sending files (images, screenshots) in the chat are not active, as neither the XPhone Connect Setup nor the DBFileTableActivator.exe tool change the customer instance.

These instructions describe the steps for manually activating file sending in the chat.

Fundamentals

Microsoft describes all the necessary steps in Enable filetable on Microsoft Docs
- however, extended options are configured there that are not required for the desired function. The minimum configuration is therefore described below.
  
  Important
  
  If other databases FILESTREAM are used within the database instance, please note the configuration options listed by Microsoft!
Activate FILESTREAM at instance level
- Start SQL Server Configuration Manager.
- Open the properties of the XPhone Connect database instance (right-click on the Instance CONNECT > Properties).
- Switch to the FILESTREAM tab.
- Activate the option Enable FILESTREAM for Transact-SQL access.
  
  Hint
  
  If this option (and any sub-options) is already activated, leave these settings unchanged!
- Save the setting with Apply and close the dialog with OK.
- Start SQL Server Management Studio.
- Connect the XPhone Connect database instance.
- Open the properties of the database instance (right-click on the Instance CONNECT > Properties).
- Select the Advanced page.
- In the FILESTREAM section, change the value of the FILESTREAM access level (FILESTREAM Access Level) from Disabled to Transact-SQL access enabled.
  
  Hint
  
  This corresponds to the value 1 in the Microsoft documentation instead of the value 2 (full access) required there. If Transact-SQL access or full access is already activated, leave this setting unchanged!
- Save the setting with OK.
- Restart the CONNECT database instance.
- If necessary, adjust your firewall rules for FILESTREAM by following the documentation from Microsoft or your firewall.
Providing a FILESTREAM file group at database level
- Start SQL Server Management Studio.
- Connect the XPhone Connect database instance.
- Open the properties of the XPhone Connect database (right-click on XPDATA > Properties).
- Select the Filegroups page.
- Add a new file group IMDATA in the FILESTREAM section and activate the Default option.
  
  Hint
  
  FILESTREAM files is initially set to 0, this will be automatically changed to 1 later.
- Save the setting with OK.
Definition of a directory for FileTables at database level
- Open the properties of the XPhone Connect database XPDATA again.
- Select the Files page.
- Add another entry to the Database files list:
  
  Logical Name: IMDATA
  
  File Type: FILESTREAM Data
  
  File group: IMDATA
  
  The other default values remain unchanged.
- Save the settings with OK.
Customize further options of the database
- Open the properties of the XPhone Connect database XPDATA again.
- Select the Options page.
- In the FILESTREAM section, enter the value IMData under FILESTREAM directory name (FILESTREAM Directory Name), non-transacted FILESTREAM access (FILESTREAM Non-Transacted Access) remains switched off (Off).
- Save the setting with OK.
Restart the XPhone Connect Server and a file table IMFile will be created.
Checking the FileTable activation
- Start SQL Server Management Studio.
- Connect the XPhone Connect database instance.
- In the XPDATA database, expand the Tables > File Tables node.
- The IMFile table is listed:
  
  This completes the setup of the database for sending files in the chat.
Function test in the client
- Start an XPhone Connect Client version 7 or higher.
- Open a new individual or group chat.
- Insert an image file or a screenshot in the text input via the + icon and send the message.

SQL Express Server Update#

A special case is updating the local SQL Express server to the latest version supported by XPhone Connect.

The Backup Manager supports this migration. Proceed as follows:

Update the XPhone Connect Server to the latest version and then restart the XPhone Server service. The SQL Server will remain on the old version.
Create a backup of the system using the Backup Manager.
Stop the XPhone Connect Server service.
Install the new SQL Server instances using the installation scripts provided.

The scripts are located on the installation medium and must be executed with admin rights:
.\Software\Server\3rdParty\sql\MigrateSQLServer_UC.BAT
.\Software\Server\3rdParty\sql\MigrateSQLServer_STAT.BAT
The new SQL Server instances are now called XPConnect2019 and XPAnalytics2019, respectively.

In the file atlas.xml, change all instances of the old service name or instance names to the new names/designations of the SQL Server instances. You can find the instance name in the Windows services in parentheses:

OLD: connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPVDIR;Data Source=(local)\XPCONNECT"

NEW: connection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XPVDIR;Data Source=(local)\XPCONNECT2019"

After the database has been successfully migrated, the old SQL Server instances are no longer required by the XPhone Connect Server and can be uninstalled from the system.