Tag: tsqltuesday

T-SQL Tuesday #108 – Logic Apps & Cognitive Services

T-SQL Tuesday #108 – Logic Apps & Cognitive Services

For this 108th edition of T-SQL Tuesday, Malathi Mahadevan (b|t) has come up with a wonderful topic – non SQL Server technologies. As the Microsoft data platform continues to expand, our jobs as data professionals are requiring us to learn and embrace technologies outside of the traditional relational database platform. After spending last week at PASS Summit 2018 and being reminded again about the amazing diversity of the SQL Server/Microsoft Data Platform ecosystem, this topic seems especially well-timed. Nicely done, Mala!

The non SQL Server technologies that I’ve selected for this post are the pair of technologies that have been oddly involved in my life for the last year or so: Azure Logic Apps and the Cognitive Services API. I was fortunate enough to be selected to speak about these topics at PASS Summit this year and it’s been a strange journey from, in a little over a year, never having heard of Logic Apps and Cognitive Services to creating training for them and speaking in the community about them. Long story short, I heard a Men in Blazers podcast last October where they jokingly mused about ranking English Premier League teams by the supporters’ feelings rather than actual results on the pitch. I had recently read some logic apps blogs from Brad Ball (b|t) and thought I could take what I learned from those and turn it into the “mood table” that was discussed on the pod. If you’re interested in the technical details of what I did, click here and read my blog from last year about how I did it. That post also links to a deep dive post with even more detail.

That post, and the mood table’s weekly appearance on the podcast for months, set the table for me to begin presenting a logic app and Cognitive Services session at PASS community event and non-PASS technical meetups as well. That drove me to better understand the power of logic apps and what else I can do with them with or without wiring in Cognitive Services as well. I submitted that session for this year’s PASS Summit and it was selected so I dove into these topics even further in order to build a robust 75-minute session.

The story behind my initial effort for the podcast and how that turned into a session brings us to today. What are my plans for learning more about this? I’ve partnered with a company on some logic apps training and will release that information when the course is released. There is a possibility of building onto that course with a subsequent advance course. Creating that training content forced me to really dig into some concepts within logic apps and cognitive services that I was not very familiar with from just my “playing” with these technologies for the mood table. It’s also really opened my eyes to just how powerful logic apps are for coordinating workflows for real companies with real data flows (not silly soccer-related podcasts :-)). I’m looking forward to integrating Azure Logic Apps and Cognitive Services with customer work in 2019 so customers can see how useful Logic Apps can be when moving and transforming their data. Cognitive Services can offer huge value to customer-facing departments such as customer support and marketing. I can’t wait to begin talking more about these technologies with my customers and learning more about them as we work together.

T-SQL Tuesday #83: Can’t We All Just Get Along?

When the topic for this month’s T-SQL Tuesday was announced here, it really got the wheels turning in my mind. So, in the spirit of this month’s topic, here is my spin on the official topic: “In the 10+ years I have been a database professional, we’re still dealing with a lack of communication between DBAs and developers as well as DBAs and operations staff”. In other words, can’t we all just get along?

tsql2sday-150x150

Let us go ahead and separate this into two sub-topics: DBAs not talking to developers (and vice-versa) as well as DBAs not talking to operations staff (and vice-versa). While the consequences of each communication gap may be different, in my mind the solution is the same in both cases.

First, both when I was an operations DBA and now that I am a data platform consultant, I would consistently talk to DBAs who say “I don’t need to understand the applications running on my server(s), I just need to keep the lights on.” While that may technically be true by your job description, that will cause two problems, one for that type of DBA and one for the team with which they’re interacting. The problem for the team is that any performance and/or design guidance the team requests from that DBA will be, at best, misguided or, at worst, destructive. Blindly offering generic database advise or approving index or code changes from the team without the DBA(s) having a thorough understanding of the impacts truly does a disservice to the team being supported and will likely eventually lead to the team turning to Google/Bing for “expert” advice rather than their internal DBA staff. The problem for the DBA can be much more significant – if all you’re doing is keeping the lights on you are A) replaceable as you do not have much specialized knowledge and B) replaceable because your job may be automated or outsourced.

Second, I still talk to some database professionals who proudly declare their agnosticism regarding the servers their databases reside on, the network rails their data rides, and the storage their data uses. While I understand this decision, especially given the always-increasing amount of things “SQL Server people” are supposed to know, my experience in a lot of middle of the night phone calls over the years is that “I don’t know” is not a popular answer when it’s 3 AM, there’s something wrong with “the database”, and the company’s creeping ever closer to violating an SLA if the issue isn’t resolved ASAP.

I do understand our frustration, as a data professional, when a non-technical person accuses “the database” of being broken when what’s actually down is the SAN, the network, the battery backup on the UPS during a power outage, etc. That said, this is where having some basic knowledge of the system you support can go a long ways. If your answer to the manager called into the fire alarm call and demanding a response is “I don’t know” that will not reflect well on you. If your answer is “there doesn’t appear to be a SQL Server issue but let’s look at <storage, network, etc.> because I see X in my monitoring tools” that will not only bring about a faster resolution of the issue, most likely, but it will show those up the chain from you that you are a professional who thoroughly understands the environment you’re supporting.

The solution to all these issues is simply this: communicate. In a very real sense, the developers, the DBAs, and the network/storage/operations folks are all in this together because if the application you’re coding/refining/supporting runs into significant issues it can put everybody’s career at risk. Even if management doesn’t realize this and doesn’t facilitate the communication, make it happen for yourself and your colleagues. Break bread with them, have a beer, setup inter-departmental lunch and learns, or just ask them what’s new in their field or if they got any shiny new toys at work. I’ve done all of these things and seen them all work. Even if all else fails, maybe try this after receiving appropriate responses from all parties:

grouphug

Feel free to contact me on Twitter with any follow-up questions or comments. Thanks for reading!

T-SQL Tuesday #080: Change Always On Endpoint Ports

T-SQL Tuesday #080: Change Always On Endpoint Ports

Thanks to Chris Yates for hosting this month’s T-SQL Tuesday (https://chrisyatessql.wordpress.com/2016/07/06/t-sql-tuesday-080/)! This month’s topic is fairly broad (a SQL-related present so I wanted to put together a quick post whose information definitely would have saved me some time if it had existed a few years ago.

birthday_present

I’ve seen this particular Always On issue occur at two different customers. Both would occasionally have issues where one (or more) of the replicas would fail to restart their communication within the availability group. The error message was usually something like this: The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process. 

In a large, complex corporate environment, the DBAs may not always have permission to run the netstat commands necessary to figure out what application is “stealing” the port. Even if they’re able to figure out which application is causing the port conflict, it may take a fair amount of time to change that application’s behavior.

Since a communication failure within an AG is usually a “hair on fire” kind of event, a quick fix may be desired. The quickest fix I’ve found is to change the port on which the AG endpoint is listening. While the Microsoft help pages on this contain enough information to put together the right script eventually, the first time this happened to me I really would have liked a blog post specific to this issue that gave me the right script to use.

In keeping with this T-SQL Tuesday’s theme, below is my SQL Server present. Please note that I used 5023 as an example but your choice can be flexible depending on what else is consuming ports on your machine. Hopefully this helps somebody (or me if I manage to travel back in time and encounter this same issue):

ALTER ENDPOINT [<endpoint name>]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
GO

ALTER AVAILABILITY GROUP [<AG name>]
MODIFY REPLICA ON ‘<replica name>’ WITH (ENDPOINT_URL = ‘<server name>:5023’)
GO

If this post helped you and you live in the Louisville, KY area, please consider registering for SQL Saturday 531 on August 6, 2016 and attend my Always On session there – you’re guaranteed to get what you pay for since SQL Saturdays are free!

Thanks for reading! This weekend I’m competing in rounds 3 and 4 of the The Hoosier Tire US Formula First Championship Series at Mid-Ohio Sports Car Course so I’ll be interrupting the SQL Server stuff usually posted here with some news about my first event of the 2016 racing season.