Database Setup & Maintenance

In this article

• Database Requirements

• Connection

• Indexes

• Partitioning

• Routine Maintenance

Requirements

Tascu requires a Microsoft SQL Server database to store all production data, this can be held on a local server, or in a cloud database.

Supported versions of SQL Server:

• SQL Server 2012

• SQL Server 2014

• SQL Server 2016

Azure SQL Server

Installation

We will provide a SQL script to create the Tascus database.

Permissions

Full Permissions

To implement automatic Tascus updates, it's useful to have the CREATE TABLE permissions applied - this will let the Tascus client run a database update script, creating any necessary tables within the Tascus database when needed.

Minimum Required Permissions

If it's not permitted to use db_ddladmin, the minimum permissions users connecting to the Tascus database need the following permissions to run the Tascus client application: db_datawriter AND db_datareader:

If you are implementing minimum permissions, it's worth explicitly denying the CREATE TABLE permission, just in case the Tascus client tries to run a script to create a table, we've seen instances where 'ghost' tables have been created.

Connection

When Tascus starts for the first time, each station will need to define the connection to the Tascus database.

Behind the scenes, Tascus uses an SQLOLEDB connection string to connect to SQL server, using port TCP 1433

Indexes

Over time, tables and indexes can become fragmented, which can slow down performance. Running a defragmentation or reorganization (especially on indexes) can help improve speed.

To check for and address index fragmentation in SQL Server Management Studio (SSMS), follow these steps:

1. Check Index Fragmentation

First, you can check the fragmentation level using the sys.dm_db_index_physical_stats dynamic management view.

1. Open a new query in SSMS.

2. Run the following script to identify fragmentation levels for a specific database:

   /* Checks the fragmentation in percentage on every database index  
   5-30% fragmentation: Consider reorganizing the index.
   Above 30% fragmentation: Consider rebuilding the index.  */
   
   USE Tascus_Demo;  -- Replace with your database name
   GO
   SELECT 
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       indexstats.index_id,
       indexstats.avg_fragmentation_in_percent,
       indexstats.page_count
   FROM 
       sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
       INNER JOIN sys.indexes AS i ON indexstats.object_id = i.object_id AND indexstats.index_id = i.index_id
   WHERE 
       indexstats.database_id = DB_ID()
   ORDER BY 
       indexstats.avg_fragmentation_in_percent DESC;
   

   • This query returns the average fragmentation percentage for each index. Focus on indexes with high fragmentation levels:

     • 5-30% fragmentation: Consider reorganizing the index.

     • Above 30% fragmentation: Consider rebuilding the index.

2. Reorganize Indexes

After identifying fragmented indexes, you can reorganize or rebuild them to improve performance.

A reorganization is a lighter operation suitable for moderate fragmentation (5-30%) that does not lock the table.

1. Open a new query in SSMS.

2. Run the following command to reorganize all indexes in the database:

   USE Tascus_Demo_2;  -- Replace with your database name
   GO
   
   DECLARE @TableName NVARCHAR(128);
   DECLARE @IndexName NVARCHAR(128);
   DECLARE @SQL NVARCHAR(MAX);
   
   -- Cursor to loop through each index in the database
   DECLARE index_cursor CURSOR FOR
   SELECT 
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName
   FROM 
       sys.indexes AS i
   JOIN 
       sys.objects AS o ON i.object_id = o.object_id
   WHERE 
       i.type > 0  -- Only include non-heap indexes
       AND i.is_disabled = 0  -- Exclude disabled indexes
       AND o.type = 'U';  -- Only user tables
   
   OPEN index_cursor;
   
   FETCH NEXT FROM index_cursor INTO @TableName, @IndexName;
   
   WHILE @@FETCH_STATUS = 0
   BEGIN
       -- Generate the reorganize command with LOB_COMPACTION for each index
       SET @SQL = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@TableName) + ' REORGANIZE WITH (LOB_COMPACTION = ON);';
       
       -- Execute the reorganize command
       PRINT @SQL;  -- For debugging/logging purposes
       EXEC sp_executesql @SQL;
   
       FETCH NEXT FROM index_cursor INTO @TableName, @IndexName;
   END;
   
   CLOSE index_cursor;
   DEALLOCATE index_cursor;

Note that in this query, the REORGANIZE command includes WITH (LOB_COMPACTION = ON) to ensure that large objects (LOB data types) are compacted, reducing fragmentation.

Partitioning

For larger tables, partitioning can improve performance by allowing queries to scan only relevant partitions rather than entire tables.

Routine Maintenance

Schedule regular maintenance tasks like updating statistics, rebuilding indexes, and cleaning up any unused or temporary data to keep the database running smoothly.

To maintain index health automatically, you can create a SQL Server Agent job to periodically reorganize or rebuild indexes based on fragmentation levels.

Scheduling as a SQL Server Agent Job

To automate this process, you can wrap this script in a SQL Server Agent job using the following steps:

1. Create a new SQL Server Agent Job and name it (e.g., "Dynamic Index Reorganization").

2. Add a New Job Step:

   • Set the job step to execute the script above in the target database.

3. Set a Schedule for the job (e.g., weekly during off-peak hours).

4. Save and start the job manually to test it, ensuring it works as expected.