May 8, 2012 at 3:00 pm
Anyone up for doing some hand-holding for a contractor dba? I've been doing mostly SQL Development but am now at a company that has 4 IT employees and no dba. Oddly their site is crashing regularly (d'oh!).
Here's what I find:
SQL Server 2005 on a co-lo.
8 processor box
C: 68 GB (38 free)
D: 43 GB (36 free but some junk to remove)
E: 515 GB (168 Free but at least 250 GB of junk)
Single DB that is supporting a retail web app.. We'll Call it Prod
Size: 13 GB
Current state (chaos!):
DBs/Logs/TempDB/Backups are all on E. All are single file mdf/single file ldf.
All are on Primary
First ever (since 2002) backups of system dbs will occur tonight
Prod db is getting a weekly full backup and nightly differentials
Prod Logs are not being backed up
DB mail is not enabled so no way to push alerts (will need to set up via co-lo)
Nightly job to rebuild indexes was pointing at Master DB (Prod indexes are a disaster and will be rebuilt/reorged tonight)
Haven't looked at missing indexes or statistics yet.
Goals:
Nightly full backups of Prod (and Master, MSDB, Model)
Log backups every hour once full backup is happening
I need to verify with the co-lo as to which drives are being backed up to tape/alternate storage but I'm thinking of splitting the files as:
Data on E
Logs (and backups?) on D
TempDB... E or C? split into multiple files (8)...
Yes? No? Feedback?
Thanks!!!
May 9, 2012 at 1:41 am
firstly if you can get more drives added to the server
i've seen some people say that with the system databases (excl TempDB) that the MDF and LDF are ok on the same drive, I personally like to have them on seperate drives. TempDB should be on its own set of disks. User mdfs on 1 drive, User logs on another drive. Dedicated drive for backups. If you can and costs permit all RAID 10.
Usual layout I go for is, but is always subject to what is happening or will be pushed through the server.
C: - System and SQL binaries
D: - Page file set at 1.5 x RAM
E: - System DB MDF (Master,Model,MSDB)
F: - System DB LDF
G: - TempDB MDF
H: - TempDB LDF
I: - User Data
J: - User Logs
K: - Backups
As for changing the nightly full backups, this may be overkill. While the DB's are small you might get away with it but when they get bigger, you might be better with doing a weekly full, nightly diffs and then TX backups every hour.
Also speak to the business, do they have a RTO, RPO, RTA and a RCO to comply to which will ultimatly determin what backup strategy to take.
Nightly rebuild of indexes again may be overkill, rebuild and monitor and check fragmentation over a period of time, doing things like setting the fill factor of the rebuild might get away with doing a rebuild/reorg weekly. Granted some indexes may need daily rebuilds but I wouldnt of thought the whole DB needs doing daily.
Are you seeing contention on TempDB? If so adding extra files to TempDB will help but if not its an extra task on something which isnt required, but make sure its noted in documentation that if excessive TempDB usage starts that adding extra files and sizing them will help.
This is just my personal view on the details you have said. I'm open to suggestion from anyone else aswell.
May 9, 2012 at 7:23 am
anthony.green,
The drives that you proposing can i know it deeply, we have a virtual server and base configuration i dont have if i ask them to add a drive, what type of drive needs to be added do you want the drive to be SAN drive and will that be altogether different physical drive as i am poor in hardware configuration.
other things definetly i agree with you for indexing.
Regards
Durai Nagarajan
May 9, 2012 at 7:43 am
Thanks for the feedback. I'm hoping to meet with the co-lo reps soon to find out what the plan is for any upgrades--as well as find out what the terms of the service are. Currently I only know of issues with adding RAM.
There has been serious temp db contention so I will try adding more files today. Do you think it is helpful to add files to other drives or at this point just add files to tempdb on the E drive where everything else lives?
May 9, 2012 at 8:24 am
durai nagarajan (5/9/2012)
anthony.green,The drives that you proposing can i know it deeply, we have a virtual server and base configuration i dont have if i ask them to add a drive, what type of drive needs to be added do you want the drive to be SAN drive and will that be altogether different physical drive as i am poor in hardware configuration.
other things definetly i agree with you for indexing.
if you can a seperate raid array for each drive, but that can get costly. usually we have an array for a number of different servers user MDF/NDF files and an array for LDF files over our default minimum of 8 disks in a RAID10 for each array. system database are stored again on seperate arrays to which a number of servers share that array.
May 9, 2012 at 8:48 am
I just set up additional files on the D drive for temp db and altered the file path for the original data and log files. I haven't restarted the service yet--that takes down the web site. However, since we've been bouncing the service at least once per day I figure temp db will be moved today at some point.
Now, indexes. I tried to rebuild the highly fragmented indexes last night and it appears that about 42 indexes are still above 30%, 31 above 50%. Some tables have binary data and I haven't looked through them enough to understand whether there are daily updates on them or if the fragmentation is just due to duration.
Any suggestions for coping with lots of .jpg files in the db? Yanking them out is not an immediate option but part of my contract here will involve a revision of the architecture. Debating keeping them in or chucking them out of the db. Suggestions?
Thanks!
May 10, 2012 at 1:36 am
If you can look at filestream for storing blob data outside of the DB.
May 10, 2012 at 1:44 am
As far as I remember, Filestream isn't available in SQL Server 2005, so you're probably stuck with storing images in the database. You could purchase some document repository software or roll your own, but you're likely to be best off how you are.
As for high fragmentation, check whether the sizes of the tables concerned. Fragmentation is not really a problem in small tables, up to 1000 pages, say. You'll want to do some testing of that limit and not just take my word for it.
John
May 10, 2012 at 1:49 am
Doh, thanks John, I have my 2008 head on and totally forgot I was in the 2005 forum, my bad.
May 10, 2012 at 7:09 am
Fly Girl
RE: server specs.
Unfortunately - as the folks here will agree - sometimes being a DBA is playing the hand you're dealt. Getting additional resources to beef up the drives would be good but unless this is the company's "money machine" I wouldn't count on it. Even if it [was], I wouldn't count on it.
"Old school" strategy was to segregate data from log/backup files to different drive spindles. With the advances in array technology that's not necessarily true (but to avoid being chewed on by the forum, I'll mention there can still be "hot spots" that the hardware guys need to deal with). TempDB files [may] be "distributed" as all other databases would be on your server. It'll rise to the dimensions it needs to be in order to handle your toughest processing, and will remain at that size. As long as you don't outgrow the drive it's on, you're golden. I like to call it "self-leveling."
I'll comment using the order of your list and goals.
Don't sweat the "single file .MDF" thing or being on PRIMARY. It's all good. Later you might circle back to see if you can break out things like indexes to a different file but I wouldn't worry about that now.
System backups are huge. Make sure that happens. Make sure those transaction logs are created for Prod as well. That's huger. ("Huger?" seriously, I need coffee...) See comments below regarding backups.
Enabling DBMail will be a tremendous help for this maintenance plan.
Re: Rebuilding master indexes. <LOL> Yeah, you probably want to point that step to your prod database...
Indexes. My memory of versions and features is wobbly but I believe 2005 may have been "dumbed down" to only allow online rebuilding when using Enterprise. If Standard, you may have to either find some quiet time to reindex or flip over to reorganize rather than rebuild. Reorganize is OK and doesn't take the underlying table offline. Don't sweat the missing indexes and statistics yet. You're on the right track but right now stay in damage control mode to get the maintenance and backups fixed. Round 2 is optimizing.
Carving up TempDB can be a good thing but I'd throw that in the "optimizing" bucket as well. 1 file for each CPU I [believe] is the rule of thumb. I'm sure other posters on this forum will set me straight or ding me for talking out of my butt... <grin>
Re: maintenance plan
Personally, I think the maintenance plan is a good starting point but I roll my own and use a script. You may think about it after Round 3 is complete. (yeah, "3". That's not a typo.) A script takes out the "padding" in the schedule and lets me add a few extra DBCC commands I just like to run for the heck of it.
After completing the optimization we'll call Round 2, I'd strongly suggest Round 3 being
--practice restoring the system--.
Get to know how to restore your production server somewhere else either via a script or SQL Management Studio's GUI. ProTip: the master database restores differently than a user database. Get good at this. In crunch time, only rookies panic.
Re: optimizing
Read up on the Index Tuning Wizard. This should get you going in the right direction. There's also a tool by DBSophic called Qure Workload Analyzer. There used to be a free version that would probably help but I haven't used it lately.
Re: dealing with jpgs
I'd leave them in the database for now.
Anyway, I've babbled enough. The list you provided in your post shows your head is in the game and you're in the right direction. The maintenance plan will get you started with the care and feeding of the database, and give you a card to play for disaster recovery. I wish you the best. Remember that you can always post here and someone will be glad to help. They're a good bunch here.
Invariably, someone will always suggest "Well, you need to get a faster server, more disk space and upgrade" but in the real world if your management doesn't want to spend any money on a faster server, more disk space or a license for SQL 2012, just do the best you can with what you have.
Cheers,
Ken
May 10, 2012 at 8:32 am
Thanks! You guys are great. I do appreciate the advice and am endeavoring to follow it.
Yesterday the web site didn't tank for the first day since I've been here. 'tanking' has been being 'fixed' by the locals here by bouncing the SQL Server service. I'd like to think it's my fabulous skill--but I may have just gotten lucky. (YEOW!)
So far:
1) System databases are backed up (yeah!)
2) Indexes are defragged (and yes, it took me a while to understand that the remaining list of highly fragmented indexes were just from tables with less than 8 pages of data--d'oh! that hasn't been an issue on other systems I've worked on lately).
3) Yesterday I split out temp db into multiple files and moved it to a different drive.
4) Late last night I also added indexes... Previously the only existing indexes were PK indexes on the identity columns. Used the missing indexes listed in the dm views and cobbled together ones where there was a lot of duplication. Will be looking at page splits today and seeing if I can get any feedback on performance from internal users.
Today's goals include cutting over to full recovery and Tran Log backups rather than simple recovery. This db is taking on-line orders. Seems to me that 1 weekly full and nightly differentials is not quite on target. Thoughts?
Your input is much appreciated!
May 11, 2012 at 6:50 am
OutSTANDing! You rock.
Weekly full and nightly differentials are part of a perfectly acceptable way of recovering user databases. Keep in mind that: a.) the differentials store [cumulative] changes after the full so if you do a full on Sunday, by Saturday you've got the sum of the data deltas of Mo, Tu, We, Th, Fr and Sa. The Saturday differential may be a whopper. Conversely, you can ditch "prior" differential backups to free up space. Personally, I'd keep that last two but that's just me and I usually have the luxury of a lot of drive free space.
Differential backups also infer that your differential is [your last point-in-time] recovery point. Transaction log backups would bring you much closer to your actual "last activity".
Here's some good news though: if you do both differentials and transaction log backups, you have a built in "Plan B". If the latest differential suffers a bit burp and is whacked, you can load the full then load all the transaction logs you've dumped since the full. That'll give you the same point-in-time recovery as restoring the full/diff/tlogs. You can use either strategy to recover.
Note: If you "automate and consolodate" the differential backups as part of your maintenance plan, master is going to bark at you. It doesn't like differential backups.
Also, as I mentioned in my last post, practice recovering both master and the user databases. Review the STOPAT parameter - that'll get you to a point right before somebody fatfingered data or forgot the WHERE clause in an UPDATE statement. (BTW: if this happens, you need to pull the application/front end plug immediately. When you do the point-in-time recovery, all data entered after the "disaster" is lost.)
If you practice and become comfortable with disaster recovery, you're golden. Sorry to repeat myself but it's [that] important.
Once you get into this DBA stuff you'll find it's not really that hard and can be fun sometimes. Maybe that's why I enjoy my job so much.
"Forget that fighter pilot stuff. I wanna be a DBA."
Cheers,
Ken
May 11, 2012 at 6:05 pm
Ken,
Thanks! I had to explain why I was laughing to my husband when I was reading this.
When I tried to add transaction log backups to the maintenance plan it wasn't an option (using the stock SSIS maintenance plan objects). I'm assuming from your post that I can still do this via code. If so, that would be an adequate solution. Thanks for the point that the nightly diffs are from the last full backup rather than from the last differential--I was missing that datapoint. Hopefully I got that right. Data shipping to another system is enabled so, in theory, the data loss for orders is only 3 minutes--assuming we catch it immediately and that orders are the only critical data elements. Being a control freak, I'm not happy with the idea that we could only restore to midnight of the current day, but I have asked the manager to work on a company concept of acceptable data loss/down time.
No crashes since Tuesday and as of Thursday I convinced the sys admin to stop restarting the service as part of her morning routine. Managed today to get the Prod server set up as a linked server to my VM dev machine that has db mail enabled and working so now I have a way of getting some alerts. Couldn't read the error logs off of the prod machine that way, though.
I will be trying to restore as soon as schedule allows. Apparently no one has ever done that. Got a load of development work dumped on me today so I'm not sure when that will be... Paranoia will have me making time for that.
Have a great weekend.
May 12, 2012 at 11:12 am
If you have a chance you can also take a look at Ola Hallengren Maintenance Plan as its all inclusive and will do a lot of things for you AFTER you get your systems up and on track. Its a setup that is VERY well recognized and respected within the SQL Community.
Looks like the advice you have gotten is spot on and excellent...great job on your part as well. Just my .02 🙂
DHeath
May 15, 2012 at 7:12 am
I'm glad things are going well. You certainly have a promising future as a DBA if you decide to pursue it.
I haven't reviewed DHeath's suggestion (re: Ola Hallengren Maintenance Plan) but it's got me curious enough to check it out. I guess you learn something new every day. Thanks DHeath! I'd coded my own procedures/scripts to perform DR script generation, maintenance, backup, etc. In fact, my latest mod has been to make everything standalone scripts executed via SQLCMD with command line parameters, in anticipation of using an enterprise scheduling app. The app uses agents so I needed to make my whole admin "mousetrap" folly portable. <g>
The error logs are just a text file named ERRORLOG on the server. Find out where they are located and just grab a copy of it via a UNC path (don't open it on the server unless you are absolutely certain your text viewer/editor doesn't lock the file. That would fall into the 'bad things' list.) You can also leverage sp_cycle_errorlog to keep the size down, but make sure to increase the number of errorlogs SQL saves (default is 6).
Re: Restarting the service.
<rant>
Any self-respecting admin may [think] restarting a service daily is "the fix" but in reality that only [may] apply to third party apps with services written by chimps. I think Microsoft has that whole "write services well" part mastered so we can conclude that stopping MSSQLSERVER daily is stupid. In fact, think about the repercussions like [rolling back transactions in mid-flight when the service was stopped]. Every restart may have been costing you business. SQL is wonderfully, gloriously, amazingly good at recovering when shut down abruptly and all newer versions have never failed me, but being an old timer I consider each "service bounce" a roll of the dice. (BTW: I've been working with SQL since 4.21 on OS/2 and no, I wasn't a DBA when I was 12 <g>)
Your 'convinced the admin' comment made me laugh because by the time I got to that part in your post, I was already fired up and thinking [my] 'convinced' would have been much more unpleasant.
</rant>
Sorry 'bout the rant.
Re: laugh
A good sense of humor is critical to being a DBA. I laugh at stuff that might make a serious guy weep. Profiler: "SELECT DISTINCT * FROM...", developers being told that "stored procedures make things go faster" and finding them executing dynamic SQL: "CREATE PROCEDURE _ _ _; EXEC _ _ _; DROP PROCEDURE _ _ _" all in the same statement. Forget that "laugh and the world laughs with you" stuff. "Laugh when you're alone in your cube and people will not bother you."
Feel free to post here as questions arise. I just happen to be gabby enough to post these long rants but there are a lot of others rattling around in here as well, all sharp, experienced and willing to help if you need it.
Cheers,
Ken
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply