This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at https://www.sqlskills.com/help/AccidentalDBA. Enjoy!
SQL Server 2012 is deceptively easy to install. You can pretty much click on the “Next” button a number of times, and you can get SQL Server 2012 installed without really having any idea what you are doing. SQL Server 2012 will run with all of the installation and configuration defaults, and depending on your workload, you may be perfectly fine for some time. On the other hand, this is somewhat like a ticking time bomb, waiting to blow up in the face of the unfortunate accidental DBA at the worst possible time.
If you have a more substantial workload or you just want to make sure that you have the best installation and configuration possible, you need to do some preparation work and planning before you install SQL Server 2012, and you need to make the correct choices during the installation process. You also need to make some key instance-level configuration changes after you are done installing and updating SQL Server 2012.
This begins with having an appropriate server and storage subsystem available. The exact details of your server and processor selection matter a great deal because of the core-based licensing in SQL Server 2012. Once you have the server and storage subsystem in place, and you have Windows Server 2012 installed, you want to make sure that the main system BIOS, all of the firmware, and all of the device drivers have been updated to the latest versions. You also want to make sure that Windows Server 2012 has been fully patched, using Microsoft Update (which is a superset of Windows Update).
In most environments, you are going to want to have a dedicated Windows domain account for each SQL Server service that you will be using, such as the Database Engine, SQL Server Agent, etc. These should be regular domain accounts with no special rights, since the SQL Server installation program will grant them the local rights that they need during the installation. Depending on your organization, getting these accounts created could take some time, but you will need them (along with their passwords) during the installation.
You also want to make sure that your hardware power management (in your BIOS) is either disabled or set to OS control. Along with this, you need to ensure that the Windows Power Plan is changed from the default of “Balanced” to the “High Performance” setting. Forgetting to make these changes is one of the most common mistakes that I see in the field, even among experienced database administrators. It is also very important to make sure that Intel Turbo Boost is enabled in the BIOS (which it usually is by default). Intel hyper-threading (HT) is somewhat more controversial. If you have an OLTP workload, I would always enable HT on newer processors (Nehalem and newer) unless you have done testing with your workload that shows that you are better off without HT (which will be very rare). With DW workloads, you may want to disable HT, but you really should do some testing with your workload before you decide.
You want to think about how you are going to configure your storage subsystem in terms of RAID levels, and the number and composition of logical drives that will be presented to Windows. This will be determined by how many drives you have available, your anticipated workload, your desired performance characteristics and your necessary drive space. After your logical drives have been configured and presented to Windows, you should test their performance, first with CrystalDiskMark, and then with SQLIO, to make sure you are getting the performance you expect. If you are using RAID controllers with dedicated hardware caches, you want to make sure that the cache policy is enabled so that the cache is actually being used.
You should also (in almost all cases) enable Windows Instant File Initialization (IFI) and Lock Pages in Memory (LPIM) by granting these rights to the SQL Server Service account. Once you have installed SQL Server 2012 you will want to get it fully patched with the latest Service Pack and Cumulative Update (CU). Installing SQL Server Cumulative Updates is also somewhat controversial, but I am a proponent. Especially with SQL Server 2012, many important issues have been corrected with CUs and Microsoft has even added new features though the CU process.
You should also set the instance-level Maximum Server memory setting to an appropriate value (especially if you have enabled LPIM), and you should enable the “Optimize for ad hoc workloads” instance-level setting. I also generally will start out with at least four tempdb data files, making sure they are the same size, with the same autogrowth increment. Finally, I will add Trace Flag (TF) 3226 as a startup parameter to disable the logging of successful database backup messages to the SQL Server Error log.
I wrote a three-part article series that goes into more detail on this subject last year:
I covered how to install, patch and configure SQL Server 2008 R2 in my book, SQL Server Hardware.
Our online training (Pluralsight) courses that can help you with this topic:
11 thoughts on “The Accidental DBA (Day 4 of 30): SQL Server Installation and Configuration Best Practices”
Excellent article Glenn.
Note that “Instant File Initialization” is granted by assigning to the SQL Server Service account the “Perform Volume Maintenance Tasks” security privilege.
Hi Glenn,
Would you elaborate on your recommendation to install CUs by default?
Up until now, I thought SPs are tested more thoroughly by Microsoft and you really don’t want half tested changes running on Prod.
Thank you!
Mark V
I don’t understand this, am I missing something? Microsoft Update is a superset of Microsoft Update?
“You also want to make sure that Windows Server 2012 has been fully patched, using Microsoft Update (which is a superset of Microsoft Update).”
That is just a typo. Microsoft Update is a superset of Windows Update.
Hi Glenn, Could you expand on hybrid deployment best practices for applying patches, and specifically SPs and CUs? I’m trying to find some clear references as to why you should apply them to distributors first, publishers second, and subscribers third in a replication topology. I know that’s best practice, but in our organization, we have many subscribers that are used for load-balancing, and it seems less risky to apply patches to one first and if it bites the dust, we have many more that carry the load. Is there a risk to the publisher and/or distributor?
I know that if the replication components are affected, there could be problems with compatibility, and that if it fails on a subscriber then subscriptions may have to be reinitialized. What if the SP/CU has no references to replication? Have there been actual examples of this in practice?
Especially when it comes to SPs and CUs, I think your idea of patching the subscriber first (especially when you have multiple subscribers) makes a lot of sense. If the SP or CU causes an issue (which is actually pretty rare), then you will have only affected that one subscriber, which would cause less of an impact than if you caused a problem with the publisher or distributor.
Any best practice pointers on figuring out the best page file size for a 64 bit server with (larger) memory sizes? I see quite different recommendations – but if I have SQL max memory set to something (4 to 8 gig less) then memory, and the appropriate settings so SQL can lock pages, am I correct that the most I’d for a swap file is the memory (or 1.5x?) reserved for the OS?
(and given no other processes other then SQL components…how much should be reserved?)
thanks!
It is actually pretty common (and I think it is a good idea) to make the Windows Page File pretty small, such as 4-6GB, and fixed in size. With SQL Server, if you are ever using the Windows Page File, that means you are under severe external memory pressure, and your performance is going to be very, very bad.
Questions on Firewalls.
1. Do we need to allow specifically the sqlservr.exe in firewall as the following article is talking about: http://bit.ly/1W6SJh9
is that a good practice?
2. Do we need to specifically allow 1433 in firewall?
3. I think we should allow port 1434 for Dedicated Admin Connections in firewall. Please correct me if I am wrong here.
Thanks.
Usually, opening ports 1433 and 1434 are all you need or want to do.
When working with firewall setting for Sql, I like to go by port number rather than the application. If you create a rule for sqlservr.exe, I believe it will be only for one instance. Here is my quick inbound rules for SQL instances:
Open TCP port for 1433 and 1434 when only using a default instance. if you have a named instance, also open UDP port for 1434 and always make sure Sql Browser is running. Of course if using more SQL features, make sure to open the respective ports.
Thanks.