Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sql server backplane for signalr core #5277

Open
SebaVDPInfra opened this issue Mar 31, 2017 · 33 comments

Comments

Projects
None yet
@SebaVDPInfra
Copy link

commented Mar 31, 2017

When will signalR sql server backplane be released for dotnet core?

Is there a roadmap of signalR core?

@SebaVDPInfra SebaVDPInfra changed the title realease sql server backplane for signalr core sql server backplane for signalr core Mar 31, 2017

@moozzyk

This comment has been minimized.

Copy link
Contributor

commented Mar 31, 2017

@SebaVDPInfra - why do you need SqlServer (as opposed to e.g. Redis)?

@muratg

This comment has been minimized.

Copy link
Contributor

commented Mar 31, 2017

We're not planning to implement this for 1.0, so backlogging.

cc @DamianEdwards

@SaltyDH

This comment has been minimized.

Copy link

commented Aug 16, 2017

Any thoughts around cost of entry? Our current topology includes Azure SQL but to also include Redis with data persistence is a massive initial outlay so that we can have SignalR.

@davidfowl

This comment has been minimized.

Copy link
Member

commented Aug 16, 2017

it wouldn't be that much work. We'd start by porting the old scale out back end. It's just a very very bad way to do real time notifications as that's not what sql was meant for.

@SaltyDH

This comment has been minimized.

Copy link

commented Aug 16, 2017

Thanks @davidfowl. Completely agree with SqlServer being the wrong fit when at scale. For smaller user counts it still does the job. Once I reach a point where SQL no longer supports my scale, I can justify the Redis costs for the entire platform - it was just a thought that might be shared by others as SignalR gains more visibility approaching its release.

@reicher001

This comment has been minimized.

Copy link

commented Apr 10, 2018

You can add my name to the list of users that would be interested in a SQL Server option.

@aauronn

This comment has been minimized.

Copy link

commented Jul 27, 2018

Will this feature come soon?? 🙈

@anurse

This comment has been minimized.

Copy link
Member

commented Jul 27, 2018

We still have no current plans to build a SQL Server backplane.

It would help us prioritize if we could understand why a SQL Server backplane would help you. SQL isn't really designed for this purpose (real-time pub/sub). A SQL Server backplane would require that your server support the Service Broker (to provide notifications of changes to the database) which requires the significantly more expensive Azure SQL Database/Managed Instances tier, or a local on-premises Enterprise edition of SQL Server.

Is this about fitting in to existing infrastructure or is there some value that you feel SQL Server will add over Redis or the Azure SignalR Service?

@Zonciu

This comment has been minimized.

Copy link

commented Sep 8, 2018

Does ASP.NET Core SignalR supports Redis Cluster?
In scaleout-with-redis there is a note says SignalR scaleout with Redis does not support Redis clusters., which is for ASP.NET SignalR.
But for ASP.NET Core SignalR, there is no document for that.

@davidfowl

This comment has been minimized.

Copy link
Member

commented Sep 8, 2018

@Zonciu can you file a new issue. This issue is about SQL.

@abarger-bss

This comment has been minimized.

Copy link

commented Oct 25, 2018

For our part, we need a backplane that works on-premise, in a Windows environment in order to support on-premise Service Fabric clusters. We don't want to manage any form of Linux installation, so Redis is out (we don't trust the archived Windows port). Our customers are not ready to move to the cloud, so Azure Service Bus is out (Azure Stack is also cost-prohibitive for us). Windows Service Bus is dead. The possibility of a Service Fabric backplane was rejected.

Given these constraints, a SQL Server backplane seems like our only option at the moment. If SQL is not the right tool for the job, so be it, but it would be nice to have something that works on a local Windows install. It's weird to me that a technology which started on Windows wouldn't have this support in its core.

@bradygaster

This comment has been minimized.

Copy link
Member

commented Oct 25, 2018

Are you also opposed to an in-container implementation of Redis due to your desire to avoid Linux? SQL isn't as good a backplane as is Redis, frankly, due to the nature and performance of the two architectures Redis is definitely preferred.

So is the Redis-in-container also not acceptable?

@abarger-bss

This comment has been minimized.

Copy link

commented Oct 25, 2018

We don't have experience with containers but I'll concede that would be one way to work around the issue. It would be a wart in our architecture as we are using containers nowhere else. Overall we're not thrilled as a Microsoft shop to use a technology that has Windows nowhere on its radar. Honestly, I think if this was our only choice we would sooner roll our own backplane.

@reicher001

This comment has been minimized.

Copy link

commented Oct 25, 2018

@davidfowl

This comment has been minimized.

Copy link
Member

commented Oct 26, 2018

So let me pose this question: If we gave you an exe you could run that wasn't highly available but worked well enough to support signalr on multiple nodes that you had to run in your local on-premise environment, would that be sufficient?

@abarger-bss

This comment has been minimized.

Copy link

commented Oct 26, 2018

Maybe. In this scenario, is the exe managing the storage for the backplane? Would a package for integrating with the exe be provided (Similiar to Microsoft.AspNet.SignalR.Redis)? Keep in mind that we're looking for something we can deploy to production on-premise clusters - our use case is more than dev/test. I suppose the lack of high availability could be mitigated by deploying this exe as a guest executable in the cluster.

What is the benefit to this product of providing an exe instead of code to integrate with an existing Windows product?

I don't mean to belabor this point, but we're not stuck on the idea of SQL Server as a backplane. We are committed to any backplane which supports production, on-premise Windows deployments. Maybe this needs a separate issue - I mostly presented our scenario in response to @anurse 's request.

@davidfowl

This comment has been minimized.

Copy link
Member

commented Oct 27, 2018

The backplane’s job isn’t storage, it’s to get messages to the subscribers. It’s volatile and transient storage, just to push the messages to other nodes.

It would be lighter weight, faster, it would avoid polling and persistent storage that requires clean up (as sql would).

@andrewtyped

This comment has been minimized.

Copy link

commented Oct 27, 2018

I see your point. Yes, this would work for us assuming the exe is Windows compatible and the ability to scale out is somewhere on the horizon as an enhancement. I wonder if what you're describing here ties into #968, which I'm just now seeing. I see the value in a product custom-built to be a backplane for SignalR.

The only consideration left for me then is timeline - If porting the old SQL backplane could be released ~6 months sooner than a new product, I think we'd still rather have it and live with the consequences in the interim. Bird in the hand etc.

@davidfowl

This comment has been minimized.

Copy link
Member

commented Oct 28, 2018

Releasing software is one thing, the real cost is in the maintenance.

@legistek

This comment has been minimized.

Copy link

commented Nov 1, 2018

A SQL Server backplane would require that your server support the Service Broker (to provide notifications of changes to the database) which requires the significantly more expensive Azure SQL Database/Managed Instances tier, or a local on-premises Enterprise edition of SQL Server.

Service Broker is supported in SQL Server Standard Edition.

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-2017

The reason would be in the very common scenario where an application is already using a Microsoft SQL server plus an arbitrary number of load balanced API servers, it would be nice to use the existing SQL server and not have to set up an additional box just for Redis.

@anurse

This comment has been minimized.

Copy link
Member

commented Nov 1, 2018

Service Broker is supported in SQL Server Standard Edition.

Good to know! It still remains true that SQL Server was always a most costly and less efficient way to run a backplane. It's not yet clear to me if people want a SQL Server backplane or just a Windows On-Premise backplane. The APIs are also very simple and extensible (the Redis implementation is a single core class with a couple small helpers and quite readable IMO). As @davidfowl said, releasing something is one thing, maintaining it is bigger concern. The SQL Server backplane for ASP.NET SignalR has a history of consuming significant resources on the SQL server, so we need to be careful about how to move forward with it.

@legistek

This comment has been minimized.

Copy link

commented Nov 2, 2018

@anurse I don't disagree SQL server isn't necessarily the best backplane in the abstract (indeed, I've had a lot of trouble with it in SignalR in ASP.NET-not-core - and debugging on localhost with it is next to impossible - but it is functional). But again it's just a matter of not wanting to force my customers to deploy a dedicated Linux box on top of an already complex (all Windows) infrastructure. So it's not a matter of preferring or disliking Linux, it's that we have customers who have invested heavily in MS proprietary tech like SQL Server and Windows and want to continue exploiting those investments before moving to Linux and/or the cloud.

Would it not be possible to handle the issue in a peer-to-peer fashion? All the load-balanced servers could broadcast all SignalR messages to each other for routing to their respective connected clients? In principle that wouldn't be all that different from backplaning, which as I understand it means that each server sending an outgoing message hits common database and the database in turn notifies all the other connected servers of the message so they can also send it to their own connected clients. Why not just skip the middle man? The database could just be utilized so the servers could be aware of each others' existence (registering their IP address, etc.) with the rest handled P2P.

@anurse

This comment has been minimized.

Copy link
Member

commented Nov 2, 2018

But again it's just a matter of not wanting to force my customers to deploy a dedicated Linux box on top of an already complex (all Windows) infrastructure.

Yeah, as I said, I definitely buy the scenario that a Windows-based On-Premise backplane makes sense. I just wanted to reframe the discussion in terms of that instead of SQL Server since it's been problematic :).

Would it not be possible to handle the issue in a peer-to-peer fashion?

Yep, this is something we've been tracking in https://github.com/aspnet/SignalR/issues/968 for a while :). It's a good idea, we just haven't got a good design for it yet (other priorities at the time ;)). It'd also require something like a gossip protocol (#2263) or manually registering the IP addresses of the other servers (which isn't possible in Azure App Service, but this backplane would be designed for on-prem anyway).

The big consideration right now is building and maintaining such a backplane for production-quality deployment is quite a bit of work and we need to prioritize it among other things on our queue.

@bradygaster

This comment has been minimized.

Copy link
Member

commented Nov 15, 2018

Adding to the 3.0.0 milestone to consider a solution for Windows on-premise deployments. SQL may not be the answer when we're all done, but we're considering this for our next release milestone.

@legistek

This comment has been minimized.

Copy link

commented Nov 16, 2018

Sounds great. Thanks all. @anurse yeah I hear you and look forward to seeing what you guys come up with.

FYI I wound up implementing my own P2P routing system using SignalR connections between the servers and a SQL database just for the servers to "declare" themselves and their IP addresses. It was very straight forward and seems to be working really well. Needs more testing but I'm optimistic. Happy to share results and more specific methods if y'all are interested.

@GeXiaoguo

This comment has been minimized.

Copy link

commented Nov 26, 2018

@legistek Care to open source the code when you are happy with it? I am in the exact situation to having to implement signalr behind a load balancer without adding any new infrastructure.

@aspnet-hello aspnet-hello transferred this issue from aspnet/SignalR Dec 17, 2018

@aspnet-hello aspnet-hello added this to the 3.0.0 milestone Dec 17, 2018

@sketchau

This comment has been minimized.

Copy link

commented Jan 21, 2019

Sounds great. Thanks all. @anurse yeah I hear you and look forward to seeing what you guys come up with.

FYI I wound up implementing my own P2P routing system using SignalR connections between the servers and a SQL database just for the servers to "declare" themselves and their IP addresses. It was very straight forward and seems to be working really well. Needs more testing but I'm optimistic. Happy to share results and more specific methods if y'all are interested.

@legistek , it would be great to see your results, if you were willing to share. Using SignalR as a backplane for SignalR seems sensible.

@legistek

This comment has been minimized.

Copy link

commented Jan 21, 2019

@sketchau, @GeXiaoguo I'm not opposed to sharing generally but my particular implementation right now is: a) not tested very much, since we decided to stick with SQL backplaning and ASP.NET Framework for now; and b) not necessarily a general purpose solution as it is built into the main code and assumes our user ID system, etc., so it would be a bit of effort to make it publishable. I was waiting to see what the official project owners came up with for this before thinking about moving to ASP.NET Core and fully implementing something home grown if necessary.

That said what I did was very straightforward and took less than a day to prototype. I'll just explain the basic idea -

  • Each load balanced server self-registers with a SQL database during startup. It needs to know its own internal URL for this to work.
  • Each server subscribes to a "relay" group of every other registered server
  • Each server, whenever it needs to send a message out to one or more groups, publishes that message directly to its client groups, but then also publishes the same message to the "relay" group along with the client group names that should receive it
  • Upon receipt of a message through the relay mechanism, a server re-broadcasts the message payload to the group names from the message

In my case each user is identified by Guid, and gets their own Group. So when a server broadcasts messages to other servers for relay it knows the Guids of the users who should get it and includes the Guids with the message. Then each server receiving a message for relay iterates through the users identified in the message and sends the message payload to each group/user.

So really no magic. I just don't know if this is a general purpose solution because I don't know all the different ways people use SignalR.

@muratg muratg modified the milestones: 3.0.0, Backlog Jan 30, 2019

@GeXiaoguo

This comment has been minimized.

Copy link

commented Mar 16, 2019

@legistek, thanks for sharing the concept. I think it solves a general enough problem. At least, it solves mine problem: two web servers, sharing a single SQL DB, sitting behind a loadbalancer serving a small number of users(<20) with a small number(~1/minute) of messages to publish.

@maldworth

This comment has been minimized.

Copy link

commented Mar 19, 2019

@GeXiaoguo, you might be able to use MassTransit (with RabbitMq). As @anurse mentioned, the Redis implementation is really easy to read. So easy infact that I decided to implement a SignalR backplane for MassTransit. This does give an alternative option for on prem windows servers, which would be installing RabbitMq Message Broker. Hopefully this is helpful to somebody.

@GeXiaoguo

This comment has been minimized.

Copy link

commented Mar 19, 2019

@maldworth, a practical difficulty in big cooperation is that infrastructure is usually managed by a third party team, or in my case, multiple third party teams. Adding a managed queue like Radis, RabbitMQ is adding a infrastructure. The process cost is too high. That's why there are so many people interested in a SQL Server solution despite its potential scaling problem. Basically every application already has a database. Adding new tables to an existing database does not need to involve other teams and does not need reviews, reasons to justify, and all kinds of organizational overhead.

@maldworth

This comment has been minimized.

Copy link

commented Mar 19, 2019

Yep, I definitely understand the desire for SQLServer is to use existing infrastructure. I have just found from an infrastructure point of view (who like windows and are familiar), the installers for RabbitMq (rabbitmq server + erlang for windows), is a really easy setup experience. Redis (from what I've found) isn't so easy to setup on windows. So I just wanted to highlight it's an option with slightly less barriers for those Windows + on prem only infrastructure teams.

@GeXiaoguo

This comment has been minimized.

Copy link

commented Mar 19, 2019

@maldworth in my case, new processes, new deployment scripts are classified as new infrastructure. :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.