Category Archives: SQLServerPedia Syndication

This category is for SQLServerPedia Syndication

Pittsburgh SQL User Group is Back!

Posted on April 28, 2011 by | Leave a comment

It was an honor to be the first presenter for the reboot of the Pittsburgh SQL Server User Group on Tuesday.  I gave a sneak peek into my SQL Rally presentation, “Evaluate your daily checklist against 100+ servers while you get a cup of coffee.” I knew it was a success when I got home and saw the following tweet.

This tweet is purely SQLWinning

This tweet is purely SQLWinning

If you attended the session feel free to download the slide deck and check out my reference material.

Question and Answers

I get a kick out of helping people solve problems. If I don’t know the answer this gives me the opportunity to learn something new. Here are a few questions that were asked during my session. I didn’t have the answers of the top of my head so I am including them in this post.

Q: “How can I automate the process of shrinking a T-Log?”

A: First I highly recommend reviewing your backup and recovery plan. Frequent backups of the transactional log is key to free up your VLF’s. If there isn’t a free VLF available the log grows. With that said, if you just want to automate the process of shrinking T-Logs check out Jeremiha Peschka’s script

Q: “How do I run a T-SQL script against all databases on a single instance?”

A: First, I was thinking about doing a simple loop in PowerShell to execute the T-SQL (see Aaron Nelson’s second script) but then I remembered an undocumented stored procedure. The undocumented stored procedure is sp_msforeachdb. Both options should work.

Photos

Below is a few photos taken before I started the presentation.

Right side say, "cheese"

Right side say, "cheese"

Left side say, "gouda"

Left side say, "gouda"

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Changing SQL Server Job Owners against the SQL Server Enterprise

Posted on April 14, 2011 by | 9 comments

Last night on twitter an interesting question was asked by @just_samson using the #SQLHelp hash tag. He asked, “can you change the owner for jobs &dbs across instances using Policy Based Management and Central Management Server?”  The answer to this question is no because an agent facet doesn’t exist. Users cannot create facets.  Does this mean he is dead out of the water? No, because good old T-SQL and CMS can be leveraged to get the job done.

Today we are going to focus on changing SQL Agent Job owners.

How do we find owners?


SELECT
    sv.name AS [Name],
    sv.job_id AS [JobID],
    l.name AS UserName
    FROM
    msdb.dbo.sysjobs_view AS sv
    INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
    ORDER BY
    sv.[Name] ASC

Here is a screen shot from my demo so you can follow along.

image

How do we change the job owner for all jobs?

You can see that the first job “Device by Zero” has the owner name set to “PBMDEMO\Administrator.” In this example we will want to change the owner to be “sa”. The following script below will do this. The only problem is you would have to run this script against every instance. That’s where the Central Management Server comes into play.

** This script is used as demo. It will only work against SQL 2005\2008\2008R2. Run it on development before you consider using it in production. If you decide to run it in production you are on your own. Run it at your own risk. **


DECLARE @JobID uniqueidentifier
DECLARE @NewOwner varchar(200)
DECLARE @OldName varchar(200)

SET @NewOwner = 'sa'
SET @OldName = 'PBMDEMO\Administrator'

SELECT
sv.name AS [Name],
sv.job_id AS [JobID],
l.name AS [OwnerName]
INTO #SQLJobs
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
WHERE l.name like @OldName
ORDER BY
sv.[Name] ASC

SELECT * FROM #SQLJobs
WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN
    SELECT TOP 1 @JobID = JobID FROM #SQLJobs
    EXEC msdb.dbo.sp_update_job @job_id= @JobID,
        @owner_login_name=@NewOwner
        DELETE FROM #SQLJobs WHERE JobID = @JobID

END

DROP TABLE #SQLJobs

Now assuming you have Central Management Server configured just right click on the group of servers you want the change to be applied to and select “New Query.” Copy and paste the code and you should be able to execute the query.  Below are some screenshots from my demo incase you are new to Central Management Server. We will apply the script against the Production group.

image

image

Finally if we execute the initial query we will see that all jobs have “sa” as the owner.

image

Other Related Articles:

9 Comments

Posted in SQL Server, SQLServerPedia Syndication

SQL Saturday #80 date changed

Posted on April 6, 2011 by | Leave a comment

We change the date of SQL Saturday #80 from July 16th to July 23rd.

We are still early in the game so we hope this will not be an inconvenience to anyone. We sent out an email to everyone who signed up on the website. With that said, I still wanted to explain why we changed our date.

So.. Why did you change the date?

If you are like me you love SQLSaturday events and you are willing to travel to get your learn on. We changed the date of our event for the people who are willing to travel. Looking at our attendance roster from SQLSaturday #36 we noticed the majority of the attendees were travelers.

The event planners have a secret for you. We are not country music fans so we forgot about the Super Bowl of Country Music.

SQL Saturday comes back to Wheeling, WV

SQL Saturday comes back to Wheeling, WV

Its almost impossible to get hotel rooms that weekend so we pinged our host facility West Virginia Northern Community College and PASS. Once we got permission from both we pushed our event back one week.

This works out great because you can now experience our best festival while you are in town. If you like Italian food or carnivals be prepared to get a side order of fun with your free SQL Server training.

Lesson Learned

In the future we will make sure we coordinate with several group in the community to make sure we select the best date for our next SQL Saturday.

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Meme Monday #1 – Proactive Monitoring

Posted on April 4, 2011 by | Leave a comment

Tom LaRock (Blog |@SQLRockstar) a good friend of mine is starting meme monday. The concept is simple, try to get people writing. The first installment is write a SQL blog post that tells a story in 11 words or less.  

Proactive monitoring is key, policy-based management and CMS is easy.

Tom asked that we tag people to get this party started. I am tagging Matthew Velic (Blog |@mvelic)  and David Stien (Blog | @Made2Mentor)

Stay tuned as I plan to flush out some blog posts in April to help make you be proactive with your whole SQL Server farm using native tools.

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

What items are in your SQL Server Daily Checklist?

Posted on March 3, 2011 by | 9 comments

Hello everyone out there on the intertubes.  In my life I have learned that I can save a lot of time and energy by

First Annual SQL Rally is in May

First Annual SQL Rally is in May

utilizing checklists. The same is true with being a DBA.  I  manage over 100 instances of SQL Server. When my career started as a production DBA my head was spinning trying to track down failed jobs, missing backups and more on a daily basis. It could be done but it took quite a while and was a manual task. I needed to find a way to automate this process.  This is where Policy-Based Management and Central Management Server comes in. Now, I can sleep at night and just review my evaluated daily checklist first thing in the morning.

UPDATE [3/7/2011] : I added SQL Agent Notifications and Alerts as recommended in the comments. If you think an option is missing leave a comment and I will add it.

Help me Help you…

Just like Jerry Maguire I need your help.  I am speaking about Policy-Based Management and Central Management Server at the first annual SQLRally.  I need your input to help my presentation. I have two quick questions. It shouldn’t take you more than two minutes to complete the questions.  I appreciate your help and support.

[Note: Please make sure to click on the vote button for each question.]

What items are included in your Database Administration Daily Checklist?

View Results

Loading ...

How do you evaluate your Database Administration Daily Checklist?

View Results

Loading ...

9 Comments

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Baby Beluga has arrived!

Posted on February 24, 2011 by | 5 comments

Shortly after I attended the SQL PASS Members Summit in 2010 I announced a code project named “Beluga.” We are excited to announce that the Release to Market edition of project Beluga is named Gregory Corbett Sterrett. Gregory Corbett Sterrett was released on Feburary 20th at 7:32pm. Greg weighted in at 6lbs and 11.5oz; He is also 21 inches from head to toe.  All three of us are doing well and are excited for whats in store for us in the future.

I will be taking a few weeks off from blogging.  I will also try to unplug myself from the interwebs. Don’t worry as I have plans to come back strong with a series of post about Policy-Based Management and Central Management Server.  In the meantime  enjoy the following photos.


5 Comments

Posted in Family, SQLServerPedia Syndication

Speaking at the West Virginia SQL User Group

Posted on February 15, 2011 by | Leave a comment

I am presenting SQL Server 2008 for Developers  at the West Virginia SQL User Group (WVPASS) meeting on February 17th at 6PM.  The meeting is free, open to the public and pizza will be provided.  If you’re in the area (Pittsburgh peeps this includes you) they would be glad to have you.  Registration is still open for the event.

I also submitted two abstracts to SQL Saturday #75 in Columbus, OH. The abstracts are SQL Server 2008 for Developers and Evaluate your Daily Checklist with Policy-Based Management and Central Management Server. The event is scheduled for June 11th. I hope to see you there!

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , , , ,

Wheeling, WV to Houston, TX – A SQL Saturday #57 Recap..

Posted on February 1, 2011 by | 3 comments

Everyone, If you attended SQL Saturday #57 in Houston, TX thank the host and the sponsors. With out their dedication and hard work this event wouldn’t have happened.  Finally, if you haven’t done so submit a speaker evaluation for the sessions you attended.

Now that the basics are covered lets move on to the recap.

Why Houston?

Last week I took a trip out to H-Town to speak at my eighth venue in the last twelve months.  Family was a huge motivator for attending SQL Saturday #57.  Being a graduate of James E. Taylor High School in Katy, Texas I was able

John Sterrett SQL Server 2008 for Developers Presentation

If you build it they will come!

to double dip and visit my parents and my oldest brother.  I flew in on Thursday to surprise my father for his birthday. I got an extra bonus when I arrived.  I found out that this was also going to be the same day my nephew was born.

Venue

On Saturday, we meet up at the church for the first SQL Saturday in Houston. Yes, that sentence is correct.  The Houston Area SQL User Group was able to get the Bammel Church of Christ to host the event. I have to be honest when I found out that a church was going to be used I was skeptical.  Normally, these events are at a community college, university or Microsoft Office.  I wondered if a church be a good venue for a SQL Saturday? I was right, it wasn’t a good venue. It was a fantastic venue.  Kudos to Nancy Hidy Wilson (Blog | Twitter) and the Houston User Group for selecting this venue.

Connecting (Volunteer & Speakers Dinner)

Normally, when I am selected to speak at a SQL Saturday I always attend the volunteer and speaker dinner and the after party. Its a great opportunity to network.  We met up at the Outback Steakhouse in Tomball.  I felt like I was back at summer camp as I hanged out with some friends and also made some new friends. (Thomas LeBlanc, Wes Brown, Ryan Adams, Jen and Sean McCowen, Sri Sridharan, Tim Mitchell and more…)

My favorite quote comes from Sri, “John you cannot say its a once in a lifetime opportunity. Your Steelers are always in the Super Bowl.”

Learning (Presentations I attended)

  • Dean Richards – Best Practices for SQL Server on VMWare
    With the economy changing we all need to find ways to do things cheaper. One of the easiest ways to do this is to virtualize your servers. The concept seams great but SQL Server can be tricky to manage.  How do you configure the guest memory? How does the host share its CPU power with guests? I learned this and a lot more during Deans session.
  • Ryan Adams – Manage Your Shop with CMS and Policy Based Management
    Those of you who know me know I am very excited about Central Management Server and Policy Based Management. I currently use it to evaluate my daily checklist against 100+ instances of SQL Server with 3,000+ databases.  I am extremely interested in seeing how other DBA’s use it.  Ryan is into mirroring and loves the mirroring facets provided with PBM.  I am glad he showed this to me.
  • Thomas LeBlanc – Transition from DBA to BI
    Any time I can sit in the front row on a sofa and watch Thomas speak its a win-win. This talk was special because  I got my learn on and felt like I was at comedy hour.  I enjoyed his path from DBA to BI.  I really liked how he was able to use his DBA skills to make a good first impression.  He also showed us a great spreadsheet out there on the interwebs to build a data dictionary for your facts and dimensions.

Sharing (My Presentations)

  • SQL Server 2008 for Developers (About 30 attendees)
    Every once in a while I submit this topic to see if people are still interested in seeing demos going over the new features provided for developers with SQL Server 2008.  During the presentation I polled the attendees to see who hasn’t migrated to SQL Server 2008.  There still is a crowd that wants to know how to implement DateTimeOffset, T-SQL enhancements,  Merge, Table Value Parameters, Change Data Capture.  I spoke right before lunch and had a few people stay extra to go over a bonus demo.  I also talked with a few of the attendees later in the day and I got some great feedback.If you attend my session please submit feedback.  You can find the slide deck and sample code on my blog.
  • Evaluate your Daily Checklist using CMS and Policy Based Management (5 Attendees)
    My PBM and CMS talk was included in the last session.  This was my first presentation using VMWare Workstation.  I did this for two reasons. One, Brent Ozar made me do it. Finally, I wanted to throw up an instance of SQL 2000 for this demo and I couldn’t do it with Windows 7.  Even though the crowed was small one attendees told me he is going to start using PBM and CMS due to my presentation. This is the greatest complement I could receive. 

    If you attend my session please submit feedback.  You can find the slide deck and sample code on my blog.

Bloggers SQL Saturday #57 Posts

3 Comments

Posted in Events, SQL Server, SQLServerPedia Syndication

Tagged , , , ,

I want your vote for #SQLRally

Posted on January 26, 2011 by | Leave a comment

Hello Everyone, I am John Sterrett coming to you from Wheeling, WV and I need your vote for SQL Rally! I need your vote because I eager to share what I have learned about Policy Based Management and Central Management Server in the past four months.  This presentation will give people the knowledge they need to start evaluating a daily checklist.

Let me take a step back for a second and explain. I am currently responsable for over 100+ Instances of SQL with more than 3,000 databases.  We rarely didn’t evaluated a daily checklists against all of our production databases until I learned about Central Management Server and Policy Based Management.  Now, I can evaluate my daily checklist in the amount of time it takes to get up out of my seat and brew a cup of coffee.  I am eager to show you multiple ways to start evaluating your checklists.

What is SQLRally?

 SQLRally is PASS’ new regional event that fills the gap between our free PASS SQLSaturday 1-day training events and PASS Summit, the world’s largest conference for the SQL Server community. One of my favorite parts about SQLRally is that the community votes for the presentations.

Hopefully, I will be selected to speak at the first annual SQLRally. Regardless, I look forward to presenting this abstract at user groups, virtual chapters and  future SQL Saturday’s. In fact, I am giving this presentation at SQLSaturday in Houston this Saturday. If you would want me to deliver this abstract or one of my other presentations contact me.

How do I vote?

 Click here to vote. You will see my session under the fifth group (PBM).  You get two votes for this catagory so I will also recommend Jorge Segarra  (Blog | @SQLChicken) because I wouldn’t be using PBM without his contrubutions to the community.

What am I voting for again…..

Evaluate your daily checklist against 100+ instances of SQL Server while you get a cup of coffee

Abstract

You will walk away from this session with an understanding of how to use the Policy Based Management and Central Management Server to complete a daily checklist against your whole SQL Server farm. We will cover the basics to evaluate, monitor and apply best practice policies, which are included out of the box with SQL Server 2008. We will also make custom policies to cover additional best practices to evaluate daily checklist items. Finally, we will combine Policy-Based Management and Central Management Server with the Enterprise Policy Management Framework on codeplex to automate the monitoring process and create manager friendly reports to monitor your checklist items against the whole SQL Server farm.

Deliverables

  • You will be able to install and configure Central Management System
  • You will be able to use CMS and/or PBM to complete a daily checklist
  • You will be able to evaluate and build custom policies.

Session Prerequisites:

Session prerequisites assume you are familiar with SQL Server Management Studio, have an instance of SQL Server 2008, and that you have administrative access with SQL Server. You don’t have to be familiar with Policy-Based Management or Central Management Server, you will learn the basics during this session.

Related Posts:

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Un-SQL Friday #2 – My Tech Giants

Posted on January 21, 2011 by | 7 comments

UnSQL Friday #2

The second round of UnSQL Friday is here and the topic is so good I am typing while I eat lunch rushing to beat the deadline to get this in. UnSQL Friday was created by Jen of @MidnightDBA’s to provide an avenue for SQL Bloggers to focus on a non-technical topic. The topic is as follows:

“Read this blog, and then write whatever you want about Tech Giants. Be sure to mention in your blog that you’re writing for Un-SQL Friday, and link to this post. Oh, and have it up any time before the weekend (Saturday Jan 22) hits, mmkay?”

Jen said, to write about whatever I want so here we go!  I am using this venue as an opportunity to say thanks to a bunch of people who have helped become who I am today. I will also explain how they fit into my definition of a Tech Giant.

There are several other people I could include but I have a deadline so I will have to find another way to thank them later.

What is a Tech Giant?

This definition is subjective and will be defined differently from blogger to blogger so here is my definition. To me a Tech Giant is someone I look up too within IT; someone who has helped me out; someone who is mentoring me; someone who is mentoring me without even knowing that they are mentoring me.

In no particular order here is a list explaining why these individuals peeps are my tech giants:

David Hoerster, Eric Kepes & John Hidey

David Hoerster, Eric Kepes & John Hidey: These are three stand up .NET developers who are highly involved in Pittsburgh .NET User Group and the Pittsburgh code camp for years. To this day I will always remember my first code camp four years ago. The next year they gave me my first public speaking opportunity. Every year the Code Camp in Pittsburgh gets bigger and bigger. This year there were two code camps with over 150 attendees.  David, Eric and John showed me first hand how important it is to give back to your technical community.

Side Note: How the heck are none of these guys a Microsoft MVP? This almost makes me question the MVP program. I am going to do my part in correcting this by nominating them at the Microsoft MVP site.

Thomas LaRock aka SQLRockStar

Tom LaRock: Last year Tom asked the community if anyone wanted to have a mentor on his blog.  I jumped on this opportunity and we have been skyping (is this actually a word?) monthly. His book (check out my review) and those sessions have shaved years off my learning curve moving from a developer to becoming a valuable Production DBA.

I finally meet Tom in person at the 2010 SQL Pass Member Summit. He introduced me to almost everyone in the community that week. It’s weird, in a way I feel like he is my older brother from another mother.

Tom also has a great blog. In my opinion it’s one of the most entertaining technical blogs out there because he does a good job teaching with stories. A perfect example is “Everything I Needed to Know about Waits and Queues I learned From the TSA.”

Side Note: Other than his addiction to bacon and being a Patriots fan he is a great guy! Can you tell I am a die hard Pittsburgh Sports Fan?

Brent Ozar

I love this book!

Brent Ozar: I ran into Brent virtually two years ago at the Pittsburgh SQL User Group. At the end of his presentation he challenged the crowd to start blogging. I found his intro to blogging series and gave it a try.  A little later I was hooked.  Brent made me a syndicated blogger at SQLServerPedia. People will see this blog post because he took a chance on a new blogger.

Its amazing how much time and energy he puts into the SQL PASS Community with his presentations and blog posts.  His blog is a great resource for SQL Server, Professional Development, Speaking and Blogging.  Did I mention that he also co-authored one of the best SQL Server books (its the image on the left side)?

Side Note: I am working on creating a VMWare Workstation image right now due to his blog post on “How to rehears a Presentation.” Brent, I will be shooting you some question soon 🙂

Andy Warren

Andy Warren: The first SQL Saturday in West Virginia wouldn’t have happened without Andy’s help.  Andy and I had several phone calls where he gave me the guidance I needed to make it happen. He helped me from the point where I wondered,can I make this happen up to the week of the event.  Now, I have spoken at three SQL Saturdays and am adding another one to the list next week.  I don’t think this would have happened without Andy’s support.

Side Note: At SQL PASS Summit I learned that Andy is referred to as the godfather of SQL Server. I am not sure why this is true. I will have to follow-up with more at a later date.

Justin Siebert

Justin Siebert: There is no way I am going skip the great state of West Virginia. And there is no one better qualified than Justin. He started his own consulting company to provide Online Marketing and Search Engine Optimization. He now does business with Fortune 500 companies.  He is a big supporter of LAMP and is doing great things with WordPress.  His website is a perfect example.

Once he found out I was interested in blogging he talked me into using WordPress. Justin gives me several tips on WordPress and SEO and has never asked for money.   He also pulls me aside every once in a while to make sure I am focused on my goals and heading down the right path.

Side Notes: some people call Justin by his name. I call him the SEORockStar.  Justin and I might be the only remaining die-hard Pittsburgh Pirates fans left in Wheeling (Yes the owner Bob Nutting also lives in Wheeling).

7 Comments

Posted in Professional Development, SQLServerPedia Syndication

Tagged , , , ,