Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
SQL Server Express and WHS Shares RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello all,

    I am having trouble creating databases on the WHS shares. I use SQL Server Management Express, and when I try to change the location of the files to the share, I get an 'access denied' error. Also, this may be related, but I cannot browse directories below the main shares I create in the console, with the management studio. Anyone have any ideas what is going on, or am I doing something wrong?

    Thanks for any help you can provide.
    Saturday, August 15, 2009 12:48 PM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote
    JD. 

    I run SQL Server Express on my server to support MyMovies.  I took the advice espoused here and created the database itself on a drive not added to the pool and NOT ON C:.  In fact I had such drives anyway because that is where my movies are stored, on drives not added to the pool. 

    BTW, SQL Server takes ALL AVAILABLE MEMORY for itself as it's default behavior.  You really don't want that!  In order to tweak SQL Server, you need to install SQL Server Management Studio Express.  Once you do so you will full control over your installation.  In general you can leave the default databases that SQL Server installs on the C: drive.  WHS does us no favors making the default drive so tiny but that is another subject entirely.  Just understand that if you were to use the SS installation heavily you could run out of disk for the log files and such.

    1) Using Management Studio, disconnect your database (not the system databases), move them to a drive not in the pool, and reconnect them. 
    2) Now right click on the server instance (in management studio) and bring up properties.  In Database Settings page change the Database Default Location to a subdir out on your extra drive.
    3) In the Memory page, Set the minimum and maximum server memory to a fixed portion of your physical memory.  SQL Server is supposed to release memory if requested by other programs, and it in fact does so, but it can be sloooooooowwwwwww doing so which will cause other processes to be slooooowwwww when they need more memory.

    The MyMovies database runs just fine on my WHS, but doing what I recommended above made it much faster.

    Understand though that the SQL Server master database installs on C: unless you specified otherwise when you ran the install.  As a result if you ever have to reinstall WHS... SQL Server will be gone until you reinstall it (of course) but more importantly, any tweaks to it using the master database will be irretrievably lost because a new install will overwrite the old database files.
    jwc
    • Marked as answer by JDSteffen Sunday, August 16, 2009 12:43 AM
    Saturday, August 15, 2009 5:13 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    SQL Server services usually do not run in user context, but under a specific service account.
    Since this account has no permissions for the shared folders, this will give you Access denied.

    Besides that the solution kariya proposed is the best - have a separate disk for this.
    You could setup a job, which performs a nightly backup of the SQL databases to a WHS shared folder. This would allow you to keep the data under control and save it to external drive using the integrated server backup functionality of WHS and emulate kind of duplication.

    Best greetings from Germany
    Olaf
    • Marked as answer by JDSteffen Saturday, August 15, 2009 2:41 PM
    • Unmarked as answer by JDSteffen Saturday, August 15, 2009 2:41 PM
    • Marked as answer by JDSteffen Sunday, August 16, 2009 12:44 AM
    Saturday, August 15, 2009 2:33 PM
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote
    Yes, I understand that installing SQL Server Express on WHS is unsupported.

    I am just curious as to what is causing this error. My initial impression is that the W2K server under WHS sets permissions that does not allow direct acces to the WHS shares, to keep DE from getting confused. Is this incorrect?

    That's not completely correct.  WHS does use standard NTFS permissions (and you can access the shares from the server destkop using the Shared Folders on Server icon on the server desktop).  It just creates groups and the groups are managed from the WHS Console.

    I will ask this question in the SQL forum as well, but can programs running on the server access the D: drive shares as if I were accessing them via the share folder on a network connected machine? (i.e does DE deal with writes to D: from the server the same as writes to //server/someshare?)

    Sometimes yes, sometimes no.  But either way, accessing D:\shares on WHS for any reason is unsupported and not recommended.  All interaction with the shares on WHS is designed to go through the UNC path only.

    I find it interesting that SQL Server Management Studio Express cannot browse the D: drive subdirectires that are located in the shared folder. Maybe I am barking up the wrong tree. Anyone have any experience with a similiar situation?

    Thanks

    • Marked as answer by JDSteffen Sunday, August 16, 2009 12:43 AM
    Saturday, August 15, 2009 3:14 PM
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote
    Since the SQL database is always hold open, Drive Extender would not be able to duplicate the files and sooner or later WHS would greet you with file conflict messages. So no, putting SQL databases on a duplicated share is not a good idea.
    Best greetings from Germany
    Olaf
    • Marked as answer by JDSteffen Sunday, August 16, 2009 9:39 PM
    Sunday, August 16, 2009 8:05 PM
    Moderator

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    Hello all,

    I am having trouble creating databases on the WHS shares. I use SQL Server Management Express, and when I try to change the location of the files to the share, I get an 'access denied' error. Also, this may be related, but I cannot browse directories below the main shares I create in the console, with the management studio. Anyone have any ideas what is going on, or am I doing something wrong?

    Thanks for any help you can provide.

    First, installing SQL Server Express (or any app) on WHS is unsupported.  Having said that, if you really want to install it anyway, you should connect a hard drive to the server, but don't add it to the storage pool, then keep your SQL DBs there.

    As for why it doesn't work, my guess is that's related to Management Studio, not WHS.  Can you open the shares using the Shared Folders on Server icon on the desktop (from the same computer that you are trying to use Management Studio on)?  If so, then it's definitely a problem with Management Studio so you should ask your question in the SQL Server forums.
    Saturday, August 15, 2009 2:25 PM
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote
    SQL Server services usually do not run in user context, but under a specific service account.
    Since this account has no permissions for the shared folders, this will give you Access denied.

    Besides that the solution kariya proposed is the best - have a separate disk for this.
    You could setup a job, which performs a nightly backup of the SQL databases to a WHS shared folder. This would allow you to keep the data under control and save it to external drive using the integrated server backup functionality of WHS and emulate kind of duplication.

    Best greetings from Germany
    Olaf
    • Marked as answer by JDSteffen Saturday, August 15, 2009 2:41 PM
    • Unmarked as answer by JDSteffen Saturday, August 15, 2009 2:41 PM
    • Marked as answer by JDSteffen Sunday, August 16, 2009 12:44 AM
    Saturday, August 15, 2009 2:33 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes, I understand that installing SQL Server Express on WHS is unsupported.

    I am just curious as to what is causing this error. My initial impression is that the W2K server under WHS sets permissions that does not allow direct acces to the WHS shares, to keep DE from getting confused. Is this incorrect? I will ask this question in the SQL forum as well, but can programs running on the server access the D: drive shares as if I were accessing them via the share folder on a network connected machine? (i.e does DE deal with writes to D: from the server the same as writes to //server/someshare?) I find it interesting that SQL Server Management Studio Express cannot browse the D: drive subdirectires that are located in the shared folder. Maybe I am barking up the wrong tree. Anyone have any experience with a similiar situation?

    Thanks

    Saturday, August 15, 2009 2:39 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Yes, I understand that installing SQL Server Express on WHS is unsupported.

    I am just curious as to what is causing this error. My initial impression is that the W2K server under WHS sets permissions that does not allow direct acces to the WHS shares, to keep DE from getting confused. Is this incorrect?

    That's not completely correct.  WHS does use standard NTFS permissions (and you can access the shares from the server destkop using the Shared Folders on Server icon on the server desktop).  It just creates groups and the groups are managed from the WHS Console.

    I will ask this question in the SQL forum as well, but can programs running on the server access the D: drive shares as if I were accessing them via the share folder on a network connected machine? (i.e does DE deal with writes to D: from the server the same as writes to //server/someshare?)

    Sometimes yes, sometimes no.  But either way, accessing D:\shares on WHS for any reason is unsupported and not recommended.  All interaction with the shares on WHS is designed to go through the UNC path only.

    I find it interesting that SQL Server Management Studio Express cannot browse the D: drive subdirectires that are located in the shared folder. Maybe I am barking up the wrong tree. Anyone have any experience with a similiar situation?

    Thanks

    • Marked as answer by JDSteffen Sunday, August 16, 2009 12:43 AM
    Saturday, August 15, 2009 3:14 PM
    Moderator
  • Question
    Sign in to vote
    2
    Sign in to vote
    JD. 

    I run SQL Server Express on my server to support MyMovies.  I took the advice espoused here and created the database itself on a drive not added to the pool and NOT ON C:.  In fact I had such drives anyway because that is where my movies are stored, on drives not added to the pool. 

    BTW, SQL Server takes ALL AVAILABLE MEMORY for itself as it's default behavior.  You really don't want that!  In order to tweak SQL Server, you need to install SQL Server Management Studio Express.  Once you do so you will full control over your installation.  In general you can leave the default databases that SQL Server installs on the C: drive.  WHS does us no favors making the default drive so tiny but that is another subject entirely.  Just understand that if you were to use the SS installation heavily you could run out of disk for the log files and such.

    1) Using Management Studio, disconnect your database (not the system databases), move them to a drive not in the pool, and reconnect them. 
    2) Now right click on the server instance (in management studio) and bring up properties.  In Database Settings page change the Database Default Location to a subdir out on your extra drive.
    3) In the Memory page, Set the minimum and maximum server memory to a fixed portion of your physical memory.  SQL Server is supposed to release memory if requested by other programs, and it in fact does so, but it can be sloooooooowwwwwww doing so which will cause other processes to be slooooowwwww when they need more memory.

    The MyMovies database runs just fine on my WHS, but doing what I recommended above made it much faster.

    Understand though that the SQL Server master database installs on C: unless you specified otherwise when you ran the install.  As a result if you ever have to reinstall WHS... SQL Server will be gone until you reinstall it (of course) but more importantly, any tweaks to it using the master database will be irretrievably lost because a new install will overwrite the old database files.
    jwc
    • Marked as answer by JDSteffen Sunday, August 16, 2009 12:43 AM
    Saturday, August 15, 2009 5:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I guess what I was trying to accomplish was to have SQL Server use .MDF files stored in a duplicated folder on a WHS share. Is this possible at all? Is anyone doing it? I really want my database to be stored in a duplicated share, so that in event of a disk failure, I still have my DB. I heard some people suggest that I just copy the database at regular intervals to a duplicated folder, but I am trying to avoid that if at all possible.

    Any thoughts are highly appreciated.

    Thanks,
    JD

    Sunday, August 16, 2009 1:35 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Since the SQL database is always hold open, Drive Extender would not be able to duplicate the files and sooner or later WHS would greet you with file conflict messages. So no, putting SQL databases on a duplicated share is not a good idea.
    Best greetings from Germany
    Olaf
    • Marked as answer by JDSteffen Sunday, August 16, 2009 9:39 PM
    Sunday, August 16, 2009 8:05 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ok, I agree that having the SQL databases on the WHS shares is a bad idea, but I have multiple databases on my my laptop which are backed up nightly with my WHS Connector. Can I assume that these databases will restore correctly if I have a need to restore from backup? Or do i need to run SQL Backups and then backup these files via WHS?


    Monday, August 17, 2009 5:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I would really recommend using the SQL Backup tools to dump your databases to disk, and back up those files, rahter than relying on Windows Home Server to get a good backup.
    I'm not on the WHS team, I just post a lot. :)
    Monday, August 17, 2009 5:57 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    WHS uses volume shadow copies on the client for backup, so it should work. But SQL servers are sometimes sensitive in regards what is backed up how, so its better to have a second choice.
    Best greetings from Germany
    Olaf
    Monday, August 17, 2009 8:50 PM
    Moderator