January 30, 2009 at 5:19 pm
Dont let the subject fool you. 🙂 I know that the system databases definitely need to be backed up, and I do.
My question is though about location of the actual system databases.
We have a growing SQL Server environment here. Currently 8 servers, but that is going to double this year. DB's range in size from 2gigs to 50gigs, and will have a 300gb by start of next year.
I am planning the environment (hardware and storage) for a large project we have coming down the pipeline. This particular project will be on Windows Server 2003 running SQL Server 2005 SP2. We are going to attach it to our SAN have have 3 LUNS presented to the OS for SQL Server as well as HA options.
First LUN will be fore the database files
Second LUN will be fore transaction log files
Third LUN will be used for tempdb.
My question though in regards to the system databases is that, upon default install, they get installed on the C: drive (unless I missed a part I can specify a location...).
Now, with this new setup, does it make sense to put ALL the system databases on the SAN as well? Master, model, msdb, myresources? Or should I leave them where they are?
Trying to get a feel on how I should lay these servers out.
Thanks,
Jason
February 1, 2009 at 4:00 am
up to you entirely really, tempdb is the one you should ideally relocate. Bear in mind if you do move master, where ever you put master.mdf the mssqlsystemresource.mdf and ldf must go in the same directory. On our systems the user and system databases are relocated from the C drive and the drive they sit on is SAN based
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2009 at 8:05 am
you can change the installation location of the system db files (and program files) by going into advanced options during the install and expanding the database engine tree structure.
I would definitely move the system dbs onto the SAN. with your proposed setup the same LUN as tempdb would be the obvious location. It is also common practice to separate out SQL executables from the OS, so perhaps a LUN for SQL exes and system dbs (though still separate out tempdb. Also I think you should consider another LUN for your backup files to separate out that I/O activity and give greater resilience.
---------------------------------------------------------------------
February 2, 2009 at 7:13 am
On the "Advanced Options" of the installation, if you highklight the "DataBase Services" line at the top, clisk "browse" at the bottom and this will change the location of the os executable files. I have found that I can change the drive letter but have had trouble changing the path.
Then, highlight the "Data Files" at the top and click browse at the bottom. This will change the location of the entire data files directory structure that SQL installs starting with MSSQL.1 > MSSQL > and then 7 or 8 more directories including your data files.
Tim White
February 2, 2009 at 10:54 am
Perry Whittle (2/1/2009)
up to you entirely really, tempdb is the one you should ideally relocate. Bear in mind if you do move master, where ever you put master.mdf the mssqlsystemresource.mdf and ldf must go in the same directory. On our systems the user and system databases are relocated from the C drive and the drive they sit on is SAN based
Did not realize that. For the myssqlsystemresource.mdf, what is the proper way to move that? I know it is 'hidden' for the most part, but if I move the master DB, I need to but the above in the same directory? Correct way to do that?
Thanks
Jason
February 2, 2009 at 10:56 am
george sibbald (2/1/2009)
you can change the installation location of the system db files (and program files) by going into advanced options during the install and expanding the database engine tree structure.I would definitely move the system dbs onto the SAN. with your proposed setup the same LUN as tempdb would be the obvious location. It is also common practice to separate out SQL executables from the OS, so perhaps a LUN for SQL exes and system dbs (though still separate out tempdb. Also I think you should consider another LUN for your backup files to separate out that I/O activity and give greater resilience.
Thanks for the input. I appreciate it.
Interesting. I am pretty new to SQL Server, but quickly learning. So it sounds like for my best practices and if I could have everything my way, i would need 5 LUN's:
1. SQL executables
2. Database files
3. Transaction logs
4. TempDB
5. LUN for backups
That would be in a perfect world. 🙂
Sounds good. Appreciate the advice.
Cheers,
Jason
February 2, 2009 at 11:11 am
its in BOL but here goes
open sql configuration manager and modify the startup parameters for that sql instance. The paramaters will look something like this
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
to move the master mdf and log use the following (E:\sqldata for my path)
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
accept all changes and stop the sql instance. Now start sql instance from cmd prompt using
NET START MSSQLSERVER /f /T3608 (substitute MSSQLSERVER with the service name for your instance)
from the command prompt via SQLCMD run the following
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
do not change the file names only the paths and these should match the location of master.mdf
once done move the resource db files to the new location and then set read only
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
exit sqlcmd and restart instance normally
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 2, 2009 at 11:21 am
try and avoid having to move the system db files by placing them where you want them as part of the install process.
Moving master and resource database can cause problems with later upgrades. It is certainly vital master and resource are kept together.
---------------------------------------------------------------------
February 2, 2009 at 11:24 am
IIRC the only time you get problems with upgrades after moving the master database is if the resource database is in a different location. That is why they need to be kept together, otherwise there shouldn't be a problem
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 2, 2009 at 11:41 am
Perry Whittle (2/2/2009)
its in BOL but here goesopen sql configuration manager and modify the startup parameters for that sql instance. The paramaters will look something like this
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
to move the master mdf and log use the following (E:\sqldata for my path)
-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf
accept all changes and stop the sql instance. Now start sql instance from cmd prompt using
NET START MSSQLSERVER /f /T3608 (substitute MSSQLSERVER with the service name for your instance)
from the command prompt via SQLCMD run the following
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
do not change the file names only the paths and these should match the location of master.mdf
once done move the resource db files to the new location and then set read only
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
exit sqlcmd and restart instance normally
Sorry about that. I should have asked if it was the same as moving system. I just did a round of that on a bunch of SQL Servers the other day. :hehe:
Appreciate the help
Cheers,
Jason
February 2, 2009 at 12:46 pm
your welcome Jason
as we have stressed already, just make sure resource is i the same place as master.mdf otherwise when you come to upgrade you could\will hit problems
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 2, 2009 at 3:01 pm
Perry Whittle (2/2/2009)
your welcome Jasonas we have stressed already, just make sure resource is i the same place as master.mdf otherwise when you come to upgrade you could\will hit problems
Will do. Bookmarked that section of BOL (I really do like the bookmark feature in BOL...saves a ton of time)
Cheers,
Jason
February 4, 2009 at 3:12 am
Personally, I would not move master or model, for slightly different reasons.
The SP2 install places a new version of mssqlsystemresource into the original location of this database (as defined at install time) and upgrades the master database to the new build level. If master points to a mssqlsystemresource database at a different location to where SP2 put the new version, SQL will refuse to start.
My view is we have to assume Microsoft will make further mistakes in this area, and if master or mssqlsystemresource are not in their original locations there may be problems when maintenance is applied. Therefore I feel best practice is to leave these databases in their original locations.
There are no performance or integrity benefits in moving model, so in order to avoid unnecessary work this can be left in its original location.
Tempdb normally has to be moved to get the best I-O performance, and there are no issues in doing this.
Msdb sometimes gets heavily used in a given site, sometimes hardly used. If a site stores many SSIS and/or DTS packages in msdb or are heavy users of other facilities provided by msdb, then I think it should be treated the same as other user databases and located according to site standards for user databases. I do not know of any issues with applying maintenance to msdb, so this should be safe to do.
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
February 4, 2009 at 3:54 am
February 4, 2009 at 8:48 am
EdVassie (2/4/2009)
The SP2 install places a new version of mssqlsystemresource into the original location of this database (as defined at install time) and upgrades the master database to the new build level. If master points to a mssqlsystemresource database at a different location to where SP2 put the new version, SQL will refuse to start.
if you are aware of this when applying this SP then it would be easy to start the instance with the appropriate trace flag (T3608) and move the resource db in the documented way. This would get you back on track
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply