August 23, 2009 at 11:19 am
Hi,
I'm installing SQL Server 2005. I have the following drives:
C -OS, D -Data, E -Log, T - Tempdb, Z -Backups. So while installing SQL Server, I have select the C drive as C:\Microsoft SQL Server.
After the Installation, I have noticed that the .mdf & .ldf of System databases were created at C:\Program files\Microsoft SQL Server\MSSQl.1\MSSQL\Data.
But for user databases, I have selected D:\Data and E:\log
But I came to know that we should NOT install any SQL Server binaries on C drive. It must be dedicated to OS installation only.
What happens we Install SQL Server on C drive?
Is the performance effects? Is SQL Server won't work properly?
August 23, 2009 at 12:46 pm
gmamata7 (8/23/2009)
Hi,I'm installing SQL Server 2005. I have the following drives:
C -OS, D -Data, E -Log, T - Tempdb, Z -Backups. So while installing SQL Server, I have select the C drive as C:\Microsoft SQL Server.
After the Installation, I have noticed that the .mdf & .ldf of System databases were created at C:\Program files\Microsoft SQL Server\MSSQl.1\MSSQL\Data.
But for user databases, I have selected D:\Data and E:\log
But I came to know that we should NOT install any SQL Server binaries on C drive. It must be dedicated to OS installation only.
What happens we Install SQL Server on C drive?
Is the performance effects? Is SQL Server won't work properly?
Theres nothing as such documented for this.
And it has nothing to do with SQL Server working.
It has been documented that data file of DB should be placed on different disk & the Log file on different disk.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 23, 2009 at 2:06 pm
gmamata7 (8/23/2009)
After the Installation, I have noticed that the .mdf & .ldf of System databases were created at C:\Program files\Microsoft SQL Server\MSSQl.1\MSSQL\Data.
You have the option during the installation (click on Advanced button when selecting where to install). Drill down to the data files section for the database engine and you can change where the system databases are installed.
But for user databases, I have selected D:\Data and E:\log
Once the installation is complete, you then change the database settings in the properties for the server to the default locations you want:
But I came to know that we should NOT install any SQL Server binaries on C drive. It must be dedicated to OS installation only.
I hear this a lot and have to wonder why anybody is concerned about installing the binaries of any application on the C:\ drive. The Program Files folders (and ProgramData in Windows Server 2008 and greater) are designed to hold the binaries for applications.
Especially when it comes to a database product - where the binaries are located is less important than where the database files are. You can always re-install SQL Server - but if you lose your database files they are gone.
What happens we Install SQL Server on C drive?
Is the performance effects? Is SQL Server won't work properly?
Nothing, other than having the binaries on the C:\ drive - no issues or problems. Besides, no matter what you change there is always going to be parts that are installed on the C:\ drive (or more approriately, on the system drive). You cannot avoid that no matter how hard you try.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 24, 2009 at 9:00 am
The data files (which includes log and tempdb) should not be on the C: drive to avoid contention with the swap file. Although SQL Server memory is not paged, SSIS memory cannot be easily regulated, and can cause paging, but that is another story. Another reason is to avoid contention with virus scanning of executables on the C: drive. There should be VS exclusions set for the database and backup files.
September 16, 2009 at 2:00 pm
I need a little more detail on the following statement
You have the option during the installation (click on Advanced button when selecting where to install). Drill down to the data files section for the database engine and you can change where the system databases are installed.
I have installed SQL 2005 standard edition but I don't see where the advance features options are.
Could someone provide a screenshot or more detail?
I just want to install SQL 05 binaries on C:\, and Data/Log on D:\
September 17, 2009 at 10:33 am
The system databases are created on the disk where you put the SQL program folders.
Best practice is to leave master, mssqlsystemresource and model where they are installed.
You should always plan to move tempdb, perferebly to its own disks (with data and log on separate disks).
There is a miix of views over msdb, with some people saying leave it where it is installed. Other people say to move msdb (my preference) as it can hold a lot of information about jobs, packages, etc.
You may also want to look at SQL FineBuild, which helps a lot in getting a best practice install.
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
September 26, 2009 at 12:57 pm
Thank you so much for taking the time on answering my question. I will definitely look at the things you have suggested.
September 27, 2009 at 9:28 am
The system databases are created on the disk where you put the SQL program folders.
Best practice is to leave master, mssqlsystemresource and model where they are installed.
Hi,
I have initially installed SQL Server 2005 on D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Later I have moved master.ldf, msdblog.ldf, modellog.ldf to E:\SQLLOGS
So, all .mdf files of System & User databases are on D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
and all .ldf files of System & User databases are on E:\SQLLOGS
and I DID NOT move mssqlsystemresource.mdf & mssqlsystemresource.ldf. They are on D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Is there any harm in moving master.ldf, msdblog.ldf, modellog.ldf to a different drive from the original installed place???
please advice...
September 28, 2009 at 2:47 am
Microsoft say that mssqlsystemresource must be in the same folder as master for SQL 2005.
If they are in different folders and you apply SP1, then the SP1 install will fail and SQL will not start. It is possible to fix the problems this causes, but it takes time. I have not tested going from RTM direct to SP3 with these databases in different folders - having got my fingers burnt with SP1 I now advise to leave master and mssqlsystemresource (and model) where Microsoft puts them.
Because Microsoft are assuming these databases will be in the same folder for SQL 2005, we cannot assume that any future SPs or CUs will run correctly if they are in different folders. If you want to keep them in different folders, then you have to accept the risks that SQL may not start after you apply some maintenance.
For SQL 2008, Microsoft puts the mssqlsystemresource DB in the \binn folder along with the SQL binaries. Master is placed in the \data folder. My advice is to leave both of them where Microsoft puts them.
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
September 28, 2009 at 1:07 pm
Moving the master and Resource Databases http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx
The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move the Resource database to the same location as the master data file. Do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.
In my case, I have moved masterlog.ldf to a different drive but master.mdf and mssqlsystemresource.mdf & mssqlsystemresource.ldf are on the same location, which is satisfying the above statement "The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf) ". So I believe, I will face NO risks in future..
Please advice..
September 28, 2009 at 1:31 pm
gmamata7 (9/28/2009)
In my case, I have moved masterlog.ldf to a different drive but master.mdf and mssqlsystemresource.mdf & mssqlsystemresource.ldf are on the same location, which is satisfying the above statement "The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf) ". So I believe, I will face NO risks in future..Please advice..
Why are you moving any of the system database files? The reasons for moving the log files to different drives is to improve performance of a system. For user databases where you have a lot of transactions, moving the log files improves performance by separating the types of writes that occur in a log file (sequential writes), from the types of writes that occur on the data file (random writes).
You also improve performance because you can make sure the sequential writes are happening as fast as possible.
Moving the log file for the master database is not going to help performance in any way, since there are not enough transactions happening in that database to make any difference. In fact, by moving the log file - you are making it harder to manage the system because you now have to know that the log file for that one database has been moved.
If you do not want the system databases living on the C:\ drive, then during installation of SQL Server you select the Advanced option and move the data files to a different drive. This is actually required (and you get prompted) when performing a cluster install - because the databases cannot exist on locally attached storage and must exist on shared storage.
I would recommend keeping all system database files (mdf and ldf) together in the same location regardless of whether or not they are on C:\ or S:\ or some other drive.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 28, 2009 at 5:00 pm
Why are you moving any of the system database files? The reasons for moving the log files to different drives is to improve performance of a system. For user databases where you have a lot of transactions, moving the log files improves performance by separating the types of writes that occur in a log file (sequential writes), from the types of writes that occur on the data file (random writes).
We have a\a\p cluster setup. I moved .ldf files of System databases to a different drive to just keep all the .mdf files on one drive & all the .ldf files on another drive.
If they are in different folders and you apply SP1, then the SP1 install will fail and SQL will not start. It is possible to fix the problems this causes, but it takes time. I have not tested going from RTM direct to SP3 with these databases in different folders - having got my fingers burnt with SP1 I now advise to leave master and mssqlsystemresource (and model) where Microsoft puts them
.
I have tested this by applying SP3 everything went well...
Still, I want to make sure that should I need to move the .ldf files of System databases backup to the original location??
Thank you so much
September 28, 2009 at 5:49 pm
For cluster configurations, this is how I set things up:
S:\ drive contains system databases (all files, mdf and ldf)
G:\ drive contains user database data files
L:\ drive contains user database log files
T:\ drivve contains tempdb mdf and ldf files
If I have a system where there are multiple instances, I modify the above to use a single drive letter with mount points for each type.
I do not move ldf files of system databases to another directory - I leave all system related database files in the same location, since there is no reason to move them anywhere else.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 29, 2009 at 12:36 pm
Is better to move system databases ldf files to the original location where they installed for the first time?? If yes, I will go head and do that..
please advice
September 29, 2009 at 12:41 pm
gmamata7 (9/29/2009)
Is better to move system databases ldf files to the original location where they installed for the first time?? If yes, I will go head and do that..please advice
As I said before, I don't ever separate the system database files. I keep all system database files in the directory where they were installed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply