VMware vCenter Server - database maintenance shall be a weekly task
Especially in a growing environment you will identify a performance degrade on the MS SQL Server after some time.
There is an easy way to setup the most common jobs in a simple weekly maintenance plan.
Please login to the MS SQL Server serving your vCenter Server, or directly onto the vCenter Server if you use an internal database server.
- Start the Microsoft SQL Server Management Studio and login with an administrative user, this is in many cases the "sa" user or an Administrator of the system.
- Expand the Management Folder, Right-Click on Maintenance Plans and select "Maintenance Plan Wizard" from the menu.
- In the "Select Plan Properties" window select "Single schedule for the entire plan or no schedule". We usually run this on Saturdays, so if something goes wrong we can ensure we have enough time to react before people start work back on a Monday morning.
- Click next to select the maintenance tasks which shall be run on a weekly base, you should have: Check Database Integrity, Reorganize Index, Rebuild Index, Update Statistics, Back Up Database (Full), Maintenance Cleanup Task
- You can rearrange the different tasks, I prefer to have the Back Up run after the integrity check. The next steps may vary in their order depending on what you setup as the task order.
- Define Databse Check Integrity Task: We suggest to select Database "All databases" and keep "Include Indexes" selected.
- Back Up Database (Full) Task: You should backup all databases especially as these cleans out any transaction logs stored for longer periods. We suggest to have the weekly backups after 14 days which gives 2 full backups on file. We also suggest to store into an external folder or share to ensure if you loose the complete MS SQL Server you have a backup.
- Define Reorganize Index Task: Choose "All databases" and also check mark "Compact large objects"
- Rebuild Index Task: Again choose "All databases", "Reorganize pages with the default amount of free space", "Keep index online while re indexing".
- Define Update Statistics Task: Choose "All databases", "All existing statistics", "Full Scan"
- Maintenance Cleanup Task: Choose what should automatically been removed from the environment, so that you do not run out of back up space for example
- Finally Report Options: You should provide a folder to store the maintenance results in, otherwise it will be harder to identify what goes wrong.