I have a client who operates off of a mission-critical SQL database (MS SQL Server 2008 R2).
For several reasons (disaster planning, remote access, etc.) I would like to create a virtual SQL server on an existing cloud service that would replicate this database. My goal is to have a Remote Desktop server on the cloud side that would run the same application they run at the physical site but connect to the cloud SQL Server. Changes made to either server would need to be immediately synced.
My question is, is this possible? If so, how does it handle record-locking during edits. If the application at the physical site locks a record for editing does the replicated server know to do the same?
What would be required to make this happen?