Categories: SSC

Goals 2012

by Jason Brimhall
Categories: News, Professional, SSC
Tags:
Comments: No Comments
Published on: February 8, 2012

As I have wanted to do each year, here is my goals post for 2012.  Last year I set out to do this very same thing but was very unsuccessful at completing the post.  Here is all I had written:

Personal

  • Family
  • Blogging
  • Build out my Virtual Environment

Professional

  • SSIS
  • SSRS
  • SSAS
  • Blogging
  • Writing (articles)
  • Community
  • Presentations

Education

  • Certification
  • Books

Fitness

  • Running
  • Exercise

This year, I will do much better.  As proof of that goal, this post will get published – one way or another.  Despite not having published an actual list of goals, I think I did fairly well with this list.  I wrote a few articles.  I was able to maintain a steady pace with my blog.  I completed my MCITP for both Admin and Dev tracks in 2008.  I became very familiar with SSRS and learned a substantial amount with SSIS.

So, as I set out to establish my goals, I decided to keep the list very short.

Professional Development

I will finish the MCITP for the BI track in 2008.  I plan to have this done by March 2012.

I will study for and take the MCM written and Lab exams for SQL 2008.  I will take more time to achieve this goal but will have it done by year end.

Continue writing.  I will continue to contribute articles for publication as well as write on my blog.  I hope to also do some writing professionally.

Personal

I have several rooms in the basement to finish.  I plan on finishing the family room and my office.

Community

I will continue to volunteer at the UG level as well as at the PASS level.  I like volunteering with PASS and helping to achieve the greatness that is PASS Summit.

Despite some setbacks already, I will push forward and get a SQLSaturday event to happen in Las Vegas.

There you have it.  Nothing too elaborate.  I’m trying to keep the goals short and simple without overly complicated detail.  I think this year will be a successful year as well.

S3OLV – Get your Merge On

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: No Comments
Published on: February 7, 2012

We are rapidly closing in on the February meeting for S3OLV.  You might remember reading about the upcoming meeting here.

We are looking to have an excellent meeting this month.  We have had a good run on superb presentations, and Troy aims to keep us going in the right direction.

Troy will be teaching us how to use the Merge statement.  He will help us to learn how this statement can help improve our queries.

We will also continue our ugly code segment.  Bring your ugly code.  Send your ugly code to admin at s3olv dot com.  We are doing this segment in an effort to have the group help each other and learn from each other.  Honestly, it is also an effort to help drive up group interaction.

Here is the information for the meeting.

Date: February 9, 2012

Time: 6:30 PM Pacific

Virtual Meeting Info

Attendee URL: https://www.livemeeting.com/cc/UserGroups/join?id=H3ZGRQ&role=attend

Meeting ID:  H3ZGRQ

Physical Meeting Info

M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128

Freedom

by Jason Brimhall
Categories: Book Reviews, News, SSC
Tags: ,
Comments: No Comments
Published on: February 7, 2012

Not too long ago, I blogged about a Book called Daemon.  Freedom is the sequel to Daemon.

I enjoyed reading this book.  The story continues from Daemon, but in a largely different direction.  I like the direction the book took, for the most part.  I was hoping that there would be more tech talk and not so much of the sci-fi tek.  I liked the sci-fi tek, I just thought it would have been more down to earth to keep it closer to reality.

Daemon was scarily real.  The exploits were real, the danger seemed real.  In Freedom, I felt it made a big reach.  When looking at both books together though, it seems like a natural progression.

I like the plot.  The story culminates with a confrontation at a compound in Texas.  I really thought that the confrontation was a huge let-down.  I really expected more conflict at that point.  Everything was mounting to that, but the ultimate goal was to demonstrate that the group of people from the darknet still could think for themselves.

In the end, it was a good story.  I would like to have some of the technology.  I must also admit that Freedom was harder to put down than Daemon for me.  I found myself rooting in favor of the Daemon.  I started rooting for the Daemon at the end of the first book.

System Base Tables

by Jason Brimhall
Categories: News, Professional, SQLServerPedia Syndication, SSC
Tags: ,
Comments: No Comments
Published on: January 30, 2012

On January 19th, I published a post about the Dedicated Administrator Connection.  I spoke very briefly of the system base tables in that article.  Today, I want to dive into these tables a little bit more.

First, let’s get the Microsoft definition for these tables.  ”System base tables are the underlying tables that actually store the metadata for a specific database.”

Have you ever queried sys.objects from the master database and wondered about some of the results?  You can see all of the System base tables when querying the sys.objects view.  These tables are denoted in sys.objects by type of ‘S’ and a type_desc of ‘SYSTEM_TABLE’.

Here is a simple query to take a quick peek at these tables.

SELECT * FROM sys.objects
	WHERE Type = 'S'
	ORDER BY NAME

There is no need for a Dedicated Administrator connection in order for this query to work.  You can view these results with a non-DAC connection to the instance so long as you have adequate permissions to query sys.objects.  That said, not all objects returned by that query are System Base Tables.  Furthermore, it appears that the list from MSDN is not comprehensive.  One such example is the reference to sys.sysserrefs that does not appear to exist in SQL 2008 R2 and the missing System Base table called sys.sysbrickfiles (which is used by sysaltfiles as shown in this execution plan).

If I try to query the sysbrickfiles table (as an example) without connecting via DAC, I will get an error message like this:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysbrickfiles'.

This is normal behavior.  You cannot query the system base tables without first connecting via DAC.  Having said that, the obligatory warning is required.  As explained on MSDN, these tables are intended for use by Microsoft.  Proceed at your own risk and please make sure you have backups.

In addition to these System Base tables, you will find tables not mentioned in the article nor in the master database.  These System Base tables are found within the Resource database.  The resource database does contain most of the tables mentioned in that article, but there are some differences.  I will leave that discovery exercise to the reader.

There is plenty about SQL Server that many of us take for granted.  Under the hood, there is much more to learn.  Taking a peek at the System Base tables is one of those areas that will help you to learn more about SQL Server.  My question is this: How far are you willing to explore to learn more about SQL Server?

Slammer, Alive…Barely

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: 1 Comment
Published on: January 24, 2012

Slammer

By now you must have heard of the SQL Slammer worm.  It was quite an infectious little nuisance.  The harm it caused came largely due to unpatched, unprotected SQL Servers.

We are now 9 years out from the initial discovery of this worm.  The worm has made its way onto the endangered species list – but it is not yet extinct.  I don’t know if I should be surprised by that.

My initial reaction is “No way that worm is still causing problems.  Everybody knows about it.”  But yet, I just caught several infection attempts from remote hosts that were affected by Slammer.  When I take a step back, I recall that many people out there are still running on unpatched servers.  I know of many places that are running SQL 2000.  I know of a large pool of servers across different versions and editions that are not patched.  I even know of a few places that are still running SQL 6.5.

When I take all of that into account, finding that Slammer is still active does not surprise me – but it should.

So for fun, here is what I was able to trap from the recent attempts at my machine with SQL Slammer.

Time:		 1/23/2012 3:59:03 PM
Event:		 Intrusion
IP Address/User: 202.56.192.195
Message:	 Attack type: MSSQL Resolution Service Buffer Overflow (Slammer)

When I trace that IP back to its source, I get a host name of the machine.  If I search on the Host Name of the IP Address, I find this page.  If I were a hacker, I now have a lot of valuable information.  I can also assume that this particular host has many virii.

This entire little foray has made me wonder how many people out there are concerned about security.  Do you know what the patch level is of your server?  Is your AV software up to date?  Are you running any form of HIPS?  If you are in IT and your focus is Data, you may want to check those things.  After all, our focus is to protect the data.

S3OLV February 2012

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: 1 Comment
Published on: January 23, 2012

Do you recognize this person?

 

If you are from the Colorado Springs area, you probably do.  This is:

 

 

 

Troy Ketsdever (twitter)

Troy will be presenting to the Las Vegas SQL User Group on February 9, 2012 @ 6:30 Pacific.  Here is his bio:

Troy Ketsdever is a data architect with over 15 years of commercial software development experience, and has maintained a love/hate relationship with SQL Server since version 4.2. In addition to his “day job”, Troy enjoys writing articles and presenting at user groups on a variety of database design and implementation topics.

His main objective and vision is “making the right information available to the right people at the right time”.

The topic that Troy has chosen for this meeting is titled: Zero to “MERGE” in 60 minutes.  And here is the abstract for that presentation.

Description: SQL Server 2008 saw the introduction of the new MERGE DML statement. In this session, we’ll take a look at the basic syntax and capabilities of the command. Once we have reviewed some simple examples, we’ll dive into some of the more advanced uses (abuses?) of the command, reinforcing our understanding by looking at more complex examples.

Bring your questions.  Bring your ugly code.  If you are remote, bring your own PIZZA.  Yes, this meeting will be both virtual and in person.

Virtual Meeting Info

Attendee URL: https://www.livemeeting.com/cc/UserGroups/join?id=H3ZGRQ&role=attend

Meeting ID:  H3ZGRQ

Physical Meeting Info

M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128

S3OLV – Jan 2012 Meeting Recap

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: No Comments
Published on: January 20, 2012

Last week (Jan 12, 2012), we held the user group meeting for the SQL Server Society of Las Vegas (a.k.a S3OLV or SSSOLV).

Presenting at that meeting was Josh Lewis (Twitter).  Josh presented on a pretty tough topic in my opinion.  He chose to present to us the topic of XML for the DBA.  You can read his abstract here.

We got the meeting rolling a little bit late.  Traffic must have been a bear down in LV.  Nonetheless, it got rolling and was a good meeting.

During this meeting we had our second installment of “Crap Code.”  Unintentionally, the crap code was a perfect segue into the presentation.  The crap code demonstrated extracting elements from XML related to the blocked process report.

Josh did a great job on the topic.  We recorded the meeting and you can view that here.  Check it out.  Get a little free learning on a difficult topic (for some us like myself).

Stay tuned, the February announcement is coming soon.

 

Dedicated Administrator Connection

by Jason Brimhall
Categories: News, Professional, SSC
Tags: , , ,
Comments: No Comments
Published on: January 19, 2012

Recently you may have read my article about some hidden functions in SQL Server.  In that article you learned that those functions were in some DMOs and that you could get at them through the resource database.

Today I found myself learning more about the resource database.  Due to what I had learned in my prior foray into the resource database, I was curious if certain other functions might call some hidden functions in that database.

Sadly – they did not.  But in my travels I did happen across something else that is in that database.  Those items are called system base tables.  Unlike the trio of functions from the last article – you can get to these but it is STRONGLY advised to not do it.

Naturally, I want to check these tables out – especially since the MSDN article does say how to get to them.  I will write about some adventures into looking at these tables in the future.  I already found one interesting thing that seemed odd – but first I will need to login using the DAC and start testing to confirm a hypothesis.

For now, I want to cover how to create a Dedicated Administrator Connection.  This should be something that DBAs know how to do.  It isn’t difficult, and I will only cover one method and leave the other method to the Microsoft documentation.

You can create a DAC through either SSMS or through SQLCMD.  You can create one remotely, but you will need to enable that option since it is disabled by default.  You can find the method for creating this connection via SQLCMD here.

To create a connection through SSMS, it is rather easy as well.  You simply add (case insensitive) “admin:” to the beginning of your server as shown in this image.

In order for this to work, you will need to have the browser service running.  If it is not running, you will get an error message.  This error message is informative if you read it.  It will provide a clue to look at the browser service.

Once you have successfully created this connection, you can now use it when necessary to perform administrative tasks or for some learning opportunities.  If you open a query using this connection you will see something like this next image in your query tab.

You can see in the tab of this query tab that there is the label “ADMIN:”.  This is your DAC connection.  You are limited to one of these at a time – period.

If you try to create a second connection, you will get a nasty message.  The message is not entirely informative – just understand that you are getting it because you already have a DAC open.

It is a good idea to become familiar with how to connect via the DAC.  I have a connection saved for quick access.  Luckily I have a development server which I can test and use for learning opportunities.  As the warning MSDN states: “Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.”  If you venture into the system base tables via the DAC – Microsoft will not support it if you break it.

Blackout

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV
Tags: , , ,
Comments: No Comments
Published on: January 18, 2012

What more can I say.  I disagree with the kind of legislation that is being presented via SOPA and PIPA.

In support of the community, my site will be dark 18 Jan 2012 between 10:30 and 21:30 GMT-8.

Normal services will return after that.

 

You can see support of this from some more reputable sites as well.

Wikipedia

 

Steve Jones @ SqlServerCentral

Grant Fritchey

Gail Shaw

 

A Trio of Functions

by Jason Brimhall
Categories: News, Professional, Scripts, SSC
Tags: , ,
Comments: No Comments
Published on: January 17, 2012

I found myself perusing an execution plan the other day.  I know, big surprise there.  This execution plan showed me some interesting things I had never really paid much attention to in the past.  When I started paying attention to these things, I found myself jumping down a rabbit hole.

It all started with a bit of curiosity to see if I could make an “admin” script perform a bit better.  The execution plans started showing some table valued functions that I knew I hadn’t included in the query.  Subsequently, I found myself wondering – what is that?

The items that made me curious were all table valued functions.  There were three of them (different) in this particular plan.  I started looking hither and thither to find these functions.  It didn’t take long to figure out that I could find them in the mssqlsystemresource database.  So I proceeded to making a copy of the database and attaching a copy of it for further learning opportunities.

The three functions are:

SYSSESSIONS

FNGETSQL

SYSCONNECTIONS

Knowing the query and based on these names, I began looking in the appropriate DMOs to see what I could find.  Here are the scripts for each of those DMO’s.

USE [mssqlsystemresource_test]
GO
 
/****** Object:  View [sys].[dm_exec_connections]    Script Date: 01/16/2012 22:39:32 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_connections] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSCONNECTIONS)
 
GO
 
/****** Object:  View [sys].[dm_exec_sessions]    Script Date: 01/16/2012 22:39:37 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [sys].[dm_exec_sessions] AS
	SELECT *
	FROM OPENROWSET(TABLE SYSSESSIONS)
 
GO
 
/****** Object:  UserDefinedFunction [sys].[dm_exec_sql_text]    Script Date: 01/16/2012 22:39:55 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [sys].[dm_exec_sql_text](@handle VARBINARY(64))
RETURNS TABLE
AS
	RETURN SELECT * FROM OPENROWSET(TABLE FNGETSQL, @handle)
 
GO

Cool.  I can now see the internals of each of the DMOs – sort of.  You see, there is an OPENROWSET call in each of these objects.  Each call uses an undocumented feature called TABLE.  This is an internal command used by the engine and you won’t find much on it (mostly people asking what it is and Microsoft saying they won’t tell).

Here is the fun part.  If you try to run that code outside of querying the DMO, you will receive error messages.  If you try to create a new view utilizing the Openrowset, it will fail.  It is reserved for internal usage.  With that said, just continue to use the DMO and you will be fine.  Personally, I was curious to find out how it worked so I tried a bit to find it.

So there you have it.  If you are curious what is the internal makings of these DMOs, you can script them from the resource database.  Alternatively, you could also run sp_helptext.  I like to check these things from the resource database.  It feels more like an adventure.  Have fun with it and see what you will learn.

page 1 of 17»
Calendar
February 2012
M T W T F S S
« Jan    
 12345
6789101112
13141516171819
20212223242526
272829  
Follow me on Google+
Jason Brimhall

In 253 people's circles

Add to circlesi
Content
Categories

Categories

Popular Posts

Popular Posts

Blogroll

Blogroll

Now Reading

Now Reading

Planned books:

Current books:

  • ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running

    ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer

  • Advanced Marathoning – 2nd Edition

    Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas

  • SQL Server MVP Deep Dives

    SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg

  • A World Without Heroes (Beyonders)

    A World Without Heroes (Beyonders) by Brandon Mull

Recent books:

View full Library

SQLHelp

SQLHelp


Recent Posts
  • Goals 2012 by Jason Brimhall

    As I have wanted to do each year, here is my goals post for 2012.  Last year I set out to do this very same thing but was very unsuccessful at completing the post.  Here is all I had written: Personal Family Blogging Build out my Virtual Environment Professional SSIS [...]

  • S3OLV - Get your Merge On by Jason Brimhall

    We are rapidly closing in on the February meeting for S3OLV.  You might remember reading about the upcoming meeting here. We are looking to have an excellent meeting this month.  We have had a good run on superb presentations, and Troy aims to keep us going in the right direction. [...]

  • Freedom by Jason Brimhall

    Not too long ago, I blogged about a Book called Daemon.  Freedom is the sequel to Daemon. I enjoyed reading this book.  The story continues from Daemon, but in a largely different direction.  I like the direction the book took, for the most part.  I was hoping that there would [...]

  • System Base Tables by Jason Brimhall

    On January 19th, I published a post about the Dedicated Administrator Connection.  I spoke very briefly of the system base tables in that article.  Today, I want to dive into these tables a little bit more. First, let’s get the Microsoft definition for these tables.  ”System base tables are the [...]

  • Slammer, Alive...Barely by Jason Brimhall

    Slammer By now you must have heard of the SQL Slammer worm.  It was quite an infectious little nuisance.  The harm it caused came largely due to unpatched, unprotected SQL Servers. We are now 9 years out from the initial discovery of this worm.  The worm has made its way [...]

  • S3OLV February 2012 by Jason Brimhall

    Do you recognize this person?   If you are from the Colorado Springs area, you probably do.  This is:       Troy Ketsdever (twitter) Troy will be presenting to the Las Vegas SQL User Group on February 9, 2012 @ 6:30 Pacific.  Here is his bio: Troy Ketsdever is [...]

  • S3OLV - Jan 2012 Meeting Recap by Jason Brimhall

    Last week (Jan 12, 2012), we held the user group meeting for the SQL Server Society of Las Vegas (a.k.a S3OLV or SSSOLV). Presenting at that meeting was Josh Lewis (Twitter).  Josh presented on a pretty tough topic in my opinion.  He chose to present to us the topic of [...]

  • Dedicated Administrator Connection by Jason Brimhall

    Recently you may have read my article about some hidden functions in SQL Server.  In that article you learned that those functions were in some DMOs and that you could get at them through the resource database. Today I found myself learning more about the resource database.  Due to what [...]

  • Blackout by Jason Brimhall

    What more can I say.  I disagree with the kind of legislation that is being presented via SOPA and PIPA. In support of the community, my site will be dark 18 Jan 2012 between 10:30 and 21:30 GMT-8. Normal services will return after that.   You can see support of [...]

  • A Trio of Functions by Jason Brimhall

    I found myself perusing an execution plan the other day.  I know, big surprise there.  This execution plan showed me some interesting things I had never really paid much attention to in the past.  When I started paying attention to these things, I found myself jumping down a rabbit hole. [...]

Categories
Recent Comments
  • David about Missing Indexes
    Has anyone figured out how to get this to work in a database at compatability level 80 on a 2005/2008 ...
  • jonmcrawford about S3OLV February 2012
    You know you could just call Pizza Hut and have them deliver a pizza to my door...that'd be swell of ...
  • Jason Brimhall about Missing Indexes
    @Neil - thanks for the additional script. Be careful of sp_msForeachdb. It has limitations and will throw some ...
  • Neil about Missing Indexes
    I have worked an alternative without a cursor. DECLARE @STATEMENT NVARCHAR(MAX) SET @STATEMENT = N'USE [?]'+ CHAR(13) +';' +CHAR(13) + N' SELECT SO.name , ...
  • Jason Brimhall about Slammer, Alive...Barely
    As a follow-up to this, I have monitored my logs throughout the day and continue to see various different hosts ...
  • Jason Brimhall about Missing Indexes
    Yes - that would definitely be an issue. Thanks for getting back on it.
  • david about Missing Indexes
    I found that the problem was related to the database being in compatibility mode 80. It works fine for compatibility ...
  • Jason Brimhall about Missing Indexes
    I created the script on 2008 R2. Are you using the script as is, or did you modify? ...
  • David about Missing Indexes
    does this only apply to SQL 2005? I get an error on SQL 2008 R2: Msg 102, Level 15, State ...
  • Jason Brimhall about Missing Indexes
    Yes. The higher the value, the more likely the index would be of benefit to you.
Welcome , today is Wednesday, February 22, 2012