none
Where is a good place to keep database files? RRS feed

  • Question

  • I'm using XP Pro SP3 and SQL Server 2008 Express.

    Where is a good place to keep database files?  I notice that the default path is in Program Files.  I'd rather keep them in a documents folder or somewhere off the root.  What would be considered good practice?

    Fred
    Thursday, September 10, 2009 10:18 PM

Answers

  • hi Fred,
    the good practice is to use the "standard"... if you leave that "way" you are on your own regarding permissions at NTFS level, so that you have to check the account runnning the SQL Server instance has been granted them to access the alternate path of your choice.. and you probably have to check the interactive account has enought permissions on it as well...
    personally wedo prefer an alternate path as well.. at install time we do specify in the docs that the chosen path must be evaluated and granted permissions as required, and we do mandatory require setup to be executed as local administrator and not traditional limited interactive user, so that the one installing can therefore manage those permissions before leaving place to the actual users.. this is not a traditional pattern, but it's the way we chosen to work with..
    again, this is not the standard way to deploy databases..
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    • Marked as answer by fhutchings Saturday, September 12, 2009 9:11 PM
    Thursday, September 10, 2009 10:27 PM
    Moderator
  • hi Fred,

    >I guess it would give Everyone access to read My Documents folder, though.  Is that a good idea?
    no, it's not :)

    the "best advice" is to stay with the standard or... as regard "my self", we usually advice for a "root" folder in a disk different than the OS one.. if the disk is a "real one" and not just a partition it's even better :)
    so, something like "f:\YourCompanyName\Data\"
    and the "installing person" is informed the account running SQL Server service must be "configured" with appropriate permissions at NTFS level, but very often the end user just uses the LocalSystem builtin account so that such a requirement just vanishes... we do recommend against LocalSystem but our "post installation checks" on site often find such a setting..
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    • Marked as answer by fhutchings Saturday, September 12, 2009 9:11 PM
    Saturday, September 12, 2009 12:20 AM
    Moderator

All replies

  • hi Fred,
    the good practice is to use the "standard"... if you leave that "way" you are on your own regarding permissions at NTFS level, so that you have to check the account runnning the SQL Server instance has been granted them to access the alternate path of your choice.. and you probably have to check the interactive account has enought permissions on it as well...
    personally wedo prefer an alternate path as well.. at install time we do specify in the docs that the chosen path must be evaluated and granted permissions as required, and we do mandatory require setup to be executed as local administrator and not traditional limited interactive user, so that the one installing can therefore manage those permissions before leaving place to the actual users.. this is not a traditional pattern, but it's the way we chosen to work with..
    again, this is not the standard way to deploy databases..
    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    • Marked as answer by fhutchings Saturday, September 12, 2009 9:11 PM
    Thursday, September 10, 2009 10:27 PM
    Moderator
  • Hi Andrea,

    Thanks for the info.  The new? web install gives no option to change the default.

    I have noticed that All Users\Documents will allow me to browse there to attach databases or to make it the default.  My personal Documents folder will not.  I checked the security on All Users\Documents and it has extra entries, particularly Everyone with Execute, List, and Read permissions.  If I add that to my user name, it appears to work.  I guess it would give Everyone access to read My Documents folder, though.  Is that a good idea?

    So, in any case, would you recommend a folder off the root, SharedDocs, my personal Documents folder, or something else?

    Thanks.

    Fred
    Friday, September 11, 2009 4:51 AM
  • hi Fred,

    >I guess it would give Everyone access to read My Documents folder, though.  Is that a good idea?
    no, it's not :)

    the "best advice" is to stay with the standard or... as regard "my self", we usually advice for a "root" folder in a disk different than the OS one.. if the disk is a "real one" and not just a partition it's even better :)
    so, something like "f:\YourCompanyName\Data\"
    and the "installing person" is informed the account running SQL Server service must be "configured" with appropriate permissions at NTFS level, but very often the end user just uses the LocalSystem builtin account so that such a requirement just vanishes... we do recommend against LocalSystem but our "post installation checks" on site often find such a setting..
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    • Marked as answer by fhutchings Saturday, September 12, 2009 9:11 PM
    Saturday, September 12, 2009 12:20 AM
    Moderator
  • Hi Andrea,

    Thanks so much for the good information.  I appreciate it very much.

    Fred
    Saturday, September 12, 2009 9:10 PM