SQL Saturday 440 – Pittsburgh

Once again it’s that time of year. The committee and I have been working on spreading the word, getting sponsors, getting attendees, organizing lunch, and so much more. It takes a lot of work and has a lot of moving pieces. This year is lining up to be the best yet. We have a ridiculous schedule this year and it’s going to be hard for me to decide which sessions I want to attend. Check it out here.

I highly recommend coming this year. Take a Saturday and invest it in yourself by learning something new and meeting some new people who have the same interests about SQL as you do.

Here is the info:

SQLSaturday is a full day FREE training event for SQL Server professionals and those wanting to learn more about SQL Server and network with the SQL Server community.  Pick from more than 30 different sessions and learn from the experts.

Don’t know anything about SQL? Back by popular demand, the “Wanna be a DBA” makes its return.  Explore the life of a DBA and see if it can be yours too.

 

DateOctober 3rd, 2015

LocationPittsburgh Technical Institute – 1111 McKee Road, Oakdale, PA 15071.

TimeCheck in starts at 7:30am. Sessions throughout the day

Cost: Admittance to this event is free, but we do charge a lunch fee of $10.00

Registrationhttps://www.sqlsaturday.com/440/registernow.aspx

 

Call for speakers: Call for speakers is still open. To submit a session go here – http://www.sqlsaturday.com/440/speakers/submission.aspx

Seating is limited so register today!

The Premier SQL Server training event in Western Pennsylvania

Join the Amazing Pre Con this year!

Brent Ozar, Jeremiah Peschka

Developer’s Guide to SQL Server Performance – Live!

http://www.brentozar.com/training-in-person/#pittsburghoct-2015

 

Want to know more? Of course you do! Go here –  http://www.sqlsaturday.com/440/eventhome.aspx

Still have questions? We can answer them. Mail us here – pittsburghsqlsaturday@gmail.com

Like twitter? We do too. Follow us here @SqlSaturdayPitt and use the hash tag #SQLSat440

WTF is the DL on COTS EMR DBs?

Well it seems I have missed April and allllllmost May. I had a nice 2.5 month vacation from work but just this week started a new position in Pittsburgh. During my time off I monitored #sqlhelp, read some articles, and tried to keep up with the ever-changing world of databases. I also did a lot of relaxing, played too much skyrim, and traveled quite a bit.

The company I started working for is having a conference for their users this week so almost no one is in the office. It gave me a good chance to read through their documentation.

It’s very easy to get lost in documentation when you are new to a company. Especially when most companies these days use lots and lots of acronyms. Luckily, a company acronym dictionary was given to me in my intro packet. It came in handy while reading through documents and meeting with various people. It’s still easy to get lost in conversations involving simple things.

Do you use a lot of acronyms at your company? Do you document them? Love em/Hate em?

1 year old sandwich

It’s been an interesting day. It was the 1 year anniversary of SQLSandwiches today. It was also my last day working in New Zealand. That’s right, I decided to leave my current role as DBA at Foodstuffs. Mrs. Sandwiches and I are headed back to the States to be closer to our family.

Foodstuffs was my second job in New Zealand. I enjoyed my time working there. My managers were great and let me database my day away. I learned a lot and worked with great people. There is a reason people stay there for a long time and after only a short while, I can see why. I also had the best ID ever there.

you got something better?

Back to the sandwiches. I’ve totally slacked off on the blog so far this year in preparation of this big move. Now that I will have some down time, I can catch up on all the half written blogs that I have in my backlog. I’ll be taking the next couple weeks off to travel around Japan and then to the States! Until then it’s going to be lots of reading, running, blogging, and studying for some exams.

So here’s to 1 year blogging under SQLSandwiches and the end of a short but great job in Auckland.

BEER!!!

Final words to Foodstuffs…David, you can keep DTM! HA!

w...t...f....

Bring it on 2012

Well, I’m about a week and a half late with the whole New Year blog post but I figure better late than never.

So just a little recap of my year. 2011 was the first year I started blogging about SQL. It was pretty rewarding and I hope to do more of it this year. When I got in the zone things just flowed but when work or other life distractions got in the way things slowed down a bit.

Last year I was able to learn a good bit about:

  • Powershell, Powershell, Powershell – I am still at the beginning of my journey but some of the things I have done so far have saved me so much time.
  • Access (unfortunately) – some critical systems got tossed my way that were dependant on access. I had to learn how to deal with them. It wasn’t fun but it was awesome once everything worked (for that week).
  • Big DBs – This year I got to work with some monsters. It’s been a good oppurtunity so far. It makes tuning even more fun.
  • Indexes – used, needed, overlapping, and of course…fragmented.
  • DBCC commands – Just when you think you know some of these, you learn a new one that totally changes how you work.
  • Twitter – this is the first I have ever used twitter. I used to think it was the stupidest thing in the world. I started using it a bit and holy crap, a whole new world opened up. I can’t believe the people I have interacted with on there. SQL has a great community and I hope I can give back a little more this year.
  • Double Hoping and SPNs – every DBA needs to go through this at least once.
  • Extended Events – Profiler? Who needs that?

Overall it’s been another great year to be a DBA. I like what I do and just want to get better and increase the awesome meter.

This year I’ll be focusing a lot on powershell, puffy clouds of computers, 2012 of course, extended events, and probably get another cert or 3. I WILL also present 1 or 2 times this year. Something I have never done before. If you got advice, send it my way.

The other area I am hoping to dip more into is BI. I have a feeling that there will be a big industry swing once 2012 comes out for really good BI people. I’m hoping to jump on that wagon.

I will also be moving to the states in the near future. That is going to be a whole new world of adventure that I am ready for.

So 2012, bring it.

What’s your build number?

Without looking, name your most important server. Or if you have a bunch, name the top 2 or 3.

Now tell me what edition you are running. Not too hard. “SQL 2008 R2 Standard” or whatever.

Again, with out looking – what’s the build number? Hmmmm. 10.5…something or other.

Now imagine your server gets hit with a bad case of the Hulk Smash’s and it’s gone. Well good thing you have backups.

Now let’s just take our master database and restore that sucker right there on our backup machine. Uh-oh. What’s that error? 3168!?!?! NOO!!!

whoops...

If you need to restore a master database (and let’s hope you don’t), you better know what build it is before you start down that path. What’s the best way to know all your build numbers for all your servers? Ugh…documentation. I know, I know. We all hate it but in this case it’s going to save you a headache.

Granted, you can get the build number from the error message above but do you really want to spend the time adding a service pack to your backup server before restoring your master DB? I’m sure the big boss man doesn’t want to have extra downtime.

I use a department wiki that has facts about all the production servers. Sometimes it’s a pain to keep updated but it’s necessary.

Do you keep your build numbers documented?

 

How pure is your data?

It was a normal Monday. I was going over some HA designs and planning the joys of setting up log shipping on a SQL 2000 instance when I got one of “those” emails.

“Hey Database Person, I’m trying to run this query but I keep getting this message:

Msg 9100, Level 23, State 2, Line 1
Possible index corruption detected. Run DBCC CHECKDB.

Can you fix it? I need to run these reports for our production line ASAP!”

Crap. I was just reading an article about all different kinds of corruption last week but didn’t run into this.

My first thought was to do what SQL told me to do, which was Run DBCC CheckDB. Luckily, it wasn’t that large of a database.  After it finished, it returned fine and dandy with no errors. Cool, let’s try that query again and hope everything is fine.

Msg 9100, Level 23, State 2, Line 1
Possible index corruption detected. Run DBCC CHECKDB.

Crap part 2. Possible index corruption? Maybe; SQL says it’s possible. I needed to rebuild the indexes on this table. Since the table contained over 11 million records, I double-checked with the person in charge of the database to make sure nothing would be damaged. I also checked out what was happening within the server. The server was relatively calm and had very low CPU usage. Nice, let’s go.

Couple minutes later, indexes were rebuilt. Awesome, let’s run that query again and see our results.

Msg 9100, Level 23, State 2, Line 1
Possible index corruption detected. Run DBCC CHECKDB.

You son of a…. Well, there weren’t any errors and the indexes were fine. What else could it be? Good ‘ol Google led me to a couple different pages where I finally found CHECKDB with DATA_PURITY.

I ran DBCC CHECKTABLE with DATA_PURITY because I knew exactly which table had the problems. Oh, did the error messages fly then. So many in fact that SQL said there were too many to list (sounds like someone is being lazy if you ask me). I tried to manually find a record or two that had contained bad data.  The column was a float so I figured it might have had some crazy number shoved in there somewhere.

Then I spoke with the person who sent me the error and she said that the DB was upgraded to 2008 from 2000 in the last year. Before, they used “NAN” – not a number, in that column. Bingo. That was it. Now I just had to find the bad records.

I took a backup of the DB and restored it to a dev playground. Next I did a nice SELECT INTO a new table but instead of keeping the column as a float, I converted it a varchar. I checked out a couple of the bad records I found before and lo and behold, there were about 3,200 records that had “#”.

Finally, I contacted the DB owner and told them about the bad data. Luckily, since the records were old they told me to just NULL them out to fix the data. A quick UPDATE statement later and it was back to check the original query.

Booyah! Query complete.

In today’s lesson, I learned about checking the purity of your data.  Also, it’s something to check for when upgrading databases. As BOL says: “For databases that were created in earlier versions of SQL Server, such as SQL Server 2000, SQL Server 7.0, and versions upgraded to SQL Server 2005, these checks are not enabled by default.”

So much Internet…

SQL, SQL, SQL.

When it comes to the SQLs, I know only a fragment of what I could. To fill in those gaps, I read. Oh, do I read. I’ve only read a few SQL books but I have read hundreds of articles and blogs.

Most of the time it goes like this:

“Hmmmm, that looks like an interesting article. Let me give it a nice ‘ol read”

“Oh…what’s this link? To another blog? Sure! Why not?!”

time to reeeeaadddd

Then I must pull myself away and jump back into the databases.

I like blogs that stick to material I am looking for while also keeping my attention. At the same time, I try to avoid blogs that get too personal or complain too much. I keep my personal blog personal and my technical blog focused on the SQLs. I like to read blogs that try to do the same.

Here is my list of the Top 10 blogs I prefer (in no order):

  1. Grumpy Old DBA – the name says it all
  2. DBA Rant – good stories here
  3. SqlBalls
  4. SQL Rockstar
  5. Shaun J Stuart – my recent favorite. Not a ton of updates but great posts
  6. Pinal Dave – Used to be my favorite but there is such a thing as too much content – it’s hard to keep up
  7. Happy SysAdmin – Great for learning some Admin skillz
  8. Paul White – Wowzer. This guys knows a lot.
  9. sqlchicken
  10. SQL Princess – She loves some CTE’s
I like bloggers who I can relate to. Many times they have the same problems I have and/or present alternative solutions.

The last thing I would like to say to all these blogs is – thanks! You’ve helped me a lot.

Do you read blogs? If so, which ones?

 

Can Scrum Work for the DBA?

Agile! Scrum! Development methodologies! Sprint!

If you have a manager who reads any web page about being a manager, then you have probably heard about Scrum. Scrum development is very attractive from a manager or stakeholder’s point of view. You get short bursts of development and can see results quickly.

With my experience, companies with no scrum experience who want to adapt this method search the web and find a scrum coach and then book 1-3 day sessions. Being in these sessions before, I know they are long and extremely boring.

Having said that though, I have seen scrum in action and think it works great for teams that do it right. If you have a dedicated team working on one project then scrum can do wonders for you. Even if you have a couple projects going it can work well with an experienced scrum master.

Being a DBA though, I find it hard to fit into scrum. I have been part of scrum teams developing some application but usually only brought on to write some SP’s, create a package, or another smaller task. I end up sitting in on 2-3 hour sprint planning meetings and end up with a couple hours worth of work.

The other problem with a DBA and scrum is that a good chunk of the work that I get isn’t planned a week (or 2) in advance.

“Database guy, I need access to server 2930dkdks. It’s holding up production”

“Are you the guy who gives me the reports? I need 10 reports by 1pm today!”

“Mind helping out with this query? I’m not sure why it’s slow, I only use UNION ALL 10 times”

“Mind checking out that server? It’s out of space”

I would love to tell all these people to wait till next sprint but then I would have a lot of angry co-workers complaining to my manager.

This is why I feel scrum might not work for DBAs. Sure, I could factor in 30-40% of my time into each of my sprints to take outside requests but what happens when I only get one user all sprint who needs help for 30 minutes? Or what happens when Joe the engineer deletes the wrong database on the wrong server in the middle of day and you have to spend the rest of the day fire fighting?

The other problem is that I’m not on a team of DBA’s. I do dev work, I do admin work, and I do BI work. Whatever is needed from my co-workers and my company.

I love the idea of Scrum and thinks it works well for developers but not so sure it works for DBAs.

I currently manage my tasks with a to-do list and a backlog. I revaluate the priorities of my to-do list daily and if I finish everything I reach into the backlog until another request comes in. If the backlog is a bit bare, there are always improvements to be made to current systems.

Are you a DBA that uses scrum?

If so, how do you make it work?

If not, how do you get around the manager who loves scrum and insists that you be a part of it?

Welcome to SQL Sandwiches!

Hi,

My name is Adam and I am a DBA. Welcome to my kitchen of delicious SQL tips, tricks, and whatever else I can offer you. This blog is for me – to document my experiences with SQL, for you – maybe you can learn my mistakes or view something from a different angle, for the community – the more useful information out there, the better (sometimes), and for Ween – because they are awesome.

Like most DBAs, I didn’t always dream of working in the world of data. One day I was handed a terribly overdue project and was working nights. The next thing I know I am restoring backups, then looking at stored procedures, then the next – I am a certified DBA. Maybe not that fast but you get the idea.

So feel free to leave comments if you find something helpful, if you find mistakes in my work, or just to say Ween is awesome.

Enjoy!