Solved: SQL Server Installation

[RBehr]

We are in the evaluation phase for OTRS (and it's looking great!). I have a question regarding the use of a SQL Server database.

When we first installed OTRS we used MySQL and it worked well. Now, having set up a SQL Server database, performance is radically reduced and we get occasional 404 errors.

In the /Kernel/Config.pm file I have used the following lines for database connectivity:

# DatabaseHost
# (The database host.)
$Self->{'DatabaseHost'} = 'OURSERVER';
# Database
# (The database name.)
$Self->{'Database'} = 'OTRS';
# DatabaseUser
# (The database user.)
$Self->{'DatabaseUser'} = 'OTRS_User';
# DatabasePw
# (The password of database user. You also can use bin/otrs.CryptPassword.pl
# for crypted passwords.)
$Self->{'DatabasePw'} = 'password';
# DatabaseDSN
# (The database DSN for MySQL ==> more: "man DBD::mysql")
$Self->{'DatabaseDSN'} = 'DBI:ODBC:driver={SQL Server};Server=OURSERVER;database=OTRS;uid=OTRS_User;pwd=password;';
$Self->{'Database::Type'} = 'mssql';

As I say, connectivity is there but performance has taken a nasty hit.

Have I set this up correctly? The Admin Manual deals with the DatabaseHost, Database, DatabaseUser and DatabasePw settings but does not mention the DatabaseDSN and Datase::Type settings (I nicked them off another post on this forum). Any suggestions regarding best practise when setting up with SQL Server would be greatly appreciated.

Many thanks

[jojo]

MS SQL will be much slower than other databases as OTRS needs to use ODBC...

[RBehr]

I was rather hoping you would tell me to use a SQL Server connection string there. Is MS SQL not directly supported by OTRS then?

[jojo]

as they are no Perl Drivers for MS SQL OTRS needs to stick to ODBC, there is no native connection.

[ferrosti]

One could give ADO a try. (ActiveX Data Object)
I have used ADO during my trainee program a century ago to connect to mySQL, DB2 as well as Access and MS SQL. So my knowledge has aged a little about that. There is a driver for DBD as well.
I cannot tell you anything about side effects. I needed it to make use of server side cursors, which gave a huge performance improvement when the client did not need any output.

[RBehr]

The performance hit came when testing in our DEV environment, which is on virtual (VMWare) servers. Put the MS SQL database in our production environment (i.e. on a physical server) and performance is greatly improved and quite acceptable.

Have hit a new problem with inserting new queues in 3.0.5 which did not occur in 3.0.4, but raised this in separate forum topic (see http://forums.otrs.org/viewtopic.php?f= ... 08&p=31260).

[ferrosti]

As long as you do not place your DBMS and its DB files into a virtual disk everything is just fine.
I have had the same issue here with mySQL. DB Servers come into performancec lacks, when the VM needs to schedule its I/O. Usually this also rises CPU cycles a lot.

We do run all servers on ESX machines, including mySQL, but '/var/lib/mysql' is mounted via cifs from a large disk appliance.