May 15, 2009 at 10:19 am
Hi. I'm not a DBA, but am trying to install SQL Server 2005 Standard Edition, so that database and log files (for system databases and any user databases created subsequently) end up on different drives (on different arrays), as I gather this is best practice.
I've found the template.ini file and a parameter to specify data file location, but none for the log files. Can anyone tell me exactly how to do this?
Alternatively, is there an option to specify the log file location when user databases are created?
Any advice much appreciated.
May 15, 2009 at 10:24 am
If you use SSMS after installing SQL Server you can modify the default locations for your Data and log Files by right-clicking on the server and selecting properties. Then you go to the Database Settings Page and you can set the database default locations (Data and log files)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 15, 2009 at 11:14 am
Thanks, Jack. Much appreciated. Easy when you know what you're doing!
I did the install with a template.ini and then changed the settings as you suggested and created a test DB. Sure enough, the .mdf and .ldf files were created in the correct separate locations.
One more question, if I may: would you advise moving the sysem DB log files, and, if so, how would a novice like me go about doing it?
Thanks again.
May 18, 2009 at 3:05 pm
Altering system DB is not the safest thing to do.
But you can move TempDB to which ever drive you want with an "Alter Database tempDB ". There are examples of that in BOL. You will have to restart SQL Server for it.
-Roy
May 18, 2009 at 4:04 pm
The best practice for separating data and log files does not apply to system databases (tempdb excluded which can be on its own drive and is a special case)
the resource database is read only anyway so no benefit in separating data and log, master and model databases are as near as damn it read only so same applies. Also DO NOT separate the master from the resource database you will get failures on upgrades.
msdb has write activity but you would have to have a lot of SSIS packages, jobs and backups to get noticeable benefit, so my advice to you is keep your installation simple and as standard as possible and leave the system database files where you originally installed them, which of course means place them correctly in the first place as part of the installation process.
---------------------------------------------------------------------
May 19, 2009 at 5:18 am
Thanks, George, Roy and Jack for your replies. All much appreciated.
May 23, 2009 at 3:28 am
Hi Edtraviles,
Several months ago I was in exactly the same situation that you're now in, I needed to deploy an SQL Server without knowning it at all. To get prepared and understand what to do, I just read and read, and read again tons of threads and articles on this forum, BOL and MSDN. Although I've lost some valuable links, the others have survived. You can have a look at:
http://qa.sqlservercentral.com/Forums/Topic647409-357-2.aspx
http://qa.sqlservercentral.com/Forums/Topic635970-146-2.aspx
http://qa.sqlservercentral.com/Forums/Topic383395-266-1.aspx
http://www.mssqltips.com/tip.asp?tip=1604
http://support.microsoft.com/kb/224071/en-us
http://blog.sqlguy.dk/2007/11/moving-system-databases.html
Shortly,
1. to set data and log files location for both system and user DBs away from \Program Files, during setup of SQL Server, on the components page, click SQL Server Database Services, click Data Files below, then configure a new location; after setup finishes, you can change default location for user DBs' log files (see 2 below)
2. to set default location for user DBs' data and logs files after setup, SQL Server Management Studio –> -> server’s Properties -> Database Settings -> Database default locations (before doing this, set NTFS permissions for this location for SQLServer2005MSSQLUser$$ as Full Control)
3. to set system databases' files location after setup, see the procedure in links above; although there's no special reason to separate data and log files of system databases, you still can do it in this procedure; note: it must be done after SP2 is applied, otherwise attempt to apply SP2 fails; this operation is very delicate, so it's vitally important to perform it properly; I found a perfect script that did it right way (this some customization, of course), but I've lost the link of the original author's web-page, and I'm not sure whether it ethical to provide this script in my post.
However, only after I completed the setup and put the server in the production environment, I've read about lack of the reason to separate logs and data files of system databases (except for tempdb). Nevertherless, the server's been up-and-running and felt good for several months already, having all data files on one disk array, and all log files on another disk array. So it's up to you whether to do it or not.
BTW there's a perfect article on this site by one of the gurus that describes when and how to configure location of database files. AFAIR, again shortly, this configuration shouldn't be the purpose itself, it should depends on the size of your installation. For small and simple installation it'd be enough to leave default configuration; for small-to-medium installations it's reasonable to put database files on a separate physical disk; for medium installations it's be good to separate data and log files of the user databases on physical disks; for large installation it may be necessary to put tempdb on a separate physical disk. As the other system databases have insignificant activity, there's no special reason to separate their data and log files.
Good luck!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply