June 22, 2009 at 1:51 pm
When I became the Lead DBA for new big company I discovered most of the databases were not backed-up at all, some 30% were never making it to tape and noone ever re-indexed. Users found fault with SQL Server across the organization and the universal opinion was that only small projects could use it because it was inferior.
My job took 3 primary tasks:
1) get the darn things backed-up and sent to tape
2) discover the offending databases; especially those with universal exposure
3) evangelize SQL Server.
Here's how I did all three.
1) disable all those vendor created maintenance plans, disable the veritas client that mostly didn't work, start sending the backups to a standard directory (dbbackups) and call a tape policy from a SQL Job.
2) run sp_who2, profiler, filemon, sysmon (in a 2000 environment at first) and trap the bad guys. restructure the bad guys by adding file/filegroups and dumping big tables and new non-clustered indexes to the new filegroups.
3) when they accuse SQL Server of being a dog, challenge the claim. Say "Hey, let's look at SQL Server and see if it is having a problem. Nope, no errors in the logs, no blocks, no backed-up disk I/O, or spiking CPU". we might need to investigate elsewhere. What do you think?"
Now my 100 plus servers are running quietly, never give me alerts, all systems are fully recoverable and I spend my time reading over-written articles by people with less experience.
June 22, 2009 at 2:29 pm
Before doing any of the 9 steps, I would actually consider, as the first step, if you even want to take ownership of the database. Under step 1, The first thing I would do is look at how bad it is. If it's really bad then the second step should be is how to cleverly get this responsibility handed off to someone else if possible.
June 22, 2009 at 2:40 pm
Very funny, Ken! Be sure to get an early start on the blame-storming!;-)
June 22, 2009 at 3:54 pm
Good points, Sylvia, that many including me would not have thought of.
Another to add to the list: Check the location of data and log files and separate them if necessary. I've inherited many a database where they are both on the same drive.
Also if set to "autogrow" make sure it is a reasonable value like 20% or more rather than 1 mb for a large database that grows by hundreds of mbytes per day.
- Paul
http://paulpaivasql.blogspot.com/
June 22, 2009 at 4:59 pm
I could not resist. Humor is my only remedy for the database nightmare I inherited 5 years ago.
June 22, 2009 at 11:02 pm
most of the points mentioned are useful, but dropping a least used stored procedure or table causes a lot of problems, before dropping either of them its needed to be well judged whether to drop it or not.
and if possible i have a request to have a similar view on administration part also.
June 23, 2009 at 6:25 am
don_goodman (6/22/2009)
...I spend my time reading over-written articles by people with less experience.
I must believe you're not referring to this article, or I misunderstood your post.
I think the community gets richer with ideas from everyone.
For sure it gets more from this article than from spot comments from "the real experts" that are reluctant to share their knowledge.
I found this article very interesting and valuable. I look forward seeing the "second part" that includes all the suggestions from this discussion.
On the deletion of old objects I can only say that I had to code a tool to scan all db objects and applications code to find candidates for drop. I usually move the dead objects to the "tmp" schema and then I delete all after 6 months.
Regards
Gianluca
-- Gianluca Sartori
June 23, 2009 at 6:38 am
Glad to see that back-ups and research are the first two things mentioned when inheriting a DB. Those are both extremely important.
I was in a situation where I inherited a database and the very first thing I did was to write process documentation on how to use the thing! This is especially important if your database is intended for end users, which mine was.
I was left with a DB from a guy who was based out of Michigan (I'm in Connecticut) who up and left the company with no notice, so it was painstaking and time consuming but you can absolutely learn the logic/process behind other peoples' code, even if you can't talk to the developer(s).
June 23, 2009 at 5:39 pm
Although this article is about what a developer should do when s/he inherits a database instead of a DBA should do, here are few more things I would suggest:
Health and protection of the database:
a) Backup regimen - already mentioned in the article - absolutely correct. Talk to the DBA about it.
b) Database Checks (DBCC CHECKDB, Statistics Update, Index Reorganise and Rebuild) - talk to the DBA to see if these are already in place. if not, ensure they are implemented.
c) Database security - work with the DBA to ensure only relevant users are in the database.
Internal workings of the database:
a) Try to find the ER diagram of the database. If not present, when you have a copy of the database in the development workstation or a development server, try using the SQL Server's built in-tool to generate one for you.
b) Most important thing would be to check dependencies. Although a tedious process, try to undertake it to see what stored procedures are calling what tables etc.
c) To help in the step b) above, try to use a profiler (if possible in production) with in-built trace templates like TSQL_SP etc. to check the most commonly used stored procedures and queries being thrown at the database. This can help identify key queries. This approach is an iterative process.
External dependecies of the database:
a) Try to find all the linked servers, all the users/logins, all the jobs, DTS/SSIS packages, reports, ODBC connections and application connection strings etc. associated with the database. Just like the inner working of the database, these are also needed.
Query Optimisation
a) If the database is in SQL 2005 or later (fortunate enough), run the SSMS built-in reports on the database to see the database object properties (top tables etc.)
b) If possible, run several iterations of the Profiler trace template for tuning during production data processing. Run these trace loads through Database Tuning Advisor and see if any improvements can be made by implementing indexes, new stats etc. This will be a pro-active task.
June 23, 2009 at 5:53 pm
One thing I forgot to mention above is that whatever the findings, one should always DOCUMENT them, preferably in a consistently formed list of documents and make those documentations VERY visible to the company (e.g. DBA, development teams, management etc.). That way, there will be a reference point for others when you leave.
Why the documentation?
a) For knowing your system better. Although you will be responsble for other databases and chances are that you will not have enough time to "know" your inherited database, you may want to spend some time if the inherited DB is classed as a critical system. The more you know the inner working, the better it would be to troubleshoot any query problems etc.
b) For future migration purpose. If you are upgrading from SQL 2000 to say SQL 2005 or 2008 Enterprise and you know about the table that has 5 billion+ rows (which you know causes queries to run slow), you can easily suggest / implement partitioning or at least filegroup stratgey. The same holds for external components - people need to know what jobs or DTS /SSIS packages also need to be moved when a database is migrated from server to server. Documentation serves its purpose during those times.
June 25, 2009 at 4:43 pm
Jon Monahan (6/22/2009)And I'm a big fan of the idea of renaming objects - and keeping them for a year (if you just inherited the database and/or server). Too many objects get used for one tiny, but critical, app that gets run once per year... No point in taking any chances. After the year is up - feel free to delete all of those renamed objects.
Well ... renaming tables? Sure. Renaming views, stored procs and UDFs? Why? You have them in the source control, don't you?
Jenda
August 18, 2009 at 5:25 am
Profiling the database would also be a good idea - get an general idea of performance. Cross reference this when speaking to the clients to allow you to formalize tuning plans.
August 18, 2009 at 12:20 pm
Also, profiling the people who handed it off to you will give you a good indication of the condition of the database.
January 13, 2010 at 2:11 pm
I would strike the word delete from this list and add DBCC CheckDB, alerts for fatal errors and error 825.
February 26, 2010 at 4:03 am
Perform Impact Analysis - which database objects are used and find out a dependency chain.
DO NOT rename database object with prefixes/suffixes - add your comment to dedicated key in extended properties.
Well documented dependency chains allows you to map client requests to db table/table column/view/view column/sp/udf.
This is A MUST for system enhancements.
Viewing 15 posts - 31 through 45 (of 83 total)
You must be logged in to reply to this topic. Login to reply