Current implementation: I have one system that is been used by several thousands of customers, each customer has it's own SQL database and has the option to login via web browser or mobile app. Today user is verified by user name, password and domain name which is equivalent to database name. Future Requirement: User should login only using email address and password without domain name. This basically means that a global search has to be done on all those thousands of databases to find one user. How to make such global search without effecting performance? is there any cleaver way to work out the issue?

CodePudding user response：

first of all, if you are looking for performance and you will search in each one separately, use the reverse procedure and clean ADO.NET.

Alternatively, it is possible to merge the users into a separate database as preferred and search it.

The third option is, again with the reversed procedure, to make the request extract data from as many databases as you want.

CodePudding user response：

If it was me I'd make a new table with an ID, website name and database name. Implement a Primary key to reference the data. This might help. Ref: https://programmerbay.com/difference-between-primary-key-and-secondary-key/