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.
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.
Open a new query in SSMS.
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.
Open a new query in SSMS.
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:
Create a new SQL Server Agent Job and name it (e.g., "Dynamic Index Reorganization").
Add a New Job Step:
Set the job step to execute the script above in the target database.
Set a Schedule for the job (e.g., weekly during off-peak hours).
Save and start the job manually to test it, ensuring it works as expected.