June 14, 2012 at 9:31 pm
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
June 15, 2012 at 1:23 am
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)
June 15, 2012 at 3:40 am
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...
June 15, 2012 at 3:42 am
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.
June 15, 2012 at 3:47 am
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.
June 15, 2012 at 4:26 am
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. 😀
June 15, 2012 at 4:46 am
"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
June 15, 2012 at 6:38 am
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')
June 15, 2012 at 6:48 am
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.
June 15, 2012 at 7:15 am
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.
June 15, 2012 at 9:52 am
Steve is right...you don't want to just unilaterally delete objects when you're not sure of their function.
We've started renaming objects to be deleted with the prefix "del_", and adding that day's date to the end. If no problems arise within a certain length of time, then the object can be deleted. But if a problem comes up, it can quickly be reverted back to its original name.
June 15, 2012 at 10:00 am
matt.penny (6/15/2012)
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.
Absolutely, though I'd hope these were stored securely somewhere. I've had this happen before where we didn't know passwords.
June 15, 2012 at 11:00 am
Good article Sylvia! This would also be a good checklist for starting a new job. Very well thought out.
June 15, 2012 at 12:49 pm
Obsolete objects is such a huge issue. Is it technically feasible to track the last time any object was used or would the overhead of doing so be just too problematic?
June 15, 2012 at 1:03 pm
Chrissy321 (6/15/2012)too problematic?
Really depends. If you can run the profiler for extended periods, you can still easily miss something which is only used once a year.
I tend to rely on inspection and logging because I like to design systems where the database exposes as much as possible in a controlled fashion (i.e. mainly stored procs). It is relatively easy to find and instrument any stored procs which refer to a table to find out when they are used, and you are assured that you catch all usages. You can simply use the SQL Server dependencies information (make sure it's refreshed) to inventory the system.
In a system with direct table access, it's not nearly as easy.
Viewing 15 posts - 61 through 75 (of 83 total)
You must be logged in to reply to this topic. Login to reply