I'm dealing with some new topics. The more I read about the more questions I have, not finding answers.
Maybe some of the questions can be answered here. Would be great :-)
we are starting a new project using SQL 2012 and AlwaysOn High Availability Groups.
On on the requests: we have to store files.
What for? A customer, using a frontend, can execute different queries an save the results to different files. Then the customer can show a list of his files and download them at any time.
Some of the files to store can grow to a size of 200-500 MB.
Our database wil be placed on a high availability group, using synchronous mirroring.
1) Will the files on the file system copied to the secondary replicas too?
2) If adding a 500 MB file to a file stream table, will it automatically be transferred to the storage of the seondary replica(s)?
3) If yes, will this be done as a single transaction? This would take some time blocking all other transactions transferred to the secondary replicas?
4) What difference would it be to use the FileTable-Feature with non_transacted_access instead of FileStream only?
5) Are there other senseful possibilities for saving files of this size having context to the database?
Thank you for any help :-)