March 9, 2009 at 2:47 am
Hi,
I'm newly employed as DBA in a company, in which it's 4 years that software projects are developed. Actually, there wasn't any accurate DB design in any of those projects. so there are many redundancies, bad design, same stored procedures/views in functionality with different names, etc. There are 50 servers serving projects data in our companies
Now I'm asked to start my job in reviewing all those databases and tell developers where they have written same stored procedure with another one in another server, etc.
Besides, I have to start my job in controlling all changes to Database to prevent bad design from now on.
The thing worries me is how to do all these stuff and how to remember changes in all databases.
please let me know if my question is not clear.
Any idea is appreciated
March 9, 2009 at 3:37 am
What if you do finetune the development things like SP, Triggers, Business logics/Rules etc... does it improve the performance???
I guess if everythings are scattered and not maintained; it's always better to start with Database Design.
Database Design is the base on which all your Development stuff will stand so I guess this is the first stape for the performance improvement.
Correct me if I missed anything.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 9, 2009 at 3:53 am
Thanks for the reply:)
I'll start tuning stored proc, etc.
However in reviewing database designs I think I need to know projects well and since the number of projects are high, I'd like to know if there's any better solution or I have to start step by step and learn projects then review the related DB design
March 9, 2009 at 6:07 am
I would start off by getting the company a data modeling tool. Reverse engineer all the databases and then sort them in the diagram by purpose. We have a database diagram for our main DB that is entirely color coded so we can tell what sets of tables do what.
Part of this process will involve not only learning the databases' schemas but their functions and why the developers designed them the way they did. While you're doing this, you'll be able to clean up procs / views / other objects that are no longer used. And believe me, that part will save you a lot of headaches later on. However, before deleting, it's always wise to rename incase something depends on that object.
Another good idea is to use the system views to locate dependencies on various objects. A data dictionary for each database would be ideal. It takes a great deal of work up front, but it can save your life if you do the dependencies list correctly.
While all this is going on, develop a list of standards you would prefer to see in these databases. Get your boss to sign off on it (naming conventions, etc) and pass it around to the developers. Tell them that going forward, this will be the new standard. And everytime they modify something old, they have to convert the old object to the new standard.
These suggestions will put you in a position of knowing what you're dealing with and give you the ability to find the truly duplicated stuff a lot faster than if you just pick & choose what stuff to look at.
March 9, 2009 at 6:15 am
That was a helpful comment thank you Brandie 🙂
March 9, 2009 at 6:19 am
That was a helpful comment thank you Brandie 🙂 I'm using enterprise architect for reverse engineering
The other thing I'm thinking of is that databases are being modified daily. So when I'm finished with reviewing DB Design of the 50th server there are many changes done on the 1st server which makes all my knowledge about that DB and documents out dated and useless. Do you have any suggestion for handling updates in such situation?
March 9, 2009 at 6:27 am
If the databases are being modified daily, there is something seriously wrong with the development process. There should be an SDLC (software development life cycle) process in place which forces your developers to go through IT testing, BU Testing and QA Testing before anything ever reaches production.
And there should be NO reason why the schema has to change daily. Yes, there will be new stuff that comes down the pipeline, but I can't see it happening on a daily basis. The developers should be able to leverage existing objects for future development.
This sounds like a training issue to me. As in the developers need actual database development training with a best practices guide. Maybe you need to get them into a room now and explain to them your thoughts on database design, what to use, what to avoid, etc. Then force them to go through an approval process and pick apart all their changes until they start re-using stuff. An SDLC process would assist you with this, preventing you from having to go all reactive on everything because it would give you breathing space.
March 31, 2009 at 1:25 am
Yes Brandie,
Nice suggestions!!!
It's of no use you are tunning the SPS' where DB designs changes often.
Also I understand that they do not have proper environment like Production, Staging and Development.
Cheers!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply