Negative Port Numbers

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: December 31, 2019

As a DBA, one of the more critical tasks is to confirm proper setup of a SQL Server. In order to confirm proper setup, I like to validate tcp/ip and port settings. I do this not just through the SQL Server Configuration Manager, but also via query through Management Studio.

Occasionally, when validating the port configurations through this method, you will encounter a fun little puzzle. If you are paying attention to the results, you just may see something that could cause you to scratch your noggin.

Of course, it all depends on the query method you choose to use to perform your validations. There are two easy access methods to use to query for the tcp/ip and port settings. Each may produce slightly different results. Let’s explore these methods.

What’s in a Port?

First let’s go with the easiest of the queries. We will query sys.dm_exec_connections with something that looks like the following.

Executing this query via sqlcmd on my server in question, I might see something like the following in the results.

As you can surmise from the image, the instance I am setting up and validating happens to have multiple IP addresses and a more complex setup because I have it listening on different ports depending on the IP address source. We will focus on the non-default IP address for the purposes of this article. In the local_tcp_port field, you can see that the ports are being reported as expected. And for the sake of simplicity, these ports are correct.

Let’s now divert our attention to the alternative option – CONNECTIONPROPERTY().

In the results window, I see something like this:

Take quick note of the port number I have circled in red. This doesn’t match the original query at all. In fact, it doesn’t come anywhere close to the actual port number. In addition, the port number shown here is a negative value. Obviously a negative port is not correct as TCP/IP ports only range from 0-65535. So what is happening here? Let’s change this query just a little bit and combine the two sources.

The results of this query give me the following.

OK, cool. So we can see that some sort of masking has been created to conceal the port number when it is a “dynamic” port. What is the significance of 65536 though? Well, it just so happens that 16 bits is 65536. This would give us port values of 0-65535 with port 0 being reserved and unusable. Thus, we simply do a little math to figure out what the actual port is by adding 65536 to the negative port value. That is shown in the following code snip.

When calculating the value between sys.dm_exec_connections and ConnectionProperty(), ensure you perform a conversion on the value from ConnectionProperty(). Despite documentation showing that it is an integer value on the port, it is not. The data type for the port value from ConnectionProperty() is actual sql_variant and an implicit conversion won’t work there.

Put a bow on it

Validating your server setup is an integral component of your duties as a SQL Server DBA. When performing those validations, it is possible to run into an intriguing difference in reported port values. That difference of value is easily rectified if you understand that the ConnectionProperty function is doing a bit of a port mask by subtracting 65536 from the actual port number value.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the seventh article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

Get a List of Files with Data

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: December 30, 2019

Suppose you have a directory on your server that houses over 300,000 csv files. These files are automatically created by an automated process related to your SQL Server operations and are automatically created when the process runs. The process runs on a schedule every 15 minutes.

Now suppose that you have been tasked with figuring out which of the files have data of value in them and which can be ignored. The files that can be ignored will all be either 0k or 1k in size. However, some of the 1k files can also be ignored because they only contain a header. Every csv file should have a header. So, we must figure out a way to filter out those files that are 1k or less and only have a header row.

Filtering out these files with this criteria will allow for us to find files that have useful data in them. Now why do you need to figure out which ones have useful data? Let’s just say that maybe some of the files were created with some bad data in them and you need to figure out which files may have bad data so those particular files can be regenerated. As luck would have it, the automated process does not have any sort of logging and does the bare minimum to create the files in the first place. (Sometimes you just inherit a flawed process where inadequate thought was given.)

Finding the Files

The hard part has been done at this point. At least you know some of the attributes that will help distinguish wanted files from the unwanted files. Sorting through these files by hand could be rather bothersome and cumbersome. The trick here is to find a way to search all of these files quickly and filter easily the bad from the good. Sounds like an opportunity for another automation or script. I have just the powershell script for that.

With this script, I have a couple of things to help me find files of interest quickly. First, I have a filter in place to allow me to search a specific number of days worth of files. Second, I have a filter in place to ensure it only returns file names where there is more than just a header row present.

Now, instead of spending hours perusing files trying to find something of value, I have reduced my time spent to just a mere fraction of that. Next steps after this would be to go and add some additional logging and better robustness to the initial process to reduce the chance of bad data being put into the files in the first place.

Put a bow on it

Automated processes are fantastic. We as DBAs strive to have more tasks automated than not. That said, automation without planning is just creating a time sink later on in the job. At some point, you may need to employ some powershell script similar to this in order to find where your automated process has gone wrong (if you did not plan that process well enough from the start).

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the sixth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

What is the Merit of the Job?

Categories: News, Professional, SSC
Comments: No Comments
Published on: December 29, 2019

For the past several months I have been pondering over the topic of meritocracy. Meritocracy seems to be a hot button topic that goes between hot and cold cycles in various circles and climates for whatever reason. There seem to be quite a few political fires that can be easily stoked with this topic. For me, I haven’t been thinking of it from a political standpoint. For me, I have been pondering how the rat race of meritocracy has affected me unwittingly.

What is meritocracy? Well, there are a great many ways to describe this idea, but let’s just stick to the base definition.

Meritocracy: a form of social system in which power goes to those with superior intellects or ability.

I have no doubt that there is an absolute need for meritocracy in certain situations. For example, as a sports coach, you want your best athletes on the field in order to compete. The best athletes are determined through a series of assessments and monitored efforts and performances. They have earned their way into that position based on merit. Let’s be honest here, as spectators we also want to watch the best athletes and not the scrubs else we wouldn’t pay money to attend. It is a disservice in certain situations to not award those with the greatest ability the merit of more playing time in an athletic competition.

Serious Flaw

When we start employing a merit system to our personal lives or to our professional goals, things can get a little dicey. One of the major attributes of a merit based system is extreme competition. I view competition as a good thing. It keeps me on my toes and progressing personally. That said, there is a hidden undesirable impact that comes from extreme competition – and that impact affects interpersonal relationships.

As IT professionals (and the running joke even in IT is that DBAs are far worse than everybody else) we are far too often introverted. We are internally driven to achieve certain levels of success. Along with that internal drive, we far too often focus on the merits of the job that affect our career status and seldom pay adequate attention to those merits that affect our personal relationships and feelings as human beings.

You see, meritocracy is a rat race. In a meritocracy we are driven for results and in business the results frequently come at the cost of human interactions and relationships. How well do you know the people around you? Have you taken much effort to try and establish a rapport with your co-workers or clients – beyond the ones that sign the checks?

This is the serious flaw in a merit based system. One tries to climb the ladder of success but at the cost of being an approachable human being. Nice people don’t need to finish last. Maybe your merit review should have a line item about building relationships of trust and determine how well you have succeeded at that requirement (did you exceed expectations?)!

Put a bow on it

Merit based systems certainly have their place in business and in life. How well we govern our personal interaction with the merit based system and how we conduct our personal growth should matter more than most other achievements in life. Interactions with people is crucial to our growth as a professional and as a human being.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the fifth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

It’s an Honor: Microsoft MVP

Comments: No Comments
Published on: July 1, 2019

Today (July 1st) is the notification day for all Microsoft MVPs. Microsoft changed it a couple of cycles back to be all on the same day helping all candidates to come to a single emotional day. I received my notification early this morning that I was awarded the MVP again for the 2019-2020 cycle. This is my 6th award overall.

I am humbled to be among so many great professionals and honored at the same time for having been chosen. Are you new curious about this MVP thing? Well, its sorta like being named MVP for a sports league while at the same time a little bit different. Here is what Microsoft has to say about the MVP. I personally think you may get more out of reading about the MVP here (a little bias might be involved).

This is a community award for your efforts to promote and help others work with Microsoft products. I was awarded for my efforts in the Data Platform. Many others receive the award for their efforts in AI, Powershell, or Azure (as examples). Generally, the people that are awarded are seen as being passionate about the Microsoft Product. These people spend a lot of time, effort, energy, and sometimes money to help share their enthusiasm for the product.

Microsoft is doing a fantastic job at recognizing people for their efforts in promoting the MS brand. Not everybody that promotes that brand gets to be an MVP – sadly. If you didn’t make it this time, keep trying. I just advise that the goal be more about the community than the award. The MVP award is great, but giving to the community is the real reward.

I want to congratulate all those awarded a Microsoft MVP award past, present and future. I also want to thank all of those who have given to the community regardless of whether an award was received or not. There are many of you out there that have been a positive influence whether you know it or not – just for giving to the community and helping somebody else. THANK YOU!

Note to Self

TSQL2sDay150x150TSQL Tuesday

The second Tuesday of the month is upon us once again. That means it is time for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

Sands of Time

This month, MOHAMMAD DARAB (b | t) invites us to do the 40 yr. old self reflection. You know that age that is almost as dreadful to reach as the righteous age of 50? From there age only gets better – or at least I have been told that. I have done a couple of articles similar to this in past TSQLTuesdays. Similar yet still different just enough. Check out my article on passion,  or this on one influence.

The problem with this particular challenge is the request to write a note to your 20 yr. old self. Well, maybe not an extreme problem other than the time travel part. As luck would have it, I recently discussed the topic with my grandmother. There was a lot of wisdom in what she taught me as we discussed the topic and I must admit, I was in agreement prior to even broaching the topic with her.

Passing Notes

Here is basically what my note to myself would say: “Self, you are doing good. Keep it up!”

That is even if I chose to send myself a note. You see, I would not want to change how things have happened. Life experience is good – both the good and the bad. All of it is necessary to grow and develop. Just like Mater’s dents, each experience is valuable and shouldn’t be changed.

Now, what if I were to create a note of advice for other more junior level professionals that I might be mentoring? That is a different story. My note to them would be simple – “Listen to me.” Maybe not so literally “me” and maybe more precisely as advice to listen to anybody who is giving their time to present, mentor, blog, or write (articles, books, etc). They are imparting of their experience and time and you should respect that and take advantage of it.

Not only should you pay respect to those giving of their time, but you should spend a little time to also give back to the community. Why? Well, I covered that pretty well in this article here. I will say it again, the more you give of yourself to help others learn your trade, the more you will personally learn and grow in your professional and personal self. It is an investment well worth taking.

 

Wrapping it Up

I don’t believe in trying to alter who I have become with any sort of visit back to my past self. The changes could cause me to change my path and not end up where I have reached due to personal growth and experience. Trial and error is good for growth. Everybody needs to learn that for themselves and cannot be forced into it. Just learn to grow and be patient with anybody you may mentor.

Puzzles and Daily Trivia

TSQL2sDay150x150TSQL Tuesday

The second Tuesday of the month comes to us a little early this month. That means it is time again for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

The Why?

This month, Matthew Mcgiffen (b | t) invites us to come to a little puzzle party for our TSQL Tuesday party. Bring your favorite brain teaser, puzzle, questions, or interesting and complex TSQL problems/solutions.

This ties pretty nicely into the topic from last month (well at least for me). If you recall, last month Todd asked all of us to share some of our uses for databases in our personal lives. I submitted my article here, but forgot about one of my favorite uses for a database in my personal life – a daily trivia set about SQL Server.

So, why not elaborate on that database a bit today. Almost like a two for one. However, there is one little quick departure I want to make. My first puzzle solved with TSQL was written about many moons ago and can be found here.

TSQL Challenges

A long time ago, Jacob Sebastian ran regular challenges involving TSQL to get you to think about ways to solve problems using TSQL. The site is no longer available, but challenge #97 was about solving Sudoku puzzles. Here is my solution to that particular challenge. A little TSQL and a bit of the black arts and there is a nifty little solution. Even though I have this little trick in the bag, I still solve the Sudoku puzzles the hard way.

What a nice little stroll down memory lane there. That solution alone could satisfy the request for this TSQL Tuesday. Alas, we won’t stop there!

QOTD

Several years ago, I put together a little database to help with daily trivia questions. The database is a simple design and had a primary function to help teach SQL Server facts and internals to those with varying levels of DBA experience as well as helpdesk members. That said, the topic of questions doesn’t have to be SQL specific – it was my primary use.

With just a few tables and a few procs, I have a database that I can use to create questions, track responses from participants, and email questions and answers to participants on a daily basis – automatically. If I run out of questions, I just add more to the table. Nothing super complex there.

What this offers me is a mechanism to mentor multiple people without the burnout and while also gauging their true interest level in improving their SQL skill set.

Wrapping it Up

One of my favorite database automations is to send trivia style questions daily. This helps me to mentor and assess other dba talent within an organization without being too aggressive. Not only can a database be used for automation but it can also be used for numerous other automations. Beyond being highly useful for automation and training, there is also the possibility of using TSQL to solve puzzles like Sudoku puzzles.

 

Making Databases Personal

TSQL2sDay150x150TSQL Tuesday

The second Tuesday of the month comes to us a little early this month. That means it is time again for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

The Why?

This month, Todd Kleinhans (b | t) invites us to share something personal about ourselves. Well sort of. The invitation to each of us is to share a little about databases that we use in our personal lives or that we have created in our personal lives to help us in some fashion or another.

I think this is a really cool idea. Data and databases are very integrated into my life. The integration is enough that it would be difficult to un-blur the line where personal life and professional life begins when talking about data and databases.

Take a moment and reflect on the importance of data and databases within your life. At a minimum, you have taken the approach that data is your profession of choice and hopefully it is a career for you and not just a job.

If you have taken it down the career path, then you probably study your craft in your personal time to try and make yourself better at your trade-craft. Often times, if you are like me, these studies lead to trial and error, experiments, and possibly rabbit holes. If data is just a J-O-B, it is quite likely none of this will apply to you. Those are the types that fall into a category Steve Jones wrote about recently, and I would urge you to change that sooner rather than later. (Side note, I read that article as if it applied to me directly because I really do need to learn and practice more about my craft too!)

Personal Databases

Over the years, I have had sooo many personal databases that I have created for one reason or another. Some still need to be finished. Here are some examples of what I use a database for in my personal life.

Finances: A simple little database to track (sort of like a checkbook yes) credits and debits. More importantly, it tracked bills and due dates with dollar amounts for each bill. This is useful both for reminders for bills but also to see if there are any noteworthy trends with spending or increased costs. Now, many banking (and credit card) sites offer the same types of services – so long as the spending is done with their card.

Exercise: Back in the day, I tracked all of my activities in a database. I would track the duration, activity type, effort level and various biometric measures after each activity. Once again, this is something that is now tracked by so many different apps and databases that it seems counter productive to maintain my own system in favor of something that works more easily on a wearable device.

I have also previously done databases to inventory books, movies, track personal studies and link thoughts about studies from day to day, or even track routine chores (calendar reminders don’t offer much for tracking details). The short of it is, a database can be created to manage so many things as a part of everyday personal life.

 

Wrapping it Up

If you can use a database to create automation routines to help sustain your work life, you can certainly do the same to sustain your personal life. Databases are an essential piece of both my personal and professional life. Not only do I use them to create automation (such as this or this) for things, but I stand databases up to test numerous things from corruption to recovery to performance tuning to help improve my personal abilities and craft in the realm of data.

 

Passion, Challenges, and SQL

Comments: 1 Comment
Published on: February 12, 2019

TSQL Tuesday

The second Tuesday of the month comes to us a little early this month. That means it is time again for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

The Why?

Long time friend Andy Leonard (b | t) invites us this month to do a little checkup on ourselves and talk about the “why” around what we do. This could be a very easy topic for some. Equally, this could be a very difficult topic for those same people at different times in their lives. Thus the problem, the topic is simple in nature but sure requires a firm reflection on self and what you have been doing.

The problem for me is less about the “why” behind what I do, and more about how to stretch it out into something more than a few sentences. Think! Think! Think!

Challenges

One of my biggest reasons why I do what I do, boils down to the challenges that I frequently get to encounter. There is a wild satisfaction to working on a very difficult and challenging task, product, tool, profession, skill, etc. This satisfaction often involves reward and a sense of accomplishment.

The challenge can be anything from how to effectively communicate with a difficult person, a tough to find internals problem in SQL Server that could be causing a performance issue, or taking over a project and turning it back from the edge of failure and onto a track of success. Sometimes, the challenge may be as simple as converting a pathetic cursor into a set based approach and gaining an improvement of 100x in performance.

I really do enjoy some of the puzzles (challenges) that I get to work on routinely. This gives me an opportunity to improve my skillset as well as continue to learn. Being able to continually improve is a great motivation for me. The frequent challenges and continual opportunity to learn presents a great opportunity to evolve ones self and career. In a constantly changing world, being able to naturally and easily evolve your personal career is a bonus!

Passion

“Do what you love and you will never work a day in your life.” This is a common saying in the United States. Agree or disagree – there is some truth to it. Being able to do something one loves makes the really hard days a lot easier. Knowing, I may be able to solve a complex problem makes it easier to face the day.

I really enjoy the opportunity to face difficult challenges and resolve those challenges. The passion to solve these puzzles with data doesn’t end there. I also really do enjoy the opportunity to learn which brings up two other challenges that help me learn: speaking and writing.

By putting myself out there regularly to speak and write, I am becoming a better technical person. I am becoming better equipped to solve many of the puzzles I face. Those are great benefits. That said, I don’t feel I could get out there and talk about something about which I wasn’t passionate. I have learned to become passionate about writing and speaking – though I still have plenty of room for improvement (just as I do in my quest to become a good DBA).

TSQL2sDay150x150Wrapping it Up

I really do enjoy the challenges I get to face on a frequent basis in the world of data. This is the big “WHY” for me to continue my progress in this career.

Find something you are passionate about and strive to envelop your career with as many opportunities to do that thing. If that means accepting some less wanted tasks in order to do more of the thing you love, it could very well be worth it!

Automating like an Enterprise DBA

Comments: 1 Comment
Published on: January 8, 2019

TSQL Tuesday

The second Tuesday of the month comes to us a little early this month. That means it is time again for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

Automation

The theme as chosen by Garry Bargsley (b | t) is about automation. Specifically, Garry has provided two requirements about automation for this month. As is always, there is leeway in a post that participates in TSQL Tuesday.

One of the things that should seem very commonplace to a data professional is the effort to become a lazy DBA. A lazy DBA is not a bad thing. It just means the DBA works hard to automate the repetitive mundane tasks that may be tedious and/or time consuming. Time can always be better spent somewhere else, right?

If you are lacking in any ideas for what can be automated, here are a few TSQL Tuesday roundups from when we have talked about automation previously (yes it is a hot topic – ALWAYS!).

  1. August 2010 – Beach Time – what do you automate to earn beach time?
  2. February 2011 – Automation in SQL Server – Give your best tricks for making your life easier through automation.
  3. January 2014 – Automation – How much of it is the same?
  4. September 2015 – The Enterprise – How does one manage an enterprise of databases?
  5. September 2017 – PowerShell Automation – Find something and automate it.

In the past, I have written about automation a few times. Some of my favorites are automated restores, automation in the cloud, and my poor mans automated audit.

I automate many processes and have automated loads of tasks over the years. You see, automation means I can spend more time doing other tasks that require more time, more thought/concentration, more effort, and frankly more interest. So what have I automated recently that may be different from what I have previously written? This time, I have something that may seem utterly trivial but in the end it is rather tedious and time consuming to manually check over and over and over.

PowerShell

When I automate a task, I generally will try to use the tool that seems the most appropriate for the task: windows scheduler, SQL Agent, TSQL, SSIS, VB, C#  and now I am trying to add PoSh to that list. I don’t believe there is a one size fits all automation tool. Sometimes, one has to be flexible enough to adapt other technologies into the tool-belt.

I have been working with a client to check their servers for SQL Server version, SSMS version, PoSH version and so on. All of this to try and get the appropriate updates installed on the server. Believe it or not, many of their servers were still running PoSH v2 and didn’t have any Service Packs installed for their database servers. OUCH!

Touching every single server (even if it is only 10 servers) is far too tedious and error prone. So, I spent a little time klooging together with my neanderthal level PoSH skills and found a way to retrieve various pieces of information from the servers and then store those data points in a database so I could report on the entire environment easily with TSQL. In addition, I could show change history and find approximately (at worst) when an update was installed.

Of all of the things I scripted to start tracking, the one I want to share this time can also be used to audit security on each of the database servers. I use the following script to audit the localadmins on each of the database servers in the enterprise. In order to trap each local admin on the server, I also recurse through domain groups to find all users of a group to find everybody that may have access. Here is a version of the script that is similar to what I use now.

Could I improve on the efficiency of this script? Most definitely I believe there is room for improvement. Remember, I am very novice at my PoSH skills. Scripting issues aside, it works and basically fetches a list of servers from a database, then iterates through each of those servers to fetch the complete list of local admins on each of the servers. Then the script writes out the complete list of admins for each server back to my database so I can generate a history of changes to the admins or report on who has admin access on the server.

For anybody that has admin access to a database server, the permission path (nested group path) is recorded in hierarchical form separated by the carrot character (^). Using this script, I have been able to provide a report to domain admins to clean out various unwanted individuals from access that was not intended or necessary.

Wrapping it Up

TSQL2sDay150x150Automation is an essential tool for every data professional. Wait, no, that’s not accurate. Automation is an essential tool in all facets of IT. Automation is a definitive method to work more efficiently and offload some of the mundane repetitive tasks that consume too much time.

Even if the task is not trivial but needs to be repeated and done so without error, the best tool is automation. Performing tasks over and over naturally leads to higher risk of error. The way to minimize that risk is to perform the task via some automation script or routine.

T-SQL Tuesday Participation Over the Years

Comments: No Comments
Published on: December 19, 2018

Loads of TSQL Tuesdays

TSQL2sDay150x150

Years ago the TSQL Tuesday party was started by Adam Machanic (b|t). The premise of the monthly event is to get people a topic each month to try and ease some of the difficulty with writing (figuring out what to write) and to get more involved in the community. The party started in December 2009 and is now embarking on the 10th year. The party is running strong and will likely continue for many years to come.

I have personally participated in quite a large number of these events. I am far from a perfect participation record like Rob Farley (b | t) but I do participate when I can. Every now and again I think about what I might have participated in or what I have written in the past for a TSQL Tuesday. This post will serve as a table of contents for all of those articles. This is to help make it easier to find for me (as well as give me a chance to review and fix problems from really old posts). This post will provide a quick synopsis of my article (if one exists) along with topic and links to original invite and roundups for the monthly party.

T-SQL Tuesday #001: Date/Time Tricks (Dec. 2009)

Invite and roundup

My Article: N/A

T-SQL Tuesday #002: A Puzzling Situation (January 2010)

Invite and roundup

My Article: TSQL Tuesday – But I was late

This was the first time I participated in TSQL Tuesday. I was very new to the blogging concept – and it shows. The puzzling problem I encountered in the article was a fun little job that kept giving different results than running the same code from SSMS. Check it out!

T-SQL Tuesday #003: RELATIONSHIPS (February 2010)

Invitation and summary

My Article: Relationships

I covered all sorts of different relationships that affect SQL Server from work, to marital, to table relationships in a database. It all ties together in requiring an individual to constantly tune the different types of relationships.

T-SQL Tuesday #004: IO (March 2010)

Invitation and roundup.

My Article: IO IO IO

What if we could create a numbers table without IO?  What if we could perform several different kinds of queries without IO?  Itzik Ben Gan proposed a cascading CTE solution that does this sort of thing. This article shows that method in use to help improve certain IO conditions.

T-SQL Tuesday #005: Reporting (April 2010)

Invitation and roundup.

My ArticleIP and Default Trace…T-SQL Tuesday #005

Having been introduced to a requirement to report on IP addresses of connections, I dive into a solution that will help show hostname, ip address and a few more tidbits.

When it is necessary to provide reports on activity occurring on the server, it pays to do a little prep work.  Be Prepared.  It is not an easy task to be able to go back in time and report on data that isn’t captured.  The little prep work that one may need to do is well worth the effort in the end.

My 2nd ArticleBLOB Report T-SQL Tuesday #005- Reporting

Learning how to decrypt the individual object sizes broken down into type and filegroup. Then report on the data that was retrieved from the DMOs.

T-SQL Tuesday #006: “What About BLOB?” (May 2010)

Invitation and roundup.

My ArticleT-SQL Tuesday #006: A Blobbing We Will Go

In the DMVs for SQL 2005 and SQL 2008 there is more than one place you can find the information about the size of your LOB data?  And it goes without saying that there is more than one way to find information about LOBs in your database.

T-SQL Tuesday #007 – Summertime in the SQL (June 2010)

Invitation and roundup.

My Article: Data Compression

I see database compression, as offered with SQL 2008, to be more like these file compression utilities than DriveSpace.  Data compression in SQL 2008 is not an all or none implementation.  You get to pick and choose what gets compressed.  That is a big time bonus for me.

T-SQL Tuesday #008: Gettin’ Schooled

Invitation and roundup.

My ArticleGettin’ Skewled

I am learning that learning is not just formalized education in a classroom or in specific settings.  There are things to be learned from all aspects of life.  This can be learned if only a little observation is used.

T-SQL Tuesday #009: Beach Time (August 2010)

Invitation and roundup.

I hosted this particular event.

My ArticleR & R

I find it completely useless to go on vacation if I am going to be checking email or project statuses every 10 minutes.  There is no rest or relaxation in doing those things while I am supposed to be doing something else.  Vacation should be fun and enjoyable.  Thus, if I am to enjoy vacation, I need to do a few extra things in the office prior to leaving.

T-SQL Tuesday #010 – Indexes (September 2010)

Invitation and roundup.

My ArticleTSQL Tuesday Indexes and Blobs

How does one find what columns were LOB columns in the database.  I knew I had some past blog posts about various aspects of LOBs, but I had never broken it down to find the columns in the LOB.  Even better was that I wanted to know what columns were in what index that were also a BLOB.

T-SQL Tuesday #011 – Misconceptions in SQL Server (October 2010)

Invitation and roundup.

My ArticleA Haunting TSQL Tuesday Tale

I chose the myth that truncate is unrecoverable. Check it out and see how a truncate is most certainly capable of a rollback or being recoverable.

T-SQL Tuesday #012 – Why are DBA skills necessary? (November 2010)

Invitation and roundup.

My ArticleT-SQL Tuesday #012 – Skills

As a DBA, we occasionally have the opportunity of attracting a new client or a new job or a new database.  It seems that more often than not, some of the skills requisite (at least they should be) of owning a database are missing. (Nunchuku skills could come in handy from time to time too!)

T-SQL Tuesday #13 – What the Business Says Is Not What the Business Wants (December 2010)

Invitation and roundup.

My ArticleT-SQL Tuesday #13 – Business Requirements

I think a common area that is easily overlooked when it comes to requirements and interpretation of requirements is report creation.  A common problem is that there are no defined or written requirements for the creation of a report.

T-SQL Tuesday #014 – RESOLUTIONS (January 2011)

Invitation and roundup.

My ArticleTSQL Tuesday 14: Committed

This month was all about resolutions and goals. My list of goals were pretty good and entirely profession based.

T-SQL Tuesday #015 – Automation in SQL Server (February 2011)

Invitation and roundup.

My ArticleT-SQL Tuesday #15 DBA Automaton

I shared a script that will load a sample of data from every table in every database for every column and give you the length of the pertinent columns (I have excluded obvious columns such as numeric types and certain LOB types).

T-SQL Tuesday #016 – Aggregate Functions (March 2011)

Invitation and roundup.

My ArticleT-SQL Tuesday #016: Aggregates and Statistics

Super geeky article on stats, quartiles and the like. For my data analysis and trending, I wanted to find a simple distribution across quartiles.

T-SQL Tuesday #017 – APPLY Knowledge (April 2011)

Invitation and roundup – unavailable on origin site.

My ArticleT-SQL Tuesday #17 – APPLY Knowledge

In this article I take an unexpected twist in my application of the APPLY operator. In that twist, I show a quick and simple script to grab role members.

T-SQL Tuesday #018 – CTEs (May 2011)

Invitation and roundup.

My ArticleT-SQL Tuesday #18 – CTEs

I shared a rather complex series of CTEs that are used in the same way a hierarchy and ancestry tree would be generated – except for Foreign Key relationships. That script was a load of fun.

T-SQL Tuesday #019 – Disasters and Recovery (June 2011)

Invitation and roundup.

My ArticleT-SQL Tuesday #19 – Disasters & Recovery

Who needs a single disaster when you can enjoy multiple disasters in a single sitting? I share three different types of disasters. I am sure many have had experiences with all three and probably many more types of disasters.

T-SQL Tuesday #020 – T-SQL Best Practices (July 2011)

Invitation and roundup.

My Article: N/A

I missed the announcement or something like that.

T-SQL Tuesday #021 – A Day Late and Totally Full Of It. (Aug. 2011)

Invitation and roundup.

My ArticleTSQL Tuesday 21 – FAIL FAIL FAIL

I don’t hide much as I share my feelings about crap code and then show my own crap code followed by a little bit of explanation on an improved version of the same code.

T-SQL Tuesday #022 – Data Presentation (September 2011)

Invitation and roundup

My ArticleT-SQL Tuesday #22 – Data Presentation

I chose to touch on several aspects of data presentation – Performance, Accuracy, Display, and Business Requirements.

T-SQL Tuesday #023 – Joins (October 2011)

Invitation and roundup.

My Article: N/A

D’oh – missed another one in 2011.

T-SQL Tuesday #024 – Prox ‘n’ Funx (November 2011)

Invitation and roundup.

My ArticleT-SQL Tuesday #024: Prox ‘n’ Funx

Big takeaway from this month was the value of a very good string splitter.

T-SQL Tuesday #025 – Invitation to Share Your Tricks (Dec. 2011)

Invitation and roundup.

My ArticleT-SQL Tuesday #025 – Holiday Gifts

In the spirit of the Holidays, I wanted 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.

T-SQL Tuesday #026 – Second Chances (January 2012)

Invitation and roundup. (Original site toasted).

My ArticleTSQL Tuesday #26 or #23 – Identity Crisis

Having missing TSQLTuesday 23 and the topic being about Second Chances, I chose to write about JOINs and sort of fulfilling the requirements for both TSQL Tuesdays in one shot.

T-SQL Tuesday #027 – Invitation to The Big Data Valentine’s Edition (February 2012)

Invitation and roundup.

My Article: N/A

After that second chance I blew it the very next month. Luckily I have a good reason – Valentines Day!

T-SQL Tuesday #028 – Jack of All Trades or Master of None (March 2012)

Invitation and no roundup.

My ArticleT-SQL Tuesday #028 – Jack of All Trades, Master of None?

This one brings up bad memories of when as the DBA it was also required to be the janitor.

 

More updates coming soon!

What is T-SQL Tuesday?

T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Steve Jones via the tsqltuesday website – here.

How to Participate

  • Your post must be published between 00:00 GMT Tuesday and 00:00 GMT Wednesday.
  • Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
  • Trackbacks should work. But, please do add a link to your post in the comments section below so everyone can see your work.
  • Tweet about your post using the hash tag #TSQL2sDay.
«page 1 of 7

Calendar
April 2020
M T W T F S S
« Mar    
 12345
6789101112
13141516171819
20212223242526
27282930  

Welcome , today is Wednesday, April 1, 2020