SQL Server Maintenance Tasks

1.1. SQL Server Maintenance Tasks

12d Synergy itself requires very little maintenance. However, as it uses Microsoft SQL Server to manage all its meta data, SQL Server itself has some basic maintenance tasks that are required to ensure good database health.

1.2. Index Fragmentation

Indexes are used to speed up how an application works with an SQL Server database. However, over time and as more and more data is written in to database tables, the indexes may become fragmented. Like in disk drive fragmentation, this can have an impact on speed.

It is therefore important to rebuild or reorganise the indexes in your databases regularly. How often you should do this depends on the rate of data being added, but it could be as regularly as once a week or once a month. 

Failing to do so can lead to timeouts and other unexpected behaviour.

1.2.1. Index Fragmentation with less than 30 Users

1. In the 12d Synergy Administration application click General tab > Maintenance tab > Database tab.
   
   
   
2. Click the Tune Now button.
   
   
   
3. Click the Yes button, if you are ready for the procedure. 

1.2.2. Index Fragmentation with more than 30 Users

At least twice a month, you should consider rebuilding or reorganising your database tables. General practice is to only rebuild or reorganise for indexes >75% fragmentation and more than 500 pages. At this level of 12d Synergy usage, it is recommended to use the SQL Server Agent to schedule this operation rather than doing it manually. 

1.3. Transaction Log Truncation

SQL Server has two backup modes.

1. SIMPLE mode - When you use this mode, you can only rollback to the last full or differential backup. You do not have to truncate your transaction log.
2. FULL mode - When you use this mode, SQL Server keeps a full transaction log of all INSERTS, UPDATES and DELETES which can give you the minute rollback in case of disaster. This is a very useful tool, but large transaction logs can be slow to write to or to grow. You must truncate your transaction log or it may lead to timeouts.

Please see the guide on backup best practices, which is available here.