Azure SQL Analytics – How to Escape a Hyphen in an XML Tag

All connections to Azure SQL Azure are encrypted even when encryption is not specified. When a client first attempts a connection to SQL Azure, it sends an initial connection request. Consider this a "pre-pre-connection" request. At this point the client does not know if SSL/Encryption is required and waits an answer from SQL Azure to determine if SSL is indeed required throughout the session (not just the login sequence, the entire connection session). A bit is set on the response indicating so. Then the client library disconnects and reconnects armed with this information.

For example, when you set "Encrypt connection" setting on SQL Server Management Studio you avoid the "pre-pre-connection", you are preventing any proxy from turning off the encryption bit on the client side of the proxy, this way attacks like man-in-the-middle attack are avoided.

As you were told by Support, Azure SQL Database's geo replication uses a special flavor of Always On Availability Groups to replicate data. The "special flavor" doesn't matter much in the context of your question, so I am going to respond in terms of "Replication vs Availability Groups" since it applies to your case, too.

Replication

SQL Server Transaction Replication is a way to replicate specific data between databases. By default, it will replication data changes between the Published and the Subscriber. The Distributor has a Log Reader Agent that reads the transaction Log of the Publisher, and converts that into separate update statements, which are sent to the Subscriber via a Distribution Agent.

A single update statement that updates 1,000 rows on the Publisher will generate 1,000 separate update statements on the Subscriber, doing row-by-row updates based on the PK.

There are alternatives, but in most cases, this is the configuration people use, as it is the default behavior. (For example, you can configure stored procedure executions to replicate, so that rather than replicating row-by-row data updates, the Subscriber executes the stored procedure again... You probably aren't doing that.)

As you can imagine, this row-by-row update on the subscriber can be inefficient. Each individual update on the Subscriber will be quick because it is a single-row update based on a Primary Key--but the row-by-row vs set-based operation isn't always ideal.

Availability Groups

Availability Groups (AGs) handle things differently. With AGs, the entire database is replicated, not just specific data. The replication method involves writing to the transaction log on the secondary server, and then redoing the transaction on the secondary server. These two steps are generally referred to as "send" and "redo" phases.

A simplified version of how it works: The overhead on the Primary server for "send" is essentially just that it has to write to the transaction log locally, and also send that same exact information to the Secondary for it's copy of the transaction log. The overhead on the Secondary server for "redo" is essentially just processing that transaction the same way it was handled on Primary.