I'm working in the development of an ERP type .Net WinForms application consuming a WCF service. It's to be used by many small companies (in the range of 100-200).
Database is SQL Server 2008 and the service will be hosted as a Windows service.
Even thought there will be a single DB Server, our customer insists in having separate databases for each company. That is because of stability/support concerns (like DB being damaged or took offline for some reason thus affecting all clients). Concerns coming from previous experiences (not necessarily with same platform).
With a single database, connections to the DB would be opened at service start up and pooling used, but, I'm not sure how connections could be managed in a multiple DB scenario:
Could a connection to the corresponding DB be opened and closed for each service request? would performance be acceptable?
If a connection is opened and maintained for each company accessing the system, what's the practical limit of opened connections (to different databases)?
It would be very interesting to hear your opinions and suggestions for this situation. Tanks