Kevin3NF

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

Azure: SQL VM vs SQL Database

September 13, 2016 by Kevin3NF 2 Comments

t-sqltuesday

“The Cloud” edition

Azure SQL Database vs. Azure SQL Virtual Machine

In keeping with my “this blog is for the beginner DBAs” style, I’m going to keep this short and simple. I will also confess that I have only played with the cloud, and only Microsoft Azure so you won’t see anything about AWS or any other cloud provider.

Some basic terminology:

Cloud: No such thing. It is just your stuff on someone else’s machines that they maintain for you.
Virtual Machine (VM): A Virtual Server on some physical servers…yours, or someone else’s.
Azure: Fancy name for Microsoft’s cloud. As a noun or an adverb it means “blue”. Or a small butterfly.
Azure SQL database: Just a database in Azure on some storage
Azure Virtual Machine: A VM on Microsoft’s Azure servers, that you do not have to maintain the underlying physical infrastructure.

When the boss comes to you and wants to look at “moving SQL to the cloud”, please make sure you are comfortable with all of the above terms so you are on the same page, or know where he is misusing some buzzwords.

Let’s assume a very basic scenario: You have one database that your company website uses to sell its widgets. It needs to be up pretty much 24×7, and your building has occasional internet connectivity issues that are out of your control. Enough so that you are losing sales and customer confidence. Your traffic is low, and your business is small so every sale is important.

You basically have three choices:

Move to a colocation provider and manage it all yourself
Move to Managed Hosting solution such as Rackspace
Move to the cloud

For this conversation, let’s assume the first 2 choices are ruled out due to cost. For Azure pricing, there is a handy calculator here.

Virtual machines are basically priced based on resource quantity (CPU/Ram/Storage) combined with number of hours per month it is turned on (on/off is via the Azure portal, not shutdown from the VM desktop). You can get an 8 core, 56GB Ram, 605GB storage VM for $1.32/hr. or $982.08/month.

Azure SQL Databases are priced based on DTUs x number of hours/mo. A single database with 250 DTUs and 500GB of storage runs $1.25/hr. or $930/mo.

Note: Pricing can and does change, so this is based on prices in the calculator as of 9/10/2016. Also, you can change service tiers as necessary at any time. Ramp up for seasonality, down for off-season, etc.

Pros and cons:

Virtual Machine:

Pretty much the same as a virtual or physical machine in your network.
You worry about the O/S in the VM, Microsoft takes care of the underlying infrastructure
Spin one up in minutes with SQL Server already on it
SSMS, database management, etc. is all the same as your local (on premises) servers. Use the SSMS GUI as you always have, or T-SQL
Backups are your responsibility and under your control (can be automated via the portal)
You patch O/S and SQL as you see fit (Automated is available through the portal)

azure_vm_screenshot

SSMS and Services from my Azure SQL 2016 VM on Windows 2012 R2

Azure SQL database:

Connectivity is through connection strings for SSMS and other applications. Strings are available through the Azure portal
Patching is automated under the covers by Microsoft…existing connections may be reset when they happen. You have no say in this.
Backups are completely automated
Can be created in just minutes (assuming storage accounts, etc. already configured)
Some commands only available through T-SQL
Geo-replication for DR in a few clicks (DR server needs to be created of course)
TDE in two clicks

azure_sqldb_screenshot

SQLDB from SSMS on my laptop, with Properties and Create User template…

This is not a comprehensive list of every feature available, nor is it intended to be. Microsoft is adding more and more to the Azure SQL products all the time, so no list will really be able to keep up.

Rather, the goal here is to give the junior DBA a reference point to the major differences between SQL Server in an Azure VM vs. am Azure SQLDB.

The VM is most likely the more familiar choice for those new to cloud computing, but each workload and company will have to make the decision based on their requirements as to which of the two is the optimal choice.

Hope this helps clarify some things for you! Please ask questions in the comments and I will answer what I can, as long as you remember the disclaimer at the top J

Kevin3NF

The OnPurpose DBA

Dear Junior DBA…

September 2, 2016 by Kevin3NF 2 Comments

Congratulations on getting your first SQL Server DBA job!

Presumably you have a tech background or education, and have been through some basic training in SQL Server administration. I also assume you intended to be a DBA and want to be really good at it so you can advance your career and get mad raises/bonuses.

With any luck at all, you are in an environment where there is at least one other DBA there that knows more than you do. Ideally a Senior that is really into mentoring that can guide your path.

If not, here are some of the basic things that you may already know how to do in SQL Server Management Studio, but don’t really know the inner workings or the T-SQL to make them happen.

Also, almost everything you can do in current SSMS versions can be scripted. Look for the script button and click it after you make all of your selections so you can start learning the code behind the GUI. In time you’ll prefer going straight to the Query Window for some functions of your job.

The list I want my juniors to get intimately familiar with:

Backup and Restore…beyond the Maintenance Plans
Creating/Deleting databases
Creating Logins and Users (and knowing the difference)
Creating and maintaining indexes
Other database maintenance items
Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions

All of these can be setup/monitored in the GUI…so make sure you know all of the options there, and then start working on knowing them deeper. Start with Books Online/MSDN and go from there.