Cookie Consent by Free Privacy Policy Generator Update cookies preferences
All Categories Getting Started Database Setup & Maintenance

Database Setup & Maintenance

In this article

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.

db_ddladmin

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

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:

db_datawriter

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader

Members of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.

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.

Permissions.png

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.

Was this article helpful?

Thanks for your feedback!