SQL Server with Mr. Denny

September 26, 2014  5:11 PM

Recommended reading from mrdenny for September 26, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
EMC, EMC Arrays, EMC VNX, Microsoft MVP, SQL Server 2014, SQL Server Express, VMware

On a personal note, today is my 15th wedding anniversary to Kris.  Thank you to my Kris who’s put up with me for all these years.

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: SueBrownawell also known as Sue Brownawell

Hopefully you find these articles as useful as I did.

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


September 24, 2014  7:00 PM

All Your Hardware Should Be Of The Same Class

Denny Cherry Denny Cherry Profile: Denny Cherry
Cisco UCS, Data Center Hardware, SQL, SQL Server

I’m seeing a really disturbing trend in enterprise hardware purchases these days. Companies will spend hundreds of thousands of dollars on their Compute environment (the servers) but they cheap out on the storage. In the last two weeks I’ve talked to people from two different companies who have top of the line Cisco UCS servers, but for their storage they have low to mid end storage systems.

This just doesn’t make sense to me. If you know that your workload is going to be high enough to justify the cost of the Cisco UCS platform why cheap out on the storage which is really the key to making things run faster? If I know that I need to run 200 VMs in my farm, including a bunch of SQL Server instances, why are you going to buy storage that can handle 14k IOPs max (at RAID0) then after RAID something like 8k IOPs? That just doesn’t make a lot of sense at all to me.

If you are going to spend this kind of cash, spend a little more and do it all right. Everything will work much better in the long run this way.


September 19, 2014  5:09 PM

Recommended reading from mrdenny for September 19, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
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: ygtaktn also known as Yigit Aktan

Hopefully you find these articles as useful as I did.

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


September 17, 2014  4:00 PM

Getting Windows Authentication to Work Without a Domain

Denny Cherry Denny Cherry Profile: Denny Cherry
Active Directory, Authentication, SQL Server, Windows authentication, Windows Workgroups and Networking, Workgroup, Workgroup Servers

Not every company has Active Directory setup to give them a centralized way of managing authentication. For those who manage SQL Servers within a Workgroup getting authentication to work correctly with Windows logins can be tricky if you haven’t done it before.

In order to get Windows logins working correctly the trick is to create a local account on both the machine which you log into and the SQL Server with the same username and the same password. Once that is done you can grant permissions within the SQL Server instance to the local account.

So if your workstation is PC1 and your SQL Server is SQL1, then within the OS of SQL1 you create a login named dcherry. Then within SQL you create a login for SQL1\dcherry and give it the rights which are needed. Now in PC1 create a login called dcherry. Now log into PC1 as dcherry, open SQL Server Management Studio, and you can connect to SQL1 using your Windows account.

Now if you need to change your password for any reason, you’ll need to log onto SQL1 and change your password there as well as the passwords on both machines must be the same.

If you’ve got even a few computers I’d recommend looking at getting Active Directory up and running as it’ll make your life easier in the long run as you don’t have to do all this to get Windows Authentication working.


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.


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.


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.


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.


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.


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.


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.


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: