Best practice for SQL maintenance and backups for GP clients
Best practice for SQL maintenance and backups for GP clients
1. Nightly backup
o Schedule daily at midnight
o Only Full backup and Maintenance clean up task selected
o Only user databases are selected
o Maintenance clean up pointed to the backup folder, file extension bak and to include first-level subfolders. Usually set up for 2 weeks.
2. Transaction Log backup
o Make sure all GP databases are set up Full recovery mode (database properties, options, recovery mode)
o Create transaction logs as Daily, most common approach is to do either every 15 min or 1 hour depending on client’s needs and capabilities of the server and only during business hours
3. Select only Backup Transaction Log and Maintenance Task
Select All databases (this list will only include database that are set up to have full recovery mode)
4. Point Maintenance Clean up to backup folder, enter TRN for file extension and select to Include first-level subfolders
5. Most common approach is to set to delete files every 2 days.
System Database backup
- Schedule it weekly over the weekend
- Select only Full backup and Maintenance Cleanup Task
- Select System database
- Point Maintenance Cleanup Task to backup folder
- Enter file extension bak
- Mark to include first-level subfolders
- Delete files every 2-4 weeks
Database maintenance plan
- Schedule it weekly after nightly backup (for example Saturday at 2am)
- Select:
o Check Database Integrity
o Reorganize Index
o Rebuild Index
o Update Statistics
Do not include backup. The idea is to recover to midnight backup in case something goes wrong with maintenance plan.
Select all user databases and accept the defaults. You can exclude individual DBs if needed.
Note: If you do not select to make a backup to include first-level sub folders, the clean up maintenance will fail.
Other SQL best practices:
Make sure that SQL memory allocation is capped at 80% of server memory
Make sure that owner for Dynamics and other GP companies is DYNSA
Make sure that SQL version for each individual DB reflects correct Compatibility Level
For clients with many GP companies use for example:
sp_msforeachdb ' USE [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN ALTER DATABASE [?] SET COMPATIBILITY_LEVEL = 130; END
