0

Regarding costs and maintenance speed/simplicity in case of hardwar failures, does it make any sense to keep SQL data files on a NAS server, separate from the SQL Server instance? Our app stores large amounts of measurements (time-series) from many devices, so the amount of data is relatively big to keep on relatively small SAS server disks (~200GB monthly).

Although there is a greater risk of accessing files over ethernet (even if only the db server and NAS are on the same switch), having data files completely separated seems like it would simplify things in case of hardware issues due to lower coupling - the database server can be much simpler (I can migrate the simple image quickly, it can even be bundled with the application server for all simpler apps), and fixing NAS failures should also mostly involve switching disks, or switching to a copy in case of failures.

Are there some better (cost effective and faster to apply) ways to manage quick migration in case of failures which would not involve separating the data files, or is this idea not so problematic?

Groo
  • 1,287
  • 2
  • 16
  • 25

1 Answers1

1

I depends on what software you're dealing with but...

Usually (historically speaking) you would not want to put database storage on a NAS, because that would mean using a network filesystem like NFS or CIFS, etc... which could cause all sorts of data integrity and performance problems. There is still iSCSI though which is not a filesystem., but it still means (like you said) running things over a network rather than a SAS connection. That could mean a big difference in performance and reliability.

For MySQL with NFS

http://dev.mysql.com/doc/refman/5.7/en/innodb-init-startup-configuration.html

If reliability is a consideration for your data, do not configure InnoDB to use data files or log files on NFS volumes. Potential problems vary according to OS and version of NFS, and include such issues as lack of protection from conflicting writes, and limitations on maximum file sizes.

That's just one example... Google can provide more.

I suggest you research your particular database software in detail to determine if storing it's data on a NAS is a recommended and decent configuration. If you have a NAS vendor, they may have some input as well (off hand I know NetApp does).

As for your last question, I don't understand. Standard database replication is not enough? Can you reword the question?

Ryan Babchishin
  • 6,160
  • 2
  • 16
  • 36