Blog Archives
Good Best Practice Advice from the Pensacola Pre-Con, Round Two
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:
- Instant File Initialization is critical for performance, read more from Kimberly L. Tripp (twitter|blog).
- 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.
- 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.
- Learn the principle of least privilege and apply it to your servers.
- 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.
- 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.
- 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!
Good Best Practice Advice from the Pensacola Pre-Con
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.
- Backups are the single most important thing we do, end of story. If you screw this up, you just might be updating your resume.
- Practice restoring your backups on a regular schedule. If you do not verify the backups then they essentially are useless.
- Use AD groups instead of individual logins for database security to reduce maintenance.
- 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.
- 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.
- It’s a good idea to maintain your scripts in a source controlled library with versioning.
- Scan your network regularly for new instances of SQL Server to see who is installing new databases in your environment without your knowledge.
- Use a group policy for service accounts to ensure that the particular account is being only used on the intended server and nowhere else.
- 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!
- 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!
Leaving Today for SQL Saturday #132, Pensacola
The day has finally arrived: travel day! I am leaving today after work for the six-hour drive to Pensacola for SQL Saturday #132. We have a new John Grisham audiobook, Calico Joe, ready for the ride. This one sounds like a great book: “A surprising and moving novel of fathers and sons, forgiveness and redemption, set in the world of Major League Baseball…”
Having experienced heavy rains all week in central Florida, I am a little concerned that we may not have a beautifully sunny weekend at the beach. However, the hotel does have an indoor pool so my wife is not too worried because we do live in Florida after all and this is a common summer time event.
Last night I was invited to the speaker’s dinner, thanks to the coordinator Mark Holmes (twitter|blog) for including me. In the past I have attended these speaker dinners as a volunteer, so I am moving up in the world. Thanks again Mark, I really do appreciate it and the event sounds amazing. I also want to thank Karla Kay (twitter|blog) for convincing me to take the leap and present as well as everything she does for the SQL community. She really is wonder woman as represented in her avatar.
Tomorrow is also my first pre-con training: “Demystifying Database Administration Best Practices” presented by Microsoft Certified Masters Robert Davis (twitter|blog) and Argenis Fernandez (twitter|blog). Some of my coworkers think that I am crazy for paying for this training and attending SQL Saturday’s out of my own pocket. They have obviously never been and met the #SQLFamily. Do yourself a favor and attend one of these event, you will not regret it. Enjoy!
SQL Saturday #132, Pensacola
Wow, another SQL Saturday is coming up and I am über excited! For this one I will travel up the panhandle to beautiful Pensacola with my lovely bride who will enjoy the sun and fun with me as we turn this into a four-day getaway. After our daughter’s graduation a couple of weeks ago, we could use a break.
This SQL Saturday is different for me in a few ways. First of all, I am going to the Pre-Con! This will be my first ever pre-con training as I never could get the schedule right in coordination with my work schedule. The stars aligned just right for this one, though. This pre-con will be “Demystifying Database Administration Best Practices” presented by Microsoft Certified Masters Robert Davis (twitter|blog) and Argenis Fernandez (twitter|blog). Being passionate about best practices, I am excited about this session as well as meeting two fellow tweeps.
The second difference is that I will be presenting for the first time ever! Going old school MTV style: This Saturday live at 1 pm come see the world-premiere video (er, I mean) presentation from SQLGator. It is a lightning talk presentation which will be great for my first time to help me get over my stage fright and conquer my goal of speaking. I am still considering submitting for SQL Saturday #151 Orlando and this will be a good test of whether or not I can do this. I am confident I can do this, but I am having some serious nervousness.
Hanging out with #SQLFamily will be amazing and I am looking forward to meeting some new people especially some of the bigger names in the SQL Server community. Come on out and enjoy the free learning. See you there!
Removing a Perfectly Good Cluster, Part Two
Yesterday I started a new project to downgrade our two new SQL Server 2008 R2 clusters down to SQL Server 2008 clusters. The uninstall went off without a hitch as we removed both nodes and then removed the support tools. I find it interesting that when we went to install the 2008 server, there was still tempDB data files which prevented the new install from moving forward until we deleted them. I am not sure if the other system databases were there as the installer did not complain about those. In hindsight, I probably should have removed all of the directories and files before installing as a general best practice but I did reboot the server prior to the new install and thought it would be fine.
Because I do not spin up new clusters everyday (that would be a great job), I took screen shots during the initial 2008 R2 install to serve as a guide because I knew that I had a total of three clusters to build by the end of the year. In this scenario, documentation is an amazing thing (well it is amazing in most areas but most DBAs become complacent about doing it myself included). Originally I built the first two clusters back in January and since they were the first clusters that I had ever built I wanted to document it as I am responsible for many systems and quite honestly I would not have remembered the settings chosen on each screen. Having worked with SQL Server for sometime, I could have configured a stand-alone server in my sleep, but I was not as confident with clusters. My confidence is building at this point.
Now, it appears that building 2008 SP3 servers is almost identical to 2008 R2 servers from slipstreaming through the configuration for best practices. In a future post, I will discuss some best practice troubleshooting I did for these reinstalls. Enjoy!
Something Technical This Way Comes
To quote Ray Bradbury in Something Wicked This Way Comes: “Everything that happens before Death is what counts.”
Wow! How simple and profound. Do you live your life as if it counts for something? Do you have a passion? Are you living your life for that passion?
My passion is best practices for SQL Server and I am taking a step forward in my professional development by putting together a presentation that I will submit to SQL Saturday Orlando. I have struggled with this for sometime now and like most people I find it difficult to speak in front of audiences. That is really sad, because from 1999 through 2001 I was a travelling consultant that would teach classes and present in front of total strangers. As I moved away from that lifestyle, I became more of an introvert for some unknown reason and it is really difficult to break those chains. So if my presentation is accepted and you attend my class, keep that in mind. I welcome your advice and words of wisdom.
Enjoy!
Microsoft SQL Server 2012 Best Practices Analyzer Is Now Available for Download
As many of you know already, I am very passionate about best practices. Thus, it is with great pleasure that I announce that the Best Practice Analyzer for SQL Server 2012 is now available for download (see link below). You will need the Microsoft Baseline Configuration Analyzer (MBCA) version 2.0 and PowerShell 2.0 installed in order to use this BPA. See my previous blog post on how to install the MBCA. Enjoy!
I Am Passionate About Best Practices
After writing for the Tribal SQL project, I think I’ve realized just how passionate I am about best practices. For example, the other day I asked my wife if she could change our bed sheets to which she replied that they had only been on there two weeks. My retort was that best practices dictated changing the sheets weekly for optimum comfort in sleeping performance. Yes, I’m coocoo for Cocoa Puffs over best practices!
In the shower this morning, as that is where most of my brilliance comes from, I decided to start preparing myself to do a presentation. That has been a long time coming with my left side usually talking my right side out of it. Or was it the other way around? I can’t recall, moving on. Part of the stumbling block for doing a presentation was finding a niche in the SQL Server community and now I think I have resolved myself to that niche so I am slowly chiseling away at my excuses for not presenting at a SQL Saturday or my local user group. There are only a couple of excuses left now and those are slowly fading away, so now I must harness the power of the SQL Force! Next stop SQL Saturday, then possibly a SQL Rally and then SQL PASS; whereby my plan for global domination will be complete.
Enjoy!
Saturday SQL Schoolhouse in Honor of World Backup Day
SQL Schoolhouse!
Today’s installment of the Saturday SQL Schoolhouse is brought to you in honor of World Backup Day, a day every DBA should celebrate! However, DBAs know that World Backup Day should be practiced daily! Here are some great backup articles for you to peruse. Enjoy!
- Introduction to Backup and Restore Strategies in SQL Server by MSDN
- SQL Server Backup Best Practices by Michael Otey (Blog)
- Questions About SQL Server Backups | SQL Aloha by Brad McGehee (Blog | Twitter)
- Backup & Restore by Kimberly L. Tripp (Blog | Twitter)
- Backup Resources | Home Of The Scary DBA by Grant Fritchey (Blog | Twitter)
- In Recovery… | Backup/Restore by Paul Randal (Blog | Twitter)
Best Practicing, the Results Part Dos
This week, we have been talking about running the Microsoft Baseline Configuration Analyzer 2.01 with the SQL Server Best Practice Analyzer for 2008 R2. Today I want to talk about one of the errors that occurred on one of the new boxes I just stood up in a new cluster. You may encounter a prerequisite error stating that the user is not a member of the Administrators group on the remote machine or PowerShell remoting is not enabled on the remote server. This is easily remedied.
- Add the user as a member of the Administrators group, or
- Run Enable-PSRemoting in PoweShell with elevated privileges, AND
- Run winrm set winrm/config/winrs `@`{MaxShellsPerUser=`”10″`} in PowerShell with elevated privileges.
Once I made these changes the analyzer was able to proceed. The analyzer will give you these directions, but I figured if you are reading this beforehand you can make the changes proactively. Enjoy!
SQL Swampland 