DallasDBAs.com

SQL Server Database Consulting

TSQL2sday

TSQL2SDay: Backup Basics

by 1 Comment

t-sqltuesday

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) just over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Then anyone who is interested blogs on that topic. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by my friend Kenneth Fisher (b/t), who has challenged us to go #EntryLevel and talk about backups.  So I did…in a video just posted to YouTube:

You’ll want to go full screen to see what’s going on.  This video is targeted at non-DBAs, but give it a watch anyway…I can take the heckling!

T-SQL Tuesday: Speaking & Presenting

by 3 Comments

t-sqltuesday

#TSQL2SDAY is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic on his blog.

This month’s topic is going to be about Speaking & Presenting with a focus on Helping New Speakers.

I have presented “publicly” exactly one time, probably 12 years ago.  It was to the North Texas SQL Server User Group, during the “Pizza and Networking” portion of the evening.  I was the warmup act for the main presenter, which was perfectly fine with me.  I was simply doing a walk through of Red Gate Software‘s SQL Compare and SQL Data Compare tools, which weren’t as well known as they are now.

I’ve done a boatload of teaching since then, but almost exclusively to people I work with.  I’ve had dozens of one hour ad-hoc sessions to teach level 1 engineers SQL Server basics such as Backups, Log Shipping concepts, etc. so that they could close more tickets without having to escalate.  These were limited to 2-3 people, and no slide decks.  All whiteboard with circles, squares and arrows.  I’m also very experienced in teaching Bible stories and concepts to teens and pre-teens.   Lastly, I’ve been a cycling coach to teenagers for almost 10 years now…teaching training techniques and race tactics.  Multiple Texas State Championships from those I’ve coached.

All that to say I’m comfortable teaching to kids and those with open minds.

Teaching to SQL Server professionals is quite a bit more intimidating.

My ideal first presentation has to be things I know very, very well to a group of people that rarely use the info, and don’t know they need it:

“DBA basics for non-DBAs”

Target Audience: developers, junior and accidental DBAs, managers, Sys/Storage Admins, Data Warehouse and BI folks.  DBA-101.

I don’t know how well this would fit into a User group meeting, since most of the attendees are fairly experienced with SQL Server.  I think it would go over really well at a SQL Saturday, where there are multiple concurrent sessions.

Topics I think would fit really well into this talk (not necessarily all):

  • Backup/restore – What they are, what your DBA is doing, and what to ask for when you need a restore
  • How to find things in SQL Server Management Studio
  • What exactly is in the ERRORLOG?
  • Installation/Setup best practices
  • Concepts and differences in Log Shipping, Replication, Clustering and AGs (no tech, just descriptions…possibly its own presentation)

The key point of all this is take the massive enterprise product that is SQL Server and boil the basics down to the new users in terms they understand without drowning them.  A fine line for sure…

I would LOVE to get your feedback in the comments!

Thanks,

Kevin3NF

The OnPurpose DBA

 

T-SQL Tuesday: Still dealing with the same problems

by 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

 

 

 

 

Azure: SQL VM vs SQL Database

by 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

 

Home Blog About Privacy Policy