Switching Database to Microsoft SQL Server

To use Microsoft SQL Server Iotellect Server's database engine, follow the generic instructions from Switching To Another Database Engine article. Here is a list of notes specific to Microsoft SQL Server:

  • Put sqljdbc4.jar into the /jar subfolder of Iotellect Server installation folder.

  • Set Database Driver in Iotellect Server Global Configuration Setting to com.microsoft.sqlserver.jdbc.SQLServerDriver. This is the java class name for the Microsoft SQL Server driver.

  • The Database URL setting for the Microsoft SQL Server database has the following format: jdbc:sqlserver://[serverName[instanceName][:port]];databasename=dbname[;property=value[;property=value]], where jdbc:sqlserver:// is known as the sub-protocol and is constant, serverName is the DNS name or IP address of the server to connect to (can be localhost), instanceName is the instance to connect to on serverName (If not specified, a connection to the default instance is made), portNumber is the port to connect to on serverName (The default is 1433). For example, if your Microsoft SQL Server runs on 192.168.0.1, on the default port, use the following URL to connect to the default instance: jdbc:sqlserver://192.168.0.1:1433. You can also omit the default port and use jdbc:sqlserver://192.168.0.1 instead.

  • The property and value pair trustServerCertificate=true is included by default. Consult the Microsoft SQL Server JDBC Driver documentation if different security strategies are needed.

  • Set Database Dialect setting to SQLServerDialect.

Native Authentication Setup

In several cases you should use Native Authentication. Follow the instructions to configure it:

  • Download and install the latest sqljdbc_auth.dll library from Microsoft site.

  • Put sqljdbc_auth.dll into the folder, which is defined by java.library.path option of JVM.

  • Use previous URL example and add IntegratedSecurity parameter: IntegratedSecurity=true.

Example: jdbc:sqlserver://192.168.0.1:1433;IntegratedSecurity=true

Domain Authentication Setup

Microsoft SQL Server JDBC Driver doesn't allow to use domain authentication. In order to configure Microsoft SQL Server connection using your domain user credentials, do the following:

  • Put jtds-X.X.X.jar into the /jar subfolder of Iotellect Server installation folder.

  • Set Database Driver in Iotellect Server Global Configuration Setting to net.sourceforge.jtds.jdbc.Driver.

  • The Database URL setting has the following format: jdbc:jtds:sqlserver://[serverName[instanceName][:port]];domain=domainValue;user=userValue;password=passwordValue[;property=value[;property=value]], where jdbc:jtds:sqlserver:// is known as the sub-protocol and is constant, serverName is the DNS name or IP address of the server to connect to (can be localhost), instanceName is the instance to connect to on serverName (If not specified, a connection to the default instance is made), portNumber is the port to connect to on serverName (The default is 1433).

Example: jdbc:jtds:sqlserver://192.168.0.1:1433;domain=COM;user=admin;password=pass

To successfully authenticate using jTDS driver Iotellect Server should be operating on Windows machine included into the domain.

Microsoft SQL Server Setup

To keep Microsoft SQL Server performance at the appropriate level it's necessary:

  • To specify a fill factor for indexes

  • To configure table and index compression

It's recommended to specify a fill-factor value of 70. To do this open Microsoft SQL Server Management Studio and:

1. Click on the database connection.

2. Choose Properties from the bottom of the list.

3. Click on the Database Settings page on the left under Server Properties.

4. Specify 70 as the Default index fill factor value.

It's important to do it before the first Iotellect Server startup, because the above configuration is applied to new indexes (e.g. newly created tables) only.

However, it is possible to change fill-factor option for already created indexes. There is a number of various ways to do this:

http://technet.microsoft.com/en-us/library/ms177459(v=sql.110).aspx

All advices cause the same effect - rebuilding indexes with new fill-factor value.

Fill-factor helps to decrease a rate of index fragmentation. But it's also necessary to track an index fragmentation level and regularly reorganize/rebuild indexes.

Use the below script to keep index fragmentation at reasonable level. For example, create a Microsoft SQL Server Agent job to run the script by a scheduler.

USE ag;

GO

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;

-- Choose indexes with fragmentation level more than 10%
-- Define current database
SET @dbid = DB_ID();
SELECT
    [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0  
AND index_id > 0 -- ignore heap

-- Declare a cursor for a processing partitions' list.
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

OPEN partitions;

-- Loop by partition 
WHILE (1=1)
BEGIN

FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30% is limit for index rebuilding  
IF @frag < 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0
    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1
    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);
PRINT N'Executed: ' + @command;
END;

CLOSE partitions;
DEALLOCATE partitions;

-- delete temporary table
DROP TABLE #work_to_do;
GO

Table and index compression allows to decrease logical read count.

It's also recommended to enable page compression for ag_properties table. See http://msdn.microsoft.com/en-us/library/hh710070.aspx for details.

Was this page helpful?