April 12, 2011 at 7:59 am
I have several Servers running SQL Server 2005.
As a general rule, when I install the instances on each Server. I set the Default data and log paths to be on different volume (different spindles), I relocate my TEMPDB database files to their own volume and I move my Master, MSDB and Model database files to my default data and log paths. I don't know if this is a good way to go.. but this is what I have always done.
it is then a good idea to leave the mssqlresource.mdf and mssqlresource.ldf files in their original location?
In other words, here is how the system db files are placed for my default instance:
I have the system database data files here:
F:\MSSQL\Servername\Default\Datafiles\master.mdf
F:\MSSQL\Servername\Default\Datafiles\model.mdf
F:\MSSQL\Servername\Default\Datafiles\msdbdata.mdf
and the system database log files here:
G:\MSSQL\Servername\Default\logfiles\mastlog.ldf
G:\MSSQL\Servername\Default\logfiles\modellog.ldf
G:\MSSQL\Servername\Default\logfiles\msdblog.ldf
and then my resource database files here:
E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf
E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf
I guess my question is.. is this a good idea? to have the master DB files in a different location from the mssqlresource database files?
April 12, 2011 at 10:11 am
It is definitely a bad idea. It causes failures with patching if the master and resource databases are separate.
Install your system databases where you want them as part of the install process and LEAVE THEM THERE. There is also no point in separating system database data and log files, there is not enough activity on them to warrant this.
As my old daddy used to say - keep it simple.
---------------------------------------------------------------------
April 12, 2011 at 10:12 am
p.s - I should clarify it is ok to move tempdb
---------------------------------------------------------------------
April 12, 2011 at 10:29 am
Thank you. I will put things back the way they were .. except for TEMPDB of course : )
April 12, 2011 at 10:35 am
Is there a query (or queries) that can be run that will tell me where the master and mssqlresource database files are located for a partucluar instance?
April 12, 2011 at 10:38 am
use mssqlresource
exec sp_helpfile
April 12, 2011 at 10:53 am
I get this
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'. No entry found with that name. Make sure that the name is entered correctly.
maybe because it is a hidden Database??
I just want to make sure I'm matchin up the correct mssqlsystemresource database and master database files.
April 12, 2011 at 10:56 am
I have never been able to query the resource database that way either - that is without restoring it as a different database.
Do you have multiple instances setup on the server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 11:41 am
I have multiple instances. I have mssqlsystemresource database files in the following paths:
E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data
E:\Microsoft SQL Server\MSSQL.3\MSSQL\Data
I know the MSSQL.1 is my default Instance...
I just want to make sure I'm matching up the correct Master, MSDB and Model Database files with the correct mssqlsystemresource database files ... ?
April 12, 2011 at 11:54 am
In the paths to where you moved the master db files, did you name the folder structure in accordance to the Instance name?
E.g. mssqlserver\Instance1 would be C:\DatabasePath\Instance1\... or something like that?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 12:51 pm
yes. the path(s) where I moved the the system data files to are named like this:
F:\MSSQL\Servername\Default\Datafiles
F:\MSSQL\Servername\Datafiles
F:\MSSQL\Servername\Datafiles
and
G:\MSSQL\Servername\Logfiles
G:\MSSQL\Servername\Logfiles
G:\MSSQL\Servername\logfiles
They are named with the actual Instance NAME - not the MSSQL.1 MSSQL.2 naming convention.
April 12, 2011 at 1:02 pm
I can't tell from the first post, but did you do the same sort of thing for your resource database (moved the files to a folder schema with naming convention like the other databases)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2011 at 1:12 pm
When I installed the instances.. all system database files (master, msdb, model and mssqlsystemresource) were in folders with the following (default) naming convention:
E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data
E:\Microsoft SQL Server\MSSQL.3\MSSQL\Data
I then relocated the Master, MODEL and MSDB data and log files to these locations:
system database data files here:
F:\MSSQL\Servername\Default\Datafiles (default instance)
F:\MSSQL\Servername\AML\Datafiles (named Instance)
F:\MSSQL\Servername\OV\Datafiles (named Instance)
system database log files here:
G:\MSSQL\Servername\Default\Logfiles (default instance)
G:\MSSQL\Servername\AML\Logfiles (named Instance)
G:\MSSQL\Servername\OV\Logfiles (named Instance)
April 12, 2011 at 1:12 pm
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id < 5
---------------------------------------------------------------------
April 12, 2011 at 1:15 pm
The Resource database metadata cannot be viewed by using the system catalog views or system tables.
Another good reason to keep the resource database with the master in its default location.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply