November 12, 2015 at 10:37 pm
What a timely topic! Yes, in my work for the services arm of a software publisher, doing customer implementation work, we had the need for a better system to help with data validation for the results of ETL runs (mostly). In a simplified version, the system we designed uses a set of three schemas to supplement the main target schema. In these, we create sets of identically named objects, on the basis of which we can automate comparisons of actual vs. expected results, to give us fully automated regression testing of ETL results of each run at the detailed row-and-column level. It's a long story, but the availability of schemas as another layer in SQL Server makes this pretty straightforward. (Much harder in Oracle, where you don't have the schema layer per se.)
November 12, 2015 at 11:23 pm
In our architecture, we use instances to separate DEV/TEST/QA/PROD, databases to separate logical architecture layers, and schemas as source system namespaces in the staging layer, and as customer namespaces in the other layers. This enables us to maintain the names of source and customer no matter what they choose. If we need to integrate several sources for a customer, we create a new namespace and resolve the conflicts there.
This also allows us to do our security on the schema-level, instead of table/view/proc level, which is much easier.
November 13, 2015 at 12:00 am
Comments posted to this topic are about the item Custom Schemas
November 13, 2015 at 1:44 am
Previously I have been on teams (so sometimes mine but sometimes colleagues' ideas and decisions) that have used schemas to separate out functionality based on modules (e.g. order processing vs. customer management), security requirements and anticipated clients (e.g. access by different systems such as a bespoke GUI as opposed to integration with a 3rd party system).
I think that there is plenty of value of schemas and, in general, have been disappointed with the number of DBAs who have dismissed them out of hand WITHOUT providing a reason i.e. a development team suggests using schemas to the gatekeepers of the databases who disallow it whilst refusing to explain why. They may have good reasons and we would have loved to know. I know that this is not all DBAs. Possibly not most DBAs. Just most DBAs in my experience to date.
(I need to work with you guys more ;-))
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 13, 2015 at 2:56 am
I use Schemas for tables in my current projects as a way of logic separation. Similar to the way .Net uses Namespaces. We have schemas for tables for Application Configuration, Business Configuration, Client Configuration, History Tables, and several Business Logic areas.
Our applications have a standard version, but also specific objects by Customer for their specific features. Other very useful usage for schemas in this case is to group specific tables and programmability objects in Customer schema. We use .Net ADO connections and the database user the applications use to connect has the Customer specific schema as "Default Schema". This way, for example, if there is a specific stored procedure for this Customer, it will be called and not the standard stored procedure (dbo).
For me schemas are a must.
November 13, 2015 at 2:59 am
I haven't ever used schemas before. In retrospect, the company-specific objects in a 3rd party application would have been better placed in a separate schema. I inherited the current setup, and it's really to late to change that situation. But in future I'll definitely keep this in mind.
November 13, 2015 at 3:21 am
It isn't to late to change. 🙂
For the SQL Server database with SSDT in Visual Studio it's even easier:
- Create new Schema;
- In the specific objects, Refactor -> Move to Schema;
- Generate database upgrade script.
If applications have code with the Schema defined, then it becomes more complicated to evolve.
November 13, 2015 at 3:26 am
Great topic to discuss,
I have played all kinds of roles for SQL developer, DBA and data modeler and I have always found schema very useful for logically separating the objects for modelling and development and maintenance.
I do not see any reason for a DBA to dislike schema because if cleverly designed it has additional benefits like managing permissions at schema level,
script out/ migrate/ move across file groups etc all are easier if similar objects are grouped into logical schema.
November 13, 2015 at 3:41 am
We use schemas to help separate different logical areas within our database. These separations help to define the different business functions we're mirroring. We also find it helps with security and permissions, which we try to grant at the schema level. So certain users can only see certain tables - it all depends on their role within the business. It's not perfect, but it has helped guide our designs and thought process when we add new stuff into our databases.
Here's some example schemas that we use:
[audit]
[clean]
[logging]
[pricing]
[readonly]
[reference]
[service]
Hopefully those examples should be self-explanatory. If not, then we've not picked good enough names!
November 13, 2015 at 3:47 am
I use a custom schema (named custom) when integrating SSIS Framework functionality and patterns into the SSISDB database (SSIS) Catalog. I get separation from the third party - Microsoft, in this case - and I can maintain referential integrity.
:{>
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
November 13, 2015 at 5:58 am
We typically use schemas to separate application object / process areas
APP
CONFIG
STAGE
MDM
DW
REPORT (app specific)
TEST
We also use a CUSTOM schema for customer's to add their own objects, such as views so that they are isolated from our deployment process
November 13, 2015 at 6:54 am
Schemas are simply containers like folders in the NTFS file system. I don't see any drawbacks but I see lots of organizational advantages to using them. Most OLTP apps will have hierarchical configurations so corporate determines billing settings, departments determine feature sets, and users determine look and feel. Having three schemas will make sense since security will be distinct for each level of configurations.
Schemas also make sense when application areas are very modular. For example, having a schema for document work flow and storage would allow you to test, upgrade, and share that module with other projects more easily.
November 13, 2015 at 7:20 am
On the other hand, schemas can be very harmful if your DBA doesn't know what he/she is doing. I worked for a company where a 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.
Be still, and know that I am God - Psalm 46:10
November 13, 2015 at 7:33 am
I have separate instances for Prod/Dev etc. due to differences in underlying hardware, installed tools, etc. I use databases and schemas in my instance to separate functionality and as security boundaries. For example, my data warehouse uses Data Vault methodology and I have separate databases for staging and data vault and then schemas within them for load staging, staging history, raw dv and business dv.
I also have a separate schema for a data access buss for developers to pull data from the DW and/or Master Data. There are multiple schemas for user data access that contain views organized around topic areas (e.g. finance, hr, accounting). AD groups control access to the schemas and then I use database roles to further refine access to those views (e.g. views with PII or sensitive data).
There are no facts, only interpretations.
Friedrich Nietzsche
November 13, 2015 at 7:37 am
Great topic!
In our Data warehouse projects we use schemas for two purposes:
First (as already mentioned by others) to separate logical areas and at the same time simplify security administration (STAGE, DW, STAR, EXPORT)
We also use schemas to separate standard version managed DB objects from temporary objects needed by developers.
Having a small number of developers it is easy to give each their own schema for this purpose.
If a new instance of a database has to be created it will only include the managed objects.
Brian
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply