Best practice for SQL maintenance and backups for GP clients

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