Goal
The Goal of this is to convey to non-dba administrators, developers and consultants the need for SQL Server Maintenance Plans and some practical ways to implement them and why. I’m going to focus on Dynamics SL and GP but this can be for any implantation of SQL. Mostly focused on small to medium sized applications.
SQL Server has become so easy to install that some key functions are overlooked or incorrectly implemented, just by ignorance of the need for them. The users installing are often not DBAs and don’t understand core concepts of managing and implementing a database server. I can’t count how many times I have been called in a year after implementation and we need to restore to a point in time and can’t or even more common and worse, there are no backups at all.
After you get SQL Installed and configured properly for your product, you need to immediately take steps to insure the stability of the SQL Server Install, the safety and proper operations of the Dynamics SL databases and to protect the data.
My Goal here is to discuss the creation of a maintenance plans and what options I select and why. There will be follow up blogs on Index Management in SQL and Scripts to shrink transaction logs when needed and if appropriate.
Environment
SQL 2012 or 2014 is installed. OS is Windows 2012 or better.
The SQL Server Install is Standard Editions.
Issue
Installing SQL is not enough to protect your data. You need to protect your install and data from many adverse events that can at least delay core mission critical access to your data and at worst cause complete loss of core mission critical data. Just to help you understand some of the things that can sneak up and affect your data:
1) Log files not being managed properly can fill up drives over log periods of time
a. Lack of space can cause corruptions in the database
2) Poorly written code or other events can cause corruptions in data
3) Hardware failures can corrupt data (bad sector on disk)
4) Hardware failure can take down server
5) Hacker attacks can take out server or corrupt data
6) Consultants not properly backing up data and running bad SQL statements can corrupt data
The list goes on. YOU MUST IMMEDIATELY on installation of SQL and creation of database implement processes to protect you from the above events and more.
Resolution
Maintenance Plans provide easy to follow wizards and tools to manage protecting your data. But first you need some prep:
1) Coordinate with bigger team at your clients organization – Network backups and online backups of SQL need to be figured and in sync with what you do. In todays world backup of images also.
2) Make sure tools needed for plans to run are installed and setup to restart.
Maintenance Plans need to be used in coordination with network backups and other backup software, like online backup tools like Backup Exec and others. We are making some basic assumptions with my plan, there is no online tool backup and network backups of the sql server happen at 1 AM and usually are finished at 1:30 AM. You will need to coordinate these facts with network administrators. Why? Because you will want to have backups created before 1 AM so that network backups grab the latest of the machine. If you are doing online backups through backup exec you may want to not keep backups on the server older than a day since it’s fairly easy to grab a days’ backup from that software if needed. You have to consider these things before you even start. Make sure you know when network backups happen and if an online backup tool is being used.
Next you need to make sure you have tools in SQL installed and running that are needed. Specifically I am talking about SQL Server Agent. Make sure it is installed and that it is setup to automatically start when the machine is rebooted. Without SQL Agent you have no plan running and this whole enterprise is useless. To make sure just launch SQL Management Studio and drill down to SQL Agent and check its status
Also you can right mouse and select properties to make sure SQL Agent auto starts on reboot
Now we get to the actual plan. There are many ways and methods to manage even maintenance plans. This is a suggested path. But I give the reasons when appropriate so you can make your own call on the throttle of parameters based on your situation. There are two ways to create a plan:
1) Just go into SQL Management Studio and under maintenance plans just right mouse and select new
2) Go into SQL Management Studio and under maintenance plans, right mouse and select the wizard. I always use the wizard, it reminds me of the steps I can do and just makes it easy to create a plan.
The plan itself can perform these actions:
1) Verify Database Integrity – Checks if the structure and content of the database is intact. This needs to be done nightly. You want to know ASAP if your data is corrupt.
2) Shrink the Database in physical size if possible – Shrinks the database files down if room is there. You NEVER want to do this. Databases get to the size they need. Of course if you move or purge data then you want to reclaim space, but that is not the norm. Reclaiming space when needed is a costly process and constant shrink and grow of files can lead to fragmentation and possible corruptions. Be careful here.
3) Reorganize Index – This task will leave the index but reorder the data in the index. This is less costly then then next option – delete and recreate. With either reorganize or rebuild it should be only when the index is fragmented to a level that makes sense. I use a stored procedure for this and never check either option.
4) Rebuild Index – This will delete and recreate the index. Costly in large databases and should be done sparingly. I never check this option, I Use a stored procedure to perform this.
5) Update Statistics – This will update statistics which are used in creating query plans. Statistics can greatly impact performance. I do this nightly.
6) Clean up history – deletes out history and logs of backups form system tables. I keep as much detail as long as I can. Keep at least a week or two. More if you can. Logs tell a history of problems.
7) Execute SQL Agent Job – This will execute SQL jobs. I use to run my Stored Procedure for index management.
8) Full Backups – perform a full backup on the selected databases. You need full backups daily if you can. Of course it depends on space. But if you don’t have room for a nightly backup then you need to rethink the size of your drives and where you are backing up.
9) Differential Backups – perform differential backups (only changes since last backup). Differentials are used in cases when full backups take too long or you have very little space. Either way, I often don’t do differentials. In the Mid to smaller dynamics space, like GP and SL the size of the Databases don’t require differentials.
10) Transaction Logs backups – perform transaction log backups. Transaction log backups are key to a disaster recovery plan. It allows you to restore to a point in time. All application databases should be on a full recovery model NOT simple and should have transaction log backups performed often and extended beyond hours of business to properly clear the logs.
11) Maintenance Cleanup Tasks – this will delete backup files when you tell it to. Under our case outlined, I keep 3 days worth of backups. This will vary. You need to consider that sometimes you need to not just keep last nights backup but a few days back because it takes a long time to get the right tap and pull the backup off tape or off site. You may just need to pull two days ago to restore and pull a table back that someone deleted by accident. My goal has always been 5 days if I can, 3 preferred but as often as space allows.
I must digress from actual plan creation to discuss Transaction Log backups. Transaction Logs are where data gets written first in a database. Then there are periodic commits of data to the database in what are called checkpoints. Depending on how you have your database setup – recovery model specifically, the log behaves differently. Using simple recovery model means that data gets pushed immediately to the database. The log stays small and holds nothing. Backing it up is useless. If you use Full recovery model then data stays till the checkpoint happens. Backing up the Transaction Log allows you to recover to a point in time, say 9:31 Am on Friday. Clients expect this. Plan for it.
In using this model you must manage the size of the log. In theory if you keep putting data in a log and check point moves data and you keep posting new data on top. Think of it as a game of Jenga where you pull sticks out from a pile, but at the same time folks are piling new sticks on the top. Because day to day operation is constantly piling data, the check points get messed up and some data piles up there. The log grows to match. You need to have a backup in the evening when all are gone to get the log to catch up and clear out. This will ensure that next day, the log is empty and plenty of room to pile new data.
Even when you do this there are times when the log still gets bigger. You need to be careful to just shrink the log because it will break the ability to restore to a point in time. But I am saying that sometimes you just need to shrink the log. You can do manually or through script. But do it after a full backup. I will follow up with a blog about how I usually do that and when.
Most of the time the log when you backup often and into off hours will just grow to a size that is needed for the client and stay there. That is goal. No costly shrink and grow operations. If you are constantly running out of space consider having hardware increased to match the needs of the client.
Walk through of wizard:
Select separate Schedule for each and give it a name
Select what to do:
For each task select databases it applies to:
For Check Integrity and full database backup select all databases
For Transaction Log backup select all user databases
Create the Schedule for each task. Time things in ways that make sense to fit with other things like network backups. For our example I do this for each task:
1) Database Integrity: Every Day of week at 8 pm, all databases
2) Update Statistics: Every Day of week at 8:30 PM, all databases
3) Clean Up History: every Day of week at 9:00 PM, all full databases backups – extension bak, then I manually create another task under this one when done to do trn files. Every 3 days I delete.
4) Execute SQL job: Every Sunday I do a SQL agent task that runs my index stored procedure
5) Daily at 9:30 PM I do a full backup of all databases
6) Every two hours, every day from 6 to 8 pm I perform transaction log backups on all user databases (keep for 3 days)
7) Clean up history every 3 weeks.
Some key parts of my plan:
Key point to remember is when you create through the wizard it has a delete backup screen in the clean up tasks above. You select directory and check to do sub directories for each database but you can only enter one extension - I put bak above. What about the trn files? Well you let it create the plan, you drill down into after and just copy this part and paste then drill in and change bak in one to trn.
Conclusion
Maintenance Plans in SQL are easy to setup and needed from the start of implementation for a robust and complete recovery plan. It must be coordinated with client needs and other operations like online backups and network backups. It needs to take into account size of files and logs and disaster recovery models needs of the databases.
I will follow with how to manage indexes in procedure and shrink log file without breaking full recovery model.
