SQL Server replication to a cloud server

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?
LVL 1
scarpenter104Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want to let users make changes in both databases you'll need MERGE replication so it can replicate on both ways (any change made in one database is replicated to the other one).
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why not move all to the cloud and end with the physical site?
You should also contact the cloud providers to see which kind of service they're offering and questioning them about the replication.
0
 
scarpenter104Author Commented:
You'll simply have to accept that this is not an option.
I'm hoping that there is an SQL Expert here with replication experience that can answer the questions.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think this is a replication issue. Depends if the provider allows you to do that or not and the only way you can know that is ask them.
0
 
scarpenter104Author Commented:
I'm asking for an explanation of how SQL Server handles record locking in a replicated environment. How is that not a replication issue?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your first question is if it's possible to replicate to a cloud. Without knowing if your cloud provider provides this service the lock question is useless.
But in any replication, records are only replicated after commit, so the locking isn't an issue for the replication.
0
 
scarpenter104Author Commented:
The question, "is it possible?", refers to the entire situation described in the first paragraph. I know I can do replication, but I want to be able to have clients accessing data from both servers. Based on what you are saying, it sounds like if someone opened a sales order on one database, a client on the second database could open the same order and make simultaneous conflicting entries which would overwrite after the commit.
0
 
scarpenter104Author Commented:
As long as a lock to a record on one server will also lock on the other it sounds like it should work OK.
So if the connection between the servers was interrupted it would just roll the missed tranactions once the connection was re-established, correct?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. Replication uses SQL Server Agent jobs to replicate data. Those jobs will run in a defined schedule so will catch all non replicated transactions and will replicate them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.