Incremental Refresh Synapse - Reduce Number of Concurrent Session

[jlf81]

Does anyone know how I can reduce the number of connections in a intial load of a incremental refresh Power BI Dataset that connects to Synapse.

I have a number of tables in a start schema that I would like to have incremental refresh set up for a 10 year period, the new data is only going to be a couple of days. So I want as small a partion size as possible.

However is seems that I can only have a total of 512 partions across all the tables, which dpesn't let me go for a fine grain of parition. Or that is roughly when I stop getting this message

'Microsoft SQL: 111219;The maximum concurrent session limit of 512 exceeded. To learn more, please visit: http://aka.ms/dwsoftlimits.. The exception was raised by the IDbCommand interface'

Does anyone know a work around to get the refresh to queue the connections rather try and run them all conccurently? I've looked online and couldn't find anyone talking much about this issue

[_sfrost]

@jlf81 

It's not clear whether you are using gateway here or not. In case you aren't using gateway, you can try setting up gateway for Synapse since synapse supports gateway.

By using the gateway, there is possibility to control the concurrent connections to the source. We need to change a setting in gateway configuration file to make it work. Please refer to the below doc.

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance-cpu

You need to change the below mentioned field value to the desired number of concurrent connections.

MashupDefaultPoolContainerMaxCount

Did I answer your questions? Give a thumbs up and accept this post as solution!

[jlf81]

@_sfrost just wanted to check with this, would you expect to use the on prem gateway and somehow shoehorn it to working cloud to cloud or is there another type?

Ive just had a look and am not sure where to start with that part, any pointers would be appreciated

[_sfrost]

Though your source is cloud and doesn't require gateway for refresh, we can still set up gateway to control the number of concurrent sessions. That's the reason I suggested you to use gateway and I am not sure if we can control this if we do not use gateway.

To start with, install a gateway and add your Synapse datasource under that gateway.

Once set up, follow the article I provided and change the gateway configuration file.

Once done, do a refresh and observe the behaviour.

[jlf81]

Morning, I spent all of yesterday looking into Gateways and the issue I have is that I don't have a machine to stand the on prem gateway on, so I was looking into VNet gateways.

Do you know if there is anyway to do the what you surguest with them.

I now have one setup and get the concurent connections errors, but I don't know where to start in limiting these in the Vnet Gateway. Any pointers?

[v-robertq-msft]

Hi, @jlf81 

According to my research, Azure VPN Gateways limit the number of Point-to-Site (P2S) connections allowed to a single gateway. SSTP Connections are limited to 128 concurrent connections for all VPN Gateway SKUs. There is not way to increase this limit, but most VPN clients will support one of the other protocols so connecting over a different protocol may help avoid the limit.

I think you can also refer to these documents to get the troubleshooting guidance and check if they can be helpful:

https://www.bluematador.com/docs/troubleshooting/azure-vpn-gateways

https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-troubleshoot

https://docs.microsoft.com/en-us/azure/vpn-gateway/tutorial-create-gateway-portal

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jlf81]

@v-robertq-msft do you know how I can set them to queue the connections rather than rejecting them when they get to the limit. 

As far as I can tell the problem is Power BI doesn't seem to intelligently manage the connections, it just keeps spamming then till it either completes it's refresh or hits an error

[v-robertq-msft]

Hi, @jlf81 

As far as I’m concerned, you encountered the problem because you reached the limitation of the concurrent sessions, and you can not set the queue of the connections manually.

Here’s the limitation document, you can take a look:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-servi...

A blog that explains the same problem as yours

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jlf81]

Hi @v-robertq-msft 

Thanks for those links, does this mean that there is no way to use incremental refresh on large complicated models? or is there another way to manage this not through the gateway?

My prefered solution on this would be to manage direct from Power BI and not use the gateway at all

[v-robertq-msft]

Hi, @jlf81 

In my opinion, to deal with the errors that happened because it reached the limitation, the only way to solve this is to reduce the data volume and optimize performance. So reducing the data connections can be the possible method in this case.

If you want to get more possible ways to deal with the error you got, you can try to open a support ticket to get help from Microsoft:

https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jlf81]

Ahh thank you, thats a great idea, I will give that a go.

We are not using the gateway at the moment, I will give that a go this morning and let you know if that solves it.