Ryan Adams Blog

SQL, Active Directory, Scripting

It was recently brought to my attention that a post with the script I talk about below could not be found on my blog.  Either I thought I blogged about it and never did or it got lost when I had to change hosting providers late last year.  My apologies for those who have been looking for it, but let’s rectify that and get down to it.

In my presentation on “How Active Directory Affects SQL Server” I talk about different ways people can gain access to your server.  The most common first step is to look at the logins in SQL to verify if someone has access.  That’s a great first step and the good news is that these days people have learned to take it a step further and check the groups that have logins as well.  It seems like that should do it, but it’s not enough.

Active Directory allows us to nest groups.  That means a group can be a member of another group.  There are three types of groups in AD and there are rules around which types can be nested in other types.  I’ll save that discussion for another day, but if you’re curious I have a diagram in my slide deck from the above presentation.  In order to fully verify access to your server, you have to traverse every single group up to the top of the nesting chain.  In a large environment it’s time consuming and easy to overlook an account when going back and forth.

The following script will look at the service accounts of all SQL instances on your machine and verify they do not have local administrative access.  I’ll put a note here that this default installation behavior was removed in SQL 2012, but that doesn’t mean someone didn’t grant the permission after the fact.  I realize this is written in VBScript and I’ll work on converting it to PowerShell, but keep in mind I wrote this in 2007.

Let me know if you have any issues or need a change in functionality and I’ll see what I can do.  As always, the use of this script is at your own risk and should be tested in a development environment.  I assume no responsibility for your use of the code.  You’ll need to rename the extension to .vbs.

GetRecursiveGroupMembership

Welcome back folks for day 2 of the PASS Summit in Charlotte, NC.  We are 6 or 7 minutes away from getting under way.   I’ll be live blogging this morning’s keynote so refresh your page often.  I’ll be taking short notes to keep you up to date as opposed to paragraph form.

We are off and running with another packed room.  We are seeing a video highlighting attendees and the things they have learned and the things they are most looking forward to.  Douglas McDowell, EVP of Finance, hits the stage and is going to give a financial snapshot of the PASS organization.  Doug reminds us that PASS is a not for profit organization and the Summit allows the organization to help fund all the other things it does for the community like local and virtual chapters.  Doug highlights the various income streams like Summit, Virtual Chapters, and the BAC conference.  He mentions that the BAC conference made about 100k in funds.

Doug is highlighting some of the transformations in how budgeting is done.  The biggest change is having a rainy day or emergency fund and we now have 1M in reserve.  This is a great insurance plan for the organization to make sure it can stay healthy in case of cancelled conferences due to natural disaster or bad turn in economic conditions.

Doug is now showing the different places where PASS is placing its funds.  7.6M in community spending and about 30% is focused on international growth.  Doug hands it over to PASS President Bill Graziano.

Bill is thanking the Directors on the board that are moving on.  Douglas McDowell is the first.  Rob Farley is next who ran the SQLSaturday portfolio.  Last is Rushabh Mehta who has been on the board for 8 years serving in Marketing, Finance, President, and Past President.

Bill hands it over to the next President of PASS Thomas LaRock.  Tom introduces the board changes and new board members.  Our new board members are Jen Stirrup, Tim Ford, and Amy Lewis.  Congratulations to them all!  I served on the Nomination Committee this year and I know that they will all do amazing things for our community.

Tom mentions the time and dates for the BAC conference and 2014 Summit. He is also telling us about all the amazing things happening this week like the community appreciation party at the Nascar Hall of Fame and the SQLClinic where you can talk to the MS folks that make the product we all love so much.

David DeWitt is on the stage to present his 5th keynote!  Today he’ll be telling us all about Hekaton.  MS marketing changed the name from Hekaton to In Memory OLTP and David encourages to vote via twitter using those hashtags.  David is explaining why MS decided they needed a new query engine.  His reason is that the software is mature and processors are starting to top out.  A common question he gets is why not just pin the tables in memory?  Latches for shared data structures like the buffer pool.  He explains how latches work and the contention and CPU they can cause.  Now we take a look at the need for concurrency control.  We have two simple rules.  First is before access the query must acquire the appropriates lock type from the lock manager.  The second is releasing the lock.  David explains that locking and latches are much more problematic with in memory databases.

Hekaton is a lock free data structure and thus there are also no latches.  Hekaton is a third query engine inside SQL along with the standard disk data engine and column store engine.  To use Hekaton we start by creating a memory optimized table and it MUST contain a Primary Key. Next we populate the table and that is done just the same as we do today and then it’s ready to use!  Typical performance boost is 3x for ad-hoc T-SQL and 5x for Stored Procedures.

David is showing a demo of how a lock and latch free data structure works.  David is very proud of his animation!  We can see how an updater can run without blocking or slowing down a reader.  Due to locking and latches the standard method is burning up!

Now we dive into concurrency control which uses optimistic, multi-version, and time stamps.  We’ll look at each of these.  Time stamps get a time from the global clock.  At the start of the transaction we get a start time and multiple transactions can get the same time, but for the end time it guarantees a unique time.  We are zooming in to see how it all works with a single row of data as an example.

David is wrapping up for us as he quickly reviews everything he just covered.  Absolutely mind blowing as usual.  I’m looking forward to playing with Hekaton myself with a product that has locking hints that cause me huge latch and lock waits.  This will also get it off of the disk so I have high hopes.

Thank you all for joining me today at this amazing event!  If you want more from the PASS Summit don’t forget about PASStv.  I’ll be doing a session on PASStv on Friday.  Here is the link.  My session is the one on Policy-Based Management and Central Management Server.

http://www.sqlpass.org/summit/2013/Live.aspx

Welcome to the PASS Summit 2013 LIVE keynote blog.  I’ll be live blogging this morning’s keynote so refresh your page often.  I’ll be taking short notes to keep you up to date as opposed to paragraph form.

We are about 5 minutes from start time and this place is packed.  The room is huge and there is tons of buzz as the family unites for our big kickoff!

PASS President Bill Graziano hits the stage to welcome us all to the Summit and introduce the board of directors who work tirelessly to help keep our organization thriving in conjunction with PASS HQ.

700k hours of free technical training was delivered by the PASS organization this past year.  Bill thanks all the volunteers that make this possible.

We are watching a video using PowerMap to zoom us around the world and witness the growth of the SQLSaturday movement.   It’s amazing to see how we have grown!

Amy Lewis is announced to be this year’s PASSion award winner. If you don’t know here she is AMAZING and since she was just elected to the PASS board we know there are great things to come.

I just go announced as the honorable mention for the PASSion award.  I’m floored and honored!

Quentin Clark the Corporate Vice President of Microsoft hits the stage. He also thanks all the volunteers that make this community tick with PASSion.  The PASS organization is doing an awesome job of thanking all of our volunteers.

Quentin is going to introduce what’s ahead for SQL Server and right now is showing us what they have done in the last year.  He announces that SQL Server CTP2 is released today.

We are seeing a live demo of an online game selling store.  First we see the times for what happens in a game search and then we see the time it takes to purchase it.  These are simulated with 20k people doing it at the same time.  Now they are converting the table to an in memory table and we go from 6 seconds to .07.  We have now coined the phrase “Wicked Fast”.

Quentin is talking about some breakthroughs in availability and recovery like Always-on secondaries in Azure and the ability to backup to Azure.  They are now showing a demo of backing up to Azure.  It’s baked into SQL 2014 and they also announce multiple encryption options for your backups.  They also have a separate download to add the ability to backup to Azure fuctionality for SQL 2008.  It’s really cool to be able to backup locally and to Azure at the same time.

We are now seeing some customer case studies on PDW with DW loads going from days to hours.  They are showing a video of a case study from the city of Barcelona.  They use HDInsight and the data to see what their citizens need and cater to them, especially during their big city carnival.  Being able to decipher this data in near real time allows them to respond that way from things like sanitation censors and even casual observations tweeted from citizens.

We are seeing Microsoft’s plan for real time insights from data like Power Query and the Data Catalog.  We have ways to manipulate the data with Power Pivot and a rich presentation layer with Power View and Power Map.

Kamal a BI Program Manager comes on stage to show us a demo.  He is going to show us an example using data from Skype.  We start in Excel with Power Query.  He does mention that the data is not real data to protect exposing Skype proprietary data.  We connect to the data, pull it in, and convert it to JSON all with just a few clicks.  The big selling point here is how easy it is.  As a data professional we know other methods to get the data, but it’s complicated.  This gives that power to a regular business user with great ease.  Now that we have the fake Skype data we are going to pull in some more information using Power Query to correlate that with a web data search for countries and their population.  This allows us to easily see calls per capita.  Now that we have our data we get a data model that we can easily use on multiple devices.  We see that it works on a Surface 2, and an Android, and laptop, and even a “fruity device” if you want to lower you standards.  I’m sure you folks can guess what device that is!

Now Kamal logs into Power BI in Office 365 and simply types in “calls per capita per population” and instantly we see a chart using the same data model.  He does it again with “calls by country and destination as map” and we get the same thing on that data model, but we get a beautiful map.  You can signup at powerbi.com and check it out by querying data with every day language.

Microsoft is announcing a Power BI contest to show them how you are pushing the boundaries with Power BI.  www.facebook.com/microsoftbi to enter the contest and they are giving away Surface 2s and Xbox Ones to the winners.

Quentin wraps it up as he challenges us to push the boundaries with Power BI.

Thanks for joining me to today and make sure to come back tomorrow for day 2!

Things have been too quiet here on the blog, but there’s a reason (a few actually).  This year has been nuts for me, but it’s all good stuff!  We just moved, and moving a family is not fun.  It was a long process from selling the old house to searching and finding a new one.  Like I said, the moving part is not fun but we couldn’t be happier now that we’re mostly settled in.  Unlike the old house, this neighborhood actually has a community and the house is right across the street from 1 of 4 neighborhood parks.  Now I can finally work on getting my oldest daughter’s training wheels off without getting run over by cars!

It’s also been a busy SQL community year for me.  I’m the President of the PASS Performance Virtual Chapter, serve on the board of directors for the North Texas SQL Server User Group, serve as a PASS Regional Mentor, and serve on the PASS 2013 Nomination Committee.  It’s a ton of work, but it’s rewarding to help others and I love every second of it!  Of course I have a day job that keeps me hopping as well.

One of the most exciting things to happen is all the speaking engagements I have been fortunate enough to do.  This is where a lot of my blogging time has gone…into creating new presentations.  There is nothing more fun and exciting than teaching someone something new or explaining things so they truly get it.  I was chosen to be a Technical Lead on SQLCruise this year (Tim Ford must have lost his mind).  It was an amazing experience to be able to dive deep with attendees after hours.  I am truly humbled to speak at these events among absolute SQL rock stars.  So where will I be?  Here we go…

If you’re going to be at any of these events, I hope you’ll come see me!  Expect the blog to start back up soon.  I’ve been writing posts, they are just in draft until I can put the finishing touches on them.

SQLCruise Technical LeadSummit2013 24HOP DevConnections SQLSat

Holy Schnikes!PASS Summit 2013

I am beyond excited to announce that I have been accepted to speak at the annual SQL PASS Summit 2013 in Charlotte, NC.  This will be my first time to speak at this event and I can’t wait!  The session I will be presenting is titled “How Active Directory Affects SQL Server”.

I have been working with SQL Server since the 2000 release and I have been working with Active Directory since it hit the shelf.  Prior to becoming a full time production DBA for Verizon, I was an AD guy and actually helped design and architect their worldwide Active Directory infrastructure.  I couldn’t be happier that this was the session that got picked from my submissions.  It’s a perfect melding of my two favorite technologies and I have a unique and rare perspective of both how they can work together and how they can work against each other.  For instance, have you enabled Instant File Initialization for your SQL Server instance?  Good!  Did you go back and make sure the setting was still there 90 minutes later?  If you haven’t then you better go double check it because Active Directory has a mechanism to not only change it, but persist that change.  If Active Directory was SQL Server’s kid then it just pulled a Ferris Bueller.

If you are planning to attend the conference make sure to add my session to your schedule.  I will explain the mechanism I alluded to in the above example along with all the other things good and bad that it can do, plus much more.  Here is the abstract:

If you have ever had a Kerberos or SSPI context error, then you won’t want to miss this session. SQL Server has a large surface area and Active Directory can influence a big part of it. I will discuss AD DNS configuration, Group Policy Objects, Kerberos (of course), and how all of them affect your SQL Server. By the end of the session you’ll have a check list of things to discuss with your domain administrator when you return to work.

Policy Based Management has 4 evaluation modes and if you are not already familiar with them you can go HERE to get more information.  Today I want to talk about the “On Change: Prevent” mode and why you don’t always see it as an option.

The options you see available are dependent on the facets used in your check condition.  In order for the “On Change: Prevent” option to be available your condition must check something that raises a DDL event.

Let’s use Auto Shrink as an example and start by verifying if it raises a DDL event or not. We’ll use the old DDL trigger method to see what happens.  Here’s the code to create the trigger, and it just does a simple PRINT statement back to the screen if it fires:

CREATE TRIGGER trg_CatchShrink
ON ALL SERVER
FOR ALTER DATABASE
AS
BEGIN
PRINT ‘Auto shrink was changed and fired the trigger’
END

Now that we have our trigger setup let’s use the below code to enable Auto Shrink and see if it fires.  Make sure to change the database name to your TEST database.

USE [master]
GO
ALTER DATABASE [MyTestDB] SET AUTO_SHRINK ON WITH NO_WAIT
GO

If you’re following along and running the code then you will have seen the text of the PRINT command in your output.  We have now verified that enabling Auto Shrink on a database does indeed raise a DDL event.  So now your homework is to go create a policy that checks to make sure Auto Shrink is not enabled.  Are you done, yet?  That’s okay I’ll just give you the answer.  If you create a policy that checks the Auto Shrink setting then you will see that the “On Change: Prevent” option is NOT available.

So why is that option not available when we have proven that changing the Auto Shrink setting does raise a DDL event?  It’s because you can only use facets where ALL properties of that facet raise a DDL event.  That’s right.  Every single property in a facet has to raise a DDL event for the “On Change: Prevent” option to be available.  Now don’t get too excited because you know Auto Shrink is in more than one facet and you think you can just use another facet.  Surprise!  None of those facets support “On Change: Prevent” either, but good for you for knowing it was in more than one place.

So which facets support the “On Change: Prevent” evaluation method?  The following script will tell you:

SELECT name
FROM [msdb].[dbo].[syspolicy_management_facets]
WHERE execution_mode & 1 = 1

Let’s not forget to clean up after ourselves and remove our trigger as well as the Auto Shrink setting we changed.

DROP TRIGGER trg_CatchShrink
GO
ALTER DATABASE [MyTestDB] SET AUTO_SHRINK OFF WITH NO_WAIT
GO

I recently had to rename my laptop on which I have two SQL server instances.  I have a default instance and a named instance.  I actually have had the laptop renamed for awhile without any issues, but it’s not production and I don’t use it every day.  I first encountered the error while creating a schedule for a new SQL Agent job.  The error I received said that the server could not be found and provided the server name that it could not connect to.  I immediately noticed that it showed the previous machine name.  If you run into this error, then you need to update the sever name in sys.servers.  Here is how to make the update.

Default Instance

sp_dropserver 'PreviousName'
GO
sp_addserver 'NewName', local
GO

Named Instance

sp_dropserver 'PreviousNameInstanceName'
GO
sp_addserver 'NewNameInstanceName', local
GO

Now that sys.servers has been updated you need to restart SQL server for the changes to take effect.  Once SQL comes back up, you can verify success by running the following and ensuring the results match your new server name.

SELECT @@SERVERNAME

In early February I received an email from LinkedIn that I have one of the top 1% most viewed profiles for 2012.  That’s really cool and I had no idea!  In fact, I couldn’t remember the last time I had updated it.  This prompted a serious, “Dude you better get out there and do some updating!”  It’s more time consuming than you think for those of us perfectionist type people, which is why you are just now getting this post.  I’m not entirely happy with it, but it’s in much better shape now.

Feel free to go check it out HERE if you have some spare time, but if you want to keep up with me in real time then you should follow me on Twitter, HERE.

LinkedIn Top 1% most viewed profiles of 2012

LinkedIn Top 1% most viewed profiles of 2012

I blogged about this back in September of 2010, but technology changes so it’s time to revisit this again.  Now I’m not anything even close to a marketing expert, but I’ve been the Director of Logistics and Communications for the North Texas SQL Server User Group for the past 2 years (I just started a new 2 year term as the Director of Programs).  I’ve also been the Director of Marketing for the PASS Performance Virtual Chapter for the last year until becoming the President this year.  Since I started those positions, NTSSUG’s average has gone from around 50 to around 80 (in fact, we had 87 last month), and the virtual chapter went from 40 in January to 235 last month.  Now that’s what I call exciting.  I’ve helped put on a SQLRally, several SQLSaturdays, an all-day virtual training event, and currently serve as a PASS Regional Mentor for the South Central region.  So I’m not a marketing expert, but I have gained some experience with all my volunteer activities.

Here are the things that I have learned and work for both local user groups as well as virtual ones.  Remember that every group and area can be different and what works in one place might not in another, so your mileage may vary.

  • Email – Discussion Lists
    • I send 4 emails per month.  The first two I send 1 week prior to the meeting.  The second two I send the morning of the meeting.  This gives people enough notice to get it in their calendars and that last minute reminder for those who need it.
    • Why two?  I send one to the group discussion list and one via the PASS website.  If you’re not running a PASS user group then the latter does not apply.  This means I get the news out to anyone who finds my chapter via the PASS website and also the current members.  It sounds simple but I see some established groups that just send to their discussion list and not from the website.  They are completely missing all the new members that signup on the chapter site.
    • Why have a discussion list and not just send email to all members on the PASS chapter site?  The chapter emailer is just a way to get news out to those who have signed up on the site.  It does not allow for 2 way communication because members cannot reply back.  I suggest creating a discussion list where members can ask technical questions.  Google Groups is my personal choice here.  It’s easy, everything gets archived, and you can always go back and search the site for past threads.
    • If you don’t have an email registration on your site like the PASS chapters, then I suggest creating a second list just for meeting announcements.  This gives people the choice to reduce the noise of the discussion list and only get meeting and event announcements.
    • Be consistent.  For me, I live and die by my Outlook calendar and I sync it to all my devices.  Add reminders in your calendar to make sure these emails get out on time.  As soon as you get the speaker and meeting information, go ahead and pre-stage it in your drafts.
    • I want you to notice that I didn’t call it a distribution list, but a discussion list.  That’s your goal; to create and foster technical creativity.  If things get quiet then throw out a question to keep it lively.  Maybe find an unanswered question on a forum and pose the question to your discussion list with a link back to the forum thread.
  • LinkedIn
    • Create a group on LinkedIn.  This is a professional networking site, so its scope is already what you are wanting to target.
    • Every month you should create a discussion in your group.  Don’t just post a link to your site.  Make it easy and post all the details along with a link.
    • Once you create the discussion you should click the “like” button.  When your connections see the discussion they will also see that you liked it, and that peaks their interest.
    • Make it a manager’s choice.  The newest discussions are listed at the top which means if your group is even slightly active your meeting announcement gets pushed off the screen.  A manager’s choice discussion gets put in its own box at the top of the right hand column.  This ensures that it is always visible.  The box only holds one item, so make sure to remove the previous month.
    • My last suggestion is to click on the discussion after it’s posted and share it.  Once you are in the discussion you’ll see a “Share Discussion” box at the top of the right hand column.  This lets you advertise the discussion topic through LinkedIn, Twitter, Facebook, and Google+.  You will notice that the first option is LinkedIn and that seems redundant since you just posted it, but it’s not.  What you did was create a discussion in your group and nowhere else.  Clicking this link lets you advertise it on your activity feed, and also post it in your other groups.  Posting it to other groups that might have an interest can make it seen by a lot of people.
    • LinkedIn used to have an events function but it was removed in December of 2012.  It was a pain to create an event and then a discussion to advertise the event you just created, but it was cool to automatically see which of your connections were planning to attend.
  • Twitter
    • Create a Twitter account for your group.
    • Tweet about your meeting, especially the week of the meeting.
    • Use a hash tag specific to your group, so others can find it and follow along.
    • If it’s a PASS chapter, then also use the #sqlpass hash tag.
    • Be as inventive as you can within 140 characters.  Words like “FREE” and phrases like “$100 Amazon gift card” can go a long way.  It’s cheesy, but it works.
  • CommunityMegaphone.com
    • This is a surprisingly unknown marketing resource.  I constantly have chapter leaders telling me they didn’t know about this.  You can create a calendar event on this site for your meeting and Microsoft pulls community events from the site to put in their MSDN newsletter. That’s a huge technical audience!
    • Note that the site says it has to be done 3 weeks before the meeting.  There are a lot of chapters out there that have a hard time getting speakers, especially that far ahead of time.  Don’t let that stop you!  Put it in the calendar anyway with a link to your site for further information.
    • Make sure to put this in your personal calendar to create an event on this site every month.
  • Website
    • This one is obvious and that’s because it’s your group’s front door.  This is where new members are the most likely to find you and where current members know they can easily find meeting information.  The site gives your group a face and personality.
    • Make sure pertinent meeting information is always right up front and clear.  Don’t forget things like directions, and throwing in an interactive Google map is all too easy these days.
    • People need reminders, so why not make it easy and give them one.  Add an iCal event to your site that they can easily open and save to their personal calendar.
    • If you are using a PASS chapter website, then I highly recommend starting to use the new Events Module if you are not already.  Things like an iCal are already baked in for you.  It also automatically moves your events to an archive module that you can put on your archive page.
      • It’s not immediately obvious, but you can use HTML in all the fields while creating a new event to customize the look and formatting.
      • HTML customization is not limited to the creation of a new event, but is also available in the module itself.  It’s a template and you change what it shows and how it gets shown.
      • In the past you might have noticed that as soon as the event start time hit, the event would get moved to the archive page.  That’s really not cool if you are a virtual chapter and someone is looking for the LiveMeeting link any more than 1 second past the start time.  It also makes your home page look out of whack if you don’t have the following month already entered.  That was recently changed and I am now told this only happens 6 hours after the meeting.
      • If you think this new module is awesome and makes your life easier then I encourage you to send a simple, “Thank You” email to the folks that made it happen.  Thank Allen Kinsel who drove the chapter tools initiative while he was on the PASS board.  Thank Wesley Chang, Benny Su, and Jason Lau in IT at PASS HQ for developing and implementing it.  I’ll make it easy for you.  CLICK HERE, and just hit send with a simple already filled out email.  If you want to elaborate, feel free to tell them you want an RSS feed for the module.  We can automate all of the above once we get that….hint….hint

You’ll notice that I don’t have Facebook listed.  The reason is that I have only seen one group say that worked for them.  The main reason is that most professionals use Facebook for personal stuff and keep their professional life on LinkedIn.  This just goes to show that not every method works for every group or part of the country.  Go try it out and see if it works for you.  If it doesn’t work; just delete it.  Go try other things, and if you find one that really works then make sure to let me know.

I hope this post comes in handy and helps some folks out with growing their groups.  It takes time to see the benefits and it certainly takes time to do this every single month (I know, I did it for 2 chapters at the same time).  If your group grows as a result of these ideas then let me know.  Any time we have successes in the community I love to hear about it; it’s just encouraging.

This post is a live blog of the PASS Summit 2012 keynote from day 2 on 11/8/2012.  This post is intended to be a live note taking.

Douglas McDowell opens day 2 by giving an overview of the health of the PASS organization.  This year PASS has added an additional community evangelist, and better IT support.  PASS continues to be financially healthy.

Doug introduces the newly elected board members Wendy Pastrick, James Rowland-Jones, and Sri Sridharan.  We congratulate and thank them all for their commitment and sacrifice.  Doug also gives a huge thank you to the PASS HQ team.  They work tirelessly to put on PASS events.

Thomas LaRock takes the stage to talk about the way PASS has changed to recognize our volunteers.  They have added an Outstanding volunteer award and mention the runners up for the PASSion award.  The runners up were Jesus Gil and Amy Lewis.  Jen Stirrup is announced as the 2012 PASSion award winner.

Quentin Clark Microsoft Corporate Vice President is up next.  He’ll be talking on “The Data Lifecycle: Turning Data into Business Value”.

We are watching map reduce pulling data from Hadoop for analysis with PDW.  The idea here is to combine any data no matter where it comes from, whether it is on premise or not, or Hadoop, or SQL.  We are digging through the data now and eyes are glazed over or closing.

If you are watching this live and wondering why I’m not updating it’s because nothing eventful is happening.  Lots of people have glazed over eyes and some are leaving.

If you haven’t abondoned me yet…you should.  We are nearing the end with nothing exciting in view.  Twitter is buzzing with this being the most boring keynote ever.