My company moved to azure and started to use azure Sql database and geo replication.
For our on-premise servers, we heavily rely on Microsoft transactional replication to move data. Now we setup transactional replication between on-premise server (publisher) and azure sql db. Then we found that the transactional replication itself is constantly consuming 100% DTU on azure(we are using a 125 DTU P1).
We upgraded it to 250 DTU and replication is much happier. However, we are not sure it is a great idea to constantly updating to higher DTU. So we did this:
- Created two 125DTU servers.
- Setup transactional replication between on-premise server and server A.
- Setup geo replication between server A and server B.
- Our customers will use server B.
The benefit is that heo replication will not trigger the 100% DTU throttle, and when we raised this to a Microsoft support, he also said it is a good idea. The support person also said that sql geo replication is much more efficient to transactional replication.
However, when we asked him what kind of technology azure geo-replication internally uses and why it is better than old transactional replication, he just gave out some marketing reply:
geo-replication leverages the Always On technology of SQL Server to asynchronously replicate committed transactions on the primary database to a secondary database using snapshot isolation. Impact to your main database is minimal as this process runs with the same context where the automated backups run, for example.
Personally, I didn't really get why geo replication is better than old replication from the reply. Does anyone know some technical detail?