asked
Things to check when taking over an existing database
I am trying to compile a list of things to check when I take over a new database. What I have come up with so far is listed below. I would love my for SQL friends out there to help fill in the gaps. I searched online last night for such a list and didn't get a complete picture. 1) Backups - that they are happing and are being copied to a secure location 2) Recovery plan exists, if not create it. 3) Consistency checks are occuring regularly 4) Updatestats is beging ran regularly 5) Auto Growth / Auto Shrink 6) Indexes are being rebuilt regularly or as needed 7) Recovery model is configured properly 8) DBO is configured properly 9) Owners of jobs, objects are properly set 10) File placement data files, tlogs, tempdb 11) Compatibility level is properly set 12) Auto stats set to standard 13) Perform regulary restores of you db's to ensure the backups are good. Make sure to pull them from the secure location. 14) Ensure there is adequate space on the drives containing SQL data 15) Check for long running queries or blocking that might be occuring during the day to see where you can start tuning the system.
I would do what you have mentioned and also checkout Brent Ozar's 60 minute blitz script, its a great way to become familiar with a new database. Are you taking over hardware too or are you familiar with that? I would also want to get a good handle on who security, who has access and why, who can confirm that the access as it stands is correct and who can authorise changes to access levels. What systems access the data, SSRS, IIS, EXE etc. Are there data import/export processes, how often, what data, encrypted transfer etc
I concur on the hardware too. I wanted to try to keep things limited to a database and then come back to what to check hardware wise for the entire server as well as instance related things such as memory, security, etc. You do bring up a good point about who has access to your database and why.
Excellent points, I just want to reinforce the security in particular and add that as part of it you should try (to the extent practical) to look for uses for dynamic SQL as those can be opportunities for SQL injection attacks.
You got a pretty good list up there. You might also want to check : 1. Make sure instant file initialization is on 2. check the size and configuration of Tempdb 3. Whether recent updates are applied 4. Max memory configuration 5. Deploy [sp_whoisacitve][1] (optional) 6. Deploy Performance Dashboard script 7. SSIS Packages running on or againest your server 8. Ask from the previous owner for documentation(if any )and some gotchas that happened in the past. 9. Check whether alerts are properly configured, also check whether the profile is based on the former owner of your database servers.if it is alerts might fail when his account is deactivated 11. Make sure operators are also created for alerts 12. Also you may want to TURN OFF Backup Success messages by using trace flag 3226. [1]:
http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx5.5.6.7.6.7.7.8.9.10.
Brent O's Blitz script is excellent. You should also look at Glen Berry's site he has several scripts specific for 2008 and 2005
http://glennberrysqlperformance.spaces.live.com/
