We have a production database running on SQL Server 2008 R2 with files stored in it. We were concerned about the size of the database so we've opted to enable FILESTREAM so the files would be stored on the disk and we could hopefully reduce the size of our backups. I have found that we can exclude the FILESTREAM filegroup from the backups but then we have issues querying those tables upon restore (because the fields are missing data, which is expected). Our next thought was then to possibly sync the files from the disk using automated FTP or dropbox or something of that nature. I haven't completely explored this yet though.
The real problem we're trying to solve here is keeping our developers up to date with the production database. With the growing size of our database it's becoming more and more of a chore to do a full backup and download the database and it will eventually become prohibitive. I'm wondering what other folks are doing to keep remote devs up to date.
I've taken a quick look at SQL Server's log shipping but that just seems like it will require a lot of custom automation (scheduling FTP and restore scripts) to get it working reliably with a few devs in various geographic locations and I'm not sure how to use the secondary database for development since it would likely be read-only. I'm open to any suggestions and please let me know if I can provide more information about our situation that would help bring out suggestions.
UPDATE: The developers only need a reasonably current copy of the production data. This helps to reproduce issues so they can be resolved. We're okay with the data being a bit stale but keeping the transfer times to a minimum would suggest frequent backups.
Thanks for the suggestions so far, just to add a bit more our database is currently 12GB or so and we expect to double that in the near future with the signing of more clients. I've run a couple test backups with compression enabled and that saved us about 1-2GB but I think that since most of the bulk is the stored files (now in FILESTREAM) that we're not going to get the drastic reduction we would hope for (though we'll probably still compress to save what space we can). I think we'd like to be able to restore the local copies once or twice a week just to keep tabs on things. I'm not exactly sure what our bandwidth cap is from our hosted server but it seems that several people downloading 20GB files several times a month won't be a great use of whatever bandwidth we have. I don't have any metrics on the transaction log growth/usage but I know we have processes to that lots of deletes/inserts so I can imagine it would need to ship a decent amount.
Best Answer
I think giving full database copy is not a good idea as this would become pain as database grows. We have same setup but not with filestream option. We use powershell to generate db schema scripts and mail it to developers every night. Developer use that script to create DB shell on their own machine (We have separate dev db's with data which they can use to query and load their application specific data) . This db shell is used for development and Unit test db code using SSDT. For debugging live issues we have logshipped copy of live db on standby server which is mostly behind by 10-13 mins only. We hardly have any problem with this setup but all depends on individual scenarios. good luck