August 12, 2015 at 12:32 pm
I have a enterprise system that comes with a SQL Server Database. I add functionality to this system by creating my own tables, views, functions, etc. and adding / modifying the .Net based front-end with these database objects.
I prefer to add these database objects to the enterprise system's database with as schema/naming convention, but my co-worker wants to use a "sister" database with our custom tables. He thinks the enterprise database is growing too large (~100GB right now)
I'm trying to come up with a list of pros and cons for each approach
Single Database Pros:
Automatically replicates to our test environments
Single Back-up and restore (if one DB fails, they could be out of sync)
Allows Foreign Key relationships
Allows indexed views
"Sister" Database Pros:
Custom data separate from "Out of the Box" data (we need to send a copy of our enterprise database to the vendor periodically for trouble-shooting)
Does not add size to the enterprise database
Can actually test custom dev separate from the enterprise system
What I am missing?
Russ
August 12, 2015 at 12:54 pm
busraker (8/12/2015)
I have a enterprise system that comes with a SQL Server Database. I add functionality to this system by creating my own tables, views, functions, etc. and adding / modifying the .Net based front-end with these database objects.I prefer to add these database objects to the enterprise system's database with as schema/naming convention, but my co-worker wants to use a "sister" database with our custom tables. He thinks the enterprise database is growing too large (~100GB right now)
I'm trying to come up with a list of pros and cons for each approach
Single Database Pros:
Automatically replicates to our test environments
Single Back-up and restore (if one DB fails, they could be out of sync)
Allows Foreign Key relationships
Allows indexed views
"Sister" Database Pros:
Custom data separate from "Out of the Box" data (we need to send a copy of our enterprise database to the vendor periodically for trouble-shooting)
Does not add size to the enterprise database
Can actually test custom dev separate from the enterprise system
What I am missing?
Russ
First thought is that if it belongs to one database then keep it in there. Sizes can be mitigated with files and filegroups, non volatile objects wouldn't change so distract those sizes from the bulk, partition might also lower the pressure. The complexity implied with scaling out on the number of databases does more than counter any benefits of doing so.
😎
August 13, 2015 at 5:48 am
I also lean towards putting it into a single database. The one question I have there is will you be violating a license agreement. Some vendors get quite put-out if you modify their databases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2015 at 10:47 am
Grant Fritchey (8/13/2015)
I also lean towards putting it into a single database. The one question I have there is will you be violating a license agreement. Some vendors get quite put-out if you modify their databases.
I wouldn't say they actually enjoy it, but they understand that we have needs that can only be addressed by customization ... but we only add to the schema, never altering the out-of-the-box setup. There is nothing in the S.L.A. that speaks to it.
For example, I have certain constants that vary by zip code, but the software only supports a single setting. Thus I have created a Zip Code table and associated settings and altered the logic to pull from my custom table instead.
August 13, 2015 at 11:31 am
If they don't have an SLA that prevents, then I'm all for going into the single database for stuff that belongs in that database for all the reasons you list.
I would recommend setting up a mechanism so you can develop and test separate from the enterprise system. That one weakness shouldn't be one you let exist. I'd say that would be a show-stopper.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2015 at 1:50 pm
I'm going to do something I typically don't do and disagree with Grant and Erikur. Unless the vendor has specific policy that states that there are specific schemas or naming convention that keeps an upgrade from wiping out your custom objects I'd keep them in a separate database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2015 at 2:14 pm
The upgrades are incremental from the vendor. They don't recreate the database, only add / alter objects. The only risk would be if the vendor introduced an object that was named like one of my custom database objects, which is extremely unlikely due to my unique naming convention.
Jack Corbett (8/13/2015)
I'm going to do something I typically don't do and disagree with Grant and Erikur. Unless the vendor has specific policy that states that there are specific schemas or naming convention that keeps an upgrade from wiping out your custom objects I'd keep them in a separate database.
August 13, 2015 at 3:11 pm
busraker (8/13/2015)
The upgrades are incremental from the vendor. They don't recreate the database, only add / alter objects. The only risk would be if the vendor introduced an object that was named like one of my custom database objects, which is extremely unlikely due to my unique naming convention.Jack Corbett (8/13/2015)
I'm going to do something I typically don't do and disagree with Grant and Erikur. Unless the vendor has specific policy that states that there are specific schemas or naming convention that keeps an upgrade from wiping out your custom objects I'd keep them in a separate database.
He he, my name is not the most common but still I've come across table names (in a world far far away....) with the same name, not placing my bet on this.
😎
August 13, 2015 at 3:23 pm
Eirikur Eiriksson (8/13/2015)
He he, my name is not the most common but still I've come across table names (in a world far far away....) with the same name, not placing my bet on this.
😎
Damn ... how did you know I prefixed all of my objects with "Eirikur_"?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply