April 4, 2011 at 6:35 am
I have been tasked with the opportunity(!) of taking over an entire database application from one of our clients. Basically, their parent company is closing their office but needs to maintain the existing system.
The DB is SQL Server 2008, and the front end is likely a .NET app. There are many other interconnected systems that communicate in some way or other with the main application/DB. We know there is very little to no documentation, but I will be meeting with some of the applications's development team. I am not convinced the developers will be in a wholly helpful mood, though.
Drawing from my DBA experience, I can certainly run some scripts against the DB to gain some familiarity with the various schema items... But I'd like to ask for opinions on different approaches to handling a project of this magnitude. I think it's best to start from a very high level and then dig down into the constituent pieces, but I must admit I am feeling a bit overwhelmed and I think I just need to get organized.
Would anyone care to share their advice/experience/links/scripts/ideas/suggestions on this topic of system "discovery"?
April 4, 2011 at 8:32 am
You might want to check out http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/. This script is from a SQL Server "Microsoft Certified Master" that goes through examining everything on a server that you would be interested in when finding a new server and needing to take control of it - seems to fit your requirements pretty good.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 4, 2011 at 10:10 am
Thank you Wayne, that is helpful and will be part of my toolkit 🙂 That covers the database system...
I was also hoping to hear basic ideas/approaches on the process of discovering all the pieces of a system (e.g. beyond the DB). When faced with a new and unfamiliar system, how should one go about the discovery process, what questions should be asked first, etc.
Maybe there isn't an answer to this, or maybe it's just the obvious approach of pouring through the code of the various pieces...?
April 5, 2011 at 6:24 am
ApexSQL Doc is an awesome product to help you document and understand the database. BIDocumenter from PragmaticWorks can document more than just the database, such as SSIS stuff if there. ERStudio from Embarcadero can reverse engineer an ER diagram for you. Profiler can trace database app calls so you can dig into things that might be causing performance problems. sp_whoisactive is an awesome free script from Adam Machanic. Learn to use its amazing goodness.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 5, 2011 at 2:24 pm
Well, the good news is that you've got your work cut out for you and don't need to worry about getting laid off anytime soon.
In most large proprietary databases with hundreds of objects, a significant percentage of the objects are just obsolete and not even used. One good place to start using a DMV query to list all objects that have been read from, written to, or executed in the past 45 days and then then sort them according the amount of activity. Add them to a database diagraming tool like Visio or the one built into SSMS. Other scripts or tools can easily identify relationships. When you meet with the developers, you'll want to make sure they give you the background on each of these, so you can start adding annotations and relationships to your diagram. I think of this as "database cartography", because you're basically exploring a huge system and mapping your daily discoveries incrementally.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 7, 2011 at 7:55 am
Along with daily usage, you should also ask about quarterly and year-end processes.
Carol Dewar
April 8, 2011 at 3:28 pm
More great ideas... thank you...
Carol... great point about periodic/quarterly/year-end processes. This is something that has bitten me in the past, and certainly something I will need to clarify!
After the first pass, we've found that many parts of the systems "may still be in use", but that no one seems to know for sure, so the devs are reaching out to the business folks and end users. Seems there is very little black-and-white, but rather mostly shades of gray 🙂
Thanks again for all the valuable input.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply