April 20, 2018 at 1:02 pm
Greetings.
My company has an application that uses a SQL Azure database, which will soon be managing multiple customers. We will need to be able to restore data from a given customer w/o affecting data from other customers, etc. From some initial investigation, it seems that including a "TenantID" column in every table that contains customer data, is one suggestion frequently recommended. I welcome any additional tips, suggestions, tools, or links, especially in relation to the scenario described, i.e., restoring one customer's data w/o affecting anything else in the database.
Thanks,
Randy
April 23, 2018 at 7:00 am
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
April 23, 2018 at 7:48 am
I've gotta agree with Grant... doing partial table restores for a given Client would make some serious sucking sounds. Having 1 database per client also has the advantages of extreme security and would more easily allow for client specific customization. It will mean that you'll mind you p's and q's in the apps and stored procedures, though. The concept of Partitioned Views could help a lot there.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply