Yesterday, I wrote about some advice that I received at the pre-con titled “Demystifying Database Administration Best Practices” which was presented by Microsoft Certified Masters Robert Davis (twitter|blog) and Argenis Fernandez (twitter|blog) from SQL Saturday #132 in Pensacola, Florida.  Here are some more gems:

1. Instant File Initialization is critical for performance, read more from Kimberly L. Tripp (twitter|blog).
2. If you do not have Instant File Initialization enabled, then when you need to autogrow your database, it will have to zero out the file which may timeout the active query.  However, the transaction log will always need to be zeroed out during an autogrow.  These scenarios remind us to properly size our database during the planning stages.
3. Page file sizing does not need to be one and a half times the amount of memory.  Your SQL Server shouldn’t be paging, otherwise review your memory settings.
4. Learn the principle of least privilege and apply it to your servers.
5. Baseline new servers for performance so that you know whether your server is faster or slower.  Do not just take a user’s word for it.  For example, use SQLIO.exe against a new LUN to see your initial throughput.
6. If you install SQL Server with the local system and decide later to use an active directory account to run the server, it will not delete the server principal name (SPN) and you will receive the “Cannot Generate SSPI Context” error.
7. Also, if you change the account, be sure to use the SQL Server Configuration Manager instead of the Services applet.

Stay tuned for more items in our next installment.  Enjoy!

For my first blog about SQL Saturday #132, I wanted to review some of the good advice I received from the pre-conference training on Friday prior to the event.  The pre-con titled “Demystifying Database Administration Best Practices” was presented by Microsoft Certified Masters Robert Davis (twitter|blog) and Argenis Fernandez (twitter|blog).   Some of these I knew, some of these I did not.  But here they are for you to learn from.

1. Backups are the single most important thing we do, end of story.  If you screw this up, you just might be updating your resume.
2. Practice restoring your backups on a regular schedule.  If you do not verify the backups then they essentially are useless.
3. Use AD groups instead of individual logins for database security to reduce maintenance.
4. DBAs should be getting at least one e-mail a day from each server.  Do not set your instances to only mail you on failure events because if there is a problem with the mail then you will just assume that everything is successful and then never receive any failure notices.
5. It’s a good idea to use wikis for documentation and may ensure that the documentation is easily searched and actually used in the future.
6. It’s a good idea to maintain your scripts in a source controlled library with versioning.
7. Scan your network regularly for new instances of SQL Server to see who is installing new databases in your environment without your knowledge.
8. Use a group policy for service accounts to ensure that the particular account is being only used on the intended server and nowhere else.
9. Use a group policy to set all of your servers to high performance power plan.  The default is balanced allowing you to save power which sounds great for a green workplace, but why would you by a powerful server and then operate it at 50% power?  UNLEASH THE POWER!
10. Maximum server memory should always be set on every instance, but always make sure that you leave enough memory for the OS and other necessary items on the server.  This amount will vary with every server.

Stay tuned for more items in our next installment.  Enjoy!