CNI's are made in a way that they abstract network logic from apps. This allows us to use CNI's without worrying too much about the details.

If you are getting started with CNI, you could use pretty much any CNI you like, as your apps will use the network as usual.

It does not matter which app / language / framework / database you use, the CNI only takes care of networking which your app will never know anything about.

CNI is mainly there to allow network policies and manage node networking.

If you want to see a great post about CNI outside Kubernetes (This will give you a idea of what CNI actually is and does). *Not written by me, but a great post to understand CNI.

So whichever you choose, will be purely merited by the CNI and not how it works with your app.

For a start, Calico is a good choice as it's simple to deploy and use, and allows Networking policies.

It depends...

TCP matters

There is great article on performance of different CNI: Benchmark results of Kubernetes network plugins (CNI)

According to the charts, almost all CNI are best adapted to TCP.

See: TCP performance among CNI

Since you are pointed in comments that you are use MS SQL, so TCP is the main protocol for your case. Default MS SQL setup uses TCP/IP protocol

So, among all CNI, according to their performance, you can choose almost any (except encrypted Clilium and encrypted WaveNet)

MTU matters too

But, if you compare chart of performance with CNIs auto-detect MTU option, you can see that only Cilium and Flannel remain champions (except for bare metal):

Does security matters?

It depends.

Assuming that MS SQL provides the proper level of security out of the box, you can skip that part of recommendations.

If you decide that additional security level does matter for your case, you can refer this matrix:

Conclusion

So, for SQL, if you choose from performance, then with a slight advantage you can choose Flannel or Cilium. If security matters, then Cilium is better.

P.S. The answer below mentioned Calico. As you can see on the TCP performance chart, Calico is the leader. But if you can setup correct MTU in your cloud.