Babysitting SQL

EmailFacebookGoogle+LinkedInTwitterShare

In the world of SOLIDWORKS Enterprise PDM administration, maintaining the Enterprise systems is really pretty easy. You need to add/remove users (assuming the HR department bothers to tell you when they hire someone) and ensure you have good reliable backups.

But don’t forget Enterprise’s playmate – SQL.

SQL is that one kid who was hard to babysit for. Sure he was good when you were watching him, but if you left him alone for too long you would catch him chewing on the furniture, burning the hair on your sister’s dolls, or using a shovel in your mom’s flower bed.

He’s a good kid, he’ll serve you well, but every once in a while you need to pat him on the head so he knows you are watching him.

Like any Microsoft product, SQL requires some occasional maintenance. Tim Kwong, SOLIDWORKS Sr. Technical Support Engineer, recommends this schedule:

Daily:

  • Preform backups of the SQL database (and log file if using Full Recovery Mode)
  • If using the SQL backup feature, offload the backup from the SQL server

Weekly or Monthly:

  • Rebuild the SQL indexes using SQL Maintenance Plan Wizard
  • Review the MDF and LDF files to make sure they haven’t exceeded the initial sizes
  • Defrag the SQL drives

Quarterly:

  • Check the integrity of the SQL backup by restoring and testing a recent database backup on a test setup