DallasDBAs.com

Explaining SQL Server in plain english

SQL

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

 

Dear Junior DBA…

by 2 Comments

Image by Pexels from Pixabay

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.

More on each list item:

Backup and Restore – I want you to be able to regurgitate exactly what the difference is between Full, Differential and Transaction Log backups.  I want you to know when you would use each.  You need to know how to restore to a point in time, to another server or as a new database name. Backwards and forwards…this is DBA 101 and the first question I ask if I interview you.  You need to be able to throw down the basic Backup Database syntax on the fly.  Also, recovery models…memorize and understand them (including Bulk-Logged)

 

Creating/Deleting databases – There are many ways to create a database…SSMS, T-SQL, Restore from a backup, deploy from a .dacpac/.bacpac, etc.  Know how to do each, when you would use each, what options are available and how they affect behavior.  Know about filegroups and best practices for laying out your .mdf, .ldf and .ndf files…for your environment.   Know what to do before you delete a database.  Does the requester mean Delete, Detach or just take offline?  What’s the difference?  Know your RPO and RTO by heart.

 

Creating Logins and Users (and knowing the difference) – I’m amazed at the number of experienced people and non-sql people that use the terms interchangeably.  Know the difference. Also learn what server and database roles are. These days, the more you know about the various pieces of the security model in SQL Server the stronger a DBA you are.

 

Creating and maintaining indexes – If your databases are only a few GB, you can almost ignore indexes…but don’t.  Know and be able to explain the difference between clustered and non-clustered indexes.  Understand how to determine what indexes are needed and which existing ones are not. What is an included column?  What is a covering index?  Memorize the ins and outs of Reorganize vs Rebuild and Online vs. Offline maintenance…this matters.  A surprising number of application developers will rely heavily on you for help in this area.

 

Other database maintenance items – DBCC CheckDB and its impact on tempdb.  How to respond to CheckDB errors. Statistics…what are they, do they matter and how do you handle them?  When?

Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
Know the difference between the various options available and what they are intended to do.  High Availability, Disaster Recovery and Distributed Computing are NOT the same things.  Understand that managers use Replication and Log Shipping interchangeably. Teach them gently.  Your environment may or may not being doing any of this.   Know your RPO and RTO by heart.
Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions – any time you have a chance to get into a performance tuning issue jump on it.  Don’t assume the issue is with SQL server just because someone said so.  Prove it is or isn’t.  Don’t automatically blame the storage or network teams unless you are never going to need them again (you will need them again…).  Start at the server level, then drill down into the DB, then queries…jumping straight to queries may just be a waste of time if a rogue application is stealing all the CPU or leaking memory.
Yes, this is a lot of stuff.  No, you won’t learn all of this today or next week.  If you went from GUI to familiarity with all of this in 6 months I’d be impressed.  Ask your mentors and teammates for help and guidance AFTER you do your research.

 

Also, there is a ton more you will pick up along the way.  Ask questions.  Go to SQL events if possible.  Read blogs from Paul Randal, Brent Ozar and Grant Fritchey (and a bunch of others)

You can make a long career out of just database administration.   But don’t deprive yourself of opportunities to learn about storage, virtualization, cloud computing, development, business intelligence, etc.  The more you know, the more successful you can be.   One of these days, you will be the mentor, not the mentee.  Be kind to the new guy 😉

Kevin3NF

Min and Max Server Memory in English

by Leave a Comment

This one is for the new DBAs…

There is a lot of confusion on memory settings in SQL Server.  Specifically Min and Max settings found in the Properties of an instance:

There are dozens, if not hundreds of blog postings that will go into beautiful detail about all of the intricacies of SQL Server memory…after all, SQL lives there!   Thus, it is very important to have a basic understanding early in your SQL DBA career….especially if you are an accidental DBA.

In the screenshot above, I have set the Min and Max on my laptop to 2GB and 14GB.  Its a 16GB laptop, so I left 2GB for the O/S…pretty basic stuff.

Max Server Memory is fairly obvious…when SQL Server reaches this point, it stops allocating and starts paging to disk as necessary.

This is pretty much the same as setting a throttle control on a car.  Or a restrictor plate in NASCAR.  Without it you can run the car engine all the way to the max if you want, but eventually the other systems are going to fail (Cooling, transmission, brakes, etc.).  Same thing if you don’t leave memory for the O/S.

Min Server Memory seems to get the most bad information spread around.   SQL Server does NOT automatically grab memory up to the Min setting when it starts.  However, once it gets there, it doesn’t give back.

Back to the car analogy…if you start up and head off down the road at 20 mph, you are above the default (0), but not at the max (100+, depending on the car and the tires…).  If you set the cruise control, you can accelerate up and down above 20, but you won’t go below that unless you hit the brakes.

So that’s it…by default, SQL installs at idle and full speed ahead.  Its your job to turn on the cruise control and not redline the engine until it blows.

There are holes in the analogy if you dig deep enough, but this should give you a real-world base to work from as you grow in your skills.

Kevin3NF

SQL 101 – Clustered index vs. Non-clustered

by Leave a Comment

More translating of SQL stuff into less technical terms for the new folks…

SQL Server (and most other database systems) offer the option of using indexes on your data to help queries go faster. The purpose of this post is to give the new SQL dude a quick mental connector while learning the concept.

Clustered Index:

Think of the White pages phone book you have at the house. Now, find the entry that has your phone number in it. Mentally, you think of your last name (parameter 1 in a query), then your first name (parameter 2). So if your last name is Gates, you flip directly to the G section. If your first name is Bill, you go to the B section within the Gates area. At that point, there may be more than one Gates, Bill entry, so your eyes start scanning through them for some other piece of identifiable info, such as a street or city (parameter 3) until you find the correct entry. You then slide over to the right, look at the phone number and return that to your brain.

The White pages are a “Two-column” clustered index on lastname, firstname in alphabetical order (Ascending). The data itself (the names) IS the index. No extra pages at the back of the book. Speaking of extra pages….

Non-clustered index:

Think of every technical book or textbook you’ve ever read. There is almost always a collection of additional pages at the back of the book called the index. These pages do not contain any of the data about the topic at hand…just pointers to where in the book you can find what you are looking for.

Imagine you are holding a SQL Server 2005 administrators book, and you want to find every reference to “Replication.” Yes, you can look in the table of contents but that may leave out an entry found in the Backup/Restore chapter, or Performance troubleshooting. So, you go to the back of the book, look through the alphabetical list of Keywords for “Replication” and now you know that the word exists on pages 45, 255-298, and 453. You have a collection of pointers to the specific data pages in your book that may contain the information you need.

What if I don’t have any indexes?

No clustered index: Imagine finding your name in a white pages that was not sorted alphabetically. You would have to start at the first entry and read every single row until you hit it (a table scan in SQL speak). Ugh.

No non-clustered index: Imagine me telling you to find the phrase “SQL Profiler” in that SQL book you bought, after I rip the table of contents and index pages out. Sounds like a fraternity hazing ritual for IT geeks 😉

How many can I have?

Clustered: 1. How many ways can you sort and print the data in the SAME book? 1.

Non-clustered: More than one, depending on the database platform and version.

That’s all for today…what indexes to have is not a 101 level discussion, other than to say…whatever you join or search on is a good candidate.

Now you are ready for part of your interview 😉

Kevin3NF

SQL 101 – Recovery models in simple terms

by Leave a Comment

A recovery model is simply a choice made on each database that determines how much data you can recover if your db goes “toes up”.

If you do not backup your database, you have chosen recovery model “terminated” or “update resume”

The 3 that are offered by Microsoft are:

  • Bulk-logged (rarely seen, and generally more advanced, so I’m skipping it)
  • Simple
  • Full

Simple vs. Full is very simply a matter of how much data can you afford to lose if the database is lost and you are forced to restore from your backups.

Simple recovery model: does not allow backing up the transaction logs, therefore you cannot restore any of the data in them. You can only restore to the point of the Full backup and any one Differential you may have.

Full recovery model: You can restore from t-log backups (assuming you have them), right up to a specific point in time. Reduced data loss, assuming the backup files are valid.

When to use:

Simple: When you do not care about the data, such as in a Development or Test environment where you can regenerate from another source. Also useful in a static, read-only database (assuming you have a full backup).

Full: Pretty much any live production database that has Inserts, Updates and Deletes happening.

Switching from one to the other:

Simple to Full: Immediately take a Full or Differential backup

Full to Simple: No specific action required, other than verifying regular data backups are in place and working.

Maintenance plan considerations:

If you have both Simple and Full recovery model databases in your SQL instance, and you create a Maintenance Plan to back up data and logs, you may run into an issue (at least in SQL 2000) where the automated deletion of old t-log backups is failing. Make two plans: one for Full and one for Simple. I have no idea if this issue still presents in SQL 2005.

I hope this is clear…please feel free to comment.

Kevin3NF

Home Blog About Privacy Policy