In this blog post we will see 5 SQL Server Best Practices.
SQL Server Best Practices
1) Backup Master. I am going to have a backup of the database using script; however, the backup script has not been updated for a long time now. Last time, I noticed that I was not getting the master database backup. And no matter what one suggests, I still think it is very important to have a backup of the master database. I am going to alter the script to have a correct backup of the master database.
2) Online Indexing. I have upgraded my server from SQL Server 2005 Standard to SQL Server 2008 Enterprise. On the other hand, I never changed the index rebuilding script. One of the tables is very huge and when indexes are rebuilt in it, it often gets broken. I plan to change the index rebuild script to include the option of (ONLINE = ON). This will be good to avoid the time outs.
3) Restarting the Server. I have the server connected to the UPS and even though we have lots of very short power outages, my server is not affected, fortunately. I almost forgot when I really restarted it. Although, it is not necessary to restart server at all, this procedure is intended for my windows box, which has many updates installed but I have not rebooted it in order for these updates to take effect. This may not be the best technique, but it works enough for me because my data is not that critical. If you are maintaining a high-availability solution, I suggest that you have failed over solution implemented before restarting the server.
4) Wait Stats. I recently learned about Wait stats. I am eager to know what Wait Statistics really are, and what they can do for my server. Due to the fact that I have not upgraded the hardware for many years now (although I have upgraded the OS, SQL Server version and huge data growth), I could find out which of my hardware needs an upgrade.
5) Fresh Air. I will make sure that after all of the above tasks are completed I am going to go out with my family and breathe some fresh air.
Reference: Pinal Dave (http://blog.sqlauthority.com)