Brian's Ramblings on SQL Server

Tech Content Has Moved

2011-04-26T08:35:00.000-04:00

I am excited to announce that my technical content has been moved to LessThanDot. From now on all my SQL & tech related posts will be there. I'm still planning on keeping this blog around for the short tech history and continuing with blogs about life, running, and other stuff.
Please take a minute to update your feeds:
While you can subscribe to just my posts at LessThanDot I suggest you subscribe to the main feed for even more great posts...
My Posts Only: Link
Main LessThanDot Feed: Link

Giving Back: Speaking, Blogging, & Community Involvement

2011-03-10T15:10:00.000-05:00

I've always said that I wouldn't be where I am today without the generous help and encouragement of the SQL Server Community and that is absolutely true. It's also true that I wouldn't be continuing to grow and learn as quickly as I am without the continued support of the community. While I've taken a lot from the community, I believe it's past time for me to start giving back as much as I can. I've always tried to do my part to help those physically around me, but I never felt I was at a level to give back to the community at large. I was wrong and I thank those around me who have never stopped encouraging and pushing me.

There Are So Many Opportunities To Give Back

Here's a few ideas to get you started thinking about how you can to give back to the community. You don't have to do them all, pick one that sounds interesting and start with that.

Join a Local User Group

To find one in your area check out the PASS Chapters Page
Also take a look at the Virtual Chapters that are available

Forums

Read and help answer questions in forums
SQL Server Central, SQL Team, StackOverflow

Blog - Start a blog and write about your experiences and interests

Brent Ozar (blog | twitter) has a great blog series on Starting a Technical Blog

Start Speaking

Choose a topic that you are interested in, passionate about, or want to learn about
This ties in great with blog posts

Twitter

Join and start following those in the SQL Community
Follow the #SQLHelp hash tag
If you don't have the first clue about Twitter, check out Brent's Twitter Book

Don't be afraid, whatever you decide to do. You don't have to know everything or be "an expert" to participate and give back to the community. Just remember to be kind and not be rude. The SQL Community is very welcoming, appreciative and helpful. There will be times when you will be wrong and that's OK. Learn from your mistakes and move on.

If you decide you want to present, pick a topic that interests you. Case in point, after being asked to co-present a session at the ONSSUG on Source Control for DBA's, I decided I wanted to continue speaking (ok, maybe there was a little prodding from Allen White (blog | twitter) which I truly thank him for) but I really did want to continue presenting because I enjoyed it. I knew I wanted to present but I wasn't sure what topic to pick. Then I saw that someone posted a session request for Service Broker for SQL Saturday #60. I thought, "I've always wanted to learn more about Service Broker" and now was a good chance. I started learning Service Broker, working with it and building a presentation. It took a lot of work but I enjoyed every minute of it. I submitted the session for SQL Saturday #60 and it was accepted. I'll admit, it was a little nerve racking giving my first solo presentation at a SQL Saturday but it was a great experience and I wouldn't trade it for anything. The session received good reviews and I was pleased for my first presentation. I would however suggest, that if you are thinking about starting to present you start with a local user group or even peers from your company. Then move up to a SQL Saturday, followed by SQL Rally and then onto the SQL PASS Summit or SQL Connections. When you are presenting there will be times where someone will ask a question you don't know the answer to and that's OK. Be honest, tell them you don't know, but also let them know that you will look into it and get back to them. You can let them know via an email or better yet, tell them to watch your blog and write about it. Again, the point is you don't have to know everything there is to know about a subject to present and to give back to the community. Talk about what you know and learn what you don't that interests you. If you find a topic interesting your presentations will be that much more interesting to the audience.

The same goes for blogging, write about what interests you and your experiences. Just because someone already blogged about a topic doesn't mean you can't. Make the topic your own by including personal experiences or situations and how you dealt with them. If you reference someone else's material or blog be sure to give credit to them and link to the material. Whatever you do, don't use someone else's material as your own.

Balance Your Time

Whatever you decide to do, make sure you balance your time. It's easy to get sucked in and spend a lot of time on whatever it is you are doing. Don't burn yourself out in a short period of time. Make sure you leave time for yourself and your family. Having a wife and two young boys (ages 6 & 3) myself, I know it's not easy to try to fit everything in and find a good balance, I'm still working on it. Set some time aside specifically for doing whatever it is you choose to do and stick to it as best as possible. It's a balancing act that will be forever changing as your life and interests change but it's possible to do it. Remember you don't have to do everything all at once. Some things make sense to do together, some don't. Look around, check out your options and choose what works for you.

What Am I Doing?

Personally, I started by getting involved with my local user group, ONSSUG, which lead me into starting this blog and presenting. Eventually, I'd love to become more active on some of the forums but for now that is waiting until I become more comfortable with my current routine. This blog isn't where I want it to be yet, but I'm working on it and it will get there. As I said earlier, I'm also presenting at user groups and SQL Saturdays. Check out my Events page to see where I'll be presenting. If you're there, stop by and say 'Hi', I'd love to meet you.

My Thank You's

To the SQL Community as whole, I say thank you from the bottom of my heart for being so open, welcoming and encouraging to myself and everyone else. This community is truly the best!

There are a few people that I need to personally thank for their efforts in bringing me out of my "offline shell"...

First, to Allen White (blog | twitter), thank you, I couldn't have done all this without you. Thank you for welcoming me into the user group and getting me involved. You are and continue to be a great guide and mentor.

Secondly, to Sarah Dutkiewicz (blog | twitter) who was one of the first people I met at the first user group meeting I attended and who has continually encouraged me and provided great insights. She was also kind enough to give me one of the MSDN subscriptions she received for being awarded her MVP status. I can't thank you enough.

To Brent Ozar (blog | twitter) & Tom LaRock (blog | twitter), thank you for inspiring me to blog and always being willing to talk, listen, & offer advice to not only myself but anyone in the community! I met both of you at my first PASS Summit in 2009 and that was where I first learned how truly wonderful the SQL Community is. It took a couple of years but I finally got my blog up and running.

To Paul Randal (blog | twitter) & Kimberly Tripp (blog | twitter), thank you for everything you do and the knowledge you share. My first experience with the SQL community and conferences was sitting in one of your pre-cons at SQL Connections in 2007. I was amazed and inspired at your dedication and willingness to talk to and help anyone who came your way.

Summing It All Up

Give back to the community in any way you can. Help make it even better and more amazing than it already is. You don't have to be an expert to do any of this, you simply need to have the interest and desire to participate, learn and share your experiences with others. The SQL Community is great because so many people want to learn and help others learn what they already know!

Trevor: Best Wishes From Cleveland

2011-02-14T20:59:00.000-05:00

Hi Trevor,
We are no place exotic, but we are thinking of you and wishing you a speedy recovery.
Here's a picture of us in our backyard in Cleveland, Ohio.

(Brian, Jenn, Tyler, Nate)

And another, warmer picture, from Devil's Lake, Michigan.

Hoping you feel better soon!
The Davis Family

TSQL Tuesday #15: Automation

2011-02-08T18:32:00.008-05:00

Now that I've started blogging I decided to throw my hat into the ring for TSQL Tuesday, hosted this month by Pat Wright aka SQLAsylum (Blog | Twitter ) and the topic is Automation.

Recently, I needed to compile a listing of all the accounts that were running all of the SQL Server services in the environment. After throwing the manual option out the window and looking at a few other options I decided that PowerShell would provide the best solution. In my environment I have dedicated database that houses information I've collected about all of the servers. One of the tables just happens to contain a list of servers. Using PowerShell I'll scan through the list of servers and then run a WMI query against each to get the accounts for each SQL service on the server. Once I have the accounts and their associated services I then upload that information to a table in my database. From there the data can be exported or displayed via SSRS.

The code is pretty well documented so let's take a look at the script (download link at end of post).

# =======================================================================
# SCRIPT BODY
# =======================================================================
#Initialize the SQL Environment
.\Initialize-SqlpsEnvironment.ps1

#Define Counter Variables
[int]$count=0
[int]$total=0

#Define the destination server and database names
$sqlsrv = "SQLSrv01"
$destdb = "SvcDB"

#Clean out the SvcInfo Table
$q = "delete LoadSvcInfo"
$reslist = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
Write-Host "Clean SvcInfo Table"

#Define the list server and database
$listsrv = "SQLSrv01"
$listdb = "ServerDB"

#Get server list
$q = "select distinct servername from ServerList order by servername"
$reslist = invoke-sqlcmd -ServerInstance $listsrv -Database $listdb -Query $q
$total = $reslist.length
foreach ($strSrv in $reslist){
#Increment Counter
$count++

#Display current server & count
Write-Host "Server: " $strSrv.ServerName " " $count " of " $total

#$m = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $strSrv.ServerName

#Get Service Account Info
$colItems = gwmi win32_service -computer $strSrv.ServerName -property name, startname, caption | where {$_.Name -like "*SQL*"}
foreach($objItem in $colItems) {

#Send info to our database
$q = "insert [SvcDB].[dbo].[SvcInfo] (SrvName, SvcName, SvcCaption, SvcAcct) Values ("
$q = $q + "'" + [string]$strSrv.ServerName + "', "
$q = $q + "'" + [string]$objItem.name + "', "
$q = $q + "'" + [string]$objItem.caption + "', "
$q = $q + "'" + [string]$objItem.startname + "')"
$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
}

$colItems = gwmi win32_service -computer $strSrv.ServerName -property name, startname, caption | where {$_.Name -like "MSDTSServer*"}
foreach($objItem in $colItems) {

#Send info to our database
$q = "insert [SvcDB].[dbo].[SvcInfo] (SrvName, SvcName, SvcCaption, SvcAcct) Values ("
$q = $q + "'" + [string]$strSrv.ServerName + "', "
$q = $q + "'" + [string]$objItem.name + "', "
$q = $q + "'" + [string]$objItem.caption + "', "
$q = $q + "'" + [string]$objItem.startname + "')"
$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
}
#$m = $null
}
Write-Host "DONE!"
#=======================================================================
# END OF SCRIPT: [ServiceAccounts]
#=======================================================================

Download Zip of ServiceAccounts Script

SQL Saturday #60 - 2 Firsts in 1 Day

2011-01-29T23:58:00.001-05:00

This coming Saturday (2/5/2011) the SQL user group I am a part of, Ohio North SQL Server User Group, will be hosting its' first SQL Saturday #60. It's been a lot of fun helping to organize what I know will be a great event. We've got speakers coming from all over along with many great local speakers and the session lineup is amazing. If a day of free SQL Server training from some outstanding speakers isn't enough to get you to come to Cleveland in February, check out the Allen White's (Blog | Twitter) Top Ten Reasons to Come to Cleveland in February. If that wasn't enough we also have a great after party planned at Wingz followed by SQL Karaoke at Grillers Pub. Come join us for a great day of SQL Server, networking, and a lot of fun!

On a personal note, SQL Saturday #60 will be special for me for two reasons. First, it will be the first SQL Saturday I've attended. Second, it will be my first time presenting. I'm presenting "Service Broker...What's That & How Do I Use It". This will be a beginner level session where will take a look at what Service Broker is and how it works. I'll walk through the setup and configuration along with some usage examples. I'm really looking forward to presenting and getting my first presentation under my belt. Hope to see you at SQL Saturday #60.

SQL Server Separation of Duties Framework 2.0 Released!

2011-01-24T10:00:00.017-05:00

Last November at PASS I attended a session entitled "Si Se Puede! Achieving Separation of Duties with SQL Server" presented by Il-Sung Lee and Lara Rubbelke (blog | twitter). Lara and Il-Sung discussed the separation of duties between the DBA and the sysadmin role and presented the SQL Server Separation of Duties Framework to help facilitate this.
Version 2.0 of the Separation of Duties Framework has just been released and it can help you create a controlled environment to manage permissions for various levels of DBA's and/or users.

Do you ever need to grant a user specific elevated permissions?
What about granting some junior level DBA's the ability to unlock a login but not the ability to alter it?

Using the SoD Framework you can setup multiple roles with specific permissions and assign users to them as necessary. This framework can help accomplish these tasks easily and in a controlled environment. It's completely user definable and as of this release is capable of handling multiple tiers of users. Check out
Lara's latest blog post for more information. Check it out and leave us a comment if you have any questions or issues.

It's been a great experience working with Lara Rubbelke (blog | twitter) on version 2.0 of this framework and we are looking forward to continuing the development of this project.

New Year, New Resolutions

2011-01-17T21:55:00.053-05:00

It's a new year...

I'll be married for 9 years in April
My oldest son will be 7 in November
My youngest son will be 4 in July
I'll be speaking at my first SQL Saturday in February (SQL Saturday #60 in Cleveland) and hopefully more afterwards
I'll have been at my current job for 4 years in September

New Resolutions

With a new year comes new resolutions. In the past I haven't always stuck to my resolutions but this year, more than any other, I plan to. These resolutions are important me and will help get me to where I think I want to be in life, so this year, more than any other in the past, I plan to accomplish them. So, without further to do, in no particular order, here are my resolutions for 2011.

Blog at lest once a month
Get out and run at least twice a week
Contribute as much as I can back to the SQL community that has given me so much
Continue to be involved with the Ohio North SQL Server User's Group
Be involved in my kids lives and have fun with them

Even though it's a bit late, Happy New Year to all. I hope you have a great year!

Hello Blogosphere - Who I am & Why I'm Here

2011-01-02T20:56:00.002-05:00

Short BIO
My name is Brian Davis and I've been in the IT field for almost 15 years. I started as a developer working with FoxPro 2.6 for DOS/VFP 6/VB6, moving up through VFP7/8/9. In 2003 the company I worked for asked me if I'd be interested in doing double duty as a developer and DBA. I said yes and started working with SQL Server 2000 and never looked back. Since then I've changed companies and became a full time database analyst. I'm currently one of a handful of DBA's responsible for maintaining servers running everything from SQL 2000 to 2008R2. I've also become involved with the SQL User Group here in Cleveland, OH.

Why Blog?
After being inspired by many people in the SQL Server community I've decided to start writing my own blog. I've found more helpful tips and tricks than I can remember from blogs written by generous members of the SQL community. My hope is that by blogging about my own experiences that I will be able to give something back to community that has helped me so much.

What to Expect Here?
My plan is to post about my experiences with SQL Server. The pitfalls I encounter and the lessons I learn, along with any scripts/code that I've created or found useful. I've already got a few blog posts lined up and will be working on them in the days and weeks ahead. Along with that I've also been working on a plug-in for SSMS that I am planning to release.

Thank You!
Finally, I want to say thank you to the SQL community for all the support and information you've shared with me. Without the community I wouldn't be where I am today.