November 13, 2015 at 7:46 am
Very timely indeed. We're currently in the process of building clean databases from the .. ummm .. less than optimal solutions that were in place as well as building out new ones.
Schemas have come up a few times and so far the answer is not to use them. We separate staging into its own database to keep people out and the main databases uncluttered. We're not integrating third party applications. And our users aren't quite savvy enough for schemas not to confuse them.
So we're sticking to good old 'dbo' as we move forward. We don't have a need to do granular security within the databases and we're not grouping by function.
I have been involved with projects that did use them to separate out the ui from the warehouse and it made sense for organization and probably security on implementation. But other than that I've kept to one schema per database.
November 13, 2015 at 7:49 am
For many years, I supported an application which had absorbed another application to add functionality, and each part had its own schema.
Because we supported geographically dispersed sites with data replication to provide a consolidated view at the headquarters, the sites had tended to act autonomously, and there was a tendency for folks to create ad hoc structures in their local databases. After a while, many sites had dozens (if not hundreds) of these ad hoc tables and views, most created by folks who had since moved on but nobody dared delete them for fear that they were still being used.
When we were doing a major application upgrade, I convinced TPTB to allow us to clean up all that accumulated junk while upgrading the database structure (we got a 2-day global maintenance stand-down for the upgrade). As part of the clean-up, we advised the sites that their post-upgrade databases would contain only 3 schema: the 2 which were part of the application, plus a schema named local -- which they were to use for any local custom data structures. Anything in any other schema would be stripped out as part of the upgrade. After some initial grumbling, the sites actually got on-board and moved the stuff they knew was in use into the local schema, and some DBAs (much later) even admitted that it helped them get a handle on controlling what was getting tossed in their databases.
November 13, 2015 at 7:53 am
david.gugg (11/13/2015)
...previous DBA thought it'd be a good idea to provide each customer of our SaaS software with its own schema. Image how incredibly hard it is to work with 100 different versions of each table, each in its own schema.
Urgh...I had tried to think of poor uses and didn't manage to stoop so low!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 13, 2015 at 8:13 am
We use schemas to group tables by functional area within a single database. For example, our application uses about 10 separate databases, each for a different "pot" of data. Within some of these databases, there are "subsets" of data that are distinct, but somewhat related.
For example, our application stores engineering data for the F-35 Joint Strike Fighter. The "main" engineering data tables fall under the base (dbo) schema. However, there are also teams that do simulations and testing. These teams have their own data and that's stored in the same database under different schemas (SIM, TEST). I can easily add relationships between tables in all three schemas since they're in the same database. If I had created three separate databases for this data, relationships become much harder (almost impossible).
November 13, 2015 at 8:19 am
I use schemas for separation of concerns as so many others have posted.
In a current ETL solution, for example, I have the source system as the schema name for any of the staging tables and all of the 'universal' tables marked with the default "dbo" prefix.
Source1.LOAD_Table1
Source1.LOAD_Table2
Source2.LOAD_Table1
Source1.PROCESSED_Table1
Source1.PROCESSED_Table2
Source2.PROCESSED_Table1
Since so many of the source systems could potentially have identical table names, schemas work well for this.
November 13, 2015 at 8:28 am
In my old job where I was at for many years, we made very little use of schemas. Mainly we just didn't get it. Then one day it sort of dawned on me how they could be useful. We had a lot of tables in our primary database and we needed to add several new ones all related to management reports, which would eventually find their way into SSRS. I decided to implement a schema for those new tables. I thought it went quite well. We could quickly find the tables in SSMS, we knew exactly what those tables were for, and even if other tables had very similar names, we knew what that management report schema was all about. I do think though, that my co-worker thought it was a waste of time. I can't say it went over well, and since it was just she and I...
At my new job they've been using schemas a lot. They don't go overboard. Every database has at least one other schema. I've not seen more than 2 (not including dbo) in any of the 25 or so databases I've seen.
Kindest Regards, Rod Connect with me on LinkedIn.
November 13, 2015 at 8:42 am
We keep separate instances (or at least databases) for our regions of DEV, TEST, and PROD. We tend to use schemas for domain bounded contexts that map up fairly well with our .NET domains (typically grouped by namespace). Of course there are some 'common' schemas that are used throughout systems.
We also tend to use a schema appendix of 'a', 'd', 'l', and 'h' to designate 'application configuration', 'data', 'logging' and 'historical'. That allows us to wipe any schema data where the schema name ends in H knowing that the application can handle zero data in those tables. The Application Configuration tables indicate where we need to script out promotions from one region to the next. This allows us to generate diff scripts quickly by focusing on entire schemas rather than specific sets of tables (and missing when a new table comes online that needs to be included/excluded). The Logging (L) and Historical (H) suffixes are also valuable for us to periodically wipe older data in our DEV/TEST regions knowing that the data does not affect the existing application or useful stored data.
Could we have everything in one schema? Yes. This just helps us to quickly find the tables we are most interested in when working with both the SQL and .NET together. It also makes us realize that when we are creating 'cross-schema queries' that we may have a design flaw in our domain bounded contexts that could be a sign of technical debt we are about to owe.
November 13, 2015 at 10:13 am
In the past, I've used a separate schema to store tables holding staging data, partial table backups, summary data for an adhoc report, etc, that I don't want to keep forever, but I want to keep for a few weeks. In conjunction with a SQL Server Agent job that drops objects in this schema older than 90 days, it made a nice little workspace area for the adhoc work required on those instances.
November 13, 2015 at 10:22 am
I have separate instances for dev vs. prod so I don't use schemas for that.
I do use schemas for ETL staging tables and table backups that I sometimes do as part of an ETL to make troubleshooting/rollback faster.
On our website database though, I've started using schemas in a couple of ways, mainly permissions related:
1. A 'reports' schema, so that SSRS has execute rights on all the report stored procs, without having any execute rights on anything else. Saves me having to grant permissions on a stored proc by stored proc basis (or granting SSRS rights to more than it needs).
2. An 'internal' schema, to differentiate between tables provided to the website from our internal systems vs. tables the website itself generates data for. This allows me to prevent the web developers from changing data in these tables as part of a hotfix, and then wondering why the change was lost when the table next synced. At the very least they know the data is coming from somewhere else.
Leonard
Madison, WI
November 13, 2015 at 10:28 am
Rob N. (11/13/2015)
...We also tend to use a schema appendix of 'a', 'd', 'l', and 'h' to designate 'application configuration', 'data', 'logging' and 'historical'...
Hungarian notation??? Nooooooooo!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 13, 2015 at 12:07 pm
Our primary database schema uses (sub)schemas in two different ways. First, logical separation into "namespaces", keeping the accounting objects in [acct] and the email objects in [mail], etc. All the objects in these subschemas are tracked by our source control system.
The second use lets us segregate objects that are known deviations from the standard schema. Our onboarding processes is mostly manual and highly customer-specific, so our conversion team can create persistent objects in the [conv] subschema for long term retention or in [tmp] for truly temporary use. In addition, we will occasionally add custom schema objects (over the DBA's objection!) but these are at least contained to the [cust] subschema. Isolating these objects into these subschemas lays the groundwork for automated schema audits and resynchronization to come (read SQL CI and SQL Release).
November 13, 2015 at 12:22 pm
I could be wrong but I'm pretty sure there is no nesting of schemas...so there is no such thing as a sub-schema. And I think setting a default schema is not even required. I doubt the dbo schema can be deleted although I never tried to and probably won't.
November 13, 2015 at 12:35 pm
Bill Talada (11/13/2015)
I could be wrong but I'm pretty sure there is no nesting of schemas...so there is no such thing as a sub-schema. And I think setting a default schema is not even required. I doubt the dbo schema can be deleted although I never tried to and probably won't.
Technically no but you can use dotted notation for schema names, e.g. [application.data] would be perfectly acceptable.
There are no facts, only interpretations.
Friedrich Nietzsche
November 13, 2015 at 12:35 pm
I absolutely use schemas and find them quite useful. I use them to separate different types of functionality. For example, the procedures that comprise the ETL portion of the database are in their own schema, as are the objects called from the web site, the objects called by web services and the occasional data backup tables. I think they're a very useful organizational tool.
I also like the ability to define permissions by schema. This allows me to give certain developers "carte blanche" permissions in one area, but they don't get to play with the rest of the objects.
November 13, 2015 at 1:08 pm
Working in a healthcare-related field, we recently started using schema to differentiate tables containing PHI from those that are more generic use (lookup tables and the like). We can lock down access to the schema as needed and if someone needs an extract or something looked up, it's easier for someone who isn't familiar with the database to know whether the data is sensitive or not.
Prior to that, I've used schema to separate functional areas or to separate things used by ETL from those used by the general application.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply