With Microsoft SQL Express edition, it isn’t possible to schedule maintenance or a backup of the Troika data that can be set to run automatically, as this function is only available in SQL Standard or Enterprise editions.
To allow Troika users to schedule a backup of their Troika database and perform other housekeeping tasks, the Troika SQL Utility can be installed to perform these tasks. Please download and install the Troika SQL Utility from here.
Installing, Configuring and Using the Troika SQL Utility
To install the SQL Utility for Troika, unzip all the files provided to you, to a folder on a hard disk on your SQL Server. The folder must exist on a physical hard disk and not a mapped drive of a different server. For example, C:\TWWDATA\SQL_UTILITY. Included in the files will be a DLL for outputting error messages and a log of commands executed.
A separate SQL script file (Maintenance.sql) will be unzipped to this folder, and must be executed from SQL Management Studio against each Troika database where a backup or maintenance tasks need to be completed.
Ensure NET Framework 4 is installed on the SQL server. To check which versions of the .NET Framework you have installed, enter ‘%systemroot%\Microsoft.Net\Framework’ into the address bar of Windows Explorer. If the folder v4.0.30319 is present, check to see if the file system.dll exists within the folder. If the system.dll or the folder is missing, please install .NET framework v4. Check the Microsoft website for the latest versions of the download for .NET Framework 4. A copy of the .NET framework 4 can also be found with the unzipped files.
http://www.microsoft.com/en-us/download/details.aspx?id=17851
Edit the config file ‘TroikaSQLUtility.exe.config’ using Notepad, and enter the following values:
Enter the servername, database name, SQL user account and password in the connection string.
Windows Authentication, use the format:
Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=SSPI;
SERVERNAME should be replaced with the name of your SQL Server.
DBNAME should be replaced with the name of your Troika SQL database.
The server name and database name are displayed on the top of the Troika Desktop.
SQL Server Authentication, use the format:
Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=sa;Password=SQLpassword
sa should be replaced with your SQL user account.
SQLpassword should be replaced with your SQL account password.
The SQL user account user must have db_owner rights to the Troika SQL database and the SQL Server SysAdmin role.
Enter a path for the ‘OutputFolder setting’. For example, value="C:\TWWDATA\BACKUP".
Edit the value for the setting "DeleteBackupAfterNumberOfDays". This value is initially set to 7, and will allow 7 days of backup files to be retained, after which the backup files older than 7 days will be deleted. The value should be amended according to your own requirements, and may be dictated by the frequency of server backups of the hard disk data.
Save and close the log file.
Scheduling the SQL Utility to Run Automatically
The SQL Utility is executed with command line entries, using a series of settings (called arguments) as outlined below. The commands can be run using the command prompt in Windows or can be scheduled to run at a specific time using ‘windows scheduler’ with the commands and arguments, run manually or executed from a desktop shortcut using a batch file.
To schedule a backup or maintenance task:
- Start ‘Task Scheduler’ program on the server and the shortcut to ‘Create a Basic Task’.
- Enter a name for the specific task required, e.g. ‘Daily Troika Task’.
- Go to the Next screen to enter a schedule.
- Select the Action as ‘Start a Program’.
- Enter the program path to the TroikaSQLUtility.exe.
Add the argument required for each scheduled task in the argument (optional) box. See the table below of the suggested tasks to create.
Example, Troika Daily Task:
Suggested schedule of maintenance tasks
| Scheduled Task Name | Frequency | Arguments to use |
| Troika Daily Task | Daily, at the close of business | /ForEx /Backup |
| Troika Weekly Task * Please note this task is no longer required if running WW+ 4.15 or later. | Weekly, out of business hours | /Balances |
| Troika Monthly Task | Monthly housekeeping, out of business hours | /CheckDB /Indexes |
The five functions performed by the Troika SQL Utility are:
/CheckDB – This performs an integrity check on the Troika SQL database.
/Backup – This performs a full database backup and deletes older backups older than a fixed number of days.
/Indexes – This optimises the SQL database.
/ForEx - This function performs a daily housekeeping job that is required to run each day.
/Balances – This function rebuilds a series of nominal account balance computations used by FISH each week.
Other Backup Media
With a daily backup task running, the specified ‘output path’ on the SQL Server will be populated with SQL backup files. These backup files will allow the Troika SQL database to be restored in an emergency or for disaster recovery purposes and should be additionally backed up to offsite media wherever possible.