SQL Server Backup Plan/Indexing
This guide shows you how to create 2 maintenance plans for the Fluentis. The first maintenance plan is needed to always have a backup of the database and delete obsolete backups automatically. The second is used to improve the general performance of the Fluentis application, and to compress the database.
Since events are scheduled in SQL, it is necessary to have the Sql Server agent up and running.
To create a maintenance plan open Microsoft SQL Server Management Studio and right click above Maintenance Plan:
Click New Maintenance Plan:
Provide a name for this plan:
This is the starting screen on which the maintenance plan can be built:
On the left of the screen there is the toolbox, with which it's possible create the actions that will execute the plan:
Click on it to open and see the list of actions, that could be dragged to the center of the screen:
For the backup plan we must have an action to make the backup and one to delete the old backups, to prevent the disk from becoming out of available memory. The actions will be performed in order, pointed out by arrows:
Connect the actions so that when one is finished, the other is performed. Right click above a task (or action) and click edit to set it:
For Database backup task, it is necessary to define which databases to back up. Include, for sure, Fluentis (production) database:
In the second tab we define the destination, the path where to save the backup file and the file extension which is usually .bak:
Finally, in the last tab, under Set backup compression, set whether the backup should be compressed or not. If necessary, the backup expiration date can also be set:
Then set the backup cleaning action, which will delete the old backup files based on the configuration we will provide:
Set the path where the backup files will be, the file extension, finally which ones delete based on the creation date. In the figure it has been configured that the shoul be deleted backups older than 3 days. Finally, it must be scheduled so that the plan is executed by the SQL Server Agent. Click on the symbol shown in the figure:
And set the schedule. For backup it is recommended to perform the actions every day at evening:
Finally click on save:
To verify go to the SQL server Agent processes and verify that there is a job (or process) for the plan you just saved:
Click on the new plan and enter a name. Insert the actions as shown in the figure and connect them:
Activity rebuilds index: Edit this action and configure it as follows, always indicating in databases, on which databases to run it:
Activity reorganizes index: Edit this action and configure it as follows, always indicating in databases, on which databases to run it:
Activity updates statistics: Edit this action and configure it as follows, always indicating in databases, on which databases to run it:
Compact database activity: Edit this action and configure it as follows, always indicating in databases, on which databases to run it:
Programming, as with the previous plan go to set the plan so that run once every day in the evening:
Finally press on save plan and go between the SQL Server Agent jobs in order to verify that the job for the plan has been created:
Summing up
The above plans are not mandatory for the correct functioning of Fluentis but highly recommended. Just think about the fact that the customer can do any operation on the data and to understand how was the initial situation you can only use backup created last night in order to verify.
For more information on maintenance plans, as well as for all other things regarding SQL Server, use the Microsoft documentation.