Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Why do you need to give each developer an exact copy of production? –  Oded Jan 15 '13 at 20:35
    
I added an update above. The local copy helps in debugging issues that occur. –  brentkeller Jan 15 '13 at 20:45
    
Developing/QAing/Staging against prod is a really good development practice, I commend you for taking this step. 1 upping your question. –  Ali Razeghi Jan 16 '13 at 1:12
    
isn't there sensitive information in the production database? –  Neil McGuigan Jan 17 '13 at 18:53
    
@NeilMcGuigan while we really don't have any sensitive data that is a good point and I think we'll use SFTP or something if we go the log shipping route. Thanks for bringing that up. –  Brent Keller Jan 17 '13 at 21:15
add comment

migrated from stackoverflow.com Jan 15 '13 at 21:03

This question came from our site for professional and enthusiast programmers.

2 Answers

I manage a DB environment for several tier 1 telcos and our data/environments are distributed across the world, there are many ways you can achieve what you're looking for but it depends on your pain points.

Questions:

-How big are the databases, how far are the nodes (or more importantly what is the latency between them), and how much bandwidth do you have?

-How much transaction log activity do you generate?

-How often do you want to restore?

-Are you enabling data compression on the backups?

Some Lessons Learned

-One thing I noticed with copying data across the world is that the Windows CIFS protocol absolutely sucks for doing this. It is highly inefficient.

We ended up using HTTPS and issuing a GET request. It was over 10x faster copying backups from Asia to Los Angeles.

Using Multithreading options in Robocopy or other copy programs was useful if you break up the backup into multiple files.

Some Options:

2 Surprising Linux Solutions

  • A solution I really like is to RSYNC with Linux servers on each end and mount the backup folder. This is really nice as backups are automatically synced using a protocol that is efficient for WANs. If you don't want to sync ALL of your backups, mount another drive on your linux server that really mounts to a windows share that you will place backups in. The RSYNC will take care of it from there and do it much faster than CIFS. Linux protocols are much better for long distance file copying than Windows CIFS based on my experience. I spoke with some of CCIEs and they stated the same.

  • You could also look at a VM WAN Optimizer (Google or 'Bing' WAN optimizer or get more specific, for example high latency high bandwidth WAN optimizer or low latency high bandwidth, whatever your environment is)

SQL Transaction Log Shipping Using FTP is not scary at all. I had a junior .Net developer get it running fast, although I was impressed and got him hired at another company I worked at. Problem is you might send a lot more data in a 24 hour period if there are a lot of UPDATES but not INSERTS using log shipping, instead of just copying backups.

SAN File System Block Level Replication: Not sure if you have hardware that supports this, but many mid range SANs allow you to replicate live data to the other ones. If yours does, this is a great option as it has 0 sql server overhead, unless your network pipe is capped in which case you have other issues.

Transaction replication to a central database: You could create a replication scheme where you have an up to date copy of the data. You could then take your backups from that copy so it doesn't have to copy full backups across the wire.

Let us know some of your pain points and we can go through other options.

Also be sure to read this excellent white papers by MS if you go with replication:

Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008

share|improve this answer
    
Thanks for laying out all of these options. We don't have a SAN so that replication option isn't on the table. Right now I think I'm looking at putting making the log shipping available and writing some scripts to have the dev pull any logs they don't have via FTP and restoring them. This is automated and could be push-button or scheduled. You make a good point about transaction logs possibly being more data than the full backups, so maybe the automation with the full backups is really the way to go. Any other suggestions are welcome though. –  Brent Keller Jan 16 '13 at 15:20
    
Brent, give us some more info when you get a chance regarding the questions section of the response. We'll be able to give you more help potentially. Also, SQL Transactional Replication doesn't require a SAN. Good luck. – Ali Razeghi 1 hour ago –  Ali Razeghi Jan 16 '13 at 18:57
    
Ali, I've added more info at the bottom of the question that will hopefully provide some more insight to our situation. –  Brent Keller Jan 16 '13 at 19:07
    
Thanks Brent, I haven't used Filestream in replication yet, heck I haven't used it in prod yet, however, the size didn't seem that huge, I would recommend you try the different protocols listed unless you know for sure the pipe is highly saturated or just too dang slow period. At least this way you'll know for certain when you'll have scalability issues. –  Ali Razeghi Jan 18 '13 at 1:44
add comment

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

share|improve this answer
    
We already maintain our schema in an automated manner. Having a debugging copy of the prod database is an interesting idea that might serve us well enough. We'd probably still pull down backups periodically but this might be a good approach for us if the log shipping turns out to be a hassle. –  Brent Keller Jan 16 '13 at 15:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.