SQL maintenance is an important thing to keep on top on when you are running SAP Business One. Many thousands of SQL transactions happen everyday in normal operations so keeping SQL running at it's best can make a big difference. You will see speed improvements and peace of mind knowing that you are actively maintaining a healthy SAP Business One technical environment.
SQL Maintenance Plan Setup
SQL maintenance plans are super easy to setup and manage. With this knowledge base post even someone without SQL knowledge should be able to piece together a good solid maintenance plan.
The first thing you do is load up Microsoft SQL Server Management Studio from your start menu icon on your SQL/SAP server. It will ask you to login so you will need to have valid credentials to get where we need to be.
Once there you will see a menu along the side in a window called "Object Explorer". Find the "Databases" folder and click the plus button to expand the databases. Make a note of which databases you are planning to maintain.
Make sure you at least note the following databases:
- Your company's production database.
Find "Management" on the menu in the "Object Explorer" window and click the plus to expand your folder. Right click on the folder called "Maintenance Plans" and select new from the menu that pops up. A box will pop up where you can name your SQL maintenance plan but the default choice is fine. Click OK.
Setting Up Your SQL Maintenance Plan
Now we are in a brand new maintenance plan with nothing setup. Find the window called "Toolbox". There you click and drag the "Back Up Database Task" onto the plan grid to the right. Right click on the newly created task and select edit. Here you will select which databases we are maintaining. Remember the earlier step to make note of your SBO-COMMON and at least your production database name. Make sure the checkboxes are selected for each database you are backing up.
Select the options create a backup for every database and create a sub-directory for each database. The default file location should be fine unless you'd prefer to keep the backup files somewhere else. Click OK to continue.
Next we click and drag a "Shrink Database Task" on to the plan. Find the green arrow from the previous task and drag it onto the new task you just created. This will link the tasks in the order in which they are performed. Right click the new task to edit and set the same databases you selected in the first step, SBO-COMMON and your production database. The rest of the settings on this page should be left to the default choices. Click OK.
Now drag a "Maintenance Cleanup Task" to the plan and link it to the shrink database task. Select the location of your database files from the previous step and make sure the extension box says "BAK".
This is the default extension for all backup files created by the maintenance plan. Be sure to check the box to include first level sub-folders. Finally, choose how long you'd like to keep your backup files.
Scheduling Your SQL Maintenance Plan
Select the little calendar icon near the top to set the schedule for running your SQL maintenance plan. I set mine to run once a week late at night. The options are pretty straight forward if you've ever set an Outlook calendar invite. When setting your plan, make sure it is off hours and no other network/server tasks are running at the same time.
Save your plan by hitting save, you can then close the plan.
SQL Server Agent
Back at the main SQL server window in the "Object Explorer" find "SQL Server Agent" on the list. Right click the folder and make sure the service is started. This is the agent that will start and perform your new SQL maintenance plan. It is possible this service is not set to automatically start when your server does so it is a good idea to set the service to run automatically.
If you need any further clarification or are just not sure, please leave a comment and we will answer them as they come up. Good work on setting up your best practice SQL maintenance plan for SAP Business One.