Migrating SQL data to Azure/Cloud

Question

I have a desktop app written on the premise that there will be many users, each with their own SQL database installation on their local machines.

If we want to migrate the entire project to the web, including user data, then, it would seem that the data tables would have to be modified to include a UserID column.

For example, if User A had records 1 and 2, and User B had records 3 and 4, we would not want User A accessing User B's records. Therefore, a column would be added to the tables to store UserID and all queries and updates would be modified to add the UserID.

This sounds like a real pain if this is necessary. I am unfamiliar with the Database schema in cloud services. Is a re-design of the database as described above really necessary, or is there some other way in which this problem has been dealt with to make a migration to the cloud more seamless?

Thanks.

Answer 1

you could take a look a federated databases, but it still means you would have to do some work: http://msdn.microsoft.com/en-us/library/windowsazure/hh597452.aspx hope this helps?

Answer 2

Does the data need to be in a SQL Server / Database store? If the data is not relational, then perhaps consider using Windows Azure table storage and having the (new) web app separate data by partition key (i.e. the userID).

If it is relational data, and there is a requirement to have separate databases, then I think your best option is to shard the databases manually, or use SQL Database federations like @techmike2kx suggested.