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?
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?
ASKER
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.
I'm hoping that there is an SQL Expert here with replication experience that can answer the questions.
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.
ASKER
I'm asking for an explanation of how SQL Server handles record locking in a replicated environment. How is that not a replication issue?
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.
But in any replication, records are only replicated after commit, so the locking isn't an issue for the replication.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
So if the connection between the servers was interrupted it would just roll the missed tranactions once the connection was re-established, correct?
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.
You should also contact the cloud providers to see which kind of service they're offering and questioning them about the replication.