Back in October of last year, Idera ran a contest called the “Get a life contest”.  In order to enter the contest you had to describe how using Idera’s SQL Diagnostic Manager helped you “Get a life” by freeing up your time from hunting down production issues.  The grand prize winner would receive a trip for two on SQLCruise!  This contest had my name written all over it for two reasons.

The first is Idera.  In addition to their great SQL Server products, Idera has become a huge advocate for the SQL Community.  If you have been to any SQL community events then I guarantee you have seen them.  I don’t mean the people standing behind their booth in orange shirts and sporting a rubber ducky.  I mean the people checking you in at registration or handing you lunch.  They are not wearing company shirts and appear to be local volunteers.  Why do they do this?  Easy.  They love SQL Server and the SQL community just as much as we do.  They may be Idera employees, but these people are DBAs and developers just like you and I.  Idera supports and encourages these employees to be involved in the community and learn all they can.  It’s this type of outreach and involvement that has ranked them high in my book.

The second reason is SQLCruise.  I’ve been dying to go on SQLCruise since the very first one.  SQLCruise means soaking up the sun while soaking up SQL knowledge from the brightest minds in the industry.  We’re talking about classroom training capped at 15 people so you can actively participate and not just become another number.  Unlike traditional training classes where you go home at the end of the day, you get to spend one on one time with the instructors.  Have you ever been to one of those classes where a concept was introduced and you wanted to know how it would work in your specific environment?  When you asked the instructor, did he say to catch him after class so he would have enough time to finish all the material for the other students?  Well that is understandable so everyone else gets what they expected from the course as well, but it’s disappointing when the instructor bolts at the end of the day and leaves you hanging.  On SQLCruise, they can only run so far before it’s all ocean!  This kind of custom tailored training is impossible to find anywhere else.

Well I can no longer be one of those people that say I never win anything, because I was the second SQLCruise winner in the random drawing.  That’s right; I won the Holy Grail of prizes!  My wife and I leave for Miami tomorrow and could not be more grateful to Idera and SQLCruise for the amazing gift.  We have had about 6 date nights since our 2 year old was born so it’s a gift of knowledge and a gift of time.  We plan to actually finish a conversation without interruption!

I volunteered to help out and I will give you fair warning that the word she uses in her post for my area of responsibility is in the loosest meaning of the word.  The short and more accurate term would just be marketing.  I’m very excited to be involved with this team and see what we can accomplish.  We have some awesome things planned that are a little outside of the box, so make sure you follow us closely.

Our first meeting for the year will be this Thursday January 26th, 4EST.  Jason Strate (Blog|Twitter) will be presenting on “Discovering the Plan Cache”.  Make sure to visit our site to check out the details.  The site has a handy calendar icon to automatically add a reminder to your calendar.

PASS Performance Virtual Chapter Site

Follow us on Twitter

Join our Google Group Distribution List for meeting announcements

• Database Mail must be enabled and configured
• Operators must be created to receive notifications of alerts
• Alert system notifications must be enabled on your SQL Server Agent

There are four error numbers that we can setup alerts for, and that will provide us with notifications for any policy using any evaluation mode.  Here are the error numbers we want to setup alerts for.

• On Change: Prevent (Automatic) – 34050
• This occurs when a user runs a transaction that raises a DDL event that violates a policy
• On Change: Prevent (On Demand) – 34051
• This evaluation method can also be run in a manual On Demand fashion, in which it raises a different error number
• On Schedule – 34052
• On Change: Log Only – 34053

Let’s take a look at creating an alert for an “On Schedule” error.  Right click alerts under SQL Server Agent in SSMS and select New Alert.  In the Alert Properties box we need to give the alert a name and enable it.  We need to select which databases we want it to apply to, and select that the error number to alert on will be 34052.

Now we need to select the Response page in the left hand pane.  Here we want to select “Notify Operators” and check the email column for the operator we want to be notified.

Last we need to select the Options page in the left hand pane.  Here we want to select to include the error text for email notifications.

Follow these steps to create alerts for the remaining three error number and you will now be notified of any policy violations that occur.

The Professional Association for SQL Server and the North Texas SQL Server User Group have been working hard behind the scenes to bring SQLRally 2012 in Dallas Texas to the SQL Community.  We are excited to announce that event registration opens November 30, 2011.

Make sure to get registered soon and take advantage of the early bird pricing.  Pricing starts at $299 and the first price increase will take place on January 15th, 2012.

What is SQLRally?

It is 2 days of SQL Server training and networking with your fellow SQL Professionals.  Not only do you get amazing training that you will be able to put into action as soon as you get back to the office, but you will make connections with others in the industry that I guarantee will be amazing.

SQLRally was designed to fill the gap between the large yearly PASS Summit event and the local free SQLSaturday events.  It is a regional SQL Server Professional training event that boasts some of best speakers and trainers in the industry.  You won’t find 2 days of training for this price anywhere.

When/Where is SQLRally?

The event will take place May 10-11, 2012 in Dallas, TX at the Dallas Convention Center in downtown.  There will also be 2 pre-con days May 8-9, 2012.  The pre-cons will run $219 a day.  If you have never attended a pre-con at the PASS Summit or a SQLSaturday I personally suggest trying to do one.  I attended 2 pre-cons at the PASS Summit 2011 and have also attended them at various SQLSaturdays.  They are by far the best bang for your buck.

You will notice three folders under the Policy Management node.  If explore them you will see that the Policies and Conditions folders are empty while the Facets folder contains many items.  Facets are created by Microsoft and you cannot create your own.  The other folders are empty because we have not yet created any policies.  This is a good time to browse through the facets and open a few to get an idea of what they contain.

We want Auto Create Statistics to be turned on for all our databases, so let’s start by right clicking on the Conditions folder and selecting New Condition.  This opens our create condition dialog box where we define the condition name, the Facet that contains the properties we want to evaluate, and the expression used to evaluate those properties.  This is what those options should look like:

Now that our condition is defined, we’re ready to create our policy.  Right click the policies folder under the Management node and select Create Policy.  We need to give it a name, select the check condition we created previously, define the targets to apply it to, choose the evaluation mode, and select any server restrictions.  Here is what our example will look like:

Now that we have our policy configured let’s test it out by evaluating it against our local server and reviewing the results.  Simply right click our new “Auto Create Statistics” policy and select Evaluate.  This will cause the policy to be evaluated immediately on the local SQL instance.  Here is what we get:

We can see that our server has four databases and one of them has violated our policy.  If you check the box next to the offending database and select apply, PBM will bring that database into compliance by changing the Auto Create Statistics setting from false to true.  Before we do that we can also select the View hyperlink in the details column to see the exact settings that caused this policy to be violated.

We can see that the policy expected AutoCreateStatisticsEnabled to be set to True, but the actual value was False.

%installdir%\100\tools\policies\

 Under that directory there are several others separated by technology like SSIS and SSAS.  We will focus on the policies in the “%installdir%\100\tools\policies\Databaseengine\1033” folder.  Let’s import the Microsoft Best Practice Policy named “Database Auto Shrink”.  Right click on the policies folder under Policy Management on your PBM server and select import.  In the Import dialog, click the ellipsis next to “File to Import” and navigate to the policies folder mentioned above.  We want to select the “Database Auto Shrink.xml” file.  Here are the options we want to choose for our import:

That’s it!  Please make a note that we imported the policy in a disabled state and I always suggest you do that.  Once the policy has been imported you can inspect all the settings to make sure they are appropriate for your environment.

Rick Heiges opens us up and introduces a special musical number by Rob Farley and Buck Woody. It was hilarious with a special lyrical shout out to Paul Randal. Rick is now walking us through the leadership changes in the PASS organization, with a very special thank you to past President Wayne Sneider as Rushabh Mehta becomes the immediate past President.

Rick is announcing upcoming events with SQLRally in Dallas on May 10-11. He also announces the next Summit in Seattle November 6-9. He also tells everyone that all Summit attendees will receive an e-book copy of the MVP Deep Dives book and to keep an eye on their email.

Dr. David Dewitt enters the stage with a great start by explaining why his wife is not here to watch him speak. There are lots of laughs. David starts his talk on big data and shares some size statistics for systems of the larger web sites like Facebook and other social media. The data sizes are astounding.

David is explaining the NOSQL movement and points out that is does NOT mean NO SQL, but means not only SQL. So he wants us to think about large data and a mix of systems to support the data. We may have one entry point or front end to get the data but on the backend some data might be in SQL and some might be in Hadoop. He is trying to get us to think about the data and that not all data is relational and is better suited for other storage systems.

David explains that NOSQL is not a paradigm shift and that RDBMS are still the best way to store data efficiently. However, some data like unstructured data does not work best in an RDBMS. He plans on talking about Hadoop and how it works.

David is explaining the Hadoop file system called HDFS. The HDFS does not replace the windows file system or NTFS but sits on top of it. The blocks are stored and replicated by a factor of three. It sounds like RAID 5 but spread across multiple nodes with seperate storage systems. The node first written to is the node where the transaction originated, the second on a node in the same rack, and the third on a node in a different rack. The replication of this data is handled by a name node or primary node (which also has a backup node). It monitors all the other nodes with heartbeats and decides how to distribute the data among the nodes.

David is now explaining how Hadoop handles failures and that it was designed to expect failures. It does use checksums for reads and writes, but expects that hardware and software failures will occur. When a failure occurs on a node then the name node finds the blocks that are missing and replicates them to other nodes to maintain that factor of three. I can’t help but think this sounds like RAID except it is supported by replication as opposed to multiple writes across disk. With a factor of three I envision it like a RAID 5 on top of a RAID 5, but all data is written by replication instead of multiple writes on a single disk array.

David is explaining how Hadoop finds the data when you request it since you don’t know where it is stored. David is moving on to how mapreduce works with an animation he jokes took him 6 hours to come up with. The audience loves the animation explanation, with some clapping. He shows how map tasks find the data across nodes and then hand it off to the map reduce procedure that takes the data from the multiple nodes and reduces it to a single output.

Now we are hearing that after Hadoop came out that Facebook and Yahoo started using it. However, they both came to different conclusions on the language. Facebook came up with something very SQL like, but Yahoo came up with something more procedural. David brings up a slide with a lot of writing and jokes that it is not meant to be read and that he will not be using zoomit. The crowd loves the comment with lots of laughs after the lack of use in other keynotes this week. David now points out that out of the 150k jobs Facebook runs that only 500 are map reduce jobs and the rest are hive sql. Now we are seeing how hive tables are designed and that they are partitioned but a particular attribute.

Now we are seeing how Hive relates to parallel datawarehousing. We can see how Hive is great for unstructured data that is not related, but how SQL is much better with relational data due to a common schema and partitioning method. Now David is talking about putting the two things together and connecting the universe. We see the difficulties in getting data from both worlds in regards to performance. He explains the SQOOP approach and the challenges and that there must be a better way. SQOOP moves the data from one world to other to get the data.

David asks what about if we don’t move the data, but put a management systems between the two that understands how to get the data from both systems seperately. This is something he is working on in his labs as it becomes clear that we will be living in a world with both types of data and a need to get information from both and relate it.

David is wrapping things up with a re-cap and driving home the major points. The biggest one is that SQL is not going away and neither is Hadoop or other unstructed data systems and we need to work with both.
1!
That’s the end of the last SQLPASS 2011 summit keynotes. The crowd is wild about David with a huge standing ovation!

Keynote
Rushab Mehta starts us out with a recap of all that PASS has accomplished in the last year. He thanks everyone involved from the PASS board to those that volunteer for SQLSaturdays or user group meetings. The SQL community is growing by leaps and bounds and it is very exciting.

Ted Kummert is the keynote speaker today. Ted recognizes the success of MS due to the work and success of the SQL community. He talks about how their success is based on community success. As everyone expected, it didn’t take long before we heard the word “cloud”. Ted talks about how the cloud is another revolutionary change in the data technology landscape and it’s economic impact. It was a profound statement, but then Ted took a little bit of a turn. We always hear MS pushing the cloud and they are always referring to THEIR cloud. However, Ted talks about how we will have a choice and mix of MS cloud, private cloud, and partner cloud.

Ted officially announced that the next version of SQL Server code named Denali will be released in the first half of 2012 and will be called SQL Server 2012. He also announces that SQL Azure will fully integrate and support Hadoop. We then got to see a demo of using the MS BI stack to consume and dig into Hadoop data.

Sessions
I had a chapter leader and regional mentor meeting so I didn’t attend the first session. It was a good meeting, with good ideas. I wish there had been more time, but the larger meeting was yesterday and I was in a pre-con. I’m very happy that they had a second meeting for those of us that could not make the first one.

I watched Andrew Kelly present on SQL 2008 Query stats. He did a good job of explaining the DMVs used to get the stats you need to understand what is going on in your system. The DMVs are great, but it is not always clear how to get the information you need. You generally have to correlate 2 or more DMVs to get what you want. Andrew’s session really helped to show those correlations.

I also watched Brent Ozar do his Blitz session. I saw his first one online, but this was his second, new, and improved version. In the session he shows a script he wrote and provides for free that has a ton of elements to assist you in assessing a new server you have just inherited. Brent added some really good additional things to the script. The really shiny thing he added was wrapping the script in a stored procedure that takes all the results of the blitz script and prioritizes the results. This is Brent…so he didn’t stop there. The SP actually uses OpenRowset to connect to his SQL Server he has running in the cloud and updates the SP definition. How cool is that? It’s like Windows Update. Make sure to go check it out at brentozar.com/blitz

Chapter Lunch
Today was chapter lunch day. Every PASS chapter had a table and sign in the lunch room. Mad props to whoever started this idea. You would be surprised how many people come to the summit and don’t know about local chapters in their area. This does not just apply to the smaller cities but even larger cities as well. It is a great way to connect people who are local to each other and grow the local chapters.

Expo Hall
This is an open networking time to talk to all the vendors and see the solutions they provide. Of course, there is always the SWAG! I had a good time and there are several things I plan on testing out, but I found the SWAG a little lacking.

SQLPeople Party
SQLPeople.com is the brain child of Andy Leonard and he held an event sponsored by Embarcadero for all those involved with the project. It was at the Tap House Grill. The Tap House is great place and perfect for a smaller event like this. I had a great time meeting some other wonderful SQL people.

Once I got to the terminal I had to check a bag, but there was a very short line.  Security was also no big deal and only took about 10 minutes to get through, so everything went very smoothly.  Of course my plane left at 7pm so the slightly later departure may have aided in the short lines.  I’m glad it all went well, because I needed to grab a bite to eat and had just enough time to get something from the food court.  I then headed over to my gate and found David Stein, and it’s always nice to run into a friend.

The flight itself was very smoothe and completely on time!  Always a good thing, but especially since my flight didn’t arrive until a little after 9PT and 11 my time.  Dave and I met up and headed down to the baggage claim to get our stuff.  From there we headed over to the Seattle Link Light Rail.  They don’t take American Express so I had to pay cash, but hardly a big deal for a one way trip of $2.75.  The whole process was easy and cheap, and I’ll certainly continue to use the Light Rail on future trips.

The trip took 40 minutes to get from the airport to the WestLake exit.  I’m staying in the Sheraton and I don’t think it even took me 5 minutes to walk from the train station to the hotel.  It was a perfect choice.  I got checked into the hotel, unpacked, ironed some shirts, and headed down to the hotel lounge to see who I could find.  I ran into Denny Cherry, Jim Murphy, and Wes Brown.  We chatted for awhile and Wes and I decided to call it a night after a long travel day.  I think I finally went to sleep around midnight PT or 2am my time.

Everything went great and it was an awesome start to the trip.

Again I was lucky to attend a pre-con today.  Today I chose to attend Denali Always On by Allan Hirt. I have always gravitated toward HR and DR solutions so I’m looking forward in getting up to speed with Denali Always On. Here are a subset of my notes from the class to give you an idea of what was covered. Please note that there will not be any formatting so they may look scattered.

You can now have a TempDB local to each node. This can have better performance. Make sure the SQL Server service accounts has rights to the folder.

When going through the add node install in CTP3 it does not create the TempDB folder. You have to create it yourself manually.

As you patch nodes in your cluster, remember to remove the node you are patching as a possible owner before patching. You don’t want SQL to attempt to failover to that node in the middle of applying the patch.

You have to enable trace flag 9532 to get more than 1 availability group replica in CTP3

AG has integration with failover clustering so if you are combining them like was done with clustering and mirroring you no longer have to tweek the mirroring timeouts. An AG will not failover until the cluster completely fails, so FC is your primary HA and AG could be HA/DR.

You can take backups of the replica, but since Full Recovery mode is required you still have to take log backups of the primary or the log will grow. This has not been confirmed, but is likely the case.

First Timers Orientation

The first timers program was a great idea and as a first timer I can say that it works. The program needs some work and organization. Even the big brother/sister sponsors really didn’t know what was going on. When you entered everyone was given a sticker with a number and color. The idea was to find the person with the same number as you but the opposite color. It was a great idea to get people to meet someone new, but this process was never explained.

PASS did a really cool entrance of the first timers into the reception, but we were just told to split on one side of the room or the other and watch the video. That’s all we knew and had no idea what was going on. Rushab Mehta was doing the introduction and at the end he tells everyone to look toward the end of the room for the curtain. All of the first timers were trying to make sense out of it while turning and staring at a blank wall. It was not until the curtain opened on the other end of the room when you could hear a collective, “Ohhhh”.

The orientation was great and I definately think it should be continued, it just needs some better communication and organization. This was the launch of this new program and as with anything new there are always growing pains.

Welcome Reception and Quiz Bowl

The welcome reception was a great time of networking and an awesome opportunity to meet some amazing people. The quiz bowl was fun, but it didn’t seem like very many people were watching.

SQLServerCentral Party

This was a fun event. When you get there you get a ticket to exchange for chips. Depending on how much you win, the more tickets you get for the prize drawing at the end. I spent so much time chatting and networking with all the great people I never played a game.