February 22, 2010 at 6:50 am
I have installed SQL Server 2005 on our Windows 2003 Server. (Just a simple install and I am trying to see how the installer is creating directories and storing files.) During the installation, under the Program Features Dialog, I clicked Advance, expanded Database Services, clicked Data Files and changed the Installation Path to D:\mssql2005\MSSQL\.
After the install, the data and log folders were created in D:\mssql2005\MSSQL\MSSQL.1\MSSQL\Data. This is such a long path name. Should I have just enterred d:\mssql2005? It appears the installation creates a sub-folder named MSSQL withn MSSQL.1.
I noticed the D:\mssql2005\MSSQL\MSSQL.1\MSSQL\LOG directory contains SQL Error Logs, SQL Profiler Trace Files and SQL Agent Error Files. Should the database log files be stored here also? I did not see any configurable path for the database log files during the install. I noticed the data and log files for the system databases (and Pubs database) are stored in the above (D:\mssql2005\MSSQL\MSSQL.1\MSSQL\Data) directory.
Regarding Analysis Services:
During the installation, under the Program Features Dialog, I clicked Advance, expanded Analysis Services, clicked Data Files and changed the Installation Path to D:\mssql2005\MSSQL\.
After the install, the data and log folders were created in D:\mssql2005\MSSQL\MSSQL.2\OLAP\Data. Again, this is such a long path name. Should I have just enterred d:\mssql2005? It appears the installation creates a sub-folder named OLAP withn MSSQL.2.
Is MSSQL.1 sub-folder for the Database Services and MSSQL.2 for Anaylsis Services?
Thanks in advance, Kevin
February 24, 2010 at 11:55 am
After the install, the data and log folders were created in D:\mssql2005\MSSQL\MSSQL.2\OLAP\Data. Again, this is such a long path name. Should I have just enterred d:\mssql2005? It appears the installation creates a sub-folder named OLAP withn MSSQL.2.
Is MSSQL.1 sub-folder for the Database Services and MSSQL.2 for Anaylsis Services?
I am running Windows 7 my SQL Server 2005 instance SSAS is MSSQL.3 so you can double click on the the folders and you will see what is stored in each folder.
Kind regards,
Gift Peddie
February 25, 2010 at 4:45 am
The naming convention does indeed separate aspects of SQL Server in a consistent way, depending on your chosen options. For my PC, MSSQL.1 is the Database service, MSSQL.2 is Reporting Services, and MSSQL.3 was created when I added an instance of Express. For servers we root the data directories under "<Drive letter>:\Data\Microsoft SQL Server". The deep path names are a lot better than shared or clashing location names - and nothing like as complex as the range of paths that Oracle uses.
I think the ambiguously-named LOG directory has become a SQL Server tradition (I expect someone will now tell me that SQL2K8 changes it)! Database log files hold data so they go under Data, and of course you then want to split them off to a separate drive; but I don't recall an option to put them somewhere different during installation.
If you plan to move LOG, it is referenced in a few places:
Error log: it's the -e startup parameter, accessible as a SQL Server advanced property under SQL Server Configuration Manager (SSCM)
Dump directory: accessible as SQL Server Agent advanced property under SSCM
SQLAGENT log: on XP and W2K3 I change this using regedit:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1
SQLServerAgent
ErrorLogFile
On a separate point - and others may know better - I also use regedit to change the default backup path at
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1
MSSQLServer
BackupDirectory
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply