I’m setting up SQL 2016 Standard for low-volume use. All I want is to put the programs on the C: drive and all data (including error logs, system and user databases and logs) on the D: drive. I missed some things during the first installation and had to start over. Twice. This time I made screen shots.
Keep in mind that during the install wizard, you can move back and forth between panes to see the effects of your selections. That sure beats starting from scratch!
On the Feature Selection pane, I chose to install Database Engine Services, Full-Text and Semantic Extractions for Search, Client Tools Connectivity, and Integration Services. Careful on the Feature Selection pane. If you change the Instance root directory, the data is moved but the SQL binaries will be on the D: drive as well. Not what I wanted. Just leave the instance root as C\:Program Files\Microsoft SQL Server\.
The next screen shows you were the SQL Server directory will be. This is, in fact, where I want the binaries:
I didn’t change anything on the Server Configuration pane.
Pay close attention to the Database Engine Configuration pane. It has several tabs. Set up your user(s) on the Server Configuration tab. The Data Directories tab allows you to modify folders. This is what we’ve been looking for. Just change the Data root directory and the rest will follow, including the grayed-out System database directory:
The TempDB tab does allow changes, but I didn’t change anything here:
Scroll down in the Ready to Install pane to double-check the directories:
That did it: the C: drive has binaries and the D: drive has data:
Very helpful…thank you
Thank you, exactly what I was looking for!
Thank you too, I’m sick of having to move them after the install
It worked perfectly for me. Thank you.
What about moving the “Shared Feature directory” to non C drive ?
@Maiz. not sure I understand your question. I was documenting how to move only the data, which I like to keep on a separate drive from the program. That way, I can restore the OS drive and get SQL back, or I can restore the data drive without messing up the OS.
Can I select 64kb allocation unit size on the disk which will be used for data root directory? Apart from mdf and ldf files, installer will create there couple of other things like log directory. I wonder if there is any performance impact in such case.
Vaadrigar – allocation unit size is unrelated to this article. I suggest you do a web search for “sql ntfs allocation unit size”. For example, see “NTFS Allocation Unit Size” in this article: https://technet.microsoft.com/en-us/library/cc966412.aspx.
I realize this blog post is a little old now, but I’m curious how you assign the rights to the drives with this setup. With binaries on C:\ but the SQL Server Data root directory on D:\, do you give the service account full control permission on C:\ and D:\? Or just C:\, while D:\ has List Folder Content permissions?
Chris, if I recall correctly, the installer takes care of folder permissions. I don’t think I needed to change anything. The service account would need to be able to change the database files and create backup files, so it would need write permissions (at least) on D:.
Excellent, just what I was looking for:
You might also find the following very useful:
https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/
Thank you, i was searching for this.
Very useful article and exactly what I was looking for.
Excellent article and was exactly what I was looking for. Thank you.
Thanks a lot.