Posted by: sqlswimmer | May 12, 2015

TSQL2sday #66 – Monitoring

A big thank you to Cathrine Wilhelmsen (blog | twitter) for hosting this month’s TSQL2sday party. Monitoring is this month’s topic and it’s a very important one. It could mean the difference between having a job and looking for a job.

TSQL2sDay150x150When I started working with SQL Server (a long time ago, in a galaxy far far away) there were no commercial monitoring tools available and quite often I would get called or paged (yes, it was a long time ago) in the middle of the night by a very angry boss because there was something “wrong” with the database. Or worse yet, I would get no call at all and show up at work the next morning with a line of angry people waiting for me when I got off the elevator. It only took a couple of these encounters for me to realize that I needed to be much more proactive or change my line of work (I had heard that underwater basket weaving was an easy gig).

I started looking at the reasons I was being called and discovered most of them were things that could easily have been avoided if I had known about them earlier. Things like database and transaction log files filling up, running out of disk space, processes/queries that were taking increasingly longer and longer. Since there were no commercial monitoring tools out there I decided I needed to essentially roll my own.

I had to start looking under the covers to find what I was looking for. This gave me an even greater exposure into how SQL Server worked. Did I mention that this was before Google? I couldn’t just search for easy answers, I had to really dig in the system databases to find what I wanted. This was in fact, one of the best things that could have happened to me so early in my career as a DBA. I was forced to learn how SQL Server worked on my own.

To this day, I still “carry” around my home grown monitoring solution in my toolbox. I have updated it and expanded it through the years to accommodate newer versions and functionality and made it more efficient based on both of those things. Not all shops have the budget for monitoring tools and even if they do, a lot of the time they are only willing to spend that money on production servers, not development or test (don’t get me started, that’s an entirely different blog post).

My little monitoring solution has come in handy over the years because it has afforded me the opportunity to discover what’s under the covers of the newest version/features of SQL Server and provide a no cost basic monitoring solution to my employers when the budget is tight or non-existent. If you don’t have your own monitoring solution I would highly recommend you create one, if for nothing more than the reasons I stated above.

Don’t get me wrong, I love the commercial monitoring tools that I have access to now, but knowing the how and why of SQL Server will only make you a better DBA and could possibly mean the difference between having a job and looking for a job.

Posted by: sqlswimmer | April 14, 2015

Becoming a PASS Chapter Leader – Challenge Accepted!

My first real introduction to PASS was back in 1998. I was an “Accidential DBA” and needed some training. I heard about this conference in Chicago and my boss agreed it was the best place to start. Little did I know how that conference would influence the rest of my career.

Fast forward to 2010, after taking a slight hiatus from SQL Server, I was ready to get back into it. What better place than a local user group? After moving over 1,600 miles (a couple of years earlier) from where I started my career in SQL Server, I needed to make some new local connections. That’s when I was introduced to the Triad SQL Server User Group. At the time, Miguel Cebollero (B | T) was the chapter leader. Miguel was so friendly and welcoming and I remember Kevin Goode was presenting a session on CLR and how it really is NOT the devil’s work, but a great tool if used correctly. I was hooked.

In early 2011, Miguel decided to move back to his home state of Florida and handed the reins over to Kevin. Kevin moved the meeting location from Winston-Salem to Greensboro and I attended meetings on a semi-regular basis, but let’s face it, life just gets in the way. By 2012, our meeting attendance started to drop off and a new BI Group had started up in Winston-Salem. It became obvious that something had to change. Kevin had the foresight to work with the new BI group and asked for volunteers to help him out with the chapter. I decided to step up as “Speaker Wrangler” and we worked out a schedule with the new BI group that would be mutually beneficial.

Now it’s 2015 and Kevin has taken on a new role in his company which means he has less and less time to devote to the user group. When Kevin approached me about taking over the user group I was both honored and excited (and a little scared) that he considered handing the reins over to me. I have accepted the challenge and I hope that I can continue to lead the group as well as both Kevin and Miguel did and provide a much needed resource to local SQL Server professionals.

We will be a little more active on social media, we tweet at @TriadSQL and use the #TriadSQL hashtag, and welcome any suggestions that will make your user group more useful to you.

I’ve been responsible for administering Analysis Services (SSAS) on a regular basis for the past 7-8 years via SQL Server Management Studio (SSMS), but developing is a whole different ball game (and set of tools). I only recently started doing development work with SSAS on a regular basis. Prior to that I dabbled once every two to three years, enough to be able to say, “Yes, I can do that, but I can’t remember exactly how.” Fortunately the tool used for SSAS development is a tool that I am very familiar with, Visual Studio (or as we data professionals now call it, SQL Server Data Tools).

I recently had to change some security for our cube and was tasked with modifying the role membership. Now this most likely seems like a no brainer and it is, as long as you know where the buttons are. What buttons am I referring to? Those precious Add and Remove buttons that allow us to modify the members. When doing development work in SQL Server Data Tools (SSDT), there are all these nice menus and toolbars at the top or on the left hand side. So when I went to do some work on the role membership, imagine my dismay when I could find no way to add/remove users to the role via the menu or toolbar.

I must have spent a good 10 minutes looking through all the menu options and toolbars, only to discover there is no way to add/remove members in any of those. So I of course freak out mode ensued. I must have the wrong version of SSDT, I must not have the right permissions to be able to add/remove users, the list goes on and on. I took another look, I mean look at all that screen real estate. You have that HUGE white space that is just dying to be used and no way to use it.

Post26Pic02

Then it happened, I the saw the light (or buttons as it were), hiding way down there at the bottom of my screen.

Post26Pic03

Like I said, this may seem like a no brainer, especially for someone who does SSAS development work on a daily basis, but for those that are new or only use it every so often, it’s frustrating. After seeing someone ask where the buttons were via #sqlhelp on Twitter, I decided I needed to write this up.

I hope this saves some other unsuspecting soul the frustration that I experienced.

Posted by: sqlswimmer | March 19, 2015

Achievement Unlocked: 24 Hours of Pass Session Submitted

I have finally submitted a session for a speaking engagement. Those who know me know that this is a very BIG deal. I have a horrible fear of public speaking, but I am slowly starting to embrace that fear. Last year I presented at the Triad SQL BI User Group and I didn’t hyperventilate, no one was more surprised than me.

I submitted the same session that I did at the Triad SQL BI User Group, Introduction to Integration Services (SSIS), to the upcoming 24 Hours of PASS: Growing Our Community.

I wanted to be a teacher when I was in college until I discovered my fear of public speaking. Now, more than 20 years later, maybe I can finally reach that goal.

Fingers crossed that my session is accepted.

Posted by: sqlswimmer | February 11, 2015

Automating SSAS Backups

Backing up databases is one of the most important jobs of a DBA. If your data is not safe, your job is not safe. Data is the lifeblood of a DBA. That said, there are so many products out on the market that will help with backing up transactional databases in SQL Server, but when it comes to Analysis Services (SSAS), you are on your own. That’s what I discovered when I became responsible for a SSAS database.

The good thing, is that there’s a very simple way to back up your SSAS databases. SQL Server Management Studio (SSMS) has this great feature that allows you to script just about anything you need to do, including backing up a SSAS database.

Here’s how:

  1. Open up SSMS and select the Analysis Services server type in the Registered Servers window.

Connect to Analysis Services

  1. Double-click your server name, so that it appears in the object explorer, then expand the databases folder. Right click on the database you want to backup and select Back Up…

Right-click your database

  1. The Backup Database dialog opens. Fill out the values appropriate for your environment. I highly recommend encrypting your backup files, just don’t forget what the password is otherwise you will never be able to restore your database.

Backup Database dialog

  1. Instead of clicking the OK button when you are done, click the little arrow next to the Script button at the top of the screen and select Script Action to New Query Window. Click the Cancel button to cancel the Backup Database dialog.

Script backup

  1. You should now have an XMLAQuery window in SSMS that contains the commands to back up your database.

XMLA Code

Wow, that was easy. Now you can create a SQL Agent job and just paste this XMLA query in the job step (be sure to select SQL Server Analysis Services Command as the job step type) and call it a day. But you probably shouldn’t. As you will notice, I selected the Allow file overwrite option in the Backup Database dialog and that is reflected in my XMLA script with the AllowOverWrite tag set to true. So, if I created a SQL Agent job to run every day and used this as my job step, I would never have any backup history, I would only have the most current backup. For some shops, this will be okay, for others, it won’t. In my shop it wasn’t enough. Policy dictated that I keep one week of backups, regardless of whether it was a transactional database or an OLAP database.

Luckily, PowerShell and I have become good friends. I was able to quickly create two additional steps in my SQL Agent job that utilized PowerShell commands to achieve my goal of maintaining one week of backups. I created one step to rename the backup file by appending the current date to the file name and the other step I created to clean up any old backup files, so that I didn’t fill up my hard drive with backup files. Here are my scripts.

Rename file:

cd c:
$today = get-date -uformat "%Y%m%d"
$oldname = "\\uncfilepath\Databasename.abf"
$filepath = "\\uncfilepath\"
$newname = $filepath + "Databasename_" + $today + ".abf"
rename-item $oldname $newname

 

Clean up old files:

cd c:
$RetentionDate = (Get-Date).AddDays(-6)
$FilePath = "\\uncfilepath"
Get-ChildItem $FilePath -recurse -include "*.abf" | Where {($_.CreationTime -le $RetentionDate)} | Remove-Item –Force

 

I won’t go into detail about my PowerShell script here, it’s mostly self-explanatory, with the exception of the first line in each, cd c:. I discovered that since I was using a UNC path, I needed to add this little tidbit to the beginning of each script otherwise the steps would fail. This is because the version of PowerShell that is being invoked inside a SQL Agent job is not EXACTLY the same version that is invoked outside of SQL Server.

Posted by: sqlswimmer | February 10, 2015

Managing Security – TSQL2sday # 63

A big thank you goes out to Kenneth Fisher ( b | t ) for hosting this month’s TSQL2sday party. Security is a big deal. How many times have you opened the paper (I’m dating myself, I know – no one reads an actual newspaper anymore, it’s all online now) in the last 6 months and there’s a story about another security breach, more records compromised or flat out stolen? Too many. While securing your data is probably the key to keeping your current employment status, there’s also a piece of security that is quite often overlooked and could be the reason for a resume generating event. Recovering from a failed server when you don’t use any of the HA features that are now available.

TSQL2sDay150x150

The scenario:
Your production server has failed and you don’t use any of those new fancy HA features like Always On Availability Groups, Log Shipping or even Database Mirroring. Your server hosts a standalone instance for the HR/Payroll department. Payroll must be processed in the next two hours or your company will be out of compliance with Federal Regulations and face heavy fines, not to mention all the really mad employees who won’t get their paychecks on time. I don’t know about you, but I do NOT want to be responsible for every employee not getting a paycheck, including myself.

You have a good backup plan in place, you take full, differential and log backups on a schedule that meets the minimum required data loss SLA and send those backups to a remote SAN data store. Your Sysadmin stands up a new standalone server for you in 30 minutes. You install and configure SQL Server in about 60 minutes (those pesky service packs and cumulative updates can take quite a bit of time). Now you are left with 30 minutes to get your databases restored and functioning. No sweat! Easy as 1..2..3, right? Wrong!

You restore your database only to discover that all your logins no longer exist on your brand new server. No problem, just recreate the logins and give them brand new passwords (SQL Authentication). All will be right with the world. You give your HR/Payroll department the okay to proceed and you catch your breath with 20 minutes to spare. The phone rings 5 minutes later, it’s HR/Payroll and it’s not working. They are getting invalid login errors. You have that momentary flashback to when you helped with the application install 4 years ago – the vendor hard coded the password into their application code, so you can’t just change it or give it a new password. That’s when you remember that you created a job to script the logins with their passwords on a weekly basis and saved the results off to file on that same remote SAN data store as the backups. Hallelujah! You find your script on the remote SAN data store, clean up the logins you created, then execute the script with the logins and their passwords. HR/Payroll is back up and running with 4 minutes to spare.

Paychecks for everyone!

While some of this may seem far-fetched, it’s based on an actual incident very early in my career. I may have embellished a little, but you get the point. You need to make sure you can recreate any login on your server at any time due to disaster/failure. If you can’t, you may just be looking for a new job.

To this day I still script the logins on all my servers on a weekly basis. I store that file in a secure location on a remote server. I’ve never had to use one since this original incident, but it’s nice to know that I can recreate the logins if I ever need to. Can you?

Posted by: sqlswimmer | December 15, 2014

Transaction Isolation Level Blues

Have you ever had a mental block in one particular area when learning something? It might be the simplest thing, but for some reason your brain turns to Teflon when you try to store the information. For example, I have a degree in Math, so I am pretty good at arithmetic, but for the life of me I cannot remember what eight plus five is. I always have to break out my phalanges to get the answer.  Why the Hell can I remember what phalanges means and not a simple thing like eight plus five?!

I have this same problem when it comes to Transaction Isolation Levels in SQL Server. I can remember that there are five of them, Read Uncommitted, Read Committed, Repeatable Read, Snapshot & Serializable, but I cannot remember the little nuances that set them apart. It’s total Teflon. So I decided it was time to come up with a little song to help me remember. My older sister is a preschool teacher and she says that if you learn something as a song, it sticks with you for life. Here’s hoping that is true!

This is sung to the tune of George Thorogood’s Bad to the Bone.

At the time I am used
No Shared locks are issued
Not blocked by X locks
It is Loosey-Goosey
Just call me crazy
No restrictions abound
I could tell right away
It was Read Uncommitted

Bad to the bone
Bad to the bone
B-B-B-B-Bad
B-B-B-B-Bad
B-B-B-B-Bad
Bad to the bone

Not breakin’ any rules
Going by the book
Not readin’ any uncommitted
Transactions it’s true
I am the default baby
Transactions alone
I’m Read Committed
That’s what I do

Bad to the bone
B-B-B-Bad
B-B-B-Bad
B-B-B-Bad
Bad to the bone

No readin’ ’til committed
Can’t read dirty data either
I use shared locks baby
And hold ’til committed
I’m the repeatable read baby
Yours and yours alone
Data’s all yours honey
And I’m bad to the bone

B-B-B-B-Bad
B-B-B-B-Bad
B-B-B-B-Bad
Bad to the bone

When I query data
Kings and Queens step aside
Every bit I meet
It’s mine it’s all mine
Serializable baby
Range blocks on keys that’s me
HOLDLOCK does the same thing baby
Serializable oo-ee

Bad to the bone
B-B-B-B-Bad
B-B-B-Bad
B-B-B-Bad
Bad to the bone

(Extra verse)
There’s no write blockin’
While I’m readin’
No locks less I’m recoverin’
You can’t switch to me
But I can switch to you
I’m a snapshot baby
A photo just for you

Bad to the bone
B-B-B-B-Bad
B-B-B-Bad
B-B-B-Bad
Bad to the bone

If your brain is Teflon when it comes to Transaction Isolation Levels, then I hope this helps. If not, I hope you got a good laugh and please don’t tell George Thorogood what I did to one of his best songs (and one of my favorites).

By the way, eight plus five is .. thirteen.

Posted by: sqlswimmer | December 9, 2014

Giving Back T-SQL Tuesday #61

First off I wanted to thank Wayne Sheffield (Twitter | Blog) for hosting this month’s T-SQL Tuesday party and Adam Machanic (Twitter | Blog) for starting this party five years ago. I can’t believe it’s been five years.

TSQL2sDay150x150

This month’s theme is Giving Back to the SQL Community.

This is a great topic, it’s kind of like the Dickens’ holiday classic, A Christmas Carol. It gives me an opportunity to reflect on what I have done in the past, what I am doing now and what more I could be doing to give back to the community that has helped me so much in my career.

Past Giving

Member of the Abstract Review Committee (2010-2014)
Member of the Nomination Committee (2012)
“Speaker Wrangler” for Triad SQL, the local PASS chapter in Greensboro, NC (2012-2014)
PASS Ambassador at Summit (2010-2014)
24 Hours of PASS moderator (2012-2014)
PASS Summit Buddy (2013)
Chosen as a mentor in Steve Jones’ and Andy Warren’s The Mentoring Experiment (2012)

Present Giving

“Speaker/Sponsor/Swag Wrangler” for Triad SQL, the local PASS chapter in Greensboro, NC
Program Manager with the Program Committee for the PASS Summit.

Future Giving

I will continue my work (as long as they will have me) with the Program Committee. I absolutely love this volunteer position, it allows me to do something I enjoy while helping the community. It’s a win-win. I will also continue my position with Triad SQL. I have stepped it up a notch by wrangling not only speakers but sponsors and swag as well for 2015.

I enjoy moderating the 24 Hours of PASS too. It always gives me an opportunity to connect with some of the speakers that I’ve never had interaction with before. Just this year, I moderated for Gail Shaw (Twitter | Blog) and then got to meet her in person at Red Gate’s SQL in the City event in Seattle just before the Summit. How cool is that?!

I have a blog, in fact you’re reading it right now (wink wink), but I don’t write nearly enough. Part of me feels, “Surely someone has written about this before, so why should I clutter up cyberspace with my drivel?” and the other part of me feels, “Maybe my post can help someone who couldn’t quite make heads or tails of something they found via Google.” So for next year, I am setting a goal to write/blog at least once a month. I bet T-SQL Tuesday can provide the subject matter for those months where nothing interesting happens at work.

My favorite way, by far, to give back to the SQL Community is by being a PASS Ambassador during the PASS Summit. I get to see all the eager faces ready to stuff their brains to the point of overflowing. I get to help someone find the registration desk so their adventure can begin. I also get some of the first hugs of the Summit just by smiling and answering questions. It really is the best. So, as long as this program exists and I’m attending the Summit, I will continue to be a PASS Ambassador.

Lastly, I may even try speaking this year.  This is a huge deal for me, so I make no promises, other than to think about speaking.

How are you giving back?

Posted by: sqlswimmer | November 10, 2014

Summit 2014

It’s hard to believe it’s over.  It felt like a whirlwind while I was in Seattle for my 7th PASS Summit, but now that I’m back home it feels like it was ages ago.  I think time moves more quickly when you’re with friends and that’s where I was, with friends.

I got to reconnect with old friends and meet new ones.  I didn’t attend nearly as many sessions as I would have liked, because let’s face it, cloning technology isn’t quite where it needs to be as Michael Keaton found out in Multiplicity.  With my luck my “Number Four” would have attended one of Paul Randal‘s sessions and I would have wound up doing God knows what to my servers when I got back.

I also got to meet people that I have “worked” with for quite a while virtually, but never met in person.  I must say it’s always refreshing when their “in person” exceeds your expectations.  There are so many genuinely nice people in our community, I am truly in awe.

In years past I have not been able to participate in most of the after-hours activities due to Summit happening right before a big annual swim meet, which meant I couldn’t take a break from training.  This year, my swim meet was the week before Summit so I didn’t need to get up at 4:30 a.m. every morning to make it to practice before breakfast.  I got to see how the “other half” lived at Summit this year.  I must say it was eye opening and entertaining.  They don’t have next year’s swim meet on the calendar yet, but I have the Summit dates, so next year’s meet just may have to go on without me.

If you’ve ever attended a PASS Summit, you know what I’m talking about when I say I’ve already started the count down until next year’s Summit.  If you’ve never attended a Summit, what are you waiting for?

Posted by: sqlswimmer | November 4, 2014

Data Driven Subscription On A Budget, Part 2

Data Driven Subscriptions On A Budget – Part 2

Yes, this is Part 2, you can find Part 1 here to get background information.

This blog will talk about Point 3 – Distribute a report to a fluctuating list of subscribers.

Distribute a report to a (fluctuating) list of subscribers

When using email as your method of delivery for a Data Driven Subscription, best practice is to use metadata to drive that process. Usually a simple table that contains the email address of the recipient and the report name does the trick. This part of the process is no different if you don’t have Data Driven subscriptions. I usually create a table similar to this:

CREATE TABLE dbo.SSRSEmailSubscribers
(
EmailAddress varchar(128) NOT NULL
,ReportName varchar(128) NOT NULL
)

Let’s say I have a sales report that needs to go out on a daily basis and the standard format for this report is Excel. Because we don’t have data driven subscriptions, we can’t just query the table and use the resulting list to email the report. Instead we need to create a File Share subscription that generates this report and saves it to a file share. From there we can “pick up” the newly generated Excel file and email it to the recipients.

  1. Create a subscription to your Daily Sales Report in Report Manager, schedule it for a one time execution at a time just a few minutes in the future, and remember the execution time. (This creates the SQL Agent job in SQL Server.)
  2. Take a look at your SQL Agent jobs in SQL Server. If you have never seen or noticed a job created by SSRS, then you will be wondering where your job is because SSRS does not use human readable names for its jobs, it uses those pesky GUIDs as names, ugh! If your server has lots of jobs you may need to sort your jobs by Category to get all the “Report Server” jobs together. Find the job that executed at the time you scheduled (this is why you needed to remember the time!), this will be the job you will need to reference in the next step.
  3. Create a new SQL Agent job and add a step for Transact SQL script. In this new step you need to execute the SQL Agent job that you created back in step 1:

exec msdb.dbo.sp_start_job N'B514C05F-07D5-4C0B-9600-666E9980C7C3'

    where B514C05F-07D5-4C0B-9600-666E9980C7C3 is the GUID from the job that SSRS created.
  1. Next you will need to add a new job step for PowerShell. In this newly created step write your PowerShell script to get the file that was generated (as a result of executing the previous step) and retrieve your list of email addresses. Once you have this information you can send the email with the report attached. There are several ways you can do this, but I chose to use PowerShell. Since PowerShell requires a double quoted semicolon (;) delimited list of email addresses when using SMTP, I wrote my SQL query to return a double quoted semicolon (;) delimited list of the email addresses. You could have just as easily used PowerShell command-lets to format your list. Here’s my PowerShell script:

cd c:

$FilePath = "c:\temp\"
$smtpServer = "10.0.0.4"
$smtpFrom = noreply@email.com

$AddressQuery = "DECLARE @List varchar(MAX);"
$AddressQuery = $AddressQuery + "SELECT @List = COALESCE(@List + '"";""', '') + EmailAddress "

$AddressQuery = $AddressQuery + "FROM dbo.SSRSEmailSubscribers "
$AddressQuery = $AddressQuery + "WHERE ReportName = 'Daily Sales Report'; "
$AddressQuery = $AddressQuery + "SELECT '""' + @List + '""';"
Invoke-Sqlcmd -Query $AddressQuery -ServerInstance "MyServer" -Database "MyDatabase" -Variable $smtpTo

$messageSubject = "Daily Sales Report was executed"
$latest = Get-ChildItem -Path $FilePath -Filter "*.xlsx" | Sort-Object CreationTime -Descending | Select-Object -First 1

$FullFileName = $FilePath + $latest
$body = "Attached is the Daily Sales Report"
send-mailmessage -from $smtpFrom -to $smtpTo -subject $messageSubject -body $body -smtpServer $smtpServer -Attachments $FullFileName

Now schedule this newly created SQL Agent job for the time you need your Daily Sales Report to be executed and Wa-La, you now have a data driven subscription that distributes a report to a fluctuating list of email addresses.

Older Posts »

Categories

Follow

Get every new post delivered to your Inbox.

Join 243 other followers