0

I would like to provide my users with a session or workspace using Azure SQL DB where they can take a snap shot of the database and cook up their changes, analyze the result and then submit the final changes, so that all the other users can see it.

  1. Do you think if the Temporary Tables in SQL Server is the answer?
  2. Do we have some middleware in the market which can be used on top of SQL server to create sessions, manage sessions and post the session data back to master DB version with proper Reconciliation of data between the version created by user and the current master version of DB?

I have seen a middleware ArcSDE from Esri which used to do it for complex Geodatabases but I am struggling to find similar Middleware for normal Azure-SQL RDBMS.

RalfFriedl
  • 1,134
  • 3
  • 11
  • 12
StackCraker_George
  • 1
  • 1
  • Can you comment on how much of the database you want to change at once? How long the user would want to work before committing? There is no first party support for exactly what you are asking as a turnkey solution. There are various mechanisms (temp tables, transactions, snapshots or restored copies of databases) which could be used to make such a mechanism. Usually the scope of transaction is the focus which is a shorter time period. – Conor Cunningham MSFT Jun 23 '19 at 22:14
  • Can you comment on how much of the database...? Ans. - This could be upto 300-400 MBs. How long the user would want ...? Ans.- In general analysis done by users in such versions would be posted within hours but sometime it could take 2-3 days. There is no first party support for...Ans. - Ok Thanks for response. – StackCraker_George Jun 24 '19 at 11:41
  • I think, Snapshot would not be applicable because I would like user to make changes in the version. The same goes for Transactions, this shall be time out by the time user performs analysis and it will probably require me to have a separate DB instance where such edits can be done. Temporary Tables is something I believe could be an answer. But, I was hoping to get a kind of component that could sit on top of my current SQL DB and act like version manager – StackCraker_George Jun 24 '19 at 12:26
  • Today Azure SQL DB does not have a checkout/modify/checkin_with_reconcile model for the data in the database like you are requesting. In the limit it could be built, but it does not exist as a first-class native feature now. if you are building such a concept in the cloud, you likely want real tables as you can get disconnects/failovers. temp tables are better for transactional operations. You don't want long-running transactions in the cloud, so things need split up. consider using different schema namespaces – Conor Cunningham MSFT Jun 24 '19 at 12:40
  • Sorry if I wasn't clear on the requirement, but ambition is not to have long running transaction. The idea is to create a database version --> make some changes --> Save changes --> then other user should be able to open this version --> run some validations or changes --> post the changes to the default version of DB. If this is not possible in Azure SQL then can you please suggest something else we are not bound with technology with in Azure and only restriction is that we would like to be in Azure cloud for such things. Thank you for response in advance. – StackCraker_George Jun 24 '19 at 13:35
  • it has nothing to do with azure vs. not. You're asking for an application-level concept from the database and it just doesn't have that feature. (neither does sql server, fwiw). Temp tables are tied to your connection lifetime in sql server/sql azure, so I suggest user tables as you can keep things in the db, not have to use long-running txns, and you can build this concept in an app if you want today. I'll keep this in mind for future features in the product (I work on SQL) – Conor Cunningham MSFT Jun 24 '19 at 14:54

0 Answers0