DallasDBAs.com

SQL Server Database Consulting

Kevin3NF

IRL #3: SQL Server HealthCheck

by Leave a Comment

IRL #3 – Slow Server

Problem:

I was recently approached by a firm outside the US for some help with his SQL Server which was “running slow.”

Background:

They don’t have a DBA, and I didn’t support international customers at the time so I told him I would donate 30 minutes of my time out of pure curiosity.

Action Taken:

I sent my HealthCheck tool along with instructions to run it once the server has been up at least 7 days.

Results:

After reviewing the gathered info for 30 minutes, these are the results I sent back as recommendations to look into:

  • Backups and Data on the same drive
  • Other databases never backed up
  • No CheckDB since 2011, if ever
  • Max Server Memory: 24,000 MB of 32GB installed
    • Memory used –3.4GB
  • Windows Server 2008
  • Auto-Shrink enabled on prod databases
  • No alerts when bad things happen
  • No Operators
  • SQL Agent offline
  • Balanced Power Plan
  • Priority Boost enabled
  • TempDB on C drive, only 1 data file
  • 10 years of backup history
  • PLE – 292 seconds
  • 32-bit SQL Server 2014 (unsupported build)
  • I didn’t even get back any index information, as those queries timed out.

Solution:

  • Immediate:
    • Upgrade to 64-bit O/S (assuming 32 bit here) and SQL Server
    • Patch to supported build
    • Turn off Priority boost
    • Set up proper alerts and maintenance
    • High performance power plan
  • Next
    • Address everything else

Lessons:

Start at the outside and work your way in.  The SQL version can be the biggest issue.  Start at Hardware and O/S, then Instance settings, then indexing, then queries (in most cases).

All of this took less than 30 minutes to find.  I can do similar (but much more thorough) work for you.

 

Other posts in the IRL – In Real Life series:

https://dallasdbas.com/category/sql/irl/

Thanks for reading!

Kevin3NF

SQL Saturday Dallas 2019 aftermath…

by Leave a Comment

The 2019 event was held June 1 on the amazing UT-Dallas campus in the Naveen Jindal School of Management building, which was a nice upgrade from last years event on the same campus but in a different building.

This is not going to be an exhaustive post of all the great sessions, conversations, etc.  Mostly because I was there as a sponsor again this year so I didn’t get to any sessions.  But, I DID get to talk to a massive number of people that stopped by the table.

First off…a shout out to the North Texas SQL Server User Group board and all of the volunteers that dedicated many, many hours of time and sweat (and homes!) to make this thing happen.  The Dallas event is one of the biggest in the world.  The very unofficial count of attendees I *think* I heard was just over 700, from 1100+ registrations.

A second shout out to our sponsors that made this event possible.  Their dollars made it easy to do all we wanted to for the speakers, volunteers and attendees.  From high quality speaker gifts to cool attendee bags, to a great fajita lunch.  And cookies.  And lots of coffee all day long!

There were 4 fantastic pre-conferences to choose from.  As a full-time DBA I decided to learn something new and went to Adam Saxton‘s Power BI class.

Adam, aka ‘Guy In A Cube’

The other 3 pre-cons were from Brent Ozar (sold out twice), Andy Leonard (b|t) and Chris Hyde (t)…all fantastic teachers and overall nice humans.

After the pre-cons, NTSSUG board member Fernando hosted the traditional Speaker Dinner at his home in North Dallas.  Fernando is a brave, brave man.  The only pic I got was of the Tres Leches cake, already partly demolished:

I love the speaker dinners, as it gives me a chance to hang out with people in a low-key environment.  A stark contrast to trying to catch up at something like PASS Summit, which takes SQL geek craziness to a whole ‘nother level.

Saturday

As a Gold sponsor of this event, I got good placement of my booth, and was close enough to the NTSSUG booth to be able to help out there as well.  Brent was kind enough to come by for some pics between sessions:

Brand new DBA (Day 1 on staff) Jeff Miller (t) and Brent

Brent and the entire Dallas DBAs staff (Including Liz’s elbow)

Another benefit for Gold sponsors is the Lunch Session.  I was able to give my “Your SQL Servers are Mi$ConFiguReDed” session to 35-40 people.  I didn’t do a formal count, and this pic is not wide enough to catch those on the far left and right of this room:

This was a great session, as there was some fantastic audience participation…from multiple people!

Something that is a great way to encourage attendees to stay all day is the end of day sponsor raffle.  Sponsor agree to bring something worth $100 or more…must be present to win.  We had 13 sponsors plus PASS and Microsoft, as well as 8 signed copies of Bob Ward’s book, Pro SQL Server on Linux to give away.  There were TONS of people that stuck around for the raffle:

The Grand prize (provided by Microsoft) was a Surface Go!  All the buckets in the pic are the raffle tickets for the sponsors.  Half of them are not showing.

Once again…this was an amazing event.  Last year was great, this was better.  I can’t wait to see how we up our game for next year!

Thanks for reading!

Kevin3NF

Your Customers Are Ridiculous!

by Leave a Comment

They may also be wonderful.

Or profitable (we hope).

But they are ridiculous.

They expect you to have ZERO downtime and ZERO data loss. That’s like 100%. Seven 9’s is cheaper…

How do I know this about your customers when I don’t even know them?

I know you.

When you log into Twitter, or Facebook, or LinkedIn…you expect all of your posts, tweets, and likes to be there. I’ve seen you freak out when the like count on the last brilliant thing you said went down instead of up.

I do it too ?

You don’t mind a missing post on Facebook? Its not the end of the world? True.

How about a missing paycheck from your bank? One electronic transaction missing vs. a different electronic transaction. Totally different level of freak-out.

Facebook…you go post on Twitter about it. 1st National Bank of YourTown – you’re in the bank president’s office ripping them to shreds and threatening to sue. Because your mortgage payment was missed.

See…you expected no data loss and no downtime from your bank….ever. And you didn’t know that is a crazy expectation.

The 100% expectation is inherent in humans, and its our job as businesses to get as close to it as we can. The more “9s” you need to guarantee, the more expensive it is.

In the tech world we have fancy acronyms for this: RPO and RTO. Recovery Point Objective (data-loss tolerance) and Recovery Time Objective (downtime tolerance). You need to set these numbers for each critical system and put the processes in place to make them happen.

Databases, Web Servers, Firewalls…all of it. They all work together to help keep your customers happy, rather than having them come unglued because you lost their data.

Once you have all that in place…test it.

Comments welcomed and encouraged

Thanks for reading!

Kevin3NF

SQL Saturday Dallas 2019

by Leave a Comment

Dallas DBAs is a Gold Sponsor of SQL Saturday Dallas

June 1, 2019

Richardson, Texas

 

Dallas DBAs is pleased to announce that we are upgrading our sponsorship of our “home” SQL Saturday event from Silver in 2018 to Gold level in 2019!

Why the change?

We believe in the mission, membership and management of the North Texas SQL Server User Group and want to give it as much support as possible.

Also, a Gold Sponsorship allows Lead SQL Server DBA Kevin Hill to present his session during the lunch hour:

“Your SQL Servers are Mi$ConFiguReDed”

Come to this session to learn the 5 or 6 most common mistakes that are made when SQL Server is installed.  While you eat fajitas!

Even if you miss the lunch session to attend a different one, stop by our booth to grab some swag and talk to Kevin, Derek, Kaitlyn, or Jeff.  We don’t do hard sell, but we will have a “SQL Saturday Show Special” to offer you.

Just for fun…here’s a picture from last year:

We will be raffling off something either really cool, or valuable, or both. Make sure you drop your SpeedPass raffle ticket in the jar and stick around all day.  You must be present to win any raffle items from any sponsor.

If you are thinking of coming, make sure to register HERE. If you have already registered and are not able to come, make sure to cancel your registration and open up that spot for someone else. Thanks!

As an added bonus we have convinced Jeff from Ignis Images to come back this year to do event photography and “quick” headshots in front of the SQL Saturday backdrop!  If you want a more professional shot, you need this service!

See you there!

The Dallas DBAs team

 

Recruiter Training – SQL Server

by 3 Comments

Hello Recruiters!

Just to start off I want to say that the whole purpose of this post and video is to help you find the right candidates and ask your clients the right questions. If you don’t know what they want, you can’t effectively ask me or my counterparts if we are a fit for the position.

You can watch the video or skip it…whatever works best for you:

Far too many of the job descriptions we see look like this:

  • SQL Server, SSRS, SSIS, SSAS
  • SQL 2016 – 5 years
  • SQL 2019 – 1 years
  • SQL 2013 – 5 years
  • Azure, AWS
  • Responsible for installation, troubleshooting, Stored Procedures, data warehouse, and Power BI.
  • Must also be able to interface with the development team and provide 24x7x365 support

When we see these, this is what happens in our heads:

  • SQL Server, SSRS, SSIS, SSAS
    • So, everything Microsoft ever released? Nope.
  • SQL 2016 – 5 years
    • Math error 2019-2016 = 3, not 5. Who wrote this?
  • SQL 2019 – 1 years
    • Its not even released yet!!!!
  • SQL 2013 – 5 years
    • No such thing as SQL Server 2013
  • Azure, AWS
    • Both? really? which one are you using?
  • Responsible for installation, troubleshooting, Stored Procedures, data warehouse, and Power BI.
    • Again…everything under the sun and you want expert level.
  • Must also be able to work with the development team and provide 24x7x365 support
    • Wait…developer, development DBA or Prod DBA?

We call these the “Kitchen Sink” job descriptions…and we assume they were written by an HR intern.

Please just go to the client and get the top 5 skills in order of priority.

So…to make all of the above easier I offer you this info, which I will update on the blog post from time to time, but not the video. Please share with your peers:

SQL Server has many different facets to it, and finding someone that knows all of them very deeply is a true unicorn hunt. I know exactly one person that can do all of these well, and he charges almost $500/hr.

Basic terminology:

SQL (S-Q-L) is a language for querying data from a relational database. Invented in 1974 and still used everyday.

SQL Server (pronounced Sequel Server) is Micro soft’s database product, just like Oracle database comes from Oracle. Both do the same thing, but in different ways. Other vendors/products are MySQL, DB2, Postgre SQL (or just Postgres).

MongoDB, NoSQL and others are a different type of database

Azure and AWS are not databases…they are cloud offerings from Microsoft and Amazon respectively, that allow you to do cool things on someone elses servers…databases, applications, website hosting, etc.

Within the SQL Server world, there are several divisions:

Developers: this group generally writes code within SQL Server, designs and creates databases, etc. This might also encompass Report Writers using SQL Server Reporting Services (SSRS), and maybe even PowerBI.

    • T-SQL – Microsoft’s implementation of the SQL language
    • PL/SQL – Oracle’s implementation of SQL

Database Admins: mostly responsible for installs, configuration, maintenance, security and performance of the SQL installation itself. There is some cross-over between Developers and Admins, and many DBAs started as developers and migrated (myself included). It is very possible to find an expert Developer that also have awesome DBA skills. Expect to pay top dollar.

    • OLTP – Online transaction processing (you buy a thing from Amazon, it goes in this db)
    • HA – High Availability
    • DR – Disaster Recover
    • Always On – PLEASE ask your client if they mean AlwaysOn Failover Instance or AlwaysOn Availability Groups. 2 different things.

Data Warehouse/BI: Anything related to aggregation and reporting on the data. There could be some Architect and Developer here, as well as SSRS, Tableau and PowerBI

    • ETL – Extract, Transform, Load
    • SSIS – Microsoft’s ETL tool
    • SSAS – SQL Server Analysis Services

Data Architect: This is a higher level position that is responsible for the overall data strategy for a product, division or company. The architect might be involved in decisions regarding vendor selection, cloud choice, hardware, database design, etc. They may also have some developer and admin duties. A good Data Architect will have examples on their resume and will be able to talk at length about migration strategies, differences between cloud providers, security issues and hardware.

That’s it for now!  Comments are encouraged…negative ones will not pass moderation!

Thanks for reading!

Kevin3NF

Pluralsight: Getting Started With Your First SQL Server Instance

by Leave a Comment

Almost a full year after I first thought I might have something relevant to contribute, my first Pluralsight course has been published!

Its called “Getting Started With Your First SQL Server Instance” and is targeted to non-DBAs that want to know more about SQL Server Administration, or just want to understand SQL Server beyond the random Google Search when something breaks.

I’m hoping this is just the first of many “Getting Started…” courses that take SQL Server all the way down to entry-level training so people can learn, then test, then do as they go.

The trailer for the course:

I want to thank Chris and Derek at Pluralsight for their guidance along the way…and a huge shoutout to Eugene Meidinger (b|t) for his valuable input before, during and after the course creation.

Thanks for reading!

Kevin3NF

Home Blog About Privacy Policy