Kevin3NF

Why SQL Summit?

October 26, 2016 by Kevin3NF Leave a Comment

Why am I attending the PASS Summit?

As I type this, I’m on AA flight 439, seat 8A hurtling through the sky hundreds of miles an hour in a pressurized metal tube. Tight quarters in here, but it’s a window seat that I picked…so still a win. 4 hour flight, direct from DFW to Seattle.

As a contractor, I don’t get paid for the three and a half days I’m using for Summit and travel. I also paid my own way for Summit registration, flight, hotel, shuttles, parking, food, entertainment…even a couple of decks of cards for Thursday’s game night should there not be enough. Oh…and a kilt. Because Grant Fritchey and more importantly the Women In Tech cause.

So again…why shell out the cash?

Because I am 48 years old. If I don’t keep fresh on the new technologies that makes me a dinosaur, relegated to taking care of the old legacy SQL Server that nobody dares to touch. Its SQL 2000 now…but SQL 2016 will also be ancient someday. I expect to be working until 60. Probably more.

If I don’t attend events like this and learn, the new technologies will go flying past me and I’ll have to chase things down on the fly (like I’m doing now with PoSH). I have a bad hip…not allowed to run!

If I don’t learn to exploit the goodness that is the cloud (which I am currently flying in) or Hekaton or the underpinnings of virtualization…I’m toast.

I’m not worried about some hotshot junior DBA taking my job. I want them to try. I’ll even help them through mentoring or team leadership. I have 18 years in, and that puts me at the top of a lot of candidate stacks.

As long as I keep up.

Why Summit?

There are webinars, SQL Saturdays, MVC classes, Twitter, blogs galore and hundreds of other places to learn things that are all a whole lot cheaper.

BUT…nowhere else can you gather all of that in one place. Add in more choices of learning such as BI, Professional development and SQL Development (I’m a pure admin)…and it gets even better.

Even more…when the best of the best are crawling all over each other to pay their own way to come and present at this event, and learn from each other…you know it’s the number one SQL training event in the world.

Add in the networking opportunities! Between sessions, at meals, after parties, community zones, luncheons, breakfasts…if you can’t make a friend and meet people here you might want to reach out of your comfort zone and say hello to the person next to you. But if not…that’s ok too. I’m an introvert as well, so I get it.

I went to Summit 2008 and suffered from information overload. Now that I have recovered, its time for a return visit. The investment dollars pale in comparison to the future dollars in income that will likely grow organically out of being here.

I’ve been like a kid waiting for Christmas the last few days. Figuratively jumping up and down as the time to board got closer (remember…bad hip…no literal jumping). My teammates back at the office all say hi! And they are glad to be rid of me for a few days I suspect.

So as I hurtle along, playing armrest games with 8B, I’m excited. On Saturday I expect to be exhausted but happy.

Why are you at Summit? Why didn’t you come? Can you come next year?

Comments encouraged below!

Kevin3NF

The OnPurpose DBA

My Summit 2016 Schedule

October 24, 2016 by Kevin3NF Leave a Comment

Just thought I would toss my tentative schedule up here for this week’s PASS Summit in Seattle. Are you hitting any of the same sessions as me? After hours activities? Let me know in the comments or Twitter!

I’ve got at least 2 sessions for every time slot. When I hit information overload (and I will) I’ll probably be chilling in the Community Zone.

No formal dinner plans any night…I’m a meat-n-potatoes type if you want to join me.

PASS Summit 2016 Schedule:

Tuesday, October 25, 2016
============================
Arrive around 6pm, check in, find someone to eat with!

Wednesday, October 26, 2016
============================
10:15 AM – 11:30 AMRoom: 618-620
[PD-200] Lighten Up: Soft Skills for Hardcore Techies

10:15 AM – 11:30 AMRoom: TCC Tahoma 3-4
[DBA-102] Powershell Jumpstart for SQL Server DBAs

1:30 PM – 2:45 PMRoom: 401
[DBA-217-PR] Architecting SQL Server Environments with Containers for Superior HA & Cost Savings

1:30 PM – 2:45 PMRoom: 618-620
[LT-103] Lightning Talks 103

3:15 PM – 4:30 PMRoom: 6E
[DBA-403] The Many Latencies of TempDB

3:15 PM – 4:30 PMRoom: 6B
[AD-302] The Query Store and Query Tuning in SQL Server

4:45 PM – 6:00 PMRoom: TCC Tahoma 1-2
[DBA-306] Query Store ? What is it all about?

4:45 PM – 6:00 PMRoom: 606-609
[DBA-206] Virtual SQL Servers. Actual Performance.

Dinner and a lovely beverage? You in?

9pm – SQLKaraoke?

Thursday, October 27, 2016
============================
10:15 AM – 11:30 AMRoom: 6E
[DBA-405-M] SQL Server 2016: It Just Runs Faster

10:15 AM – 11:30 AMRoom: 602-604
[DBA-324-M] SQL Server on Linux, Come Learn What is Happening!

WIT Luncheon – 11:30am

1:30 PM – 2:45 PMRoom: TCC Tahoma 3-4
[AD-203] Execution Plans for Mere Mortals

1:30 PM – 2:45 PMRoom: TCC Tahoma 5
[DBA-313-M] SQL Server Enhancements that Keep Your Environment Always On

3:15 PM – 4:30 PMRoom: 618-620
[PD-107] Advance Your Career by Becoming a Volunteer

3:15 PM – 4:30 PMRoom: 401
[AD-213-PR] Faster Provisioning with SQL Clone

4:45 PM – 6:00 PMRoom: 6A
[AD-104] Changing Your Habits to Improve the Performance of Your T-SQL

4:45 PM – 6:00 PMRoom: 6B
[DBA-301] Lockless in Seattle: Using In-Memory OLTP Transaction Processing

6pm – 7:30pm – find someone to eat with!

8pm – 10:30 – Game Night
Friday, October 28, 2016
============================
8:00 AM – 10:30 AMRoom: TCC Tahoma 3-4
[PD-500-HD] 500-Level Guide to Career Internals

8:00 AM – 9:15 AMRoom: 615-617
[DBA-204] Does it Hurt When I Do This? Performing a SQL Server Health Check

8:00 AM – 9:15 AMRoom: TCC Tahoma 5
[DBA-308] Monitoring and Alerting of Availability Groups

8:00 AM – 9:15 AMRoom: TCC Tahoma 1-2
[DBA-332-M] Tune Your SQL Server Engine to Turbocharge its Performance

11:00 AM – 12:15 PMRoom: 618-620
[DBA-208] Turbo Charged Transaction Logs – Internals and Performance

11:00 AM – 12:15 PMRoom: 615-617
[PD-105] What SQL Server Professionals Can Learn From Writing and Blogging

2:00 PM – 3:15 PMRoom: 611-614
[AD-101] Kick Start! SQL Server 2014 / 2016 Performance Tips and Tricks

2:00 PM – 3:15 PMRoom: 2AB
[DBA-309] Supercharging Backups and Restores For Fun and Profit

Nothing formal after this, but I don’t leave until 7am Saturday…

T-SQL Tuesday: Still dealing with the same problems

October 11, 2016 by Kevin3NF 2 Comments

t-sqltuesday

This post is my contribution to the monthly blog party known as “T-SQL Tuesday.” This month is being hosted by Andy Mallon, and covers the topic We’re still dealing with the same problems.

The topic was inspired by a Tweet from Allan Hirt. Allan is exactly right.

I entered the IT field officially ( meaning full-time) in 1998 as an Access Developer. I’ve done desktop support, database dev, network, phone systems and for the last 17 years I’ve been working exclusively with SQL Server, v. 6.5 – 2016.

In all these years, we still have users that don’t know to reboot a frozen laptop, store passwords anywhere but a sticky note and call me to “fix the internet” (pro tip…its broken. Go read comments on any news article.)

Narrowing this down to my slice of the IT world (SQL) and then more to Admin, and even further took some time. The thing that I see and deal with the most came down to “blindly clicking OK and accepting all the defaults, all the time.”

I get it.

On my first SQL installation, I clicked OK and yes right through the install wizard and had a functional SQL 2000 install on my desktop. On the C drive, which was almost full already.

Installation defaults that are going to bite you (not version specific, and the installer is getting better):

Files all on the C drive
One TempDB data file (improved in SQL 2016)
Backups on C drive
No automated backups
Allow SQL to use ALL the memory
Allow SQL to use ALL the CPUs
Builtin\Administrators group not default*
Compressed backup set to OFF

If you have any of the above, please research each and sort out why I listed it…you will learn more along the way than I can teach you here. Each of these is documented extremely well by both Microsoft and the SQL Community.

* let the religious debate start in 3…2…1…

Another issue that is not done at install time, but shortly thereafter when you create your first database is the VERY common failure of setting up a new Database using the default FULL Recovery model, without a Transaction Log backup plan in place.

There are hundreds, maybe thousands of forum posts asking the same question: “My database is 1GB, but my T-log is 500GB and filled the drive.”

I had this question brought to me just 2 weeks ago at SQL Saturday Dallas by developer turned accidental DBA. We spent 30 minutes discussing his database restore failures and why it took so long. At the very end he mentioned that the .LDF file was HUGE compared to the .MDF file, and then walked off to the next session. That HUGE .LDF is taking most of the restore time due to writing zeros into it well after the data is written.

Long story short: Make sure you understand the defaults before you install, and implement a proper backup plan. You can learn these easily and I’m expensive if I need to come fix them for you 🙂

Kevin3NF

SQL Saturday Dallas (563)

September 27, 2016 by Kevin3NF Leave a Comment

I’m 3 days out from my second SQL Saturday event as an attendee and my head is still swimming!

The most memorable things from the sessions I attended…not an exhaustive list, but highlights:

Agile Leadership Skills and Building High Performing Teams – George Govantes:

I went to this one, primarily because it was the first thing in the morning and because I am a team lead for all admins here (3 including me). Lots of great quotes and reading/viewing references. This was not about Agile software development, rather more about the difference between a leader and a manager. (See every third post on LinkedIn for pithy graphics from recruiters on the same).

Takeaways:

Monday morning is the most popular day to have a heart attack
70% of people hate their jobs. I am not one of them.
“Your team is a reflection of what you will tolerate.” Applies to the good and the bad.
“Nobody has time to do it right, but everyone has time to do it over.”

Data Masking and Always Encrypted – Sean Werick, PragmaticWorks

What admin doesn’t want to know about new ways to secure data and keep the wrong eyes from seeing it? Sean did a masterful job of presenting the concepts…not just from a technical perspective, but personality as well. Engaging, entertaining and he very clearly knew the tech part. Any time someone threw an “off PowerPoint” question at him, he nailed it.

Takeaways:

Data masking
- No Architecture difference…its all in the app, raw data is not changed
- Complements TDE and other features
- Role based
- Sysadmins can ALWAYS see the data
- Supported by Azure
- Default Masks for rapid deployment
- Not a replacement for row-level security and/or encryption
Always Encrypted:
- Data is encrypted at rest, on the fly and in use
- Deterministic and Randomized – pick the right one for how the data is used (Grouping, joining, etc.)
- .Net 4.6 required
- increases storage requirements
- Encrypt first, then mask.

Deploy SQL Server with Microsoft Azure Virtual Machine – Bala Shankar

I’ve played with Azure SQL DB and SQL VM. I even blogged about it for TSQL2sday. But, having never deployed or supported a production Azure anything, how can I not go listen to a MS guy talk about it? Bala is clearly a brilliant speaker and IT person. If you go to one of his presentations, bring a seat belt and safety helmet…it will be fast and you don’t want to miss anything!

Takeaways:

VM’s built from the Azure marketplace bring the licensing costs for Windows and SQL with them. Bring your own license is an option.
Server families: A&D (V1?) level for Dev/Test, G-series for heavy loads (G for Godzilla). My notes say DS v2 for SQL, but not finding that term in Azure portal at the moment…more research required
Premium storage is SSD, with SLA for performance and availability
Standard storage is spinning rust, SLA for availability only
Connect through Public IP or Virtual network/sql port 1433
You can now have your VM Auto-patch and Auto backup the databases (30 day retention, SQL 2014 only). VM>>SQL Server Configuration>>Automated backup
So much more…but my pen melted…

Lunch – Fajitas, chips, ice cream later, coffee all day. Cool.

Hi, my name is Powershell, let’s be friends! Intro to PoSH Amy Herold

I’ve tried to learn PowerShell off and on a few times, but inevitably I get distracted and forget it all. I went to this session to hopefully get inspired to pick it back up. The session was fine and Amy is a very entertaining speaker who clearly knows her stuff. But…I’m still struggling. I’ll probably have to hire someone to teach me one on one someday, lol!

Takeaways:

Amy is a mean DBA (and proud of it)
The Shell and the ISE have their own profiles
You can do all sorts of stuff with PoSH.
Demos break for everyone, no matter how good you are

Let’s Break Some SQL Server Availability Groups! John Harp

John is another one of those great speakers that clearly knows his material. Setting up clusters, AGs, etc. on a laptop for a presentation is no small amount of work.

This was a high-speed, high-energy presentation. I put the pen down and just watched, trying to pick up a few nuggets as we went along. John had some things break of course, since we were trying to break some AGs. The ones that broke outside of the plan were fixed/resolved quickly.

Random Acts of Senseless Databasing – Kris Hokanson

I went to this one due to its time slot at the end of the day, my brain being at capacity, and just to hear some crazy things people do. This blog used to be dedicated to that topic! Former name – “You want fries with that?”

Single biggest takeaway was to look into sp_executesql instead of Exec() for stored procs. It may not be perfect for every situation, but it may also save your bacon. Kris referenced the SQLSkills post on it here.

Funniest part of this was the actual live crickets in the room being the only sound at one point after “Any questions on this?” {crickets}…

Overall:

I had a great time! The event was well organized and well run. The presentations started on time and the rooms were easy to find. A wide variety of topics and well known speakers makes for a must attend event. SQLSaturdayKC was happening the same day, and MSIgnite was firing up as well. Its a testimony to the passion of the SQL Server Community that these did not dilute the speaker pool!

Kudos to the leaders and volunteers for a top-notch event that wore me out 🙂

Kevin3NF

Analyzing an Upgrade after the fact

September 26, 2016 by Kevin3NF Leave a Comment

We had a major upgrade to our main application recently, so I thought I would share some of the “thoughts from the aftermath.”

The good: We gave the application back to the business 1/2 of a business day earlier than expected. Win.

The bad: We were hoping to give it back a full day earlier

The ugly: we wasted a full day having to rollback and start over

Some lessons learned:

Full backups ahead of time are a great idea – check
Restore scripts for those backups are a great idea – check
Using SQLCMD to apply vendor .sql files is a great idea – check
Forgetting the -I parameter in SQLCMD is a bad idea (not -i, but -I for Quoted Identifiers, which is false by deafult in SQLCMD, but True in SSMS)
Not having a full setup lab environment to test everything – bad
Using the default settings in SSDT Schema Compare – bad
Re-ordering column order in tables for cosmetic reasons – bad (this one is on the vendor…we had to re-write some code that broke)
KrispyKreme donuts in the war room followed by a huge box of cupcakes – bad. Didn’t affect the upgrade but I ate too much 😉

Those are the big items.

This post inspired by Tim Mitchell‘s post today : Studying Failures

Feel free to share your deployment/upgrade brilliance or derp moments in the comments below.

Kevin3NF

Restore all databases at once

September 15, 2016 by Kevin3NF 5 Comments

We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.

The catch is that there are close to 300 identical databases involved.

This is easy enough to do if you know where info is stored in MSDB related to the last backup.

I’ll just leave the code I used right here for your amusement:

Use MSDB;
go
 
--generate the list
Select
    max(backup_start_date) as Backup_Date,
    database_name,
    Max(physical_device_name) as physical_device_name
Into
    #lastFullBackup
From
    msdb..backupset bs
    join msdb..backupmediafamily bmf
        on bs.media_set_id = bmf.media_set_id
Where 1=1
    and backup_start_date > getdate() -1
    and type = 'D'
Group By
    database_name
Order By
    database_name
 
--Create the restore commands
Select
'restore database ['+database_name+'] From disk = ''' +physical_device_name+''' With replace, recovery'
From
    #lastFullBackup
Where
    database_name like '%MyDB%[0-9]'  -- database1, database2, etc.
 
--Cleanup
Drop table #lastFullBackup

There are probably much easier or prettier ways to do this, but I’m only 2 cups of coffee in thus far, and one of them got cold…

If any of the above doesn’t make sense, please drop a comment. It works on my system and should be valid for SQL 2000 – 2016…the system tables referenced have not changed.

Have a bright, shiny day full of puppies or muscle cars….whichever you prefer!

Kevin3NF