February 19, 2012 at 11:46 am
Hello All,
Currently, we have an instance with many databases some consisting of only two tables etc. I’m thinking about setting up a database to support some of those processes we have in place that only requires one, two or three tables and using multiple user schemas to separate and identify the use of those tables. From what I’ve read, there’s also some security related benefits to doing this.
I’d like to know what other thought of this approach. Are there any performance gains or negative impacts to this solution? If you don’t think using multiple schemas is a good solution, are there any recommendation to accomplish the same goals?
I just thought it was better to hear from not only the experts but from those either using the same setup or are familiar with its use.
Thanks in advance for your help.
February 19, 2012 at 11:54 pm
Other than security I did not see any other reason to implement schemas in my database. The other possiblity is the needs of business may require to create schemas. Same object name with different structure with different data is possible with schemas.
Atleast not to me I have a valid reason to create schemas.
---- Babu
February 20, 2012 at 12:35 am
Ronnie Jones (2/19/2012)
Hello All,Currently, we have an instance with many databases some consisting of only two tables etc. I’m thinking about setting up a database to support some of those processes we have in place that only requires one, two or three tables and using multiple user schemas to separate and identify the use of those tables. From what I’ve read, there’s also some security related benefits to doing this.
I’d like to know what other thought of this approach. Are there any performance gains or negative impacts to this solution? If you don’t think using multiple schemas is a good solution, are there any recommendation to accomplish the same goals?
I just thought it was better to hear from not only the experts but from those either using the same setup or are familiar with its use.
Thanks in advance for your help.
We use schema to group data domains.
I advise to at least create your own schema to hold user objects and not rely on dbo ( and stay away from it )
Best practice is to always use schema qualified objects in queries etc, so it ( having your own schema ) helps promoting/enforcing this behavior. ( unless you set that schema as default schema for users )
Granting at schema level can be an advantage, but may also violate "minimal authorities" advice.
Creating schema to hold sets of sprocs/function/... can also help, but how far are you willing to go with this ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2012 at 7:38 am
Thanks Babu for your input. I appreciate it!
February 20, 2012 at 7:45 am
Thanks Johan for that great information. I'm sure there will be a few extra steps involved with getting this going. It's always good to hear about the pros and cons from a "real-world" perspective. One of my developers mentioned this to me which is what initiated all this.
Thanks again for you input!!
Ronnie
February 20, 2012 at 12:52 pm
The question for me is "what benefit to you gain by combining all of these tables into 1 database?" Are they completely different tables and structures, or are they the same tables and structures with different permissions and/or data? Of course there are databases out there with 2 or 3 tables, but my guess is very few (unless used strictly for analysis). If I built an application that had to house multiple companies and they each had their own installs and requirements, but the databases were the same structure for each one... I would create separate databases for each company. If I had an application housed on my servers and offered software as a service to multiple customers, I "may" decide to keep them all in 1 database and differentiate them by a company id. If it was 1 application with different add-ons or departments I may use schemas in 1 database.
Could you give us an example (even if fake) that at least applies to why the databases are all separate? I imagine that we can help you with a better solution and I am also guessing there is not a good reason for all of these to be separate. That being said, it may not make sense to combine them and use schemas strictly because it makes maintenance easier. If they belong in separate databases (different applications, different customers, QA versus dev versus prod, etc) then schemas would not work. However, if you have data from HR department in 1 db, sales data in another, and ops in yet another... "Maybe" it makes sense to combine those into 1 database and separate by schema.
Jared
CE - Microsoft
February 20, 2012 at 8:08 pm
If I understand you correctly, that is take separate DBs supporting different customers. Combine those into one DB, using SCHEMAs to keep each individual customer's data separate. Let us say you go ahead and do that, and all seems to be working well. Then comes what some call a "black friday" and customer "A". Screws something up like deleting/changing data that should not have been deleted/changed, and this customer must have THEIR DB" restored. Yes it could be done, but how long will it require, do you have standby hardware on hand to use, and while doing all the work to do so, how do you explain the slow down to Customers B and C ?
Yes I am being negative, but you must think of the negatives as well as the positives in your scenario.
February 20, 2012 at 8:12 pm
bitbucket-25253 (2/20/2012)
If I understand you correctly, that is take separate DBs supporting different customers. Combine those into one DB, using SCHEMAs to keep each individual customer's data separate. Let us say you go ahead and do that, and all seems to be working well. Then comes what some call a "black friday" and customer "A". Screws something up like deleting/changing data that should not have been deleted/changed, and this customer must have THEIR DB" restored. Yes it could be done, but how long will it require, do you have standby hardware on hand to use, and while doing all the work to do so, how do you explain the slow down to Customers B and C ?Yes I am being negative, but you must think of the negatives as well as the positives in your scenario.
I don't think we know this yet from the OP. That is why I was throwing several different scenarios, to make it clear that in order to recommend anything we must know the purpose of all of these databases with 1-3 tables.
Jared
CE - Microsoft
February 21, 2012 at 8:12 am
Hi Jared,
Thanks for the response.
Basically, my group supports a tool called SCCM and from time to time, we will develop a process to improve on a specific function. So, we do not store any “external customer” data. The data we store is our own from various software and hardware inventories. So, for example, we created an exclusion process which basically excludes specific servers from MS monthly updates. We only need 3 table to support this process and therefore would need to create a new database. That type of activity is common in my group and we have a dozen or so small databases like that consisting of only a few table. So, our thinking was – what if we create a database that we use for those function or processes that only require a few tables and identify the process or function by schema name. I’m not sure if we are saving anything by doing it that way verses just have many small databases. Is there a performance improvement or impact? Does it improve on maintenance or security. If I grant an external system access to a table, I still have to setup the access to that table regardless of whether it’s contained in one database or several. But is that a better solution to have it all contained in one database. Now, I’m not suggesting that this be done for everything. I realize that it will be a judgment call, but I’m just trying to decide if it’s even worth it. Are other folks seeing benefits to doing it that way.
Thanks,
Ronnie
February 21, 2012 at 8:21 am
Ronnie Jones (2/21/2012)
Hi Jared,Thanks for the response.
Basically, my group supports a tool called SCCM and from time to time, we will develop a process to improve on a specific function. So, we do not store any “external customer” data. The data we store is our own from various software and hardware inventories. So, for example, we created an exclusion process which basically excludes specific servers from MS monthly updates. We only need 3 table to support this process and therefore would need to create a new database. That type of activity is common in my group and we have a dozen or so small databases like that consisting of only a few table. So, our thinking was – what if we create a database that we use for those function or processes that only require a few tables and identify the process or function by schema name. I’m not sure if we are saving anything by doing it that way verses just have many small databases. Is there a performance improvement or impact? Does it improve on maintenance or security. If I grant an external system access to a table, I still have to setup the access to that table regardless of whether it’s contained in one database or several. But is that a better solution to have it all contained in one database. Now, I’m not suggesting that this be done for everything. I realize that it will be a judgment call, but I’m just trying to decide if it’s even worth it. Are other folks seeing benefits to doing it that way.
Thanks,
Ronnie
To me, it sounds like you really only need 1 database called SCCM or something. Just because you are adding tables does not mean you need a new database. If you only want certain people to have access to certain tables, views, and stored procs then separating the schemas is a good idea. However, plan it accordingly; i.e. don't make a separate schema for each function if you only have 2 groups of people who work on these. Then you would only create 2 schemas. If all the developers at some point or another touch all of the tables and objects, then just keep it in one schema.
I only separate databases for separate applications or customers. If it is the same application, why separate it into different databases? Of course, that is a simplistic view and I could find reasons to separate 1 app into different dbs, but generally it doesn't make sense. If there is no reason to separate the different tables for the different functions, then don't. Keep them all in 1 schema. Again, ask yourself what will be gained by separating the schemas? Only you can answer that question.
Jared
CE - Microsoft
February 21, 2012 at 9:29 am
Thanks Jared! You make some valid points. I think is does make sense to approach it that way from the aspect of one database...
Thanks again!
Ronnie
February 21, 2012 at 9:33 am
Glad to help!
Jared
CE - Microsoft
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply