none
Multiple Schemas means Multiple Users?

    Question

  • Hello All,

    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?

    Thanks,

    Kuzey
    Wednesday, February 05, 2014 6:48 PM

Answers

  • There's no SET command or similar to specify the desired default schema at the connection level. So either use separate users with different default schemas, or schema-qualify your queries (the long term solution).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by KuzeyI Wednesday, February 05, 2014 7:27 PM
    Wednesday, February 05, 2014 7:10 PM

All replies

  • There's no SET command or similar to specify the desired default schema at the connection level. So either use separate users with different default schemas, or schema-qualify your queries (the long term solution).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by KuzeyI Wednesday, February 05, 2014 7:27 PM
    Wednesday, February 05, 2014 7:10 PM
  • Whole app depends on context aware SQL code. So, I guess I have to use separate user for each schema.

    I assume I can not use SYNONYMS for database name part of connection string.

    CREATE SYNONYM DB_A for [DB1].[SCHEMA_A];

    Then connect using;

    Server=ServerA;Database=DB_A;UserId=User1;Password=#####;

    Thanks.


    • Edited by KuzeyI Wednesday, February 05, 2014 7:28 PM
    Wednesday, February 05, 2014 7:27 PM
  • I'm sorry, but a synonym cannot replace a schema name in such a way, I'm afraid...

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, February 05, 2014 7:32 PM
  • ... let me know what you think? 

    I think you are smoking something if you believe that you can accomplish this without application changes. When you say "we are not specifying schema names", are you absolutely certain that your application NEVER refers directly to the dbo (or any other) schema?  Keep in mind that there may be functionality that is invoked at a layer beneath your code that might do this.  Certainly the ddl for your existing databases does this if you did not write the scripts yourself (and keep them) and take pains to omit the schema name - and your ddl would have to be modified for each child database to "re-point" those relationships and names from the default/standard dbo schema to the one that should be used.  That task itself is fraught with a great potential for mistakes that may be difficult to discover - imagine leaving a foreign key pointing to the dbo.xxx table when it should have been the db1.xxx table and not discover this until a strange FK violation pops up long after testing is over. 

    As far as requiring separate users for each schema, this seems likely but much of this depends on how your current databases and applications are implemented. Security will likely be more of an issue - you now have a single database with all your data and every user needs access to this database (though not every schema within it).  You will need to be much more rigorous in applying security.  As a user I would be really annoyed if someone told me that I now had to have a different login for each application (when formerly I needed only one).  In other words, you are making things more difficult for your users and perhaps giving yourself a black eye in doing so.   

    Lastly, I don't really understand your intent with the static db table changes.  You've already stated that you intend to use the database name as the schema name, so adding a third column which will contain the exact same value as the db_name column doesn't serve any useful purpose.  And really - why would you need a schema name at all in this table?  Your idea is to use the default schema setting of the user and this must be done by an administrator when setting up logins and users.   In fact, the entire purpose of this table seems to disappear given the consolidation - all of your applications will connect to the parent DB and only to the parent DB.  There is no need to store any "information" from which to create connection strings (unless there are other settings that stored in this table). 

    While you are thinking, consider your disaster recovery requirements.  Currently you can restore just one of the child databases without affecting any of the others.  Do you need to do this and, if so, how would you do it in your consolidated configuration? 

    Wednesday, February 05, 2014 7:40 PM
  • Scott,

    Thanks for the info. I understand your concerns, but the application we have has unorthodox requirements/design; many dbs over several servers, few tables but 2,000+ sprocs. Databases are being created/dropped by automated scripts initiated by the app. The app is practically an Auto-DBA.

    Drastic code changes cost A LOT. Application handles security as long as each parent db has its own db user. 

    Regarding the static table used for connection strings, I just wondered if I can get away with stating schema name instead of creating and stating 100+ usernames. The static table not only contains the db names but also the server names.  

    So, the goal is to consolidate databases with minimum cost.


    • Edited by KuzeyI Wednesday, February 05, 2014 8:07 PM
    Wednesday, February 05, 2014 8:07 PM
  • I'd also refer back to the same question on the sqlservercentral-forum:

    "Multiple Schemas means Multiple Users?"


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Wednesday, February 05, 2014 8:58 PM
  • Wait, 2000 stored procedures? When I posted my answer over on SQL Server Central, I assumed that all database calls were in the client code, but in fact that you have stored procedures?

    Well, in that case you will neeed as many stored procedures as you have to day. That is every schema will need its set of procedures, because the if a stored procedure refers to a table without a schema, it defaults to the default schema of the procedure owner.

    Over on SQL Server Central, I suggested that could consolidate all into a single set of tables, and then use views to spare the application. But that idea sort of goes out the window if you have stored procedures.

    Of course, go and a try a proof of concept with this idea, but I would not be surprised if you find out that this is a lot more complicated than expected. And in that case, your company should consider to make the investment to rearchitect the entire application. I can certainly understand that your current architecture with lots of database for every client is a nightmare to maintain.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, February 05, 2014 10:54 PM
  • Wait, 2000 stored procedures? When I posted my answer over on SQL Server Central, I assumed that all database calls were in the client code, but in fact that you have stored procedures?

    Well, in that case you will neeed as many stored procedures as you have to day. That is every schema will need its set of procedures, because the if a stored procedure refers to a table without a schema, it defaults to the default schema of the procedure owner.

    Over on SQL Server Central, I suggested that could consolidate all into a single set of tables, and then use views to spare the application. But that idea sort of goes out the window if you have stored procedures.

    Of course, go and a try a proof of concept with this idea, but I would not be surprised if you find out that this is a lot more complicated than expected. And in that case, your company should consider to make the investment to rearchitect the entire application. I can certainly understand that your current architecture with lots of database for every client is a nightmare to maintain.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Yes, we use stored procedures %95 of the time. Currently we don't need to replicate stored procedures since they are in master db and set as system objects (risks are calculated!). 

    "if a stored procedure refers to a table without a schema, it defaults to the default schema of the procedure owner."

    This was unexpected. I did a quick test, and I got "Invalid object name 'Table_A'" since there was no dbo.Table_A, only S1.Table_A and S2.Table_A. That's a show stopper now :)

    Thursday, February 06, 2014 3:52 AM
  • This was unexpected. I did a quick test, and I got "Invalid object name 'Table_A'" since there was no dbo.Table_A, only S1.Table_A and S2.Table_A. That's a show stopper now :)

    Back to the drawing board!

    Either stick with your current solution, or rearchitect to have all projects in a single set of tables. You can consider the ideas with multiple schemas as dead.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 06, 2014 8:55 AM