Monday, March 19, 2012

Database Documentation

Hi guys, my manager asked me that to do documentation; right now we are using MSSQL SERVER 2005 Databases, here you assume I am new to make documents on any database but good idea about databases currently that data warehouse in development environment need to done this documentation before go on test environment . So that way you advise me to prepare a document.

Here is my main concerned how to start and what things should we keep in mind while preparing document (what are measurements).if you any prepared documents could help to start or there tool we should use (power point) them. any help appreciated

Things to include in DB documentation, and some suggested sources:

1.Server where DB is implemented; SQL Server version used, and required (for example SQL 2005 SP1 because of fix to XXX).

2.Database design - in an ideal world, you should have a ERD for a

database design before the database was created. This may have been

done in an external tool like ERWin, Rational Rose, PowerDesigner etc.

etc. You can also use the Database Diagram tool in Enterprise manager

(sql 2000, not sure about SQL 7.0) or SQL Server Management Studio

(SQL2005) to create these diagrams. The physical DB model (or

design, or ERD, - whatever your preferred name), is, imho, the single

most important piece of documentation about a database;

3. Volumetrics - this is a field on it's own, and one that is often

largely ignored (until a problem occurs!). This is about predicting the

sizing of a database, so as to ensure that the production server has

sufficient capacity when initially created, and sufficient space during

the database life. Since space addition can be a difficult/time

consuming activity (for example, ordering disks, taking server off-line

to add them, or getting drives purchased and added to a SAN and

allocated to the LUNs for you server, if you have a SAN), you really

want to know how much space you need, and when you are going to need

more. There are 2 large sections to this: predictions based on

assumptions, and forecasts made on trend analysis.

- predictions are done during design and creation phase. For example,

the business should have some kind of idea about the viability of a

system. In that, they would, hopefully, have some kind of expectations

regarding usage - for example: We expect 2000 orders a day, typically

of an average of 13.5 products. That already tells us we can expect

2000 rows in our order table, on a daily basis, and 27000 rows in the

OrderItem table. Extrapolation that further, based on 21*12 working

days a year, and we have a prediction of 504 000 rows in the order

table at year end, and 6 804 000 rows in the OrderItem table. This kind

of information can be used to generate an estimated size for the

database, so we can ensure that the database will have enough space on

the brand new server the business bought

- forecasts are done on an on-going basis and are a pro-active measure

by the DBAs in production to determine real growth of a database. So,

after the first month, for example, the dba's note that the ORder table

has 60000 entries, and the Orderitem table has 900 000 rows. Based on

that, they can forecast that the year end sizes will now be 720 000

order rows, and 10 800 000 OrderItems. The goods news is that the

business is doing 42% more orders, at that point in time, that

predicted (see the prediction above), and has sold 58% more Items. They

can use the extra money to buy some disk space, because the forecast

says that at 60000 order per month, the DB is going to be the size that

was predicted for year-end, after less than 9 months (9*60000 = 540 000

> 504 000, for example).

I haven't covered a discussion about the increase in volumes on the

underlying server resources (for example, are the batch jobs able to

finish processing within the agreed SLA's, now that we are doing 2857

orders per day? Do we have periods where this causes excessive load on

the server? These things are NOT (imho) part of a DB document, but they

do flow out of the forecasts that are being done. A final comment on

forecasts above is that, when they are done regularly, you can also do

trend analysis - this is use for technical people - for example, orders

are growing by 10% each month, so in fact that 9 month prediction is

too far away - it needs to be earlier

(60000+66000+72600+79860+87846+96630.6+106293.66=569230 after 7 months).

4. Access - who needs access, to what. For example, Users required, and

which stored procedures they need to execute. Hopefully, there is

little of no dorect table access, but that can also be listed. This can

be done in a matrix in excel:

User -> UserA UserB

Object
TableA - S

TableB - -

SP_1 - Ex

SP_2 - Ex

SP_3 Ex ExGr

SP_4 Ex -

And a key that defines the meanings:

S = Select (D=Delete,U=Update,I=Insert)

Ex = Execute

ExGr = Execute with Grant

etc. etc. These are examples - create as seems relevant.

5. Maintenance. what is the DRP strategy - do you have off-site

backups, how often, are they tested? Do you do regular maintenance (db

indexdefrag's etc) and if so, are they maintenance plans, or specific

jobs?

depending on the target audience for the document (for example someone

using it to rebuild servers in a disaster), you could consider adding

reference to the Source Control system, so that if the database needs

to be rebuilt, they know where to get relevant code.

hmm - well that was off the top of my head. Hopefully it gives you a

good palce to start. I'd be interested to see the additional items

other people suggest.|||

You might want to check out SqlSpec. It will generate documentation for any SQL 2000 or 2005 database. It's very reasonably priced at $50, a fraction of other data dictionary software out there.

see www.elsasoft.org for more info.

|||Interesting. It does seem quite useful and covers much of what I listed|||

You might be interested in checking out dbdesc as well. This tool documents SQL Server databases and you can fully customize its reports as it uses XSL templates to generate the final files. It has built-in templates to generate Word 2003, RTF, HTML, XML and PDF reports.

It was reviewed by Mike Gunderloy (Larkware News) here.

|||

Hi, Geth. Have you gotten everything you need on this subject? If so, you can close the thread, if not, let us know and we'll try to give you some more help.

Buck Woody

|||ERm. I'm happy, but I wasn't the thread starter. Can I cl,ose someone else's thread?|||

You're right! My bad. I'm not sure if you can mark it "happy" or not. I'll ask the right person this time...

|||

Hello - did you get everything you needed? I have some more info on building your own documentor here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=108&rl=1

If you've found these answers to be helpful, you can mark this thread as "answered". Thanks!

|||IAM Happy we can close this thread

No comments:

Post a Comment