I have db consolidation task in hand.
We have a one parent db and 10 child dbs. I want to create 10 schemas with the same names as child databases, and move all tables from child dbs to their matching schemas under the parent db.
Question is how to handle db connections without code changes.
We have one db login and the same user for all databases.
We are currently using a static db table(App_Id column, DB_Name column) to store application id and their db names for the app to create connection strings.
We are currently not specifying schema names(dbo), and plan to keep it that way after the consolidation.
We want zero changes on app code.
Do I have to create separate db users for each schema (and make the related schema as its default) so that the app does not need to use schema names?
Ideally, I just want to add a schema name column to the static table (App_Id column, DB_Name column, Schema column).
Please let me know what you think?