Seattle SQL Pro Workshop 2017 Schedule

Categories: News, Professional, SSC
Comments: No Comments
Published on: October 26, 2017

db_resuscitateSeattle SQL Pro Workshop 2017

You may be aware of an event that some friends and I are putting together during the week of PASS Summit 2017. I have created an Eventbrite page with all the gory details here.

With everybody being in a mad scramble to get things done to pull this together, the one task we left for last was to publish a schedule. While this is coming up very late in the game, rest assured we are not foregoing some semblance of order for the day. ūüėČ That said, there will still be plenty of disorder / fun to be had during the day.

So the entire point of this post is to publish the schedule and have a landing page for it during the event. *

Session Start Duration Presenter Topic
Registration 8:30 AM All
Intro/Welcome 9:00 AM 10 Jason Brimhall  
1 9:10 AM 60 Jason Brimhall Dolly, Footprints and a Dash of EXtra TimE
Break 10:10 AM 5    
2 10:15 AM 60 Jimmy May Intro to Monitoring I/O: The Counters That Count
Break 11:15 AM 5    
3 11:20 AM 60 Gail Shaw Parameter sniffing and other cases of the confused optimiser
Lunch 12:20 PM 60   Networking /  RG
4 1:20 PM 60 Louis Davidson Implementing a Hierarchy in SQL Server
Break 2:20 PM 5    
5 2:25 PM 60 Andy Leonard Designing an SSIS Framework
Break 3:25 PM 5    
6 3:30 PM 60 Wayne Sheffield What is this “SQL Inj/stuff/ection”, and how does it affect me?
Wrap 4:30 PM 30   Swag and Thank You
END 5:00 PM Cleanup

*This schedule is subject to change without notice.

Seattle SQL Pro Workshop 2017

Categories: News, Professional, SSC
Comments: No Comments
Published on: October 19, 2017

Seattle SQL Pro Workshop 2017

October is a great time of year for the SQL Server and Data professional. There are several conferences but the biggest happens to be in the Emerald City РSeattle. db_resuscitate

Some friends and I have come together the past few years to put on an extra day of learning leading up to this massive conference. We call it the Seattle SQL Pro Workshop. I have created an Eventbrite page with all the gory details here.

That massive conference I have mentioned – you might have heard of it as well. It is called PASS Summit and you can find out a wealth of info from the website. Granted there are plenty of paid precon events sanctioned by PASS, we by no means are competing against them. We are trying to supplement the training and offer an extra avenue to any who could not attend the paid precons or who may be in town for only part of the day on Tuesday.

This year, we have a collision of sorts with this event. We are holding the event on Halloween – Oct 31, 2017. With it being Halloween, we welcome any who wish to attend the workshop in FULL costume.

So, what kinds of things will we cover at the event? I am glad you asked. Jimmy May will be there to talk about IO. Gail Shaw will be talking about the Query Optimizer (QO). Louis (Dr. SQL) will be taking us deep into Hierarchies. Andy Leonard will be exploring BIML and Wayne Sheffield will be showing us some SQL Injection attacks.

That is the 35,000 foot view of the sessions. You can read more about them from the EventBrite listing – HERE. What I do not yet have up on the is what I will be discussing.

My topic for the workshop will be hopefully something as useful and informative as the cool stuff everybody else is putting together. I will be sharing some insights about a tool from our friends over at Red-Gate that can help to change the face of the landscape in your development environments. This tool as illustrated so nicely by my Trojan Sheep, is called SQL Clone.

I will demonstrate the use of this tool to reduce the storage footprint required in Dev, Test, Stage, QA, UAT, etc etc etc. Based on client case study involving a 2TB database, we will see how this tool can help shrink that footprint to just under 2% – give or take. I will share some discoveries I met along the way and I even hope to show some internals from the SQL Server perspective when using this technology (can somebody say Extended Events to the Rescue?).

Why Attend?

Beyond getting some first rate training from some really awesome community driven types of data professionals, this is a prime opportunity to network with the same top notch individuals. These people are more than MVPs. They are truly technical giants in the data community.

This event gives you an opportunity to learn great stuff while at the same time you will have the chance to network on a more personal level with many peers and professionals. You will also have the opportunity to possibly solve some of your toughest work or career related problems. Believe me, the day spent with this group will be well worth your time and money!

Did I mention that the event is Free (with an optional paid lunch)?

Linked Servers and Stats

Linked Servers

A linked server is a fabulous feature in SQL Server to help accomplish various data tasks between local and remote servers. There is a time and a place for the use of linked servers. Unfortunately, some are of the belief that a linked server is the solution to all problems including the way to end world hunger.

You may recall that I have written about linked servers on a few occasions. Usually that is due to the relationship of a poorly performing process and the linked server. Here is a prime example here or here. On the other hand, here are some examples of using a linked server in a much better light here and here.

Using a linked server is entirely up to you. If you choose to use a linked server, you will need to understand there are risks involved – beyond just the documentation and performance nightmares it can cause. Today, I will share one of those fabulous gotchas – mostly because it has cropped up more than once with my clients.

Remote Stats

On more than one occasion I have had an emergency request because everything was broken. The everything in almost every incident is an SSIS package that is failing with error messages. The error message will typically have text similar to the following:

Could not locate statistics ‘_WA_Sys_00000015_346C780E’ in the system catalogs.

Due to the error, the package fails processing and grinds to a halt. When diving into the package it is discovered that the missing stats happen to be coming from a linked server query. This raises a big bright blaring alarm for me. Why is the SSIS package accessing the data via a linked server? This is rather counter-productive and definitely contrary to what is desired from a performance perspective.

You can certainly see why this is a sudden emergency right? Absolutely nothing is working anymore, right? Well, at least that is the way it seems for the person reporting the issue. Understanding that their realm of work has come to a halt is important in order to get to a resolution more quickly. Knowing that the point of failure is not as grande scale as claimed is critical because it can help you keep your cool while assessing the real problem.

Since the problem presents itself when accessing a specific object or even set of objects across a linked server, we have our scope of the issue scaled down quite a bit already. Now, what do we do from here? Obviously the statistic with that name is missing, right? Don’t be so hasty to assume it is actually missing. Let’s verify that the stat is indeed missing first. Even better – let’s just eliminate as many variables as possible. We can do this by querying the affected objects directly from SSMS.

Easy Troubleshooting for the DBA

 

For me, it is essential to eliminate variables when we run into a failure. In this case, I have just a few major variables that can potentially be causing this problem. Those variables are:

  1. SSIS
  2. Linked Server
  3. The query itself
  4. Security
  5. Bad Plan
  6. The stat is legitimately missing

I can easily assess the legitimacy of each variable through various quick tests. To eliminate or validate the “query” variable, I can issue a very simple query to retrieve data from the affected object. So let’s eliminate variables 1 and 3 in one fell swoop.

This query, in this scenario, results in the following:

This, while disappointing, is actually quite productive. This has eliminated two variables for me. I now know that SSIS is not related to the problem. I also know that query complexity is not related to the problem. There are still a few variables left to either validate or eliminate. Since I know the problem occurs when querying via linked server, let’s try querying the remote server direct (not via the linked server).

Well, while that does not entirely eliminate or validate any variables, it does tell me that the problem is still specifically related to the linked server. What if I try to use OPENQUERY instead of the traditional linked server query?

Wow, look at that? Ok, so this is a bit of trickery because I have told SQL Server to execute that query on the linked server as if it were a “local” query on that server. This does work without error and is definitely pushing the findings to be more conclusive that it is a linked server problem.

While the openquery version works, I do still need to eliminate some variables from the problem. One of the variables is security. Since the error mentions sp_table_statistics2_rowset, I googled about for that proc and found some mentions that maybe there are some column denies related to the stats in question that is giving the linked server some major fits. Or it could also be insufficient permissions to execute DBCC SHOW_Statistics. I highly doubt this to be an issue since the openquery version works while using the same pass through authentication of the linked server that the traditional linked server query would use.

In order to eliminate security as a potential cause, the test is simple (while it could be more complex, I went for the jugular to just eliminate the theory as quickly as possible) – I will add my account as the pass through account (which is a sysadmin on the remote server) and then query the linked server all over again. Suffice it to say, there was no change in the result – the error persisted.

This does not just yet eliminate the security variable because there could be a cached plan somewhere. So, just to be sure, I chose to flush the cache on both the linked server and the local server. Running the simple “Select *” query all over again yielded no difference in the query results. I can now safely saw that the problem is not related to a cached plan nor is it related to the security. At this point, I set the linked server security back to the way it was. I have effectively eliminated all variables but 2 and 6 (linked server and the stat is missing).

Let’s eliminate the missing stat variable right now.

Undoubtedly you have noticed that I built a drop statement into the result set from this particular query. That aside, the green highlighted row is the very stat that was producing the error. This stat is most certainly available on the source server (linked server). This entirely eliminates the sixth variable because the stat is not missing.

This brings us to the final step – see what happens when we drop the stat and try the query again. I have the drop statement ready to roll, so let’s just run that and see what happens. My risk here is very small. This is an auto-generated stat and will be recreated if needed. After dropping the stat, I run the simple “Select *” query across the linked server again and guess what? It works perfectly fine.

In my opinion, we can just skip a few of these steps if the problem is on a system generated stat and just go straight to dropping the stat and trying again.

That said, the better solution in this case would be to do one of two things.

  1. Modify the Package to not use the linked servers in this way and create a data source connection for each of the different server connections.
  2. Modify the Package to use OPENQUERY style queries.

My preference between those two options would be to create specific data sources. It seems very silly to pull data across the linked server to then be consumed via SSIS package.

I have just illustrated what I would classify as a nuisance problem related to linked servers. Simplifying the approach as I described with the two potential solutions would alleviate this nuisance while also giving an inevitable performance boost.

Your Name is Your Brand

Categories: Blogging, Professional, SSC
Comments: 3 Comments
Published on: October 13, 2017

This topic is one that has been stewing for a while and finally this week it decided to boil over.

My first thought with this post was to write a rant. Instead, I want to try and turn it on its end and try to put a different spin on the problem. Yes – I said problem.

Know your Audience

This started when I was setting up a piece of software (to demo to a client) for a vendor that I respect – A LOT. I noticed something peculiar about the software that was different than the documentation (and therefore expected result). I reached out to my contact at this company and he escalated a ticket to their support staff. After a few back and forth threads, my contact noticed something troubling – as had I. While I was going to set it aside but my contact was bothered by it too. The support personal started calling me by the wrong name.

Addressing somebody by the appropriate name or title is a pretty important topic. In this case, not only did they start addressing me by some other name, they had also lost sight of the existing relationship I had with this company as well as any previous information provided to them demonstrating level of expertise in the area of SQL Server or with the specific observation being reported. This is demonstrative of a lack of attention to detail – both in regards to the audience and the technical problem.

Your Name is Your Brand

If I throw a few names out there like Paul Randal, Steve Jones, Grant Fritchey or Brent Ozar, chances are you will know who those people are (given you are reading this blog post). These are people (like many other giants in SQL Server) that have built a brand based off their name. This is a good thing. You recognize the name and you recognize that they are very good at what they do.

Building your brand is not an overnight sensation. It does take a while and possibly a bit of luck here or there along the way. The one thing it always starts with is your name. This leads me to my next story.

This week I have been at Summit. This is not the traditional DBA Summit hosted by PASS, rather this is related to Dynamics (AX, NAV, GP, CRM, 365). Just like most conferences, we all get a nice easy to read name badge.

Hopefully you can easily read that mine clearly says my name is “Jason”. Besides the name, there is a QR code on the badge. Otherwise, the name is large enough you can generally read it from afar.

While meandering through the Exhibitor Hall, I stopped at one particular booth that was raffling off an R2D2 that particular day (they also raffled off a C3P0 and a Yoda on other days). And yes it is very rare for me to stop at one of these raffles – but it was R2D2.

As I was stopped to fill out the form for the raffle, I was approached by an eager booth dude. “Hey Larry, how’s it going?” he said. He was obviously talking to me, so I turned to him and said “Jason” while showing him the name on my badge. Not even 30 seconds later he did it again “So, Larry…”. At the sound of that, I had to cut him off and correct him again and reminded him I had just told him my correct name and even showed it to him via the badge. At this point I abandoned the R2 raffle and told him I was no longer interested.

This was coming from a vendor that was more than likely hoping to try and pitch their software. A lack of attention to the audience at hand just cost him a lead (albeit small compared to the number they gained). Now, that vendor has to worry about the word of mouth that may come from their lack of attention and focus.

Now, my name is pretty important to me. It may not be as important to you – but it is to me. Similarly, your name should be extremely important to you. You and I are all working at a similar task – building our own brand based on our names.

I am sure both of these vendors I have illustrated are also keen on building the brand around the corporate name they have established. Brand and name are recognizable. Now, put yourself in either of these two scenarios I have just described. Have you ever made a mistake with somebody else’s brand? Or, have you ever been on the receiving end of this kind of mistake? Suddenly the world is spinning a slightly different direction, right?

I won’t divulge the names of either vendor in this case (partly because I still have a good relationship with the one and I have already forgotten the name of the second), but think about this: What do you do for your brand when somebody steps on it? How we react (and granted I probably could have taken a higher road in the second case by gently reminding the vendor yet again what my name was) can do quite a bit for building a brand. It’s not always about what you know, but also about how you conduct yourself towards and around others.

Endpoint Owners – Back to Basics

Remember When…

sqlbasic_sargeBack in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

While the challenge may have been for the year 2016 and I haven’t contributed in several months,¬†it is still a worthwhile effort. In that vain, I am adding this article to that series.

With this being another installment in a monthly series, here is a link to review the other posts in the series – back to basics. Reviewing that link, you can probably tell I am a bit behind in the monthly series.

Endpoints

You may have heard the term endpoints thrown around in technical discussion and wondered “what the heck is an endpoint?” Well, that is a good question. An endpoint in the simplest form is a connection or point of entry into SQL server.

Another way of thinking about an endpoint is to look at the ends of a line. Generally speaking, there is a stop point at each end of the line. At this stopping point, the line may connect to something else and therefore be a point of entry into that “something else”.

When we deal with SQL Server there is a handful of default endpoints and then a handful of other types of endpoints. To figure out what the default endpoints are, it is pretty easy. The following query will expose the default endpoints.

Executing that query will produce results similar to the following:

Some of those might look pretty straight forward and then you get to that VIA endpoint. Don’t worry about that endpoint. The VIA endpoint is on the deprecation list. All of these default endpoints will be owned by sa and don’t require you to do anything with them per se other than to understand they exist. You may have noticed that I filtered my results by looking only at endpoints with an id of less than 65536. Any endpoint id lower than this number is a default endpoint. All others are user defined endpoints.

Endpoint Owners

So far so good. This is pretty straight forward to this point. If you have implemented anything like mirroring, Availability Groups, or Service Broker, then you may be interested to know that you have also created additional endpoints in the Instance. When you create an endpoint, did you know that you become the owner of that endpoint by default? It is very similar to when you restore a database or create a database – the default owner of that database will be the person that restored/created it.

Do you know who owns your endpoints? Did you create all of the endpoints? Do you know if you have any additional endpoints beyond the default endpoints? If you cannot answer yes to all of these questions, then you will probably want to figure out what the endpoints are and who owns those endpoints. Let’s try that with a slight modification to the previous query.

This will yield results similar to the following:

I took the script a step further than necessary. I wanted to illustrate potential endpoint types that may not be in use. This script covers both in use endpoint types and those not used. In my results you may note that I have a DATABASE_MIRRORING endpoint. As reality would actually have it, it is an Availability Group endpoint but those are presented as DATABASE_MIRRORING endpoints.

Note that my mirroring endpoint (aka Hadr_endpoint) is owned by “YourDomain\DBAdmin”. What if the owner of that particular endpoint was no longer present in the organization and I wanted to change it to something more sustainable? Well, I could do the following:

In this case, the default endpoints are owned by sa and it does make enough sense to assign the owner to be sa for the mirroring endpoint. Take notice that the name of the endpoint is required in order to reassign the owner. The name of the endpoint follows the :: in the script. So, whatever your endpoint name happens to be, just place “Hadr_endpoint” that follows the :: in my script.

Recap

Endpoints are a fundamental piece of the puzzle with SQL Server.  Getting to know your endpoints and the owners of those endpoints is an essential component of knowing your environment. Who knows, it may come to pass that the owner of an endpoint may no longer exist in your environment or possibly lose permissions along the way. Knowing who owns the endpoint may just save three or four grey hairs when that day comes.

«page 2 of 111»








Calendar
November 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
27282930  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, November 19, 2017