Category: SQL

Home / Category: SQL

Surprise!! You learned something you didn’t expect!

December 12, 2020 | SQL, T-SQL Tuesday | No Comments

So for this T-SQL Tuesday, Lisa (@LisaGB_sql) asked us to talk about

something else we learned while presenting. When you think of presenting, you would think the entire process is all about things you already know. I mean, if you didn’t know it, why would you be presenting in the first place. I say that because it is why it took me so long to do my first presentation. There are a lot of things I learned for that first presentation, but what follows is the something I would have never expected.

My first presentation was put together with my good friend Dan White (@crashdan). I was just in my first job as a DBA after being a developer most of my career. We did a presentation on real world use cases for DBA Tools. For those that don’t know what DBA Tools is, it is a community created toolset for SQL written in PowerShell. I have said before, and will continue to say, that it is the way I became a successfully transition to being a DBA. The whole community supports it, and it helps soften the learning curve for most things SQL. While working on the presentation, there were things I learned about standards across the toolset, such as common parameter names, common command names and being able to leverage the output from the commands.

But what I will always remember about the first time we gave the presentation at SQL Saturday is not only a piece of knowledge, but also how I learned it. We were fortunate to have a few friendly faces in the audience, most in the front row (still not sure how I feel about that). What I found out after was that there were multiple published MS MVP’s in the audience. After the presentation, one of those MVP’s, Sarah Dutkiewicz (@sadukie), came up to provide some pointers on the presentation, but also to help teach me a couple things about PowerShell that I did not know. As part of the presentation, one of the things I call out to help make things easier is leveraging profiles within PowerShell. This gives you the ability to write functions that you use often. Things like getting an array of all of the SQL servers in your environment, or your own custom logic functionality. What I did not know is that was only one of a multitude of profiles you can work with. While notepad $Profile will open your current user profile (Current User; Current Host), there are 3 other profile files. You also have Current User; All Hosts, All Users; Current Host and finally, All Users; All Hosts. These different profiles allow for easy distribution of functions across your entire team. As we all know, being able to leverage this kind of standardization is huge. You can read about profiles here, https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_profiles.

There are a couple things that this learning experience helped me with. It reinforced how powerful the #sqlfamily is and dedicated the members of the “family” are to helping everyone continue to grow and increase our skills. It also helped get over the stage freight and “imposter” syndrome that I had. Having someone who is an MVP on the core of the topic you are presenting on tell you that you did a good job, and here are some things to help is HUGE. Sarah treated me like an equal, even though her knowledge was way more than I had on the topic. What I learned while preparing and giving the presentation is not only do you have the opportunity to share your unique viewpoint on your topic, but you also have the chance to learn even more, and not just about your topic.

Read More
By Josh

MS SQL Data Community. What to do now?

December 11, 2020 | SQL | No Comments

If you follow @PASS or the SQL community behind it on Twitter, you know something is going on. There have been 3 board members that have resigned in a short time span. I am fortunate to know 1 of them more than just a name recognized in the community, and that gave me cause for concern. PASS has helped my career in a lot of ways. More importantly though, it has helped me make life long friends. They are friends that I enjoy talking about things other than SQL. Talking about their fur children, or watching the details of their trail bike riding, or just hearing their sigh inducing jokes (I’m looking at you @BuckWoodyMSFT). In this day and age, being there to support each other as the daily tribulations become difficult.

As I’ve wrote before, I’ve tried to follow or be active in the community since 2012. Initially, the thought of a future without PASS scared me. This is an organization that has given me endless opportunities for learning. And more importantly, it has given me an opportunity to build my professional network (in addition to my personal as I mentioned above). That may sound a little selfish, but in the IT world (and other industries), your network is part of your personal “brand”. When I interviewed during my current job change, one of the question I asked was what is the policy about reimbursement for training. I asked that question with PASS Summit in mind. The amount of practical knowledge I picked up at Summit was immeasurable. If PASS goes away, where am I going to get that quality education given by people with real world examples. And how am I going to share what I know to help others. PASS helped me get to a point in my career that I could give back and help others. By building up others, we build ourselves. See my most recent blog post about how while I was giving back, someone helped me expand my own knowledge. What am I going to do?!?!?

Then I took a step back and started watching commentary from those in the community, some who have been in the community for a long time. But there is a constant theme starting to materialize. That theme is that no matter what the future holds, the community will weather this potential change. It may look different than it did, and it may not be the big powerhouse that is was. But the one constant is that people will continue to support this community. The hashtag #sqlfamily is used often, but it is a very accurate way to describe how some of us feel about this community. The fact that I can text someone and ask a question about a performance issue just like I would ask a family member for a family recipe is something that still boggles my mind. All of this gives me hope.

No matter what happens, I will be there to support the community. If that means supporting the community without an central organization, so be it. But I have faith that this community will continue strong.

Read More
By Josh

Install SQL Server…FAST!!

September 7, 2020 | SQL, T-SQL Tuesday | 2 Comments

Automation! It is the magic behind all the cool stuff we see lately. It could be a timer that turns our front porch light on at sunset. It could be a motion sensor that turns a light on when you walk in a room. Or it could be a routine that runs when you ask your Amazon Echo or Google Home to turn on the TV. All of those examples are cool (feel free to ask some of my friends who have heard me ramble about it), but this article is about a way cooler automation, SQL Server installs. This month’s TSQL Tuesday is all about automations that made your life easier.

A little over a year ago, I made the jump from primarily being a developer, which I have been most of my career, to primarily being a DBA. The first task my new boss asked me to do is create a step by step set of instructions for standing up a new SQL server. One of the reasons I have the job I currently do is there wasn’t a person dedicated to handling the data environment. This had created a hodgepodge of different configurations for SQL servers. Things like different drive letters depending on who setup the server. The directories that were setup for storing the data, transaction logs or tempdb. That was just on the outside. Maximum memory settings, MAXDOP setting or cost threshold were not following a standard either. Then some of the servers had Adam Machanic’s(t) sp_whoisactive, or Brent Ozar’s(t) First Responder Kit (FRK) and some didn’t. To be clear, this came to be over years and multiple people trying to keep everything going.

So when I started working on this set of instructions, the first thing I did was go look at what is available from dbatools. This is and continues to be my go to for automating most anything in SQL. dbatools not only helps me when running one off tasks like backing up and restoring, it also helps me collect metrics on the servers. I have 3 different jobs that collect and store server parameters, disk space usage and database usage. I will be putting out a set of blog posts with these scripts attached.

So now to the cool stuff, installing SQL Server in less than 20 minutes.

There are 3 total files that are needed for this magic. The first and one you should already be aware of is the ConfigurationFile.ini. This has been helping DBA’s for years to apply the same settings to SQL installs. I have a configuration file for all the versions of SQL in our environment (there are still 2012 servers I need to stand up for dev environments). The configuration file holds settings like directory paths, authentication mode, update settings and more depending on the version of SQL you are installing. More recent versions are including best practices settings such as automatic setting for max memory.

The second file is a PowerShell script that is run on the server you are doing the install from. This script handles a couple different steps. It opens the standard ports in Windows Firewall that are needed for SQL Server. It verifies if the install media is available (or mounted) and stops the script with a warning if not. And finally, it starts the install with some standard parameters, such as the config file, the SA password and the password for the SQL service account. Once the setup is done, I restart the server knowing that I don’t have to do anymore of the standard configs.

The third and final file can be run from any system that can connect to your SQL Server. This is another PowerShell script that will finalize the standardization. This where I install our standard tools (sp_whoisactive, FRK, Ola Hallengren’s(t) Maintenance Solution). I install all of these using DBA Tools as this gives me a current version. The rest of the scripts adds the rest of our customizations. An example of this is disabling the backup component of Ola’s maintenance solution. We do this because our standard backups are handled through Veeam, however we do have scenarios that require SQL native backups, and as you know, Ola’s solution knocks this out of the park. Other examples include setting up our operators for alerts, our mail profiles, MAXDOP, parallel cost threshold, etc. The last step of the script inserts a record into our server inventory table. This is done so that the nightly jobs know to retrieve information from this new server.

In the end, using a few different tools all pulled together leveraging the power of PowerShell (see what I did there 😊 ), there is limitless potential on the amount of automations we can do. These automations will allow us the time to work on the more advanced (and hopefully more fun) projects. Please reach out if you would like a copy of the scripts mentioned in this blog.

Read More
By Josh

PASS. Thoughts from someone who is just getting involved.

August 25, 2020 | SQL | No Comments

Back in 2012, I attended my first SQL Saturday. I had been working with SQL since 7.0 but didn’t really know about the community. I had just moved back to my hometown Philly from Cincinnati and was determined to get more involved. I volunteered and I ended up just directing people. And then a I had a hallway conversation with someone, and had a break through on a different way to think about SSIS (looking back, not sure how I hadn’t thought about SSIS as RBAR by design). I went to a few SJSSUG meetings that first year. Met Joey D’Antoni(t) before he was the superstar he is now :-).

Then life happened and I couldn’t be as involved as much as I wanted to due to obligations. Then I was able to attend my first Summit in 2014. I had been fighting for it because of how everyone hyped it up. My boss at the time came with me as we were scoping out BI solutions. We were also looking at attempting to implement Service Broker, so we divided and conquered. He went to all the Power BI sessions and I went to the dev track sessions and I attended Allen White’s Service Broker Session (I blame Allen(t) for my love of this underrated feature and Denny(t) and Jonathan(t) for helping me fill where I was confused). That first Summit, I didn’t do the after events, and I to this day regret it. My boss however became a proponent to send other staff after his experience.

I was fortunate to go back to Summit in 2015 with 2 other co-workers, and I vowed to change my experience in regards to the after events. Thanks to someone we met the first night we were there, I attended a lot more events and networked more than I could ever have imagined (Thanks again Justin(t)).

I was fortunate at the time to be working with a vendor who their DBA staff was highly involved in the community, and that just helped change my determination even more. They helped me realize how much I could give back to the community. They are the reason I have presented a session created with someone who has become a close friend at not 1, but 2 SQL Saturdays. That DBA team also helped me realize something that I feel is now more important than ever.

If it was not for PASS, I would not be where I am in not only my career, but my life as well. I have gained a like minded group of friends that when we aren’t talking about SQL, we are talking about life in general. These are people that are always willing to share and help teach others to help make everyone better. These are people that I would do anything I can to help them. Not because of what they did for me or the community, but because of the community, I was able to see them more than just someone who knows SQL.

I don’t know all the in’s and out’s of the behind the scenes. What I do know is that the SQL community will continue because of the people in the community. I also know that as great as the community will be, having an organization for people to find and help get them involved will be even better. And I will do whatever I can to help, whatever the level of volunteering I need to do.

Read More
By Josh