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
