Thoughts on SSSOLV December Meeting

Categories: News, Professional, SSC, SSSOLV
Comments: No Comments
Published on: December 30, 2011

This is very late – I know.  The meeting was held on December 8, 2011 and I haven’t yet shared my recap/thoughts about the meeting.

We hold the SQL Server Society of Las Vegas user group meeting on the second Thursday of every month.  We have decided to hold the meeting as both a physical and a virtual meeting.  This decision was made to try and encourage better attendance and to allow people like myself to attend and participate.  Since I moved back to Utah and am trying to continue to help the group thrive – it has been helpful to have the virtual meetings.

But let me share another reason that I like to do it virtually.  Having lived in Las Vegas, it seems that a lot of the membership end up working during the meeting.  I know there were several times that I had to work during the meeting.  I know several people have expressed that concern to me as well.  By having the virtual meeting, they can sometimes join the meeting while fulfilling work duties.

As the announcement for the December meeting showed, I had the opportunity to present a new topic that I have been working on a lot of late.  The topic was on getting better functionality out of SSRS reports for mere DBAs such as myself.  I talked on topics that I needed to figure out in order to meet business requirements.

Some of the items touched included dynamic sorting and dynamic grouping.  These dynamic abilities were setup in a fashion that multiple reports could use the same datasets and tables within a database.  Furthermore, changing the sort or group would be as simple as changing a value in a table rather than editing the rdl and uploading the changes into SSRS.  This was all included in the demos.

Unfortunately, I focused too much on getting demos done.  I still need to go back and flesh out the slide-deck.  Yeah, I did a no-no.  I gave the presentation without a slide-deck.  It is far better to have the deck available to help control the flow of the presentation.  I’ll get that fixed.  You can see the presentation (gaffs included) here.

In addition to this presentation, I have been planning on writing up a few articles/blogs to also detail the various topics covered during the presentation.  Those should be out soon.

January 2012 Las Vegas SQL UG

Categories: News, Professional, SSC, SSSOLV
Tags: , ,
Comments: No Comments
Published on: December 30, 2011

This month I am attempting to get a jump on things a bit.  I am hoping to get the info out in a more timely fashion throughout this new year for the User Group meetings for S3OLV.

Out with the old and in with the new.  Let’s ring in the New Year for the Las Vegas SQL User Group together and with a bang.

Helping us will be Joshua Lewis as he presents the first part in his three part series on XML.

XML for the DBA – A Series (part 1)

XML has been a feature available in SQL Server for a long time, but the capabilities were limited, and it was never very easy to use.  This was always a huge disappointment to me, because XML in itself should be any DBA’s best friend when it is used properly. What we typically see in applications is not the prettiest example of XML;  it is typically used by developers to accomplish things very quickly when it comes to data.

For the most part, XML is a heap of data that can get very large, doesn’t store well, and isn’t easily indexed.

But consider this:  What if a very data-centric person were the one dictating the XML (like a DBA)?  XML, when used properly, can be a very powerful tool in data storage and presentation.

About Josh

Joshua is a Database Professional, with over 10 years’ experience in Configuration / Administration of Data Infrastructures, Performance tuning, Development, and Business Intelligence Architecture, using Microsoft’s suite of SQL Server Products.

Over the years, and in varied industries, he has supported and optimized large DataCenters, developed multiple ETL platforms for handling client eligibility / enrollment / claims, and designed security policies and procedures to meet various industry security certifications (Sarbanes-Oxley, HIPAA, and CISP).

Currently, Joshua is expanding his knowledge and skills to .NET, building Value-Add interfaces and integrations to SQL Server and Integration Services through the use of SQL-CLR and Custom SSIS Components.

 

LiveMeeting Information:

Attendee URLhttps://www.livemeeting.com/cc/UserGroups/join?id=GW2GD2&role=attend

Meeting ID:  GW2GD2

December Recap

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: 1 Comment
Published on: December 29, 2011

The past few months I have been pretty busy.  December is no exception to that.  Between normal work, moonlighting as a general contractor on my own basement, and trying to keep up with my family – it almost seems like the month has barely even been a blink.

Other than the highlights of the holidays and finishing a room in the basement, I did have some SQL related highlights.

The first highlight came with notice of having won a contest – yay.  I entered into the SafePeak performance story contest back in September.  The contest closed out in November and the winners announced the first part of December (maybe the end of November).

For that contest, I submitted this story.  In that story, I showed how duplicated business logic can prove detrimental to query performance.  By deduping the business logic, I was able to realize a significant performance gain.

As a reward for winning that contest, I received an IPAD2.  That prize came the week just before Christmas.  Now, we use the IPAD as an extra incentive with the children for improved behavior (they really like the IPAD).

The other highlight came at the beginning of the month when I presented a new topic for the S3OLV User Group.  That new topic was on SSRS and customizing Sorts, Groupings, Display Order of Columns, and a few other tricks.  The custom sorts, groupings etc can all be driven based on parameters and prompts.

It was a pretty decent presentation.  The demos worked relatively well.  There didn’t seem to be any hiccoughs with technology.  I do wish more people had attended – but we are working on that.  I plan on sharing a few articles about the very topics discussed during that presentation.

I like presenting and think I need to find more opportunities to do that during the upcoming year.  Let’s hope I actually publish my goals this upcoming year (unlike 2011 where I started writing out my goals and the post remains unpublished).

My Top 5 for 2011

Categories: News, Professional, SSC
Comments: No Comments
Published on: December 28, 2011

I have seen a few recap posts bouncing around the net and started thinking about my own blog.  So out of curiosity, I decided to take a look at my top 5 posts for 2011.

So, since I already subscribe to Google Analytics, I decided to check the stats in there.  Unfortunately, this doesn’t give me good information on what has been read through a feed reader.  For those views, I have only been able to track down the info for the past 30 days.  So for the fun of it, (and even though the numbers don’t match the feeds) let’s take a look at the most popular posts for the past year – based on Google Analytics.

5.  Activity Monitor and Profiler – This is a post where I talked about using Profiler to discover the behind-the-scenes queries that Microsoft wrote in order to give us the Activity Monitor tool.

4.  SQL Bitwise Operations – I have a few posts similar in nature to this one.  As the name implies, I demonstrate the use of bit-wise operations within SQL Server

3.  A little Dance with SSIS and Informix – This one is rather surprising.  This was written in July of 2010.  Yet, it makes the Top 5 list for 2011.  This article follows my trials with working out an SSIS package that required connecting to Informix.  I think this article is a very useful one in troubleshooting those pesky packages that involve Informix.

2.  SQL 2008 DTS – Starting to see a trend here.  This is another one of those articles I wrote as I was troubleshooting DTS backwards compatibility.  This has been a very handy reference for myself.  As luck would have it, this was also written in July 2010.

1.  70-450 Study Guide – The most popular post of the year (according to Google) was my little study guide.  This study guide merely gives links back to various topics as outlined on the Exam page on the Microsoft site.  There is useful content there to help learn some of the material for being a better DBA – and yes it will help in studying for the exam.  But the guide is designed as a high-level overview and does not delve into specifics.

When I take a look at the feed stats for the past 30 days, I get a much different picture than what Google Analytics provides me.  I am sure if I had the proper tools or even the proper configurations, I probably could see an entirely different story for the entire year.  As it is, these are five useful articles and worth the read.

 

Filtering in SSMS

Categories: News, Professional, SSC
Tags: No Tags
Comments: 1 Comment
Published on: December 21, 2011

Do you spend seconds, maybe even minutes trying to find things in SSMS?  Ever find yourself scrolling up and down thrown the tree trying to find that one specific object you seek?

Did you know it doesn’t have to be that way?  Microsoft has provided us the tools to help improve our efficiency in these matters.  Have you heard of filters?  I’m sure you have.  Did you know that there is a filter ability in SSMS?


Let’s see how that can be done using the master database in our examples.  I have expanded the tree to get down to the System stored procedures in the master database within SSMS.  It looks something like the following image.

From here, you can see that the tree looks pretty much like any other master database when viewing the stored procedures.  You have miles of stored procedures to scroll through.  Should you need to modify a stored procedure (in one of the user databases) this can be a bit cumbersome.

So let’s see what we can do to simplify this a little bit.

When you right click on the folder that contains your object you need to find, you are presented with a context menu.  One of the options on this context menu is “Filter.”

If we follow the menus, we will get something like this.

If you click on Filter settings, you will get a new window like this:

For this example, I have chosen to filter on the term “sp_helptext.”  Once I click OK, I will see a new filtered tree in SSMS that looks like this.

As you can see, I have just reduced my list of objects substantially.  I only have one stored procedure in this case to look at.  If in a user database and I need to modify that proc, then I can more quickly get to work.  This is especially handy if you have thousands of procs, or the procs have extraordinarily long names, or there numerous procs that are similar in name, or in my case – all of the above.

There you have it, my quick and easy Holiday gift to you this Holiday season to help you become more efficient.  BTW, thanks to a friend for pointing this out to me (Jack knows who he is 😉 ).

Public Role and Security

Tags: ,
Comments: 2 Comments
Published on: December 20, 2011

Having flown a fair amount lately, I was thinking about the various levels of security within an airport.  Part of that comes from seeing signs like the following all over the place.

 

These signs are placed in various places for very good reason.  But seeing a sign such as this made me wonder about the various access levels in an airport and how they might relate to the database world.

Let’s start with some of the zones that might be recognizable in an airport.  First there is the ticketing counter.  This is a general access area open to the public.  Anybody can approach a ticketing counter whether they are intending to purchase a ticket or not.  Another area similar to this is frequently the baggage claim area.  These are common areas and generally less secure than other areas.

Next, you might encounter the concourses.  Only ticketed passengers and authorized airport personnel may enter these areas after some degree of screening.  From these areas you have greater access to the airplanes.  You have been explicitly granted permission to enter an aircraft and are required to have a separate pass for each craft you desire to board.  If I were to correlate this to security in SQL server, this would most closely match the db_datareader database role – for which explicit permission has to be granted for the user in order to access each additional database.

From this same area you may witness that there are several aircraft crews.  Each member filling a specific role.  There is a pilot, copilot, flight attendants and ground crew.  Each role may have different access throughout the airport.  And in the case of the flight crew, they have to be given access to each plane they will board.  A United Airlines flight crew cannot go and pilot a British Airways craft for instance – they are responsible for specific flights belonging to UA.

Another potential role is that of the control tower.  The personnel manning the tower have access to quite a bit more than a pilot or passenger.  They have access to communications between all flights and the ground within their airspace.  They are coordinating efforts and trying to make the whole thing go smoothly.  These guys are much more like the specialized server roles in SQL Server.  They can be passengers and have public access.  They can also assist in the piloting of a craft (if you believe what you see in the movies) while giving instruction for flight path, landing and takeoff.

The point is, there is highly segregated roles in an Airport and in the air when an aircraft is involved.  The same should be true in a database environment.  There are special server roles that include public, sysadmin, securityadmin, and diskadmin (amongst others).  Then there are specific database roles that come prepackaged as well as the ability to create any number of specific roles that you need to run your environment.

Now let’s step back out again to the airport example and the public access areas.  These are the least secure areas.  Also, there is a group of people that we should call public.  I am a part of this particular group.  Every person that enters an airport is a member of this group.  The crew piloting a craft is a member of this public group, but they are also members of other more restricted groups.

Being a member of just the public group does not get me permission to enter the pilots cabin.  It does not grant me permission to enter the flight control tower.  It does not even grant me permission to stand behind the ticket counter.  You wouldn’t want just any old Joe Schmoe entering those particular areas – so they become more secure.  And the public group is denied access.

Back to the public server role in SQL Server.  This role is granted VIEW Any Database as well as Connect, but by default is limited to just those permissions.  Can that be changed?  Sure – just like I could walk behind the ticket counter or walk into a pilots cabin (the flight staff may occasionally allow you to take a peek – typically children though).

Just because it can be changed – doesn’t mean it should be done.  In the example of me taking a peek into the Pilots cabin, that is a one person permission being granted.  If I granted that permission to the public role in SQL Server, now everybody can do that same thing.  So think about it for a minute, do you really want everybody being able to change the schema in your database if you decide to grant alter any to public?  I really doubt it.

A good rule of thumb with the public role is to leave it be.  Do not add permissions to this role.  Add permissions on a per database  and per group of users basis.  Create roles within the database and grant permissions to that role – in each database.  And remember the rule of least privilege – don’t grant more permissions to a user/role than necessary to perform the job function.  Just the same as in an airport – everybody has their role and it is strictly defined.  If the user need not have access – then don’t grant the permissions.

I want to re-iterate that point.  To help prevent unauthorized access, keep permissions in the public role to a minimum and create roles within the database to manage the different job functions as necessary/possible.

Why do I Blog?

Categories: Meme15, News, Professional, SSC
Tags: ,
Comments: 1 Comment
Published on: December 17, 2011

Last week I heard about a new blog party/meme coming down the pipe for the #SQLFamily.  This new meme is Meme15 and is the pet project of Jason Strate (Blog|Twitter).  Here is the announcement.

So, as this first Meme15 begs, why did I start blogging?

Was it fame? NO

Was it glory?  NO NO

Was it the fortune?  NO NO NO

I started blogging to help improve some of my skills.

The primary skill I was trying to improve was my writing.  It’s not easy writing.  It’s even more difficult to write something that is good.  Take it another step and you will find that it sometimes is quite difficult to write something that is good and something that is technical.  Now, let’s go another level and try to write something good, technical, interesting and doesn’t put you to sleep (in other words it has character) – but without sounding like a super dork/geek/nerd blah blah blah.

At some point in your writing, after you have been writing for a while, you start to find your keyboard/voice though.  Then it starts to get a little easier for a while.  You start to learn about who you are as a writer, then you can either accept it or you can work on it and develop a little bit.  I think I have found a little bit of who I am as a writer – and I continue to work to improve on it.

Then  there is that whole aspect of putting your work out there for everybody to read.  This public display was the second thing that led to me starting a blog.  Newsflash, I am not the kind of person that likes to put myself on display.  Which is kind of weird because I have no qualms about public speaking.  But I don’t feel like public speaking is putting myself on display – I am usually well prepared when speaking.

With writing, you put yourself out there a lot more than public speaking though.  With writing a blog, you are exposing yourself to millions of people.  Furthermore, it is there for a very long time once you publish it.  You screw up, it is there for people to see.  You write poorly – it is there for people to see.  If you write some really bad TSQL – it IS there for people to see.  In spite of all of that, I wanted to overcome that fear of having my flaws and shortcomings exposed for people to see.  And you know what?  It is well worth it.  Having this stuff out there, I try harder to make it better.  I find myself doing more research before posting.  I also find myself revisiting and re-posting scripts over time.

I revisit old scripts and old posts and find myself learning and evolving as a DBA (in addition to as a writer).  As I learn something, I like to implement it where appropriate – especially when it will make my scripts run better.

As I have continued to blog over time (not quite two years now), I have seen growth in these areas.  I have benefited from this adventure.  I have also seen that I am more at ease in other areas such as public speaking or being a dork in public and letting me be myself (another thing my wife sees in our home but not in public as much as she may like).  In addition to those benefits, I have also seen the benefits in other areas such as depth of knowledge and breadth of knowledge.

Increasing my SQL Skill set is one of those benefits that I did not quite fully expect would happen.  It wasn’t one of the reasons for which I started blogging – but it is certainly a reason I recommend to people when they think about starting.  I expected to improve my skills – sure.  But I expected to see that anyway because I had always progressed.  I did not expect to see such a high correlation between blogging and increased ability in SQL.  If for nothing else – blog for that reason.  Blog so you can become a better DBA.

Oh, I know this is a bit late.  I am taking advantage of what Jason said in his announcement: “They just need to be posted on or around December 15th.”  So I am posting today because it is close to the 15th and felt that waiting any longer wouldn’t be close enough. 😉

T-SQL Tuesday #025 – Holiday Gifts

Comments: No Comments
Published on: December 13, 2011

Tis the Season

It is the season for TSQL Tuesday.  More importantly it is the season for giving and reflection.  And whether you celebrate Christmas or Chanukkah or Kawanzaa or TSQLTuesday because you believe or simply because of tradition, it is a good time to reflect and help somebody else.

Amidst the hustle and bustle, Allen White (Blog|Twitter) is hosting at least one party during this season.  And with all of our hustle and bustle, many of us will be attending at least one party this year (as evidenced by this post).

Allen’s party theme is an invitation to “Share Your Tricks.”  Well, in the spirit of the Holidays, I want to share some tricks and tips.  They can be my gifts to you during the holidays.  And maybe they can help you give to somebody else.

Tricks

ObjectProperty()

I find myself using this frequently in queries.  A popular use for myself is to use it to find the value of ‘IsMSShipped’.  But that is only one possible use for this function, there are many many more.  Check out MSDN to see more power!!

Here is a relatively meager example.

[codesyntax lang=”tsql”]

[/codesyntax]

ServerProperty()

I like to use this function regularly as well.  Sometimes it is handy to break out information about the server into a columnar set.  I can find useful information from Service Pack level to whether the Instance is Clustered or not.  Here is a quick sample.

[codesyntax lang=”tsql”]

[/codesyntax]

Primes

Now for a little bit of fun stuff.  It was suggested on Twitter last night that I show a TSQL solution for generating prime numbers.  Thanks to Adam Mikolaj (Twitter) for this suggestion.  I am not going to explain it other than to say that this will help generate the prime numbers between 1 and 1000.

[codesyntax lang=”tsql”]

[/codesyntax]

Tips

No Changes

Don’t plan any rollouts during the holiday season.  Try to have a production freeze implemented.  For many companies this is a busy season.  To further the point, many employees like to take a vacation during this time period.  With a lighter staff, key personal may be out of touch should an emergency occur due to a rollout.  So minimize your stress and minimize the chances of an emergency and don’t do any changes (excepting emergency fixes) during this time of year.  It is a good time to catch up on other items on your to-do list.

Take a Time-Out

Don’t be too busy for the important things in life.  Some of these things include self and family.  Don’t get yourself going too fast for too long that you miss out on the good stuff.  Don’t be soooo busy that you have no time for yourself to relax.  Recharge your batteries by taking a time-out.  Yes!  Give yourself a timeout.  Spend time with your spouse and kids.  If you don’t have a spouse or kids, then spend some time with a friend or #sqlfriend or #sqlfamily.

Help somebody in need

This is closely intertwined with the previous topic.  Give something of yourself to help somebody else.  A little joke, a little smile, a little service will go a long way (just as much for you as the other person).  If it means donating a toy, some food, some cash – do it.

MCITP: 4 Down 0 To Go

Categories: News, Professional, SSC
Comments: 9 Comments
Published on: December 7, 2011

Yay.  I finally took the plunge and decided to take Exam 70-451.  This is the MCITP exam for the SQL 2008 Dev track.

Frankly, I had taken my time with this one because I was a bit concerned after taking the 70-450 exam.  I saw that exam was more difficult than its MCTS counterpart and fully expected the 70-451 exam to be more difficult.  And based on that assumption I wanted to devote some time to study.

So here is what I did to study:

Well, I did not find the time to study and decided to just get it done.  I took the exam cold.  And not only was it cold by means of not studying, the building did not feel like it was heated and I walked out, after finishing the test, a popsicle.  Add to that the next level of cold – I was up until 2am working.  Then we throw in a snafu created by Prometric (happens every time though) and a 20 minute wait to start the test.  Things could have been better going into this exam.

Needless to say, experience pays off with this exam.  The biggest piece of the exam is based on practice and not so much the semantics of the code.  That said, I did have three questions that were impossible to answer.  The question description and requirements immediately eliminated all of the answers.  Needless to say, I did not like those questions and left ample comment about them.

Otherwise, the exam was successful.  I passed missing only 5 questions (by my calc).  Now I am off to start down the 2008 BI track followed by the MCM Knowledge Exam.

If you are interested here is an excellent resource to study.  Yes, I actually looked at the topics to be tested prior to taking the exam.  I felt comfortable in most areas and felt I could handle the exam too.

And here is my study dump:

 

Did you really think I was gonna give you a dump of the exam?

December Event Reminder

Categories: News, Professional, SSC
Tags: ,
Comments: No Comments
Published on: December 6, 2011

We are now only two days away from the biggest event of the week!!

 

More accurately though, it is the BIGGEST event for the Las Vegas chapter of PASS between 6:30Pm and 8:30PM Pacific.

The event is our Monthly meeting.  And this month we have a pretty good topic – SSRS.

Have a look at the S3OLV website, or my blog post to find out more about this excellent event.

 

What if you are not able to physically join us?

For this express purpose we are trying to make our meetings more available to the masses.  Every meeting can also be attended virtually.  You can find the LiveMeeting information from the linked blog post.  Please attend and show your support.

We have the presentation on tap, plus we have the “Ugly Code” segment that we will be trying.  In addition, we also have a need to get the ball rolling for SQLSaturday in Las Vegas (yay – more free training and networking).

«page 1 of 2








Calendar
December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, September 24, 2017