SQL Server Install Guide Part 3

I’m in part 3 of a series on installing SQL Server.  In the previous post I began discussing steps to take after the install is complete.  Because there are quite a number of things to consider post-install, this part 3 will continue discussing the post-install configuration items.

One of the things I always do after installing SQL Server is set up SQL Server Alerts.  This is a free, easy way to find out about problems on the SQL instance, like read retry errors or other potential nasty issues.  There are probably a number of places to find scripts useful for this task.  The one I recommend is from Glenn Barry at SQLSkills.com.  You can find his script at this link.  One of my favorite things about Glenn’s script is that it has very well defined alert names that allow the DBA to easily know what server is involved when an alert is sent.

Next up is setting MAXDOP.  The foundational advice from Microsoft concerning Max Degree of Parallelism is in KB2806535, found here.  The summary of this article is that if you have a single NUMA node and less than 8 logical processors, then keep MAXDOP at or below the number of logical processors.    If you have multiple NUMA nodes and less than 8 logical processors per NUMA node then keep MAXDOP at or below the number of logical processors per NUMA node.   If there are more than 8 logical processors per NUMA node, keep MAXDOP at 8.

So, how do you tell how many NUMA nodes there are on the machine?  I’m glad you asked!  There are several ways to determine this information.  I will refer the reader to a post by Denny Cherry on the various methods.

MIN/MAX server memory should be reviewed as part of your post install configuration items.  The MIN Server Memory setting is the minimum memory your server will allocate to SQL Server.  The trick to understanding this is that SQL Server does not automatically grab that minimum value of memory upon start up.  The memory used by SQL Server increases gradually after start up, assuming a steady workload on the instance.  Once it crosses the value set in this MIN Memory setting then it does not give that memory back to the OS.

Setting the MAX Server Memory setting will prevent SQL Server from taking so much memory that it starves the OS.  I typically leave 4-6 GB of RAM free to the OS and have not had problems with memory pressure using that guideline.

I will say though, that the machines I work with are dedicated to SQL Server, and almost always have just the database engine installed on them.  If you have other SQL Server components on this same box, or application components from one or more applications then 4-6 GB left to non database engine components probably is not enough.  Definitely monitor the Available Megabytes Perfmon counter over time so you can see how much free memory the machine has and adjust the Max Server Memory setting for SQL Server as needed.

To keep this series to a reasonable length, I’m simply going to list the next few items I have on my own personal Install Guide that I use.  The series wasn’t meant to be an exhaustive explanation of everything one would put on an install guide anyway.

The guide I use is in Excel format and I go through each item on it as I work through the process.  I mark off what I’ve done and then I save the guide.  This helps me to ensure that I’ve covered all the steps and it gives me and other people a record of what I did during the process.  Some of these steps can be entire blog posts on their own, or even an entire series written on that one step, so summarizing them here and expanding on them in later posts will be my approach.  Ok, so here’s the remaining list.

  1. Ensure Windows power plan is on high performance
  2. Configure tempdb appropriately. (Google this and do research.  Here are a couple of starting points.  See this and this.
  3. Set up a DBA management database. (This is a database on each instance that has key procedures and objects, like sp_WhoIsActive, sp_Blitz, and any other custom scripts you like to use for troubleshooting or diagnostics)
  4. Enable Query Store (SQL Server 2016 and higher)
  5. Update compatibility level of databases to newest level. (This assumes that the new cardinality estimator was tested in a Dev and test environment prior to this migration or new install)
  6. Make sure database backups are occurring as expected.
  7. Consider enabling automatic plan choice correction (SQL Server 2017)

 

 

SQL Server Install Guide Part 2

In part 1 of this series, I discussed pre-installation steps.  There are many guides available online for the actual installation process so I’m not going to give a step by step installation guide here.  Rather, I’m going to focus on what to think about after the install is complete.

Recent versions of SQL Server will now go out and download additional content for you before the install actually happens so you’re not left on the base, RTM release of the product.  However, one thing to consider post installation is still, are you on the latest patch?  How do you know?  Where can you go to compare the version number of the SQL Server you installed to what is currently available?  Well, you can look back at this post to answer that question.

Once you’ve determined whether or not additional patching is needed and completed that, then what?  Well, there is a lot of post install configuration still to do.  For example, do you need to migrate Linked Servers, SQL Agent jobs, SQL Operators?  What about setting up database mail or moving Logins?  Each one of these can take up a lot of time.  What if there was a quick way to handle those things?  Enter DBATools.

DBATools is a fantastic collection of hundreds of PowerShell commands that have been developed and tested by users worldwide.  With a simple command, linked servers can be migrated, with their password information intact. The same with SQL Server logins and their passwords and permissions.  Database mail? No problem!  In fact, if you call Start-DBAMigration and pass a source and target SQL instance, then you can migrate the entire instance in a single command.  DBATools is fantastic for ongoing maintenance tasks and a bunch of other things too so be sure to check out all that it can do for you as a DBA.

The next item on my install guide is trace flags.  Trace flags control the behavior of SQL Server in a variety of ways.  As one example, trace flag 3226 controls whether successful backups are logged in the SQL Sever error log.  Setting this flag prevents those “backup successful’ messages from cluttering up your error log.

The important thing to remember if you’re doing a migration is that you most likely want the new SQL Server to behave in the same way as the old one.  To find out what trace f;lags are globally enabled on a SQL Server, run the following code.

DBCC TRACESTATUS(-1)

If you want a list of trace flags and what they do, those lists do exist but they are sometimes hard to find.  Here is one such list that tells you whether you should be concerned about a particular trace flag being enabled or not.

If you’re starting from scratch with the database and application you’re installing, be sure to find out if the vendor has any recommendations and why they recommend those particular trace flags.  Also, do your own research about trace flag functionality and see why you personally may or may not want to use a particular flag.