Showing posts with label Advice. Show all posts
Showing posts with label Advice. Show all posts

Tuesday, May 17, 2016

How To Query Oracle Sys Views from MSSQL

So I'm now working in Oracle, but yet still working in SQL Server. Fun times (no sarcasm, I'm loving it)! But I ran into an issue today when trying to get information about Data Guard from Oracle into my SQL Server so I can report on latency.


I have the linked server set up (make sure you have the correct driver on your SQL box, edit the TNSNAMES.ORA file, then create the linked server, tons of info out there on how to do that). What I was having an issue with was getting the V$ views returned from a query without an error.


For instance, this throws an error:


SELECT * FROM <your linked server>..SYS.V$DATAFILE


You'll get:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "<your linked server>" does not contain the table ""SYS"."V$DATAFILE"". The table either does not exist or the current user does not have permissions on that table.

While this syntax works:

Select * from openquery (<your linked server>,'SELECT * FROM V$DATAFILE')

WOOHOO!

And obviously linked server names have been replaced.

Don't bother doing a Google search; I found a lot of nothing! Hence my post. Hope this helps!

Friday, February 26, 2016

It's been a while...and my MSDB has gotten fat!

New job, new house, new year...lots of changes within the last 12 months. What hasn't changed is my love of working with SQL Server. And my annoyance with certain things.

Latest: The MSDB on a server has grown immense. What the heck is going on? So I took a look, it turns out that the MSDB backup job history has never been cleaned up.  Due to the high volume of databases on this particular server combined with tlog backups every 15 minutes it bloated the MSDB database...like ridiculously, 15 GB.

Now there's a fairly easy fix for this, you could use sp_delete_backuphistory to help clean up the MSDB database. One thing though, I would recommend scheduling it to occur after hours because with an MSDB of this size it may take a while and could potentially cause performance issues.  And increasing the size of the drive would only be a temporary solution, the real problem is that the MSDB backup history is not being cleaned up.

Why isn't there a default cleanup job in SQL set for, say, every 45 days? We clean up Agent error log files, why not the MSDB history?

Schedule this cleanup to delete entries older than a certain (rolling) date and you've got yourself a permanent fix.

Thursday, July 10, 2014

How to be an amazing DBA

When you ask what makes a great DBA, what answers do you get?

-Get lots of certs!

-Oh you have to have a blog!

-Become a speaker at SQL Saturday! (or any of the other respected trade conferences)

-Mentor juniors!

....etc


While all of these are good answers, and will definitely help your intellectual credibility, none of this is worth a job if you're not customer-focused, result driven, and generally easy to work with.

A story. I tried for many years to break into the DBA world. I had quite a few jobs where I got to touch the database server occasionally: troubleshooting connectivity, doing backups, giving users permissions...simple, very junior administration. But I didn't have the experience to get a job as an actual database administrator, and where I worked doing application support there was absolutely no room (nor need) for a DBA. So I decided that getting my certifications was a good way to break into the business.

I went ahead and studied (and received) my MCDBA. I went to a small technical training firm to do this as I was able to pay for the materials and test in one lump sum, and had access to live help if I had additional questions. After receiving my certifications, the training company asked me if I wanted to work for them! They were so impressed with the fact that, when I was at the center in the evenings, I would help other students and was generally friendly and outgoing.

I worked there for three months as a trainer while trying to find that elusive DBA job. I saw an ad online for a Jr DBA position and applied. I was asked some fairly simple questions (like the difference between a user and an login) and then was invited to come in for a team interview. They asked me many more questions, most of which I knew from my training, and a few where I had no idea if they were even speaking English! Overall I thought the interview went well, I liked the team and we all seemed to mesh.

I got the job. My first REAL Database Administration job. I was so happy! I did very well, moving from Jr DBA to Sr and Team Lead within a few years of putting in a LOT of hours, studying my butt off, and working like a dog.

After I left that company, I had a conversation with my old boss. Got the news that after my interview the team didn't actually recommend that I be hired, they didn't think that I had the technical skills to be a good addition to the team. I was crushed! But luckily, my boss overrode their advice and hired me. Why? Because of one thing I said in the interview when asked about how I approach my job. I said "We're all on the same team, trying to get the same problems solved. While it may be frustrating at times, you have to put yourself in other people's shoes and see things from their point of view. In the end my job is to make their job easier."

Think about that. How much more smoothly would your company run if everyone had this attitude? You'd never hear "It's not my problem" "It can't be done" or just flat out "No". Instead you'd hear things like "Hmm, well I can't help you, but I think I know someone who might be able to...let me send them an email and I'll CC you" or "Well, it can't be done that way, what result are you trying to achieve and why? Maybe we can go about it a different way." or "Why do you need this? What's the end result you want to see?" (which brings me to something I will blog about in the future, always ask one more question, because the user may SAY they need a mauve database, when actually they might just need a report emailed to them or to be taught how to do something. Get to the root need before throwing solutions at something that may not even be an issue).

Overall what I'm saying is: to be not just a good but an amazing DBA you need both the technical chops AND the empathy to put yourself into the end-users situation. Having both of these skills will make you a very valuable employee, well-liked and respected, and never wanting for work (or friends)!