Bad habits to kick : blind SQL Server installs

In my last post in this series, I talked about some problems associated with relying on undocumented behaviors and commands. This time I wanted to touch on SQL Server configuration, and some of the items I see where more thought should have been put into the process when SQL Server was initially installed, rather than just accepting the defaults or picking the options that are "easiest."

Not surprisingly, since SQL Server is so much easier to set up than other RDBMS platforms I won't mention, a lot of people fire up the setup wizard and click Next / Next / Next without having to fill in much information at all, never mind really think about it. By stepping through the more interesting dialogs in a SQL Server 2008 R2 installation wizard, I will try to highlight some of the things that can happen during such an install, that can cause problems later:

Feature Selection screen

No joke, I see folks check *ALL* of the boxes here (and the laziest ones press "Select All"):

I can only assume that they have no idea what the application needs, and don't want to have to come back and install things later. So every install could have Analysis Services, Reporting Services, Full-Text Search, replication, BIDS, Books Online, etc. even if the destination servers are not workstations and the application does not need any of the additional services. My opinion is to take a minimalist approach here – install only what you need on a server. Not only will it reduce the memory / CPU footprint of background services you'll never need, but since most of this stuff ends up on the system drive by default, you'll also prevent filling up that precious (and far too often under-configured) space too quickly.

In the case of a cluster install, then this shouldn't be done in a rush in the first place. The requirements of the application that will utilize the cluster should be dictated well in advance, so there is no question about whether Analysis Services or Full-Text Search will be used. Though the clustered version of setup doesn't allow you to un-check some of those boxes anyway – the reason is that things like Full-Text Search are "really hard" (not my words) to configure for clustering after the engine is already installed.

As an aside, usually "Shared feature directory" is customizable, and this could be a good candidate for moving to a location other than your C:\ drive. If you are adding a new instance or already have client components installed, however, you must continue to use the directory you chose when you first installed SQL Server. This is the case in the screen shot above, because I already have several instances and workstation components installed.

Instance Configuration screen

The default here is to put your instance at C:\Program Files\Microsoft SQL Server\:

As I briefly mentioned in the previous section, your C:\ may not be able to handle an instance of SQL Server at all, never mind one with busy usage or large volumes of data. On the other hand, you may be installing SQL Server in a virtual machine with only one drive letter, or on a system with only one hard drive. In that case, well, I hope you are prepared for the event when tempdb or a database's transaction log has filled the disk.

Server Configuration screen

Ah yes, this is the one where you pick the accounts for the SQL Server services:

The "let's get to a place where we can click Next fastest" methodology takes two approaches here: set all services to run as NT AUTHORITY\SYSTEM or NETWORK SERVICE, or set all services to run using a domain admin. I can't think of many situations where either of these choices is appropriate – usually the best choice is a dedicated domain account (not a domain administrator, and not a local administrator either). Also, don't go overboard here… I've seen cases where people have gone through the Add… button and added everyone in their domain to the SQL Server administrators list. This is overkill, and not something you should need to do during setup anyway – if you need to add logins you can do that after you have the engine up and running. I don't want to get into all of the security nuances or the principle of least privilege (I plan to cite some examples of this in a future post in this series); I just want to point out that some thought should be put into how security for the instance should be set up. Granted, this is one of the settings I'll talk about in this article that is relatively easy to change after installation.

The collation setting, thankfully, is a lot harder to screw up. Wait, what is collation? The short answer: it is used for determining valid character sets, comparing strings, and sorting (for more info, start here in Books Online). You have to intentionally switch tabs on this screen, and intentionally pick something other than the default. I would strongly recommend that, unless you are absolutely positive you need a non-default configuration, that you don't even switch to this tab. While some applications might require a specific non-default collation, I'd be very careful about picking anything other than the default for an instance-wide setting. You're pretty much stuck with this choice for as long as the instance is alive, so if you need to change this later, you will be looking at building up SQL Server from scratch. I have yet to come across an application I was building out that needed a non-default collation, but in the past dealt with an inherited application with multiple database servers where some servers were in CodePage 1, and others were in CodePage 437. It wasn't pretty when we needed to consolidate the application onto a single instance – let's just say I got very familiar with the BCP command.

Database Engine Configuration screen

Here on the first tab ("Account Provisioning"), you pick the authentication mode (and sa password if using mixed mode):

For a lot of shops, mixed mode is used because there are applications that connect to SQL Server from beyond the domain (and it is not always easy to get domain trusts enforced ). While you can connect to SQL Server using Windows Authentication in SSMS to a different domain without a proper relationship in place (see this blog post on runas /netonly by James Kovacs). But if you are in a situation where you can enforce Windows Authentication, this is the more secure option of the two. As with many other choices, make sure you understand how your application will use the database, rather than just breezing through the defaults.

On the second tab, Data Directories, you have much more flexibility than you did in previous versions:

You can dictate the location of the data root (where master, msdb, and model live – you can't customize the location of the resource database, it will always be in <instance root>\MSSQL\BINN\), user database and log directories (where new database files will be created), "temp DB" (let's ignore minor spelling issues for now), and backups. When you can, split these things out as much as possible: the more spindles the better. Having them all on C:\ does not make much sense if you have other drives, or attached storage, or even an underlying SAN that could/should be utilized. Unfortunately, since these are hidden on a tab that is not presented by default, and nothing even suggests that you might want to make changes here at all, I see many cases where these were all left on C:\ – even when there is a perfectly healthy SAN with LUNs carved out for this server. Now, that problem will bite you eventually when C:\ runs out of space, or you may reserve the right to explicitly define the location of the data and log files when you actually create the database. All that is fine, but if you set the default location to a more reasonable place, then you're covered when you forget, or a database is created by someone less familiar with your architecture.

The last tab deals with FILESTREAM. This is another area where a rushed administrator might attempt to set up FILESTREAM access, without really being sure whether your application requires the feature (again to avoid having to come back and configure it later). I have not played with FILESTREAM enough to know how much of a pain it is to later turn it on or off when it has or has not been configured and that was the wrong choice. So as much as possible, try to determine *before* installing SQL Server whether or not you will need this capability.

Error and Usage Reporting screen

Perhaps this is only true in SQL Server 2008 R2 because it is not a final release, but when I get to this screen, both checkboxes are checked:

Which means SQL Server will, by default, send error reports to Microsoft. It's up to you whether or not you want to do this (and you may have a corporate report server set up to receive Windows and SQL Server error reports), but on production instances of SQL Server, I will always strive to ensure that these two checkboxes are unchecked. This is not due to a fascination with Big Brother or George Orwell, but rather to prevent as much network chatter as possible.

And there are other things as well

Once you have SQL Server set up, you may want to look at changing some of the default behaviors. I don't want to say that you should change any or all of these, just want to give an idea of things you should investigate *possibly* changing after SQL Server is running, but before they cause problems later. Just a quick sweep of things off the top of my head:

set up min/max memory, /3GB, /PAE – make sure SQL Server is seeing the memory it's supposed to, and only the memory it's supposed to.

if you need to have auditing / Common Criteria compliance set up, may as well get that out of the way now and ensure it is functional.

for SQL Server 2008 and above, Enterprise Edition at least, you can set WITH COMPRESSION as the default backup option under Server Properties / Database Settings / Backup and restore.

configure max degree of parallelism – sometimes you don't want your instance to have the ability to create plans with all processors, or even with more than one.

straighten out the proxy account for SQL Server Agent early (create and test a job that does things you'll need to do, such as access file system, call cmdExec steps, etc).

if appropriate, change tempdb file allocation (typical wisdom, if you have enough spindles, is to create a data file per physical core).

change the autogrowth settings on the model database : 1 MB for data and 10% for log? This may have been appropriate last decade but it makes no sense today IMHO.

Now, there are cases where you don't get to set the preferences, such as when some software package needs to install Express locally, and does so behind your back, so to speak. And you might not even know that they did so. Or they assume you have Express installed, then use the ATTACHDBFILENAME argument in their connection string, hard-coded to "C:\Program Files\<their app>\<their DB>.mdf" … then proceed to grow that database on your C: drive. What percentage of users installing an application like this will be able to quickly diagnose why their system drive has run out of space? What percentage of those will be able to shut down the app, modify the connection string, and move the MDF file to a more appropriate location? I think it's a shame that software still gets produced this way… any such package should first ask if you already have an existing instance of SQL Server somewhere that you'd like to use for their application. I have to imagine that this practice is responsible for a large portion of the world's SQL Server sprawl.

Summary

Those are some of the basic things that I often see mis-configured in various SQL Server instances that I come across in the wild. I hope you can take at least one thing I touched on in this article and make your next SQL Server installation smoother.

About the Author

9 comments on this post

Nitin - February 11, 2010, 2:26 AM

This blog post pretty much sums all the preconfigured features of our installation.
Is there a way to change the Filepaths for existing installation?

AaronBertrand - February 11, 2010, 6:48 AM

Sure Nitin,
To change the default data / log paths for new databases, you can right-click the server node in Object Explorer, select Properties, choose the Database Settings node, and change the paths listed under "Database default locations." If you want to do this programmatically, you can use these registry commands from T-SQL:
USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server[\<named instance>]\MSSQLServer', N'DefaultData', REG_SZ, N'<drive>:\<path>';
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server[\<named instance>]\MSSQLServer', N'DefaultLog', REG_SZ, N'<drive>:\<path>';
GO
Or you can navigate to those nodes in Regedit and manually edit them. Depending on which method you choose, you may have to restart SQL Server for the setting to take effect. I'd try it but I'm running out of steam. Note that both the UI and the registry will allow you to choose any path, whether or not it exists, and whether or not SQL Server currently has the ability to write to it (so if you have a typo or insufficient permissions, you won't hit the problem until you create a new database).
Also note that this obviously won't change databases that have already been created… you can use detach / attach to physically move existing databases.
To move system databases (other than resource), you can see these articles:
SQL Server 2000 / 2005 :http://support.microsoft.com/default.aspx/kb/224071
SQL Server 2008 :http://msdn.microsoft.com/en-us/library/ms345408.aspx">http://msdn.microsoft.com/en-us/library/ms345408.aspx
The Books Online article also has a 2005 version :http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
Hope this helps.

Uri Dimant - February 11, 2010, 9:10 AM

Hi Aaron
Creat article… I only want to add that if people choose Mixed Authentication I have seen lots of time they put no password for SA or SA as a password 🙂

AaronBertrand - February 11, 2010, 9:20 AM

Uri, Thankfully, SQL Server will no longer allow you to use a blank sa password (at least via setup – I'm not sure if you can still get away with it via sp_password?). You're right though, using sa passwords that are too simple is definitely a bad habit to avoid. However since we are typically able to shield our SQL Servers from the outside world, even when using SQL auth, getting into the data center and having direct access to SQL is a much bigger barrier than brute forcing even a complex password once you're there. I suppose if you are trying to protect the password from potentially malicious co-workers who do have data center access then that's another issue… and maybe a sign that you are not working in the right place. :-)PS I see three copies of the comment from you yet again. I have to imagine you are doing something differently that is causing this. I asked over on Jonathan's recent post but I'll ask here for completeness: what browser / tool are you using to post comments?

TiborKaraszi - February 11, 2010, 1:51 PM

Good suggestions Aaron. I also see the "check all, next next next" principle a lot.
I do want to comment on the collation selection, though. Being non-English, I actually have the very opposite recommendation from yours. Collation selection *should* be a concious decision and not left to default. This should be selected after asking three questions to the app vendors/developers:
What collations do you support?
What collations do you recommend (for me)?
Do you support having a different collation in your user database compared to the system databases.
Selecting default collations do not work in the non-english speaking world. Especially since the default is a moving target and varies depending on selections made by the person who did the Windows install.

AaronBertrand - February 11, 2010, 5:15 PM

Tibor, point taken. In my defense, I would expect that non-English readers would know they need a specific collation, and I did qualify my statements by saying, if you are sure you *DON'T* need a non-default collation…
A

Brian Tkatch - February 12, 2010, 5:12 PM

I wanted to read this blog just "to have read it", and i found that my approach of "next, next, next" was good enough. And now i can say i read it somewhere too! 😛
Thanx for the detail, Aaron.Good stuff.

TechVsLife - July 19, 2011, 6:07 AM

On collation for English speakers: the default (a legacy SQL, not a windows collation) is there for backward compatibility, and is probably not the best choice — unless you never use NVARCHAR/NCHAR. For the highest compatibility with Unicode, Sharepoint, and other drivers accessing sql in unicode, I would say this windows collation should be chosen (or equiv in earlier versions):
Latin1_General_100_CI_AS_KS_WS.
The CI_AS_KS_WS is apparently more consistent with Sharepoint and the windows filesystem.
see:http://social.msdn.microsoft.com/Forums/en-SG/transactsql/thread/b9034b04-9f06-4d56-9791-9f8554f74a51

ZNewall - June 5, 2013, 4:34 PM

Here's a reason for always installing everything…
SQL Server Setup works only on a blue moon when you've prayed to the right God. So when you come to add something you suddenly realise you need you're presented with a nice random 0x error message which no one knows how to resolve.
This has been the case for at least the last 3 releases of SQL Server.