Jump to content
  • 0

SQL database on Pooled Drives


4Frame

Question

Hey guys, was wondering if this is "safe" to do? I want the reliability of duplication (just in case a drive dies) on two or more drives for hosting an owncloud database, but I'd like to make sure drivepool wouldn't do anything quirky with having something like an sql database being written to the pool 24/7. I do notice that Drivepool in itself does take a bit of CPU overhead when doing some tasks. Is this something Drivepool could handle/worth it in the long run?

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

Yeah, there shouldn't be a problem doing os. 

But you'll want to make absolutely certain that "Real-time duplication" is enabled. (default).

 

However, it is likely that the SQL server will keep the pool activate at all times.

 

Also, keep in mind that running the SQL database on the server may make it more difficult to back up the database (if that is a concern).

Link to comment
Share on other sites

  • 0

About the backup. I would think that one would never want an OS to backup a database, that is something the DBMS should do itself. However, I run SQL Server on a client and the databases are in fact backed-up by WHS2011. So much so that SQL Server is actually aware of the backups WHS2011 takes. No clue what MS did there but I like it. Just to test I restored one and indeed could attach it and it was the full DB. Of course, I have no clue how that works AIW actual transactions occur on the DB during backup.

 

My point is, maybe Server Backup, which AFAIK uses VSS as well, can do the same as well.

Link to comment
Share on other sites

  • 0

Thanks Christopher, and that brings up a good question for me.

How does one go about backing up an SQL databse (automatically)? I know it might be different for all databases, but this one is on owncloud, which is kind of an all-in-one solution. I've found that copying the entire program installation directory (when the database isnt running) copies everything and when run on another drive the database is up and running flawlessly.

Link to comment
Share on other sites

  • 0

About the backup. I would think that one would never want an OS to backup a database, that is something the DBMS should do itself. However, I run SQL Server on a client and the databases are in fact backed-up by WHS2011. So much so that SQL Server is actually aware of the backups WHS2011 takes. No clue what MS did there but I like it. Just to test I restored one and indeed could attach it and it was the full DB. Of course, I have no clue how that works AIW actual transactions occur on the DB during backup.

 

My point is, maybe Server Backup, which AFAIK uses VSS as well, can do the same as well.

Actually, Server Backup is probably using a specific VSS/Backup Writer for the SQL Server. Since I'm sure this is MS SQL Server that you're talking about, then that would definitely be the case. And in fact, Microsoft has made sure that Server Backup is "aware" of SQL Server, and is part of the "application" section. So you may be able to restore just the database, if needed.

And it definitely takes a VSS snapshot, to prevent inconsistency in the data. 

 

 

Thanks Christopher, and that brings up a good question for me.

 

How does one go about backing up an SQL databse (automatically)? I know it might be different for all databases, but this one is on owncloud, which is kind of an all-in-one solution. I've found that copying the entire program installation directory (when the database isnt running) copies everything and when run on another drive the database is up and running flawlessly.

It really depends on the database. 

If you're using SQLite (which is most likely the case with OwnCloud), then you should be able to just backup the files. Running a robocopy (or even just "copy") script should be enough to do so.

If you're using MySQL, you can run a script to dump the database into a SQL file (or even use phpMyAdmin to do this manually). 

As for SQL Server, well that's a lot trickier unfortunately. But I do believe you can create a database dump as well.

Link to comment
Share on other sites

  • 0

Great info. In my case I'm using MySQL in phpmyadmin but I'd like to use Windows Server Backup to perform scheduled backups of the entire application directory which obviously includes the SQL database in there. That shouldn't be a problem I'm guessing?

Link to comment
Share on other sites

  • 0

Great info. In my case I'm using MySQL in phpmyadmin but I'd like to use Windows Server Backup to perform scheduled backups of the entire application directory which obviously includes the SQL database in there. That shouldn't be a problem I'm guessing?

Well, unfortunately, we don't support using Windows Server Backup to backup up the pool directly (this requires VSS/shadowcopy support, and there is literally no documentation on how to implement this on a file system). 

If the MySQL database is stored on the system drive, then there should be absolutely no issues with doing this.

In fact, by default, the database is stored in "C:\ProgramData\MySQL\....". So if you are using the default location for MySQL and backing up the system, there will be no issues whatsoever.

 

Additionally, if you want to be able to backup by command line (so you could, say script it for the Task Scheduler, here is some decent info:

http://blog.winhost.com/using-mysqldump-to-backup-and-restore-your-mysql-databasetables/

 

 

Drashna, that is good to know! I can confirm I can restore just the database (or a complete machine).

Glad to hear it.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...