SQL Database Maintenance Wizard

The following 2 database plans should be setup using the maintenance plan wizard to run on every SQL server weather they are used to store data by Great Plains or not. The steps below assume there is no SQL agent configured with the tape backup software to perform a database backup (dump) direct to tape, if a backup agent is used with tape software refer "Tape Software Backup Agent" in the last section below.

Weekly

Create a new maintenance plan and make the following changes from the default settings:

1. Run on “All” databases
2. Reorganise data and index pages changing free space percentage to 10, schedule to run once a week when things are quiet e.g. Sunday 5:15am
3. Check database Integrity (dbcc) including the attempt to repair minor problems, schedule to run once a week when things are quiet e.g. Sunday 3:15am
4. Once a week on Sunday evening Backup database with the verify option, perform the backup at the same time the daily evening backup runs e.g. 8:15pm, specify the backup to write to the subdirectory of the SQL “backup” folder with a name like backupscheduleweeklydb and remove backup files older than one week. Note: Folders will need to be created in the file system first.
5. Write the report to the text file in default SQL log directory and delete text reports older than 4 weeks, write the history to msdb and limit row to 1000 lines
6. Give the database Plan a name of “Weekly”

Daily

Create a new maintenance plan and make the following changes from the default settings:

1. Run on “All user databases” although you could specify just DYNAMICS, GP Company DB's and any other user db's. See note ¹ below.
2. Update Statistics using 10% sample, scheduled early – ish every week day e.g. 6:15am
3. Every week night backup without the verify option at a time well before the scheduled tape backup software runs e.g. 8:15pm, specify the backup to write to the subdirectory of the SQL “backup” folder with a name like backupscheduledailydb and remove backup files older than 2 days
4. With the verify option enabled backup the transaction log every hour of every day starting 12:00am finishing 11:59pm, specify the backup to write to the subdirectory of the SQL “backup” folder with a name like backupschedulelog and remove backup files older than 3 days
5. Write the report to the text file in the default SQL log directory and delete text reports older than 4 weeks, write the history to msdb and limit row to 1000 lines
6. Give the database plan a name of “Daily”
7. Change the database properties of each of the specific databases above disabling both “Truncate Log on Checkpoint” and “Select into Bulk Copy”. On SQL 8.0 this is done by changing the recovery model to “Full”. Also see note ² below.

   Notes:

   1. The choice of “All user databases” for the daily plan is the better option as any databases that are added after the Maintenance Plan has been created will automatically be included. Also any database that is removed at a later date will need no further action by the database administrator.
   2. If the database is SQL 8.0 and “All user databases” are selected then also change both “pubs” and “Northwind” to “Full” recovery model. If this is missed the log back up may indicate an error and the expired log backups may not be deleted by the Job created by this maintenance plan.

In the Jobs area of the SQL server agent the separate jobs created by the Maintenance plan wizard will be present. It is worth running these manually from here to confirm they are runable and therefore operational. If a job completes but has errors this may indicate an incorrect setting, refer note ² above.

If SQL is also being used for Great Plains data then in addition to those jobs created by the wizard it is also worth manually adding the script Grant70.sql to run often each day against all the Great Plains databases. If Great Plains is also using Fixed Assets then instead of Grant70.sql use the script grantall.sql. Details of these scripts can be found by following .

Within 24 hours have another look at the jobs area and confirm there are no errors, check the file system and identify the backup files are there. Check the SQL logs for errors, the log files can be found in the log folder in the SQL program folder. Note: the database integrity check will usually only report errors and not actually fix anything, it will report these errors into the SQL log and the text output file associated to the job. Therefore it is imperative to look at the text output file and the SQL log to check for errors. These log files should be checked often especially on the Monday morning after the Weekly scheduled dbcc integrity check is done. The Monday morning check should also identify the Sunday night weekly back files are present.

When in the SQL Server Agent jobs area resist the temptation to change any of the jobs created by the Maintenance Plan Wizard, instead make the needed change by adjusting the Properties of the associated maintenance Plan item.

Be aware the database transaction log backup is set to run as part of the Daily Backup Maintenance Plan every hour on the hour, for this reason other scheduled tasks are set to run at a time 15 minutes after the hour. Since the transaction log backup will seldom take longer than 3 minutes to write out the data to the specified backup folder and then truncate the log, it will be well finished before other scheduled tasks run.

Tape Software Backup Agent

If tape backup software like Arcserve or BackupExec is used with a SQL agent then both the daily and the weekly maintenance plans should still run with changes made as follows:

1. In the “Weekly” plan no database backup is required as this will be taken by the tape software direct to tape
2. In the “Daily” plan, the evening database backup should run an hour before the tape software starts, and expire the database backup backup files older than 1 day.

The evening backup to file is still useful as it allows the restoration of the previous evenings data without utilizing the tape software.

[@more@]