<commit_before>[[prepareMssql]]
Prepare the MS SQL Server Database for the Tigase Server
========================================================
:author: <NAME> <<EMAIL>>
:version: v2.0, June 2014: Reformatted for AsciiDoc.
:date: 2013-09-04 14:57
:revision: v2.1

:toc:
:numbered:
:website: http://tigase.net

This guide describes how to prepare the MS SQL Server database for connecting the Tigase server to it.

Basic Setup
-----------

It's expected that a working installation of Microsoft SQL Server is present. The following guide will describe the necessary configurations required for using MS SQL Server with Tigase XMPP Server.

Preparing MS SQL Server Instance
--------------------------------

After installation of MS SQL Server an instance needs to be configure to handle incoming JDBC connections. For that purpose it's required to open _SQL Server Configuration Manager_. In the left-hand side panel navigate to _SQL Server Configuration Manager_, then _SQL Server Network Configuration -> Protocols for $\{INSTANCE_NAME}_. After selecting instance in the right-hand side panel select TCP/IP and open _Properties_, in the Protocol tab in General section select Yes for Enabled property. In the IP Addresses tab select Yes for Active and Enabled properties of all IP Addresses that you want MS SQL Server to handle. Subsequently set the TCP Port property (if missing) to the default value - 1433. A restart of the instance may be required afterwards.

Configuration using MS SQL Server Management Studio
---------------------------------------------------

In order to prepare the database you can use either a wizard or execute queries directly in the Query Editor. Firstly you need to establish a connection to the MS SQL Server instance. From Object Explorer select Connect and in the Connect to Server dialog enter administrator credentials.

Using Wizards
~~~~~~~~~~~~~

- Create Login
+
In the left-hand side panel select Security -> Logins and from context menu choose New Login, in the Wizard window enter desired Login name, select SQL Server authentication and enter desired password subsequently confirming action with OK

- Create Database
+
From the Object Explorer select Databases node and from context menu select New Database; in the Wizard window enter desired Database name and enter previously created Login name into Owner field; subsequently confirming action with OK.

Using Queries
~~~~~~~~~~~~~

From the Object Explorer root node's context menu select New Query. In the Query windows execute following statements adjusting details to your liking:


[source,sql]
-------------------------------------
USE [master]
GO

CREATE DATABASE [tigasedb];
GO

CREATE LOGIN [tigase] WITH PASSWORD=N'<PASSWORD>', DEFAULT_DATABASE=[tigasedb]
GO

ALTER AUTHORIZATION ON DATABASE::tigasedb TO tigase;
GO
-------------------------------------

Import Schema
-------------

From the File menu Select Open -> File (or use Ctrl+O) and then open following files:

- sqlserver-schema-5-1-schema.sql
- sqlserver-schema-5-1-sp.sql
- sqlserver-schema-5-1-props.sql

Subsequently select created database from the list of Available Databases (Ctrl+U) available on the toolbar and execute each of the opened files in the order listed above.

Configuring from command line tool
----------------------------------

Creation of the database and import of schema can be done from command line as well. In order to do that, execute following commands from the directory where Tigase XMPP Server is installed otherwise paths to the schema need to be adjusted accordingly:

[source,bash]
-------------------------------------
sqlcmd -S %servername% -U %root_user% -P %root_pass% -Q "CREATE DATABASE [%database%]"
sqlcmd -S %servername% -U %root_user% -P %root_pass% -Q "CREATE LOGIN [%user%] WITH PASSWORD=N'%password%', DEFAULT_DATABASE=[%database%]"
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -Q "ALTER AUTHORIZATION ON DATABASE::%database% TO %user%;"
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-5-1-schema.sql
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-5-1-sp.sql
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-5-1-props.sql
-------------------------------------

Above can be automatized with provided script %tigase-server%\scripts\db-create-sqlserver.cmd (note: it needs to be executed from main Tigase XMPP Server directory due to maintain correct paths):

[source,sh]
-------------------------------------
$ scripts\db-create-sqlserver.cmd %database_servername% %database_name% %tigase_username% %tigase_password% %root_username% %root_password%
-------------------------------------

If no parameters are provided then the following defaults are used:

[source,bash]
-------------------------------------
%database_servername%=localhost
%database_name%=tigasedb
%tigase_username%=tigase
%tigase_password%=<PASSWORD>
%root_username%=root
%root_password%=<PASSWORD>
-------------------------------------

Tigase configuration - init.properties
--------------------------------------

Configuration of the MS SQL Server follows general database convention. For MS SQL Support --user-db needs to be set to sqlserver:

[source,bash]
-------------------------------------
--user-db=sqlserver
-------------------------------------

and the --user-db-uri needs to point to the configured database:

[source,bash]
-------------------------------------
--user-db-uri=jdbc:[jtds:]sqlserver://db_hostname:port[;property=val]
-------------------------------------

where any number of additional parameters can (and should) consist of:

- databaseName - name of the database
- user - username configured to access database
- password - password for the above username
- schema - name of the database schema
- lastUpdateCount - 'false' value causes all update counts to be returned, including those returned by server triggers

Example:

[source,sh]
-------------------------------------
--user-db-uri=jdbc:sqlserver://hostname:1433;databaseName=tigasedb;user=tigase;password=<PASSWORD>;schema=dbo;lastUpdateCount=false
-------------------------------------

JDBC: jTDS vs MS JDBC driver
----------------------------

Tigase XMPP Server supports two JDBC drivers intended to be used with Microsoft SQL Server - one created and provided by Microsoft itself and the alternative implementation - jTDS. Tigase is shipped with the latter in the distribution packages. However, we do not recommend using jDTS with Tigase and urge users to use JDBC driver created by Microsoft. It can be downloaded from Microsoft website: link:http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774[JDBC Drivers 4.0, 4.1 for SQL Server] then unpack the archive. Copy sqljdbc_4.0/enu/sqljdbc4.jar file to $\{tigase-server}/jars directory.

Depending on the driver used --user-db-uri needs to be configured accordingly.

- Microsoft driver:
+
[source,bash]
-------------------------------------
--user-db-uri=jdbc:sqlserver://...
-------------------------------------
- jDTS driver
+
[source,bash]
-------------------------------------
--user-db-uri=jdbc:jdts:sqlserver://...<commit_msg>change MS SQL driver documentation; #3374<commit_after>[[prepareMssql]]
Prepare the MS SQL Server Database for the Tigase Server
========================================================
:author: <NAME> <<EMAIL>>
:version: v2.0, June 2014: Reformatted for AsciiDoc.
:date: 2013-09-04 14:57
:revision: v2.1

:toc:
:numbered:
:website: http://tigase.net

This guide describes how to prepare the MS SQL Server database for connecting the Tigase server to it.

Basic Setup
-----------

It's expected that a working installation of Microsoft SQL Server is present. The following guide will describe the necessary configurations required for using MS SQL Server with Tigase XMPP Server.

Preparing MS SQL Server Instance
--------------------------------

After installation of MS SQL Server an instance needs to be configure to handle incoming JDBC connections. For that purpose it's required to open _SQL Server Configuration Manager_. In the left-hand side panel navigate to _SQL Server Configuration Manager_, then _SQL Server Network Configuration -> Protocols for $\{INSTANCE_NAME}_. After selecting instance in the right-hand side panel select TCP/IP and open _Properties_, in the Protocol tab in General section select Yes for Enabled property. In the IP Addresses tab select Yes for Active and Enabled properties of all IP Addresses that you want MS SQL Server to handle. Subsequently set the TCP Port property (if missing) to the default value - 1433. A restart of the instance may be required afterwards.

Configuration using MS SQL Server Management Studio
---------------------------------------------------

In order to prepare the database you can use either a wizard or execute queries directly in the Query Editor. Firstly you need to establish a connection to the MS SQL Server instance. From Object Explorer select Connect and in the Connect to Server dialog enter administrator credentials.

Using Wizards
~~~~~~~~~~~~~

- Create Login
+
In the left-hand side panel select Security -> Logins and from context menu choose New Login, in the Wizard window enter desired Login name, select SQL Server authentication and enter desired password subsequently confirming action with OK

- Create Database
+
From the Object Explorer select Databases node and from context menu select New Database; in the Wizard window enter desired Database name and enter previously created Login name into Owner field; subsequently confirming action with OK.

Using Queries
~~~~~~~~~~~~~

From the Object Explorer root node's context menu select New Query. In the Query windows execute following statements adjusting details to your liking:


[source,sql]
-------------------------------------
USE [master]
GO

CREATE DATABASE [tigasedb];
GO

CREATE LOGIN [tigase] WITH PASSWORD=N'<PASSWORD>', DEFAULT_DATABASE=[tigasedb]
GO

ALTER AUTHORIZATION ON DATABASE::tigasedb TO tigase;
GO
-------------------------------------

Import Schema
-------------

From the File menu Select Open -> File (or use Ctrl+O) and then open following files:

- sqlserver-schema-5-1-schema.sql
- sqlserver-schema-5-1-sp.sql
- sqlserver-schema-5-1-props.sql

Subsequently select created database from the list of Available Databases (Ctrl+U) available on the toolbar and execute each of the opened files in the order listed above.

Configuring from command line tool
----------------------------------

Creation of the database and import of schema can be done from command line as well. In order to do that, execute following commands from the directory where Tigase XMPP Server is installed otherwise paths to the schema need to be adjusted accordingly:

[source,bash]
-------------------------------------
sqlcmd -S %servername% -U %root_user% -P %root_pass% -Q "CREATE DATABASE [%database%]"
sqlcmd -S %servername% -U %root_user% -P %root_pass% -Q "CREATE LOGIN [%user%] WITH PASSWORD=N'%password%', DEFAULT_DATABASE=[%database%]"
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -Q "ALTER AUTHORIZATION ON DATABASE::%database% TO %user%;"
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-5-1-schema.sql
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-5-1-sp.sql
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-5-1-props.sql
-------------------------------------

Above can be automatized with provided script %tigase-server%\scripts\db-create-sqlserver.cmd (note: it needs to be executed from main Tigase XMPP Server directory due to maintain correct paths):

[source,sh]
-------------------------------------
$ scripts\db-create-sqlserver.cmd %database_servername% %database_name% %tigase_username% %tigase_password% %root_username% %root_password%
-------------------------------------

If no parameters are provided then the following defaults are used:

[source,bash]
-------------------------------------
%database_servername%=localhost
%database_name%=tigasedb
%tigase_username%=tigase
%tigase_password%=tigase12
%root_username%=root
%root_password%=<PASSWORD>
-------------------------------------

Tigase configuration - init.properties
--------------------------------------

Configuration of the MS SQL Server follows general database convention. For MS SQL Support --user-db needs to be set to sqlserver:

[source,bash]
-------------------------------------
--user-db=sqlserver
-------------------------------------

and the --user-db-uri needs to point to the configured database:

[source,bash]
-------------------------------------
--user-db-uri=jdbc:[jtds:]sqlserver://db_hostname:port[;property=val]
-------------------------------------

where any number of additional parameters can (and should) consist of:

- databaseName - name of the database
- user - username configured to access database
- password - <PASSWORD> the above <PASSWORD>
- schema - name of the database schema
- lastUpdateCount - 'false' value causes all update counts to be returned, including those returned by server triggers

Example:

[source,sh]
-------------------------------------
--user-db-uri=jdbc:sqlserver://hostname:1433;databaseName=tigasedb;user=tigase;password=<PASSWORD>;schema=dbo;lastUpdateCount=false
-------------------------------------

JDBC: jTDS vs MS JDBC driver
----------------------------

Tigase XMPP Server supports two JDBC drivers intended to be used with Microsoft SQL Server - one created and provided by Microsoft itself and the alternative implementation - jTDS. Tigase is shipped with the latter in the distribution packages. Starting with the version 7.1.0 we recommend using jDTS driver that is shipped with Tigase as JDBC driver created by Microsoft can cause problems with some components in cluster installations. MS driver can be downloaded form the website: link:http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774[JDBC Drivers 4.0, 4.1 for SQL Server] then unpack the archive. Copy sqljdbc_4.0/enu/sqljdbc4.jar file to $\{tigase-server}/jars directory.

Depending on the driver used --user-db-uri needs to be configured accordingly.

- Microsoft driver:
+
[source,bash]
-------------------------------------
--user-db-uri=jdbc:sqlserver://...
-------------------------------------
- jDTS driver
+
[source,bash]
-------------------------------------
--user-db-uri=jdbc:jdts:sqlserver://...
{'fraction_non_alphanumeric': 0.19140945096968043, 'fraction_numerical': 0.007716470909587545, 'mean_word_length': 2.7793548387096774, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3530042', 'n_tokens_mistral': 3802, 'n_tokens_neox': 3587, 'n_words': 1615}