Enterprise PDM: Maintaining a Healthy SQL Server

Maintaining your SQL Server is essential for all Enterprise PDM users. Below are the main areas of concern when setting up and maintaining your server.

SQL Maintenance Plan Wizard: One of the most important things you will need to do is set up the automated performance plan. This can be accessed under the Management folder > right click > New Maintenance Plan. Some recommended tasks for it to run are depicted below.

You should back up the database daily and rebuild the indexes weekly or at least monthly. The database backups should be moved to another machine for safety. It is also important to check the integrity of your backups quarterly by restoring them in a test environment.

Recovery Mode: The recovery mode options can be found by right-clicking the database > Properties > Options > Recovery model. There are two options. Full Recovery (the default option) allows you to recover your database at the point of failure or any time you choose, whereas Simple Recovery allows you to recover at the time of the last backup. Simple Recovery is recommended for EPDM to improve performance and avoid situations where the log database file (LDF) grows unnecessarily large.

Master Database File (MDF) and Log Database File (LDF) : By default, your MDF and LDF files will grow automatically. Sometimes the LDF file especially can get far too large. The first method for managing this is setting the Recovery Mode to simple, as previously explained. The next method we recommend is to manage the Autogrow or increase the file size manually. You can find the options by right-clicking the database > Properties > Files. You will have to review this regularly, and it is recommended to set up alerts to monitor their size.

Tempdb: Tempdb is a database located under System Databases. Its purpose is to temporarily store data such as tables, indexes, stored procedures, etc. There is only one Tempdb per SQL Server, no matter the number of databases. This database should also be set to Simple Recovery and its LDF and MDF file growth should be monitored as well.

For more training and tutorials on the many 3D CAD Modeling solutions in the SolidWorks family of products and add-ons, register for an upcoming Event or look into our SOLIDWORKS training.