October 27, 2013 at 8:08 pm
I am curious as to what some of the experts do ... I've worked at several places and have come in usually after SQL Server has already been installed. Consequently I have seldom needed to do the installation. It is apparent that not everyone does it the same.
My question is: when there is a typical server that has the local "C:" drive and there is available at least one "D:" drive for SQL Data , an "L:" drive for transaction logs, "B:" drive for backups, and a "T:" drive for tempdb
what is the best practice choice for where to put the system databases? do you leave them buried down in the C: drive Programs... or do you put them someplace else? do you place the master database and log in different location than model and msdb?
do you put Tempdb and TempdbLog on the same drive?
I am interested to know what would be considered the "best" and what your reason is.
Thank you anyone and everyone that weighs in on the topic.
October 27, 2013 at 10:28 pm
I do my best to not install any part of SQL Server on the operating system drive. SQL Server is basically it's own "operating system" as you will find some folks reference it as "SQLOS". You have to consider all those additional files that come along with where SQL Server is installed to besides the system databases such as ERRORLOG file, SQL Agent logs, job logs, dump files, etc. All those files add up in size and in most cases is something alot of folks forget about.
master, model, and msdb
If I am able to install SQL Server on it's own drive then I will leave these system databases at their default location. If I have to install SQL Server on the operating system drive because someone is going to kill my dog, then I would move them to another drive. I do not tend to seperate the data and log files because I don't really care about their performance. The exception to my rule might be with msdb in some circumstances where it is an ETL server or replication may be involved and this particular database will see more activity than normal.
I consider it best to never touch the master database. You will not see any improvements of performance of any kind by moving it so there is no real purpose. I don't like moving it also because of service packs and cumluative updates, you never know what Microsoft is going to do and patches are going to always touch that database.
tempdb
I will generally place tempdb data and log file on their own drive. Obviously planning of the server build you would be aware of the load that will be placed on tempdb, in a perfect world. If I am going to have multiple tempdb data files and I can have the disk I would split the data and log files. In general builds though I tend to keep them together on a seperate drive from the user databases.
General Disk/File Layout template
My template for customers that I like to have on builds:
Disk 1: OS
Disk 2: SQL Binaries/system databases (except tempdb)
Disk 3: backups
Disk 4: user database (data files)
Disk 5: user database (log files)
Disk 6: tempdb (data and log)
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 28, 2013 at 2:49 am
As long as the boot partition is on a mirrored RAID1 you can leave master/msdb/model there.
The biggest issue I see with your construct is that you have backups going to the same server. That is a HUGE no-no unless you have some other mitigating factor in play.
The tempdb/tempdblog question is a bit more difficult. I see clients where tempdb is massively overused for a variety of reasons and then isolating tempdblog with other logs could be helpful OR it overwhelm the log partition you have and cause slowness across other databases. Another issue with logging is that you WANT to have pure sequential writes there, but if you put 100 databases on the server that are all active to some degree you no longer get sequential activity on the disk - the heads are bouncing all over the place. Not good either.
There are SOOO many things that come into play with the provisioning and CONFIGURING of a new server and SOOO many ways I see clients mess it up!! :crying:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 28, 2013 at 8:41 am
Hi Shawn,
Thanks for providing detailed information. As per your information i understand and your template looks below. As per my knowledge Raid levels play a vital role in performance. Do we need to consider Raid levels for each drive? If there are then could you also provide for each drive?
Drive LetterContent on diskRaid level of disk
C$:OS
d$:SQL Bits and System DB'S
E$:Backups
M$:Data files
L$:Log files
T$:Temp DB
Thanks in advance.
Thanks,
I’m nobody but still I’m somebody to someone………….
October 28, 2013 at 9:55 am
Thank you all for the discussion.
In the world I have right now there are only 5 drives not 6.
Considering the "D:" drive is available for SQL data, user databases
and there is the log, tempdb, backups, and "C:" drives
would you put the SQLOS , etc. on the C: drive with the OS or on the D: drive with the user databases?
The C: drive is RAID5 100GB
D: RAID10
E: RAID10
TempDB RAID10
Backups are separate and files will be backed up using Netbackup.
October 28, 2013 at 10:03 am
Installing on the C drive would be sufficient for me in that situation.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 29, 2013 at 4:49 am
The majority of the time I normally just have my system databases (Data and log files) with the user data files
C Drive - no SQL
1) system databases, user data files
2) User log files
3) Tempdb
4) Backups
October 29, 2013 at 8:57 am
Personally, I have moved to the idea of putting the SQL programs and system databases (apart from tempdb and maybe msdb) on to the system drive.
In any SQL server install for SQL 2005 or above, most of the SQL binaries will always end up on the system drive because they live in the GAC. Typically a SQL install will add 2.5 to 3 GB to the system drive and under 0.5 GB to the 'program' drive if you try to separate the components. In terms of IO load, you gain almost nothing from separating the SQL components.
The master, model and resource databases have next to no IO load, even while SQL Server is starting, so there is little point in moving these from the default location. Some Sysadmins always want to put the system Page file on a separate drive, others leave it on the system drive. Regardless of what is done at system build time, you need to monitor IO to the system drive and start to get worried if you see any consistant IO queueing.
Tempdb should always be placed away from the system drive, unless you expect your SQL instance to do almost no work (some SQL Express instances come into this category). Msdb can be left in the default location or moved, depending on what use you make of the SQL components that store their data in msdb - the more you hit msdb the greater is the case for moving it.
User databases should go on their own drives, with data and log files on separate drives. Often, tempdb log (and msdb log if you move it) can share the same drive as user db logs.
Backups should go to a drive not used by any of the above.
This gives a minimum drive letter footprint for a SQL Server system of 5 drive letters, but it will not be the right footprint for heavy workloads. You need to estimate the IO load you will get on your DBs before you build your server, work out how many IO channels are needed to service the IO, then monitor the result. In simplistic terms (as simple as a desktop PC), an IO channel can be a single physical drive. For servers, an IO channel can be a LUN that is isolated from other LUNs. For the Cloud, an IO channel can be a LUN with a specified IO service level (eg Provisioned IOPS in the AWS environment).
Do not be surprised if your acutal IO load is different from your estimate. You should plan to review your disk layout on a regular basis and add additional IO channels as required to meet your performance SLA.
At my place, we are going down the path of allocating a pool of drive letters for DB data files. Small databases will be allocated to one of the disks in the pool, while larger DBs will have multiple files in a filegroup, whith a given file allocated to a given disk in the pool. We used to isolate IO for a given DB to a given drive(s), but after many years of this we reckon that just spreading the IO load over a pool of disks will achieve the same results but be easier to manage.
Finally, your standard server build will depend on how many SQL instances a single DBA has to manage. Some people manage 100s of instances, and at this scale you have to establish standard builds for small, medium and large workloads, then assign your database to the appropriate server build. If you undersize your server in this situation, the solution is more likely to be to move the workload to the right size build than tinkering with the server you have.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply