Transit Gateway to AWS Instance Encryption

By default all traffic through the AWS transit gateway is encrypted if the peering is between regions. Peering inter-region is not encrypted.

source documentation: https://docs.aws.amazon.com/vpc/latest/tgw/tgw-peering.html

Today, traffic between Transit Gateway and instances is not encrypted at the network layer.

In a more general case (and now I'm talking about every network that your packets may pass across): If you are relying on network-level encryption there are always going to be places where your traffic will be unencrypted. For example, even if using MACSec the switches and routers on your network will have access to the unencrypted packets and are therefore a potential place of intercept. There are likely others as well.

Therefore, if you need to ensure that your traffic is encrypted end-to-end then you need application-layer encryption (normally TLS but there will always be other protocols and ways of doing this).

For this particular question: I would strongly encourage you to encrypt the traffic to your SQL server at the application layer.

I think this answer should be updated with the latest from AWS documentation. Verbatim:

"Inter-Region gateway peering uses the same network infrastructure as VPC peering. Therefore traffic is encrypted using AES-256 encryption at the virtual network layer as it travels between Regions. Traffic is also encrypted using AES-256 encryption at the physical layer when it traverses network links that are outside of the physical control of AWS. As a result, traffic is double encrypted on network links outside the physical control of AWS. Within the same Region, traffic is encrypted at the physical layer only when it traverses network links that are outside of the physical control of AWS."

https://docs.aws.amazon.com/vpc/latest/tgw/tgw-peering.html