VygonDBA (8/3/2012)
Morning all,I was lucky enough to be promoted at the beginning of this year and am now the sole DBA at my company looking after 5 SQL Server 2008 R2 instances. I've been on three MS Certified SQL Server courses covering design, implementation, optimisation, maintenance, security etc and have taught myself T-SQL to a standard that I can effectively query data and perform maintenance tasks etc, here's the rub though - because the company DB's are quite small (the largest is circa 600MB) and most don't have huge I/O, I don't feel that I'm getting the exposure to some of the more challenging issues DBA's are forced to deal with (i.e. data corruption, deadlocks, disk I/O problems, performance issues etc), does anyone have any suggestions as to what I could do (with the exception of moving somewhere else!) to get some more exposure and be able to practice resolving these sorts of problems? I'm looking at taking the MS 70-432 exam towards the end of this year but I'm not confident that I'll have the relevant pratical knowledge to pass and I don't want to let my company down.
I've recently built a test/dev server running SQL 2008 R2 to enable me to practice but need some scenarios and don't know how to instigate some of the above problems. Any help would be hugely appreciated. Cheers, M.
I suggest installing dev edition on a laptop, or a desktop you can play with the system resources and drives on, something seperate from your dev environment. You devs won't be too happy about their horked environment.
Practice configuring the correct way, then try breaking it, try configuring it in the worst way you can think of that still runs and try fixing it without interruption. Start thinking of terrible what if situations, what if I have my clustered indexes in their own file group on a seperate drive and the cord is cut? Load 80 million rows at the same time index maintenance is going on. What if the master db file somehow gets deleted? What if I have two instances trying to use the same exact database files on the same network drives? Try writing a single statement that blows up the tempdb, or fills the drive the log file is on. Create a deadlock. Write a statement that eats all the procs and makes everything wait including itself. Write a batchfile that opens 1000 connections and executes inserts, deletes and selects on the same dataset. What if someone updates all of the dates to getdate()? How would you fix this? How would you prevent this?
If you have another database person around to bounce things off of you can make a sport of it.
Figure out a way to monitoring and auditing for all of this, then monitor your monitors to see what kind of load they are putting on your server.
http://www.littlekendra.com/2011/01/24/corrupthexeditor/
Also definately follow Gail Shaw (GilaMonster) and Paul Randal.