0

Background

I'm using Azure-Sql-Server. To host my database and I'm using Entity-Framework (C#) on top of it. I using Standart S3 tier. This pricing tier is limited to 200 concurrent connection to DB.

My system working in Micro-services architecture. Each of my services have open connections to the DB.

This is what my connection string looks like

metadata=res:///Model1.csdl|res:///Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=mydb.database.windows.net;Initial Catalog=production;Persist Security Info=True;User ID=user@my;Password=mypass;Pooling=true;Max Pool Size=2;Enlist=false;"

My Scenario

I also read on the SQL Server documentation that the Pooling is enabled by default and the MaxPoolSize is 100 (default).

Now, Am i woundering if it's smart at all to be using connection pooling? because my application contain 50 machines in the cluster. Even if I gave X4 connection per machine, I'll be reaching the MaxAllowedConnection by AzureSql (in my pricing tier, S3).

Scaling the DB is not an option since I'm already paying a lot for this DB. Around 300 USD. I checked it and found that in order to get 400 allowed connection, I'll have to pay 1200 USD for the database. This is not an option.

My application is non db aggresive. Just make few calculation (CPU) and insert some rows.

Questions:

  1. I really need to use Connection Pooling? How to know if that is redundant (after change)?
  2. How can it be reasonable that the connection pooling is enabled by default with this high number of MaxConnection (100)? theoretically, it's wont be work even when you pay 300USD with 3 machines.
Daniel Frykman
  • 62
  • 7
No1Lives4Ever
  • 6,430
  • 19
  • 77
  • 140
  • Have you considered creating a micro service that serves as a layer between your SQL and the other services? That way you could "proxy" the DB connections via another service which only has that purpose. Effectively giving you a way to handle the connection pool count. I'm just shooting ideas here. – AndreasHassing Jun 12 '18 at 13:34
  • 1
    Ultimately the only person who can answer this is you. You're going to need to benchmark the performance. We can guess but it really depends on your architecture, what your system does, etc. etc. – Liam Jun 12 '18 at 13:37
  • I think it's difficult to answer this question since it's theoretical. I agree with @Liam, and I think environment/actual usage are the biggest factors. Please try the approach you think is best and come back when you run into a specific issue. Otherwise, it seems like you are performing premature optimization. – Dan Wilson Jun 12 '18 at 13:39
  • As an aside you might find this interesting https://nickcraver.com/blog/2016/02/17/stack-overflow-the-architecture-2016-edition/ – Liam Jun 12 '18 at 13:40
  • Connection pooling is enabled because it makes sense. What is the harm in using connection pooling? This question makes no sense to me. Why would you ever use multiple connections in a micro service? – paparazzo Jun 12 '18 at 14:06
  • @paparazzo, connection pooling is OK. No problem with that. The problem is the default of MaxPoolingConnection=100. Azure-SQL for example, cant handle those number of connections. 200 connections cost you 300$. So, how it really should work? – No1Lives4Ever Jun 12 '18 at 14:13
  • Azure-SQL can't handle connection pooling? Question makes even less sense to me. I can't help you. – paparazzo Jun 12 '18 at 14:27
  • @paparazzo, I didnt said that. I just say that with limit of 200 connections, it's no sense to allocate 100 connections (default) per application. – No1Lives4Ever Jun 12 '18 at 14:29
  • Allocate 100 per connection? You are seriously confused. – paparazzo Jun 12 '18 at 14:31

0 Answers0