SQL Server with Mr. Denny


September 13, 2014  10:15 AM

Recommended reading from mrdenny for September 12, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Hacking, SQL Server, SSD, Support

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: datachick also known as Karen Lopez

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny

September 10, 2014  4:00 PM

Dangers of Mixing Native Backups and VSS Backups

Denny Cherry Denny Cherry Profile: Denny Cherry
Backup and restore, SQL Server

In today’s modern world of virtualization there are a lot of different ways to backup databases. You’ve got native backups, VSS backups taken by snapshotting the VMs, third party backup tools which take snapshots, etc.

When you have multiple backup techniques all being used at once you run the risk of breaking the LSN chain for your transaction log backups. For your full and differential backups you could end up having a differential backup that you took with one method which is built off of a full backup taken from another backup method. Trying to unwind this during a production down situation becomes very stressful, and depending on the tools which you are using basically impossible to restore from as some tools don’t allow you to restore from them, then restore another layer of backups from another method.

To make life simple and easy to manage only use a single backup method to backup the databases.

Denny


September 5, 2014  5:03 PM

Recommended reading from mrdenny for September 05, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database mirroring, In-Memory Database, SQL Server, SQL Server 2014, TechEd

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: jasonhorner also known as Jason Horner

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


September 3, 2014  2:00 PM

Stop Following Every Best Practice Document That You Find

Denny Cherry Denny Cherry Profile: Denny Cherry
best practices, Server storage, SQL, SQL Server

There are a lot of best practice documents out there. Some are new, some are older. Some of worth while, others are crap. Some of the older ones were good at the time, but now are meaningless.

Recently I was told that this best practices document is the gold standard that people should use.  Lets take a look at it for a minute and decide if that’s the case or not.  First other than the URL we’ve got no idea who actually wrote this (It is from someone named John Hicks) because the author is listed as “MSDNArchive”. archive In John’s tenure of writing blog posts on his msdn.com blog, we wrote 3.  Now this doesn’t by itself mean that the information that John is giving us is useless, but it’s a little odd that he’d start a blog in March 2008, post this article then not post again until April 2009, then not post again until June 2009 then never post again.

Lets go through the best practices section by section and see how they hold up to time.  The first one is “Use Different storage for data files and log files”.  99% of the time I agree, so let’s move on.

In “Configure Storage System to RAID 10″ John talks about the fact that RAID 5 is slower to write to than RAID 10. Yes this is true.  We also need to remember that some systems will be just fine on RAID 5, in fact a lot of system will be just fine on RAID 5.  This is because you aren’t actually writing to the disk.  Instead you are writing to cache which then destages to the disk.  If SANyou are using a SAN, and you are writing directly to disk, something is wrong and you need to fix it.  So I’m going to call this bullet point crap.

Use DISKPART to partition new LUNs.  If you are using Windows 2003 to host your databases then yes you need to do this.  If you are using Windows 2008 or newer then this isn’t needed as your partitions will automatically be aligned correctly.

Format the partitions with 64k allocation unit.  Yes this is still valid.

Balance Disks between controllers.  Yes this is still valid, if you are on that kind of array.

So for the Storage Section of this checklist 3/5 are useful and the other two are junk.

The next section is the setup section.  This has 8 bullet points in it.  Lets see how many are useful.

The first is to “Size and split tempdb and disable auto-grow”.  So this says to size your database to the largest table plus 10%.  I’ve got a system that I manage which has a 2TB table.  It does not have a 2TB tempdb. TempDB should be sized at whatevesetupr size it needs to be for that system.  The best way to figure that out is to run the system for a few days and see what it grows to.  Create 1 tempdb file per CPU or core.  No, don’t do that.  Use 4 or 8 files and 99.999% of the time that’s all you need.  John also recommends that you disable auto-grow.  That’s insane, unless you like getting called in the middle of the night because the tempdb is full.

“Turn off features you don’t need” is John’s next recommendation.  This is an excellent idea and should be done on every production server, SQL Server or not.

The next recommendation is to run each server as a domain account.  These days I would recommend a managed service account for every machine.  This way you get the benefits of a domain account, without needing to change the passwords manually every time.  As this recommendation is out of date, we’ll call it junk.

The next recommendation is to get a CA signed cert for SQL for when you are using SQL Authentication.  I suppose that this probably isn’t a bad idea, except that the SQL Client doesn’t do any checking to see if the certificate that is used to encrypt the authentication handshake is valid or not. So even if you did this, it wouldn’t actually stop a man in the middle attack.  So this is junk (be great if the server actually worked like this).

Changing default ports from 1433 is just annoying. If you’ve changed the default ports I can find the new port in a few seconds with a port scanner.  This just makes life harder and provides no actual level of protection.  So this is junk.

“Limit data on Primary FileGroup”.  Yes, this is a good idea, do this.

“Keep first and last partitions empty”.  I see where he’s going with this, but so few systems use partitioning, there’s no point it in being a “standard”, but I’ll give him credit here.

“Isolate and static data”.  True, this would allow you to keep the static data in a read only filegroup, but really that’s only a few megs or Gigs of data.  Compared to how big the rest of the system is this probably has no real benefits to you, and is just going to make management of the system harder.  I’ll give him credit here, because while I’d never actually do this this recommendation isn’t really wrong and haven’t become wrong over the years.

In this section of the 8 bullet points, 4 are valid and the other 4 are either old and useless or were never valid to begin with.

The final section is the biggest, with 13 bullet points.  The first is to disable HyperThreading, which according to this post is available on “some” Intel Processors (Hint, it’s on all server class processors these days).  This is wrong, sort of.  For machines which have very high CPU usage havingpost_install HT enabled will hurt performance because of the extra task switching.  For machines which have low CPU usage having HT enabled will help because the tasks which are running are usually waiting on something other than CPU.  So this one is junk.

Ensure that HBAs are teamed, not failover.  This depends on your SAN vendor and your MPIO driver.  The default Microsoft MPIO driver is active/passive per LUN, so if you’ve got multiple LUNs you’ve got access to all the bandwidth.  Teamed isn’t the write terminology here, it’s simply active or passive.  This is controlled through the MPIO drive.  I want to call this junk, but the basic idea is write even if the terminology is crap.

Enable SAN Write Caching.  Yes do this.  How much of your cache should be for write cache, that’ll depend on your environment.  But I’ll let this one slide.

His NUMA recommendations are very out of date as he’s talking about 2 and 4 core boxes, so junk.

NIC teaming should be done for high availably of the NIC, not for bandwidth.  I’ve only seen a couple of bandwidth constrained SQL Servers and that was in the 10/100 network days.  I’m calling this junk even though the idea is right, his reasons suck.

Setting the HBA queue depth.  If you are having SAN problems then start playing with these settings.  If you aren’t then don’t mess with these.  So Junk.

Tuning on Checksums, yes do this.

Turning on RCSI for the databases.  I don’t recommend doing this unless you need to.

Manage Volume Name permissions aka. Instant File Initialization, yes do this.  It makes your database auto-grows happen in a lot less time.

Turning on TF1224, if it’s a large database, then yes.  Otherwise no (I don’t have it enabled on any of the large databases that I manage).  It’s better to fix the queries which are taking large locks to begin with (if possible).

Set the NICs, yes do this.

Turning on Jumbo Frames between the users and the server.  No, don’t do this. Never do this.  There’s no reason to do this.  This is useful if you’ve got users running huge reports and downloading massive data sets from the SQL Server that aren’t getting transmitted fast enough.  Jumbo frames between the SQL Server and the iSCSI array makes sense.  Jumbo frames between the SQL Server and the users desktop doesn’t.  There’s a few reason not to do this.

  1. If your users are connected to the SQL Server via the WAN this won’t matter as the packets will be shredded back to 1500 bytes over the WAN anyway.
  2. This will require that all network traffic coming from the client machines is Jumbo, so every server, switch, router, etc. needs to be adjusted to account for this.
  3. Your border routers CPU will go way up as it now has to shred every packet that comes from the corporate network to the Internet.
  4. Your users Internet access will get slower as their packets have to be shred and resized.

In other words, this is a crap recommendation.

So of the 13 bullet points here, 5 are crap.  In total of the 26 recommendations, 11 are junk and shouldn’t be done.  Some of these are junk because they are old and aren’t valid any more for one reason or another.  Others (like the Jumbo Frames one) look great on paper, but in practice are useless, or even harmful to other parts of the environment.

When writing (or finding) a best practices document, even if it’s been pushed on MSDN, be sure to understand what it’s talking about and make sure that the recommendations make sense, and that they are for your version of the software.

Denny


August 29, 2014  7:00 PM

Recommended reading from mrdenny for August 29, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database mirroring, SQL, SQL Server, SQL Server 2014

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: Bill_Pearson also known as Bill Pearson

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


August 25, 2014  2:00 PM

PASS Summit 2014 Speaker Idol Competition

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences and events, SQL, SQL Server

PASSLogoBWIf you watched my SQL PASS 1st Timers webcast last week (which you can see the recording of here) you saw that I announced that this year the PASS Summit will be hosting it’s first ever speaker idol competition.  The goal behind the Speaker Idol competition is to give speakers who are known in their region a chance to be seen at the international level so that they can improve their chances of being selected to speak in future years, to get some real time feedback from other speakers about their presentation style, and so that they can improve as speakers.

pass_2014_banner

In order to qualify to be a contestant in the SQL PASS 2014 Speaker Idol competition there are a few simple requirements:

  1. You have spoken at a SQL Saturday or SQL Rally event (or other large non-pass event, such as SQL Bits, a TechEd Conference, etc.).
  2. You have not spoken at the PASS Summit or PASS Business Analytics Conference for a breakout, spotlight or pre-con session.  (If you gave a lightning talk and no other session you are eligible.)
  3. You will be in Seattle during the PASS Summit (attending PASS isn’t needed, and if you aren’t attending PASS we will get you to the session roomSpeaker Idol 2014 Badge for Speaker Idol).
  4. You are available to attend which ever speaker idol session you are assigned to.
  5. You have a 5 minute technical presentation which you can give.
  6. You sign up on the registration form before October 10th 2014 at 11:59:59pm (23:59:59) Pacific time according to Denny Cherry’s Verizon Cell Phone.

The rules for Speaker Idol are pretty simple.  12 contestants will be selected from those who have submitted.  Those 12 people will be broken up into three groups of four, which will be the initial rounds.  One group will compete each day (Wednesday, Thursday and Friday) with the winners from each round moving on to the final round.  A wild card contestant will be selected from the 9 contestants who didn’t win their initial round.  These four contestants will compete in the final round on Friday.  The winner will be guaranteed a session at the PASS Summit 2015.  The exact times for the Speaker Idol sessions won’t be known until the PASS Summit Schedule is announced.

Judging will be done by a panel of experienced speakers (who haven’t been announced yet) who have spoken at conferences such as the PASS Summit, TechEd, Dev Connections, Intersections, etc.  Judges will give each speaker feedback as soon as they are done presenting.

If you think you have what it takes to compete in this years PASS Summit 2014 Speaker Idol Competition, then I invite you to signup on the registration form.  You’ll notice on the form that there is no session abstract requirement which is correct.  There is no abstract review for selecting the people for Speaker Idol.

So go submit yourself, and I’ll see you at the first speaker idol session on Wednesday.

Denny


August 23, 2014  1:40 AM

Recommended reading from mrdenny for August 22, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: RealSQLGuy also known as Tracy McKibben

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


August 21, 2014  3:25 AM

SQLPASS SQL Karaoke Party Location Announced

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

sios_logoI’m pleased to say that we’ve secured a new location for the SQL Karaoke party at the SQL PASS Summit this year. This year we’ll be at a bar called Cow Girls Inc which is at 421 1st Ave S in Seattle, WA. It’s a 20 minute walk or an 8 minute cab ride from the convention center and hotels. Like past years, everyone is welcome to attend even if you don’t get signed up for a wristband. The wristband is only needed for the open bar, and a cash bar will be available for those without a wristband.

Unlike years past, the bar will only be open for us so we ask you to please register in advance (you can get in even if you don’t, but it’ll be a lot easier if you register).

Tickets are limited so be sure to register to attend.  Like in years past the party will start right after the welcome reception ends.

Don’t forget to thank the sponsor for this years party SIOS and be sure to go to their booth and learn more about SANLess Clusters.

Denny


August 19, 2014  6:26 PM

What’s New at #sqlpass / 1st Timers Session TOMORROW!!!

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences, SQL, SQL Server

Just a reminder that my SQL PASS 1st Timers Session is TOMORROW, and that you have to register for the session.  During this session we’ll cover where everything has moved in the convention center, which includes the keynotes, some session rooms, as well as some big community announcements about how YOU can get a speaking slot at the PASS Summit in 2015!

So don’t forget to sign up for the webcast and see what all the changes are for the PASS 2014 Summit.  If you haven’t attended the PASS Summit and you are attending this year, you’ll want to check out this webcast for sure.  Another big thank you to SIOS for hosting the Webcast for me this year.  Seats for this years webcast are limited, so be sure to get signed up!

I’ll see you on the webcast, and at the PASS Summit. Like last year I’ll be at my booth, the Consultants Corner with a couple of other great consulting companies, so be sure to swing by and say hello.

Denny


August 15, 2014  5:19 PM

Recommended reading from mrdenny for August 15, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
community, Heartbleed, iSCSI, Opex, Recommended reading, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: russjohnson also known as Russ Johnson

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: