In this article we look at how mid sized firms can establish the recovery protocol for SQL Server instances implemented in their organization.
In-case of most mid size firm, their primary technical requirement is that SQL Server should work continuously and effectively. They do not concentrate much on its efficiency or getting the most of it, primarily because there is not much data to deal with in a mid size firm as compared to a large scale enterprise. Maintenance tips are very crucial for maintaining SQL Server, especially if you are not a DBA. The most basic of all tips is to always run your SQL in Maintenance Plan Wizard. Once you set up the maintenance plan you will also have to use database mail for notifications. Apart from these basic tips, there are a few recurrent queries from administrators in mid size firms, which we have answered/solved for your guidance.
Which is the most suitable recovery model?
This depends on your usage and how much data can you let go of. If you can let go of a whole day’s results; then opt for Simple Recovery Model it will take care of your transaction log backups as well. If you want to protect the whole day’s work and wish for better recovery options then opt for Full Recovery Model.
How frequent should be transaction log backups?
This again depends on how much work progress can you let go of. The time limit between transaction log backups represents the maximum possible amount of work you can lose. And do not store your backups and databases on the same drive.
For how long should Backups be stored?
For as long as you need them. Most of your answers will come to you if you just ask the question to yourself once. Some companies have policies regarding the time period for which backups should be stored; you can always opt for moving them to tapes if you want to make space on your disk.
How to ensure SQL Database Integrity?
Opt for regular integrity checks for your database, before backing it up. In-case of a failure in integrity checks for any of the database, contact a DBA, integrity failure in SQL Server database is a serious issue. If you plan to handle it by yourself, refer the works or guidance of an expert and ensure that the maintenance plan clearly verifies the integrity of the created backups as well.
Is it important to re-index SQL Databases?
If you do not have a very large database it is suggested to index it regularly, probably every weekend. As with a database that is not so large, it won’t take long, you may also set your indexes to rebuild.
Is it ok to shrink the Database and reduce its size?
It is not advisable to shrink your data without any solid reason, making a big database small is not suggestible because every database grows for a reason, and making it small will again lead to its growth. Shrink your data only in case of an extra large transaction log; it is an expensive process, so do not use it unnecessarily.
Mid Sized firms should be careful about not losing data in the event of a SQL Crash
For mid sized firms where full-fledged recovery protocols are not in place, the tech team managing the database needs to plan for dealing with SQL crashes. In the event of a crash one can bring in a mdf repair tool like DataNumen SQL Recovery to extract the compromised records. This powerful utility can be used to recover a whole bunch of MDF files in one go and can even tackle several different storage media types.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/