Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


9 Things to Do When You Inherit a Database


9 Things to Do When You Inherit a Database

Author
Message
jeff fisher-291560
jeff fisher-291560
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 366
I inherited not just a database, but about 130 database servers with hundreds of undocumented databases. We have about 115 database servers should have the exact same database but I have found about 115 different schemas even different database names for the same database. Compare the schemas to find out what the differences are and then carefully and with full documentation, implement best practices and then test the crap out of them to make sure nothing else breaks. I also had to fix and document replication. I use Central Management to find other differences and to find out what databases are not doing what they should. Make sure you have backups and then make backups of those backups. I love the having a separate server to test - we didn't have that when I started and yeah, it cause a lot of problems testing databases on production servers.
And learn to live on a lot less sleep for a while. It's been over year and I just found another database server today
dick.baker
dick.baker
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 91
extras for the list ...
1. dependency list. before SQL2008 the sysdepends was incomplete but sys.sql_expression_dependencies should be ok. anyway, when you create that DEV environment try a simple SSMS script existing "with dependent objects" and apply to new temp DEV db. dependencies on target should be correct. usually produces compile fails of junk.
2. DMV to see what QP's are present in cache (i.e. some idea of what's [been] cooking)
3. DMV to see missing indexes (already noted), and busy/idle indexes (act accordingly)
4. Profiler. to identify users, [high] traffic and any deprecated stmts, recompiles, DDL
- but sparingly and don't capture what you haven't time to analyse!
5. if new box, consider changing audit failed AND success logins to see who connects
6. check users to see if still exist. suggest also part of the SSMS to temp DEV
7. logins ditto, checking for orphans. try sys.xp_revokelogin to temp/sandbox instance

+1 for DBCC
+1 for the xxx rename as easy to put back if crisis
- we used xmdd (eg x615) as datestamp to spot stuff could be dropped if 2 months unused (eg Aug15)
adlan
adlan
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 96
Another suggestion from me would be to consider creating foreign key / unique / check constraints if they don't already exist. I initially do this in a local copy of the DB, as I find that it can really help when developing an understanding of the database and testing your assumptions about what links to what. You may even find a heap of orphaned data in the process (or that you have misunderstood something - it can at least be a discussion starter with the business). You mention the built in diagramming tools - these are much more useful if you have all the foreign keys in place. It can also assist in locating 'obsolete' objects - although I would agree with everyone advising caution on this.

After you have created a dev/test environment, creating any missing constraints as part of a bug fix release may help to discover bugs that would otherwise be difficult to find.

It would also be a good idea to review indexes / statistics in the database to make sure that they are appropriate (duplicate/ unused/ missing etc). I like to try and rename indexes and constraints to a consistent naming standard - this can be helpful if you get SQL exceptions while testing, and can help to identify duplicate indexes etc. You should obviously check for things like index hints in stored procs and views etc before you do this.

I also try to run the sp_refreshsqlmodule (http://msdn.microsoft.com/en-us/library/bb326754.aspx) on all the stored procs, UDFs, views, triggers etc. in the database. In older versions of SQL server (SQL 2005) this will update the dependency information in sys.sql_dependencies. Even in the current version it will pick up if stored procs refer to columns that don't exist in existing tables (except if in dynamic SQL). It can also make sure that any user defined types used as parameters are referencing the correct types.

Another useful check is for dynamic sql using EXEC - can you modify to avoid SQL injection...
mattypenny
mattypenny
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 171
Can I just add something that might seem obvious?

Make sure you've got all the passwords! You *might* need passwords for the application, sa, odbc links etc.

Also, try to find out what you have to do to change passwords that the application depends on, if you need to.

May the road rise up to meet you. May the wind always be at your back. May the sun shine warm upon your face,and rains fall soft upon your fields. And until we meet again, May your God hold you in the palm of His hand.
Clive Strong
Clive Strong
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1182 Visits: 6588
Interesting that this one came back around. I moved jobs earlier in the year. Most of the 9 steps were done within the first couple of weeks. As mentioned by others, the dropping of objects is the danger step, but there have been good ideas around this otherwise.
Michael G
Michael G
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 153
Besides adding security review, I'd get another copy of the database running elsewhere and the app - space and resources permitting of course.
If you're following good change control practice - you'd need three copies of the DB running anyway: Development, UAT/Test and Production. A good change control environment makes for good Distaster Recovery failover while you get the main database hardware/software rebuilt.

Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. :-D
Zoran Jankovic
Zoran Jankovic
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 99
"Drop obsolete objects" <-- I have practice to rename it first into for_delete_object_name (for objects which I'm "enough" sure, that could allways be a problem) and wait "enough" time

"enough" is from case to case
rene.westerveld
rene.westerveld
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1821 Visits: 59
For the cleanup of unused objects, does someone have a script or knows a DMV to indicate when a view or function was last accessed?
I've found a script, but this only returns the last_execution_time for Stored Procedures.

Select * 
from sysobjects S
left join sys.dm_exec_procedure_stats X on X.object_id = S.id
where S.xtype in ('P' ,'X', 'FN', 'V')


dick.baker
dick.baker
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 91
the DMV repository gets wiped on service restart, so you'd only see objects that had been touched in current session, hence I suggest you don't rely on sys.dm_exec_procedure_stats (or any other). AFAIK the only way to tag usage would be some audit trigger or Profiler (or WMI) external mechanism.
mark hempen
mark hempen
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Also don't forget to check out what user account owns the stored procedures and the security role attached to that login. I have had many instances in the past where dbo priveleges were necessary and didn't exist.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search