When first starting a new job, how do you catch up to speed with DB's and Applications

Question

When first starting a new job, how do you catch up to speed with DB's and Applications

koln

SSCrazy

Points: 2189
More actions
January 11, 2008 at 10:19 am

#121771

Every time I've started a new job, I've somewhat hacked my way to learning the data structures and applications that sit on the databases.
I would love to hear best practices on what kind of tools and methods people use to catch themselves up to speed when first starting a new job.
Thank you

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 1

I would definitely suggest some kind of database modeling tool, which would allow you to reverse-engineer databases, and provide you with a graphical model of what's going on (tables, relationships, etc.).

There are several options for software that does database modeling, and choosing the right one will probably depend on your budget, and also your rdms.

You can search through the forums here for past discussions on database modeling software, and of course Google as well, for the appropriate software.

Good luck!

-Simon Doubt

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Heh... It's amazing to me how many undocumented databases there are in the world. It also amazes me how many there are with no DRI... no DRI means no modeling or reverse engineering tool in the world will help because the database doesn't know what relates to what unless you get lucky enough to find a database with a correct naming convention... yeah... those grow on trees, too.

Of course, there's always the wonderful documentation that most people put in stored procedures... ummmm... guess that's more trees.

If you're totally out of luck with DRI, naming conventions, and documentation, and I don't know if such a thing exists, but try to find a reverse engineering system that can create a datamodel based on the joins found in stored procedures... if such a thing does exist, it'll likely cost a pretty penny...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 3

I'd run Profiler, grab the most often run queries, and start dissecting what they mean and how the data relates. Most often run queries mean you'll deal with that data a lot and it also helps you to see where you might want to start performance tuning.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Now, that's a pretty good idea. Thanks, Steve.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 5

Anytime. I am usually busy, so I don't worry about the stuff that isn't being queried. Who knows how many tables are old, never deleted, out of date, etc. Same for other code, always good to concentrate on the stuff that matters, which is usualyl what's being used.

Richard Gardner-291039 Hall of Fame Points: 3603 More actions · Answer 6

I have no idea if this is any good on SQL server, I think it's a Beta, but when I first got an Oracle Database it was installed with Toad, and what a joy it is (not too hard to improve on SQl plus, in truth). As Jeff says, if there's no DRI it won't be much help, but lacking this particular experience I am going to opt for foolish optimism and state that surely NOBODY could possibly be stupid enough to implement a production system without it.

http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm

It's a pretty good modelling tool, and it does a load of other stuff too, in fact I'm going to put my money where my mouth is and install it on my SQL server right now.

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 7

TOAD has been a load on workstations before for me, but it's a good tool. I have lots of Oracle/DB2 friends that love it.

Embarkadero has ER/Studio that's a good reverse modeler as well.

Robert W Marda SSChampion Points: 13413 More actions · Answer 8

I learned about how tables relate to each other at my current job by talking with people who were here before me and modifying existing SP's to add new features.

Our existing production environment has almost nothing to help someone figure out how tables relate to each other. And so looking at SP's or talking to someone who knows helps a lot. And there is very little documentation for anything in our production environment.

I'm going to remember about using profiler for whenever I end up changing jobs (I've been with my current employer for just over 7 years and don't know when I'll feel the need to move on).

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Paul Mu SSCertifiable Points: 5547 More actions · Answer 9

Speak to the System Admin guy and see what tools he use for monitoring the network and how much of that is useful for the DBA.

Use a Monitoring tool specific to SQL Server to capture things relating to replication (if it exists), stored procedure statistics, etc. The statistics will help you to resolve potential replication and performance issues with stored procedures. In a sense, you are getting an overall picture of what is happening with your system and hence some control over it.

Lastly, see if there are any documentation lying around that might help you get up to speed with working in this new organisation.

Paul

koln SSCrazy Points: 2189 More actions · Answer 10

Great responses, thank you all for your input.

Lets say I now know how the tables talk to each other, but what about when an application sits on top of a SQL Server database, how do you guys figure out when User A enters $50,000 in his/her application, what field in the database it resides in ?

I usually dont have access to the backend code and lets say there is no cross-reference documentation available....Im assuming I can use profiler to monitor where in the database the $50,000 are entered, right ? Any other ways ?

Richard Gardner-291039 Hall of Fame Points: 3603 More actions · Answer 11

Hmmm... That's probably not the best way forward, at least not for an overview, Steve's point about the most common queries being the most illuminating is certainly valid, although it might generate too much information.

So you're essentially looking to map the front end screens to the data they manipulate then - in which case you're probably going to need the application documentation.

If you're very lucky the screens will be based on views, if you're even luckier the transactions will all be encapsulated in stored procedures. If this is the case then the developers are good and you can count on a certain amount of consistency in naming conventions and such like.

Another way to reverse engineer applications is to have a good look around the table structure - it is highly unlikely the table names won't bear any relationship at all to their function. If you think about screens in terms of their function you'll probably find each screen only relates to a very small subset of tables which relate to a chunk of business logic. Steve's advice to look at profiler in this case is good, you can try a test transaction/query through the screen and see what occurs, although in this instance I'd find out as much as I could about the bit of logic I'm trying to work out then ask someone - there are very few tecchies who don't enjoy teaching things to people who are interested, and you'll find out who specialises in what part of the system.

In my experience there are few applications that do not have some kind of "tell tale" which tells you which fields map to which table, even our custom apps do this, usually help screens.

Of course, your original workflow documentation would be useful here, if you can find it. I always map workflows down to SQL, it being the most succint method of describing your business logic, but some BAs I've come across don't seem to understand the relationship between SQL and business logic, they leave the entire workings down to the developers, whose approach to documentation and structure is variable at best.

So, to sum up :

1) Is the business logic encapsulated in stored procedures?

2) Are the screens based on views?

3) Is there a help button?

4) Is there existing documentation?

5) Is there a decent naming convention?

6) As a last resort - try stuff and see what happens in profiler.

einman33 SSCrazy Points: 2813 More actions · Answer 12

If you are supporting a B2B (or even B2C) app, many organizations hold user level training for these application. I have attended these sessions and come away with very valuable information on how the end user interacts with their data. Ask your manager if they can get some time for you to attend these sessions. I am always amazed how much of the domain data can be gathered from listening to the trainers. Weird I know, but it has always helped me get up to speed on a schema.

If no application specific training, have the development group set you up a local copy of the application system your supporting with sanitized local data(this is also not a bad opening project, can be time consuming). Then have them show you how to step through the data access layer. This will truly be a shock and awe experience, as you witness 1000 literation of the same proc call. 🙂

If none of these work for you and none of the other posts are doable, good luck my friend!!! Enter the DBA safari, we all have been there!! Your weapons are a nested join and an index seek!