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.

 

SQL Server Install Guide Part 1

In part 1 of a series on installing SQL Server, I’m going to discuss things to do prior to running the installation program for SQL Server.  There are a number of install guides for SQL Server on the internet and plenty of recommendations around installation best practices and how-to’s.  This will be what I’ve arrived at over the course of time.

This first step I’m going to introduce makes the assumption that this install is going to be for one or more databases that you’re planning to migrate from an existing instance.  My first step is to run the Database Migration Assistant from Microsoft.  This product is free and can be installed away from your SQL Servers.  I run my install from my laptop.  After opening the UI and starting an assessment, this tool will scan the source databases selected and determine if there are any breaking changes, behavior changes, or deprecated features between the source and target for your migration.

The Database Migration Assistant will also do two additional things in regards to migrations.  First, it will make suggestions on new features that may be advisable to use in the new instance.  Second, the software can also actually do the database migration for you.  That includes migration to a recent SQL Server version on premises and migration to Azure.

The next item on my list is a relatively easy one to handle, but it’s still worth at least some forethought.  The database instance needs a name.  For a default instance, the computer name will be the instance name.  Which means that in some cases your name is decided for you by a System Administrator.  If you have input into the name, try to use a logical naming convention that will have some longevity.  In some environments you might have a convention like “Application” + “Purpose” + “Location” + “some number” such that a machine name might be CitrixWebSTL01 for a Citrix web server in Saint Louis.  I would recommend something similar to this so that when the machine name is viewed the person knows exactly what it is for.  Going generic, like SQL01, SQL02, SQL03, etc. just doesn’t say much about what the SQL instance is for.

The third item on my install guide is to identify or create accounts for the SQL Server modules you plan to install.  For instance, if you’re installing the database engine, utilizing SQL Agent and maybe installing SSIS on the box, then you really should use three different accounts with different passwords, one for each service that will be installed.  Doing this will help keep the machine and the environment more secure.  If you use one account for every service and that one account is used on every SQL install, you’ve now made your entire environment vulnerable if that username and password is ever compromised.

Fourth, identify and agree upon drive letters for files.  If the machine is physical and you can have some influence over what is used, be sure to separate mdf from ldf files and be sure to get TempDb on its own drive.  Doing these things will help overall performance as each of these file types has its own usage pattern.  Mixing those files types on the same drive is going to hurt performance.

If the environment is virtual and so a SAN is involved, I would still encourage you to have a scheme for where the files go because it makes administration easier. Also, having a consistent design across all servers helps when it comes time to restore databases from one place to another.  No more pesky WITH MOVE statements in your RESTORE DATABASE scrips if across the environment there is a consistent way drives and files are laid out.

Enabling instant file initialization is the fifth item on my SQL Server install guide.  Instant file initialization is a performance enhancing option that will really help with data file growth times and restore times.  It allows SQL Server to claim space on a drive without zeroing out the data that might be on that part of the drive.  This means that the data is still retrievable by a skilled hacker until SQL Server writes over the data. However, there are many layers of security to be bypassed before that would be possible and there are substantial benefits to instant file initialization. Check out a post by Kimberly Tripp on the subject.  Instant File initialization is a multi step process prior to SQL Server 2016, but now we have a handy check box.

Setting Min and Max Server Memory Setting

Configuring the min and max server memory settings for SQL Server has a number of things to consider prior to making changes. Before we jump into those considerations, let’s learn what the min and max server memory options do.

 

MIN SERVER MEMORY

The min server memory option specifies the minimum amount of memory utilized by the SQL Server buffer pool. It may seem that this should mean that when SQL Server starts it will automatically grab the memory equal to the min server memory value. However, this is not what happens. Instead, SQL Server acquires system memory as needed to process a workload. Consequently, if the SQL Server workload remains low, it is possible that the min server memory value is never reached. However, once it is reached SQL Server will not release an amount of memory that would take it below that minimum setting.

For example, let’s suppose your server has 128 GB of RAM and the min server memory option were set to 32 GB for SQL Server. SQL Server does not immediately allocate 32 GB of RAM upon serve startup. It will acquire memory as needed for the workload. This means that if there is little activity in the database it may take some time before SQL Server allocates 32 GB of RAM. Once this happens, SQL Server will not release memory that would reduce the allocation below the min serve memory setting.

 

MAX SERVER MEMORY

To a certain point the max server memory setting does do what it sounds like. Keep in mind this setting is applicable only to the buffer pool. SQL Server can allocate some memory beyond this max number as it consumes memory for things outside of the buffer pool. Also, realize that this setting does not account for memory allocated to other components of SQL Server that may be installed and running, such as Reporting Services or Analysis Services.

 

The default value for Max Server Memory is 2,147,483,647 in MB. That number is rather large, 2,047.99 GB to be exact. So what happens if the machine SQL Server is installed on does not have that much memory and the default was not changed? As previously mentioned, SQL Server will continue to acquire additional memory based on workload. As a result, SQL Server could make requests of Windows that puts the box under memory pressure as it essentially attempts to choke the OS by leaving less and less memory for the OS. There are mechanisms in the OS that allow it to page out to disk some information in the SQL Server buffer pool so Windows can recover that memory for itself. This hurts SQL Server performance because disk IO is far slower than keeping operations in memory. Among other places, details on how this process works can be found here, which is a blog post discussing the Lock Pages In Memory feature. I will save that setting for discussion a later post.

 

The point I want to make here is that you can calculate how much memory the Windows OS and other applications may need and then subtract that from the physical memory available. There are recommendations on the internet for such things. Whatever number you arrive at would then be set as the Max Server Memory setting in SQL Server. The Available MB perfmon counter should then be judiciously monitored to ensure that there is adequate free memory left on the box, at least 1-2 GB. That will allow plenty of memory for that person who just insists on using an RDP connection to do his or her work or for a new process to fire off without bringing the server to its knees.

 

For example, let’s say that your server has 128 GB of RAM. You may decide to allocate 12 GB for the OS. Perhaps you have a vendor app that needs 4 GB. You take 128-(12+4) and that leaves 112 GB of RAM. If you are running SQL Server 2008 R2 then the Max Server Memory setting can be set to a max value of 64 GB. That is per SQL instance. SQL Server 2012 Standard supports a maximum of 64 GB and SQL Server 2014 Standard supports 128 GB. If SQL Server 2014 Standard were installed in this scenario there would be a maximum of 112 GB that could potentially be allocated to the SQL Server buffer pool for the machine described above.   Setting this limit at 112 GB, followed by the aforementioned monitoring of the Available MB performance monitor counter, would help ensure that SQL Server does not hog memory from the Windows operating system.

 

Now that there is a foundational understanding of what the Min Server Memory and Max Server memory settings are and how they work, let’s look at how to set these options in SQL Server.

 

First, there is the GUI. Open SQL Server Management Studio and connect to a SQL Server instance. Right click on the SQL Server instance name and then choose Properties. Now select the Memory page in the left pane  Manually change the values in the Minimum and Max Server Memory boxes.  Remember that this is in MB.  Then click OK in the lower right of the dialog box.  Also, remember that when you make changes in SSMS you can click the Script button at the top of the dialog and T-SQL for the particular changes will be generated for you.

 

Min and Max Server Settings

 

Second, these settings can be changed using T-SQL.  Here is an example script that sets the minimum to 12 GB and the Max to 32 GB.

 

EXEC sys.sp_configure N’show advanced options’, N’1′ RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N’min server memory (MB)’, N’12288′

GO

EXEC sys.sp_configure N’max server memory (MB)’, N’32768′

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N’show advanced options’, N’0′ RECONFIGURE WITH OVERRIDE

GO

 

I encourage you to review these settings on your SQL Server instance and consider whether they need to be adjusted.

3 Additional Tips for Using SQL Server Management Studio

In an earlier post I listed three tips for using the SQL Server Management Studio user interface. I want to offer three additional tips for SSMS.  I think you will find that these ideas will bring additional efficiency and functionality to your use of the interface.

The first tip is related to getting more viewing space after running a query. Query results obviously take up the bottom portion of the SSMS editor window. I have often found myself then clicking in the query editor window and scrolling to view various parts of my query, all the while the results portion of the screen is using a good portion of the window. Some time back an associate told me how to easily address that annoying issue. Simply click CTRL and R to collapse the query results so that the full page is again used for viewing the query text.

The second tip is related to finding objects in Object Explorer. Ever found yourself trying to locate a particular stored procedure that has a name that is a mile long and your brain only remembers part of the procedure name? What about trying to find all the tables related to Orders or Shipping? As it turns out, there is a nice way to search for such things.

Drill down into the object level you are searching through, such as Databases > AdventureWorks2008R2 > Tables. Right click the Tables folder and select Filter. Then select Filter Settings. You are then presented with the dialog below. Enter the name description in the Value column and click OK. This filtering also works for the Views, Stored Procedures and Functions folders.

Filter Search on a Table

 

The third tip relates to using color to visually identify what SQL Server instance the query window is connected to. I once saw a Developer who had five SQL Server Management Studio windows open at once. When another Developer asked him why he answered that it was so he could maintain separate connections to different SQL instances and not be as confused as if he had all the connections in one running instance of SQL Server. It was, I suppose, an admirable attempt to ensure he was running the query against the right SQL instance, but it caused me to remember that color can be used for identifying the SQL instance.

Here is how it works. When SSMS is launched the user is presented with a connection dialog.

SSMS Connect to Server Dialog2

 

Next select Options and the below appears.

 

SSMS Connection Properties Dialog

Click on the Select button, choose a color and then click OK and then Connect in order to connect to the SQL instance. Now open a new query window. The color you just selected will appear along the bottom of the SSMS editor window. To do this after you have already opened SSMS, click Connect > Database Engine > Options from the Object Explorer pane. This type of color customization can also be done with registered servers. For more information on this feature click this link: https://msdn.microsoft.com/en-us/library/hh213617(v=sql.120).aspx

 

I hope these additional tips on SSMS prove useful. Feel free to leave me a comment or another customization that you like to use.