Home Forums Cloud Computing SQL Azure - Development Suggestions for managing multi-tenant SQL Azure database (esp.restore) RE: Suggestions for managing multi-tenant SQL Azure database (esp.restore)

  • Personally, I think I'd look to split the customers by database rather than try to put them all in one. You've hit the real issue that's going to be brutally difficult for you, restoring data. You can't use backup and restore as they're designed. There isn't a "restore a row" method. Instead, you would have to do a point in time restore to a second database (actually the easiest part of this because that's how Azure RESTORE works anyway), then migrate data that you need, rows at a time. You'll have to build a set of scripts to move data, probably through some kind of merge operation. It's going to be hard and a lot of work.

    In terms of tools that will help if you really want to go down this road, Redgate (my employer) does sell a tool that can help with this. It's called SQL Data Compare. You can use it to identify rows that are different or missing between two databases (still have to use that RESTORE step) and then recover data through it. It'll help. However, it's going to require you to eyeball the process. Automation could lead to data loss unless you can filter very accurately the data movement.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning