SQL Server, PASS, and other data mishaps
Posts tagged SQL Server
Invitation for T-SQL Tuesday #19 – Disasters & Recovery
Jun 7th
Disasters
Its the first week of June and for those of us living along the Gulf and Atlantic coasts of the US, that brings the beginning of hurricane season. It also means its time for this months installment of T-SQL Tuesday.
This Months Topic
There goes your weekend/month
Disaster Recovery. This topic is very near and dear to me based on the fact that I live on a barrier island that was the site to the deadliest natural disaster in US history and more recently destroyed by the third costliest hurricane in history. Needless to say preparing for disasters is nearly instinctive to me which might explain why I’m a DBA but I digress. Anything you’d like to blog about related to preparing for or recovering from a disaster would be fair game, have a great tip you use to keep backups and recovers running smoothly, a horrific story of recovery gone wrong? or anything else related to keeping your systems online during calamity. We want to hear it!
My street a month after Hurricane Ike
T-SQL Tuesday info
Originally an idea dreamed up by Adam Machanic (Blog|Twitter), it has become a monthly blog party where the host picks a topic and encourages anyone to write a post on that topic then a day or 3 later produces a roundup post of all the different perspectives from the community.
Rules
- Your post must be published between 00:00 GMT Tuesday June 14, 2011, and 00:00 GMT Wednesday June 15, 2011
- Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
- Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can see your work
Nice to haves!
- include a reference to T-SQL Tuesday in the title of your post
- tweet about your post using the hash tag #TSQL2sDay
- consider hosting T-SQL Tuesday yourself. Adam Machanic keeps the list, if he let me do it you’re bound to qualify!
Check back in a few days to see the roundup post of all the great stories your peers shared
How deep can you dive?
Mar 24th
Go deep or stay home!
After last years Summit we launched a feedback site http://feedback.sqlpass.org in hopes of gathering all of the feedback about the event in one place. The number one thing people have asked for on that site is for there to be a track of sessions in the 400-500 level range. The problems with the community desire for higher level sessions are twofold, one we dont normally get a huge number of session submissions that are at the 400-500 level. Two, I’ve been told by those who are qualified to present those types of sessions that an hour and fifteen minutes (spotlight) is often not enough time.
Cramped for space
In the past our conference size has dictated the floor-plans at the conference center and we’ve been maxed out at 14 concurrent sessions. This year however, because of anticipated attendee growth, the logistical geniuses at PASS HQ were able to add another session room (I’m looking at you Anika and Craig). With this new room I have options on what to do with the extra session rooms!!
Changes for the Summit 2011
This year we’ll be using the space we gained from the new room addition to have longer deep dive sessions. The current idea is to offer these longer sessions in hopes that they will attract presenters who are qualified to present these deeper dive sessions. Currently, the plan is to have a maximum of 6 deep dive (lvl 400 or 500 only) 3 hour sessions. Because of the way the schedule is laid out, we will run 2 of these sessions concurrently every conference day.
Rules… Yeah there’s always rules
We will accept abstracts for this new session type in the same manner as a regular abstract. That is to say anyone can submit a half day abstract. If you submit an abstract for a 1/2 day session it will count as one of your 4 allowed abstract submissions. The session selection for these sessions will be handled by the regular respective abstract review teams. Even though we are going to allow anyone to submit abstracts for these sessions, it should go without saying that if you don’t have prior experience or reputation for being able to give an extended, strong 400-500 level session it may be best to focus on a regular summit session. What I mean by this is for these particular sessions we will be instructing the review teams to weigh the speakers perceived ability to deliver the session higher than we normally would for a regular session.
Possible Hiccups i.e. Changes
Two things could change with these sessions.
- I am considering making the sessions 4 hours long (roughly 3 regular session slots). If we do that the maximum number of sessions would drop to four. I’m leaning heavily away from this but, if anyone has a strong opinion on this I’ll listen
- Depending on the quantity and quality of the abstracts we receive, we may have less than the maximum sessions shown above (4 or 6)
- Im still considering a single DBA 101 “Accidental DBA” type session for one of these sessions but havent been swayed that there is more interest there than there is in deep dives
The 3 most important words for a DBA
Feb 15th
Occasionally I’m asked what the most important thing for a DBA to know is. It seems some people want to know what 1 thing to focus on in order to be successful long term as a DBA. I’ve pondered this question for quite a long time and I’ve given various answers over the years.
Reality intervenes
Ive come to the conclusion that there really is no one special thing or “skill” that its important for a DBA to know. Sure, there are lots of qualities that tend to make a successful DBA. Things like attention to detail, thirst for knowledge, and an uncanny love of BACON… The problem is as huge as the SQL Server product is becoming it is also becoming increasingly difficult to be an expert in everything. I am actually of the opinion that it is impossible to be an expert in everything SQL Server.
Knowing what you dont know
The most important thing for a DBA to know can be summed up in 3 simple words ” I Dont Know” Why is that important? Because, knowing what you don’t know, and being able to admit it to those around you is by far the most valuable skill that you can poses as a DBA at any skill level.
When in doubt its always good to remember that its ok to say “I don’t know” The Corollary to this statement is of course “Ill find out, or find someone who does know”. Ive seen many small issues blow up into large problems over the years because someone didn’t really understand what was going on and they were afraid to let their coworkers (or boss) know that they don’t understand. In many cases if we’ll just learn to say “I don’t know” some further pain can easily be avoided.
Database Automagic
Feb 8th
This months TSQL Tuesday is hosted by a good friend Pat right over at SQL Asylum
For this months entry I decided to keep it short and sweet, following in my Bits N Bytes theme.
The Meta Script
In the true sense of the word automation, this really doesn’t fit but, in the terms of quickly getting something done that would otherwise be a mundane repetitive task, this can save a world of time.
Lets say we have a list of objects in the Sales Schema and we have a request to grant Select and Insert access to a user for those objects. There are two approaches, 1 is to grant select and insert to the actual schema like this
GRANT SELECT, INSERT ON SCHEMA::Sales TO BusinessUser
However you might decide that you only want to grant direct SELECT and INSERT on the tables that exist in the DBO Schema today not those tables which may be created in the future (auditors love to make us do this)
A simple way to automate granting these rights is by writing a script that writes a script like so
SELECT
‘GRANT SELECT, INSERT ON ‘ + sch.name + ‘.’ + obj.name + ‘ TO BusinessUser’
FROM sys.all_objects obj JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
WHERE sch.name = ‘Sales’
and obj.type = ‘U’
and obj.type = ‘U’
This should give you a result set that looks something like the following:
GRANT SELECT, INSERT ON Sales.People TO BusinessUser
GRANT SELECT, INSERT ON Sales.Sales TO BusinessUser
At this point, run the output in a separate command window and viola you’ve automated that grant of permissions
This may not be true “automation” in the sense that Pat was looking for but, perfecting the ability to write scripts that write scripts is a huge timesaver
A tall tale of SQL database corruption
Feb 1st
This corruption story begins like many. Somebody in a server room far far away decided to make a change to a VMware guest machine and that little change rippled through our poor server like a lady Gaga Meat Dress through the VMA’s. Needless to say, it wasnt pretty. The full set of events may never be known by me but it appeared as though our guest server ran out of disk space on the OS and some form of recovery was done.
What we started with was a sql 2005 sp3 server where 1 of the drives was apparently corrupted, So 2 SQL instances wouldnt start. They were both erroring with the message :
Error: 9003, Severity: 20, State: 1.
The log scan number (23:5736:37) passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Using trace flag 3608 and startup parameters -c -m I set about to do a normal “disaster” recovery of our server
After rebuilding the master database, everything came online successfully. Then master was recovered from the previous backup. Once master was online I started getting the very same error message about the model database
Error: 9003, Severity: 20, State: 1.
The LSN (11:999:1) passed to log scan in database ‘model’ is invalid
This would prove to be a trying error! it took about several iterations and quite a time to figure out exactly what was going on.
On this server after initial setup we had moved the system databases from the install drive to seperate drives for log and data. When rebuilding master, the system db’s wind up back in the default directories but, after recovering master, the databases are pointed back to the original locations.
Once we got the server started the log scan error message for model showed up so, I began what I thought would be a normal restore of the model database. Unfortunately, there was no way for model to be restored. During the restore command, I got alternating messages that the model database log file was corrupted
Error: 3283, Severity: 16, State: 1.
The file “modellog” failed to initialize correctly. Examine the error logs for more detail
The Error 3283 Would be followed by
the database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.
After trying various iterations of deleting the existing model log & database files, copying in the newly created ones and running restores, nothing was working. I began to think the disks were actually having problems, or the backup was bad. After verifying both the backup and the disk config I was left with only a hail mary –> sp_detach_db
After detaching model, I copied in the newly created model files (from the rebuild of master) and ran sp_attach_db on them. Once the Model database was attached the instance started successfully!
After the instance started model was restored from the same backup and the instance restarted. Finally, once the instance came online, it was a standard restore of all the user databases.
Im not sure what about the logscan error in model caused the errors I saw, but, both instances behaved exactly the same. I had to detach and reattach a blank model to make the other instance work as well.
After going through this, I went back and tried to reproduce the problems by intentionally corrupting model and its transaction log in various ways. Every corruption I could cause in model behaved as I expected and a simple restore statement worked. Im still not sure WHY this happened but, hopefully it wont happen again and if it does there wont be so much testing to figure out how to get model online
