February 14, 2016 at 7:54 pm
Muhanned Maayeh-445298 (2/13/2016)
I do agree with your overall point however, I am playing the DBA role in this project. So, I will leave these "business" decisions, directions, and requirements to the Product Managers and Project Managers of this project. As far as my DBA role, I received these requirements from the project owners and in so doing I advised the team that database redesign will be performed in order to incorporate the various product lines into one database and reorganized for better database performance and so forth, and to have this as part of the project planning. So, here goes at this point.
If you were truly playing "the DBA role" on this project, then you'd understand that "business" decisions are frequently contrary to what is good for the database, the server, and the project in general and that it actually IS your job to help them make the correct decision instead of taking such a DILIGAF attitude about it all.
For example, why has someone decided that separate "DISTINCT" products need to be in a single database? And, if that's what you're going to do, why put them in separate schemas, which is nearly as painful codewise as having them in separate databases? What makes anyone think that supposed "common" objects are going to actually serve a "common" purpose when the products are in different schemas never mind separate tables?
And why does anyone think any of that is going to make for better performance? So called "partition elimination"? I don't bloody well believe so.
Then there's the problem of backups, restores, and DR. With the products in separate databases, you had smaller individual backups and faster restores. If one product database went down, only it went down instead of the whole shebang. It's not like you're paying extra licensing or having to come up with additional disk space just because you have more than 1 database. And the databases can easily communicate through things like synonyms, pass-through views (although not where DRI is concerned), and partitioned views.
Yeah, yeah... you only have to promote code to one database if the databases are combined but then you also have to be real careful that what's good for one [font="Arial Black"]DISTINCT [/font](your word) product doesn't affect other products that it might be bad for.
I'll admit that I don't know a whole lot about your particular situation but first blush understanding suggests that someone make a hasty decision without much consideration for some of the things I spoke of (especially backups, restores, and DR) and everyone is nodding their head without giving it all much thought.
If I were the acting DBA on this project, I'd suggest that people slow down and consider the things that they've not yet considered and it would definitely be part of my job to tell them the things they've not considered.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2016 at 11:18 pm
Jeff Moden (2/14/2016)
Muhanned Maayeh-445298 (2/13/2016)
I do agree with your overall point however, I am playing the DBA role in this project. So, I will leave these "business" decisions, directions, and requirements to the Product Managers and Project Managers of this project. As far as my DBA role, I received these requirements from the project owners and in so doing I advised the team that database redesign will be performed in order to incorporate the various product lines into one database and reorganized for better database performance and so forth, and to have this as part of the project planning. So, here goes at this point.
If you were truly playing "the DBA role" on this project, then you'd understand that "business" decisions are frequently contrary to what is good for the database, the server, and the project in general and that it actually IS your job to help them make the correct decision instead of taking such a DILIGAF attitude about it all.
Actually, I do not have a DILIGAF attitude as I provided the risks and disadvantages in the early meetings however, it seems like the "project owners" have already decided on this technical direction despite this so, this is why I am stating I am leaving the "business" decisions to the "project owners" because I have to deal now with the technical problem. So any assistance in this direction would be highly appreciated.
For example, why has someone decided that separate "DISTINCT" products need to be in a single database? And, if that's what you're going to do, why put them in separate schemas, which is nearly as painful codewise as having them in separate databases? What makes anyone think that supposed "common" objects are going to actually serve a "common" purpose when the products are in different schemas never mind separate tables?
The decision to combine databases was made for internal business reasons. As far as using separate schemas then, I posed this question should this be used or not and what other options are recommended that I did not consider. So, if separate schemas are used then what are the advantages vs. disadvantages or what other options should be recommended. Yes, there are common objects because the architecture of the systems lends itself to common objects such as user definitions or system settings. So, I can try to unify the database objects. Perhaps the word "DISTINCT" is inappropriate in this case.
And why does anyone think any of that is going to make for better performance? So called "partition elimination"? I don't bloody well believe so.
Actually, I never stated it was for performance or "partition elimination" but, I suppose others have tried combining databases due to these reasons.
Then there's the problem of backups, restores, and DR. With the products in separate databases, you had smaller individual backups and faster restores. If one product database went down, only it went down instead of the whole shebang. It's not like you're paying extra licensing or having to come up with additional disk space just because you have more than 1 database. And the databases can easily communicate through things like synonyms, pass-through views (although not where DRI is concerned), and partitioned views.
Yes, I agree in fact that was one of my points I raised early on. So for my design is there points I need to consider for the Backup, Restore, and DR. Also, what suggestions would you offer on disk sizing and backup for such a case.
Yeah, yeah... you only have to promote code to one database if the databases are combined but then you also have to be real careful that what's good for one [font="Arial Black"]DISTINCT [/font](your word) product doesn't affect other products that it might be bad for.
Well, I will have to be careful as there will be common objects so, perhaps DISTINCT is not the proper statement here as previously stated. Change management will be crucial here this is a disadvantage when combining databases for sure as changes to one system may impact other features.
I'll admit that I don't know a whole lot about your particular situation but first blush understanding suggests that someone make a hasty decision without much consideration for some of the things I spoke of (especially backups, restores, and DR) and everyone is nodding their head without giving it all much thought.
Well someone made the decision and not all of us including yours truly did not just sit around and nod heads but, presented alternatives to keep the databases separate and pointed out worst case scenarios that may and will occur. However, this risk was deemed acceptable never the less.
If I were the acting DBA on this project, I'd suggest that people slow down and consider the things that they've not yet considered and it would definitely be part of my job to tell them the things they've not considered.
I did that however, due to business imperatives the decision is final.
So, if you have any recommendations on design pitfalls, advantages vs. disadvantages, best practices, etc. for technical case of combining databases for myself or others who find themselves in this situation?
February 16, 2016 at 7:44 am
Muhanned, sorry if we have given you a hard time. It looks like you are very much aware of the issues being raised and are trying to make the best of the direction you have been given.
Sometimes we do get people on the forum wanting to take a particular technical approach without considering the business implications or having their backing, which is why I raised the issues I did.
I hope your redesign goes ahead smoothly.
In your situation I would either use one schema, or have separate schemas based on function (eg Sales, Manufacturing, etc). It is also worth thinking about overall database size and criticality of data. It can be worth separating segments of the overall application into separate databases (eg Online ordering and offline decision support). Decisions about how many databases should in part be guided by DR and recovery time objective. It will always be quicker to restore a 50GB sales/ordering DB and get your website running again than to restore a 2TB sales/ordering/decision support DB. Also, if you plan to use replication of Availability group mirroring, there will eventually be problems where you need to re-initialise the downstream databases, so being able to isolate the really critical stuff can help in these situations.
Ultimately, the business people neither understand or care about the details of database design. They just want it to work, and they want you to have the gift of foresight and have done the design and implementation and resilience and security yesterday.
The thing the business people will listen to is discussions about money. Tell them putting all your eggs into one basket would add 12 hours to website downtime in a DR situation and they will support you separating ordering and support into separate DBs. If you just tell them separating the DBs will make your life easier they will not care.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 16, 2016 at 11:04 pm
EdVassie (2/16/2016)
Muhanned, sorry if we have given you a hard time. It looks like you are very much aware of the issues being raised and are trying to make the best of the direction you have been given.Sometimes we do get people on the forum wanting to take a particular technical approach without considering the business implications or having their backing, which is why I raised the issues I did.
I hope your redesign goes ahead smoothly.
In your situation I would either use one schema, or have separate schemas based on function (eg Sales, Manufacturing, etc). It is also worth thinking about overall database size and criticality of data. It can be worth separating segments of the overall application into separate databases (eg Online ordering and offline decision support). Decisions about how many databases should in part be guided by DR and recovery time objective. It will always be quicker to restore a 50GB sales/ordering DB and get your website running again than to restore a 2TB sales/ordering/decision support DB. Also, if you plan to use replication of Availability group mirroring, there will eventually be problems where you need to re-initialise the downstream databases, so being able to isolate the really critical stuff can help in these situations.
Ultimately, the business people neither understand or care about the details of database design. They just want it to work, and they want you to have the gift of foresight and have done the design and implementation and resilience and security yesterday.
The thing the business people will listen to is discussions about money. Tell them putting all your eggs into one basket would add 12 hours to website downtime in a DR situation and they will support you separating ordering and support into separate DBs. If you just tell them separating the DBs will make your life easier they will not care.
Ed, thank you very much for your understanding. Yes, I can see there might be individuals want to try something cool with technical approach on an expensive system but, I would think you should do this on your home laptop on a dummy system(s) as a hobby before touching expensive business systems, let alone business systems development environments, or in our case restructuring the design of product lines.
I will try to review what was provided on this post and break the problem down further to assure a proper design.
Thanks all for your input and insights; this was very helpful.
February 16, 2016 at 11:57 pm
@Muhanned,
I agree with Ed. I ran you through the whole gambit and you've not only stuck to your guns but you clarified what you did to try to make sure that your business people made the correct decisions. Definitely not a bad attitude. I still don't think that they have made the correct decisions and, perhaps, you might not either but you've resolved yourself to doing the best you can to protect the data, the server, and the business people.
Well done. Seriously. My hat's off to you.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2016 at 12:12 am
Jeff Moden (2/16/2016)
@Muhanned,I agree with Ed. I ran you through the whole gambit and you've not only stuck to your guns but you clarified what you did to try to make sure that your business people made the correct decisions. Definitely not a bad attitude. I still don't think that they have made the correct decisions and, perhaps, you might not either but you've resolved yourself to doing the best you can to protect the data, the server, and the business people.
Well done. Seriously. My hat's off to you.
Hi Jeff,
Thank you for the kind words. Yes, it is going to be a technical challenge for myself and my fellow project team members. As I equate this to the proverbial "Take this square peg and somehow make it fit into this round hole".:-) But we are here to get the job done nevertheless.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply