Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

9 Things to Do When You Inherit a Database Expand / Collapse
Author
Message
Posted Thursday, June 14, 2012 9:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 24, 2014 12:11 PM
Points: 14, Visits: 263
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
Post #1316277
Posted Friday, June 15, 2012 1:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 25, 2012 4:09 AM
Points: 44, 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)
Post #1316355
Posted Friday, June 15, 2012 3:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:13 AM
Points: 22, 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...
Post #1316430
Posted Friday, June 15, 2012 3:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 4:38 AM
Points: 5, Visits: 124
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.
Post #1316434
Posted Friday, June 15, 2012 3:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:41 AM
Points: 1,159, Visits: 6,478
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.
Post #1316438
Posted Friday, June 15, 2012 4:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 6:44 AM
Points: 196, Visits: 136
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
http://www.michael-gilchrist.com
There are 10 types of people in the world, those who understand binary and those that don't.
Post #1316467
Posted Friday, June 15, 2012 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 3:30 PM
Points: 6, 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
Post #1316484
Posted Friday, June 15, 2012 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:11 AM
Points: 1,190, Visits: 56
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')

Post #1316550
Posted Friday, June 15, 2012 6:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 25, 2012 4:09 AM
Points: 44, 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.
Post #1316559
Posted Friday, June 15, 2012 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 25, 2012 1:59 PM
Points: 3, 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.
Post #1316583
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse