MSSQL Database System Requirements  

Mentioned below are the minimum requirements to run SupportCenter Plus on an MSSQL setup. This requirement is applicable only to the SupportCenter Plus -related database.

For optimum usage of MSSQL Database,

• Configure File Autogrowth.

• Enable Read Committed Snapshot Isolation (RCSI) and Database Maintenance Schedule.

To enable database maintenance schedule, go to Admin > General Settings > Performance Settings (or) ESM Directory > Performance Settings. Click here, to learn more.

 To learn more about MSSQL best practices, click here.

How to enable read committed snapshot isolation

All MSSQL databases usually have lock issues, which may affect an application’s optimal performance. To avoid such performance issues in SupportCenter Plus, enable the read committed snapshot isolation (RCSI) by following these steps:

This might take a while to complete and also requires downtime, so please schedule it over a weekend.

➤ Stop SupportCenter Plus application.

➤ Log in to SQL Management studio.

➤ Take the SupportCenter Plus database offline by executing

USE master

GO

ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

➤ Enable the read committed snapshot by running

ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

GO

➤ Bring the database online using

USE master

GO

ALTER DATABASE <db_name> SET ONLINE

GO

➤ Check if you have enabled RCSI successfully by running the following query                                                                                                                                            

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = '<db_name>'

If the output to this query is 1, you've successfully enabled RCSI.

Benefits of enabling the MSSQL Snapshot Mode

Simultaneous user-querying of the database will be possible without any delay or disruption (although the load on the application might exceed the usual limits).

User querying records will be maintained in a separate tempdb, which will not be affected by other user queries occurring in parallel or subsequently.

Database tables will be updated only after a transaction reaches completion.

MSSQL Database Configuration  

We strongly recommend that you have an initial database capacity of at least 5GB and extend it periodically in advance by monitoring the current database file size to avoid an auto-growth event being triggered unnecessarily. This will ensure optimum application performance and smooth data processing.

File Autogrowth for database files can be configured as well. To learn more, click here.

Creating Database

Run the following query to create a database:

USE master;
GO
CREATE DATABASE <dbname>
ON(
NAME=<dbname>,
FILENAME='D:\SCP\<dbname>.mdf',
SIZE=5GB);
GO

Extending Database Size

Find the current database size by running the query below

USE master;
GO

select (size*8)/(1024*1024) from sys.master_files where DB_NAME(database_id)='supportcenter' and physical_name like '%.mdf%';

GO

Run the following query to add 5 GB to the current database size

USE master;
GO
ALTER DATABASE supportcenter 
MODIFY FILE
(NAME = <dbname>,
SIZE = <currentdbsize+5GB>);
GO

How to configure File Autogrowth Settings  

Default autogrowth settings in the SQL Server engine may lead to disk fragmentation and performance issues. These values are set at 1 MB and 10 percent for Data File and Log File, respectively. We recommend that you configure these as fixed values and not in percentage.

To ensure optimum application performance, we strongly recommend that the Data File and Log File autogrowth values are configured as 256 MB and 128 MB, respectively.

Follow these steps to configure File Autogrowth

➤ Log in to SQL Management studio and run the following query:

USE <db_name>

EXEC sp_helpfile

GO

➤ If the configured growth values for Data (.mdf) and Log (.ldf) files are less than the recommended values, run the following query:



USE master

GO

ALTER DATABASE <db_name>

MODIFY FILE ( NAME = N'<db_name>', FILEGROWTH = 256MB )

GO

ALTER DATABASE <db_name>

MODIFY FILE (NAME = N'<db_name>_log', FILEGROWTH = 128MB )

GO

➤ Re-run the query in step 1 and check if the autogrowth values now correspond to the recommended values.

 

PostgreSQL (Applicable to SupportCenter Plus version 10.0 and earlier)

Since PostgreSQL 9.2 requires users to modify kernel resources to allot required resources for Postgres server,

i. Add the the below mentioned entry in /etc/sysctl.conf

kernel.shmmax=536870912

kernel.shmall=131072

ii. Execute sudo sysctl -p /etc/sysctl.conf to reload the configured change.

Note: This is only applicable for Linux OS installations that use bundled Postgres.

Antivirus Scan Exemption  

Any antivirus software, such as Symantec, running on the same system as SupportCenter Plus can affect the application's performance. Therefore, to ensure application performance, exempt the following items from antivirus scanning:

• pgsql.exe process and PostgreSQL folder for the PGSQL database

• Log folders for both MSSQL and PGSQL databases

Only the specified items need to be exempted from the scanning. The rest of the SupportCenter Plus folder can be scanned for antivirus.