Using Powershell and Excel to compare Microsoft Hotfixs on Servers and Virtual Machines.

Hello All,

 

Two Types of Comparisons Devised

I have been working several Clustering and Hyper-V cases Recently.  I have come to a point where I notice the Virtual Machines rarely have the same level of  Microsoft Updates that the Cluster Node does. I finally had a few minutes to look at this issue, and I even made an Excel Spreadsheet to compare updates to other machines.

Fortunately, When I got serious about making a Spread Sheet Script, I noticed there were some pretty good work already available. In fact, I found one that will likely serve to compare updates on all the Nodes, all the VMs and more. I am sure I can do cross checks using a Host as the standard, and a bunch of VM’S as the comparisons.

So we are basically showing off two different ways of doing the same thing.  The Gallery script vs. MyExcelHotfixCompare. These are different things for different reasons. But its good to have them in the same place, Because they both have their own purpose.

 

 

First thing you want to do have is a copy of the spread sheet, MYexcelHotfixCompare , and a copy of the The Gallery script

 

Why Two Comparisons

So I made one up, and I found the other by accident! That is true. However, I discovered these both served different purposes.

The First method came from Stane Monsik. What this count does, is aggregate the KB’s for every server in the survey. So your Final report answers the question “when you updated Server Group X with KB Group Summation(A-Z), did you do that same thing to all the other servers? This is what I started out to have answered! If the answer to this is no, then I always tell my customer to finish his updates right away.

However, another question I was often asking, was not being answered by this script. The Questions was, When we know that there are X updates packages for Technology Y, how many of those updates did you apply to your server? Then Question #2,  For X and Y, did you apply these updates to just the server which had problems? The answer to this question should be that you updated all the servers which contained technology Y, with every Update Package X. I found this question was often met with Guesstimates more than actual proof; this was an easy question to get a good guess on. It’s also an easy question to just accept the guess and move on.  In today’s complex world, that is no longer acceptable. So I set out to easily answer this kind of question.

 

Method1 Script

This script will take “a list of Machines” and show you what updates the entire list has and does not have. Every KB gets added to the list, no matter what server it was found on.

The Script, located at Gallery.Tech.net.Microsoft.Com , Goes to every server and pulls the list of hot fixes. Then it goes through the hot fix list and checks every computer, to see if the hot fix has been applied to the computer.

To make GetHotfixCompare_Gallery work, all you have to do is open the .PS1 file with notepad and add your list of computer names. You can add as many computers as you like, but realize it takes longer and longer and longer to complete. The text you are editing looks like this in the file:  See Figure 1. :

# —————————————————————————————————-

$computers = “ComputerName1”, “ComputerName2”, “ComputerName3”, “ComputerName4”, “ComputerName5”

# —————————————————————————————————-

The Result is a very nice HTML report:

 

Figure 1.

1comparetable

 

Once you populate the script with edits on the names of computers, you run the PS1 file by right Clicking the file and choosing Run With Power Shell. The Script runs (longer the more computers you select), then generates an HTML file of your KB’s. There is an orderly chart of stars telling you which KB’s have stars for the machine, and which do not have stars.

 

All in all, A  very good method. But in order for me to answer the technology related questions, I needed to use something different.

 

Method 2 Spread Sheet comparison (MY_excel_GetHotfixCompare)

On the Excel Help article, called “Compare two lists and highlight matches and differences” I use Example 4 of that section. Example 4 runs a comparison from column one (the update list of a server (get-hotfix)  against column two (the list of updates for the specific technology) .  The comparison Colorizes the two list columns to highlight where the updates match or are unique.

MY_ExcelGetHotfixCompare Is the name of the spread, and you just need to right click and save as to save the file as an XLSX file.

This method will always work, as long as you can find a list of key updates, Microsoft says is relevant. This list below is not unabridged. This is just areas where I work most often. Below are some Example Technology Patch lists, that will power column two, on your spread sheets.

Server 2012 and Exchange make you dig a little bit to get the actual KB list, but you get the Idea. The point is to get the list of “important hotfixes” and be able to use them to “audit” you current situation. So let’s get to the formulas and make a spread sheet!

 

Get Hot-fix Compare Spread Sheet

The Compare column Colorizes numbers in column A , that are also in column B. And Vice Versa. So there is only one caveat to this method. If the KB shows up twice in the same column, then the cell becomes colored, denoting that the patch has a match. But, This is an impossibility of the get-hot fix command. As long as your pasting in get-Hotfix results from only one machine per column, you will get a valid result. So the limitation of this, or any tool reviewing hot-fixes; don’t try to do two machines in one spread.

Steps to create:

  1. Open Excel and put your Values for Get-Hotfix (just the KB) into column 1.
  2. Copy the Hot-fix numbers for the relevant Microsoft KB numbers from your articles like above, into Column 2.
  3. With Excel at the Home Tab, Choose “Conditional Formatting” and Manage Rules . You will see a pop up window, New Formatting rule: (figure Below)

 

2spread

 

  1. Choose New Rule and “Format only unique or duplicate values”
  2. There is a little Drop down that says Duplicate or Unique- You will make that choice here

3spread

 

6.Then you see where it says no format set? (above) This is where you choose your color.

7. Hit the Format button. Then choose the Fill tab.

8. Now see the Fill tab below Choose your color and hit Ok.

3color

 

9 Then Choose OK Below

5

 

10. Now Comes the important part. You simply highlight all values in both columns and there is a wizard that will form the formula for you based on how many rows each column has in it. I am showing the screenshot below, after I clicked into the “applies to field” and held down the left click of the mouse while I selected both columns and went down enough rows to cover all values in both columns. I also found that if you pre-select both columns, before clicking conditional format, then these vales will be there automatically

6

11.  The only thing you can’t see in the picture above is the dashed line that is now hovering over both columns. Once I hit OK or apply, it all goes away and my formula is:

=$A$3:$B$13

Pre-Select your columns before you start with Conditional formatting

Now I found out some weirdness about the formula. If you have to go back and change the length of the column, you have to make the formula a little differently.  You click into the applies to field

and highlight Column one, then you type a comma, and then you highlight column 2. So the formula looks like this

=$A$3:$A$13,$B$3:$B$13

The more general solution is coming up below:

=$A:$A,$B:$B

I like the second formula better, because you can see the start and finish of each column. But the more general one covers all KB in both Columns. cant beat that. I am editing this sentence,

after the fact, and I found that if you select the columns, by Highlighting the columns entirely, you don’t have to deal with touching any formula aspect at  all.

Here the download again for My Excel HotfixCompare , You can also watch the video at the top and make your own spread.

Believe it or not, that is it! When you hit OK, your values immediately calculate out:

7

 

The next issue I tried to solve was the length of the column. It looks to me that this is the master formula, no matter how many KB s you have  (=$A:$A,$B:$B). This is for column A and B.

I do believe you could replace these values with columns a and c or b and E or whatever. This can help you move columns for your ease of use and you list can be as long or short as you want.

So use this in your Duplicate Values formula:

=$A:$A,$B:$B

Again, I am coming back after the fact, and just saying to Highlight the columns you want to use, before you start, and you don’t have to deal with the formulas at all. I have a second Excel

Method for comparing up to 6 servers, but I will let you just download the Excel File and see how it works!!! Watch the video, I performed that in about 1 minute in the video. also

after the fact, and I found that if you select the columns, by Highlighting the columns entirely, you don’t have to deal with touching any formula aspect at  all.

 

How do I get my Hot-fix list = Get-Hotfix

So this is all we have left to cover. Below these are the steps I use to get that list of KB numbers isolated from the rest of the information you find when you run the Get-Hotfix command.

 

1. Open PowerShell as Administrator
2. You may run Get-Hotfix > c:\myhotfix.txt
3. Open Notepad ++ and Open myhotfix.txt
4. Hold Control and Alt Key on your keyboard at the same time
5. While (4) use the mouse to select just the KB column
6. once the KBs are highlighted Right click and choose copy.
7. Paste you KBs into notepad for transport to your work station.

That’s It! I hope you have enjoyed this article. In closing I have the original link for the script in the initial discussion. I also have a method to update your Hyper-V Cluster automatically with a script. That can be pretty handy.

 

Advertisements

Windows Server 2016 Licensing Is Easier With a Little Help From a Calculator

Hello,

Fortunately the industry is coming to our aid for answering questions about how many licenses you need for your 2016 Standard or Data-Center Server.

This is not a long post. I am just passing on the few methods I have ran across for getting the total licenses you need to purchase.

You can do it yourself with the Excel Download. That link is here, courtesy of Dell EMC INC.

Secondly, you have the Microsoft entry, which is also a Spread sheet, but its starts for your convenience here


Since I put a line above, I figure I need some special content, so how about a Video about the New 2016 Licensing?

Figure 1. Video on 2016 Licensing and Ramifications

If you watch the above video, you will get a good idea how useful the 2016 Licensing calculator will be. You can even use it to correct what you hear from others. some sites seem to message differently to others.

*update 7/29/2017. So I did find some other documentation (in the fine print) that tries to use 8 cores and 16 cores. So I could move as far as “the use of 8 as a starting point for processors, would not be false, but would be ambiguous” . The use of  expressing the minimum license in physical cores, does not fit in with most of the documentation, so the possibility of mis-understanding is maximized here.  So I am backing off on saying the MS blog is incorrect, but i am not changing the video because that video is lost time in my life, but second, the chart I show below is ambiguous, and serves as a good example to test the licensing calculator out. My take on that chart would not be an uncommon interpretation. See the chart from the Video below:

 

Figure 2. Ambiguous chart when compared to Licensing documentation.

 

To support where Microsoft is meaning with using 8 Processors as the starting point for a 2016 License, see this 2016 Datasheet.

This comes from Pricing and Licensing for Windows Server 2016

If you read this 2016 Datasheet  article, I think you will agree with me, that it says in black and white;  It says the you need to have a minimum of 16 Core License per server.

I am putting the fine print from the data sheet below. Do you come out thinking 8 or 16? then watch the Video. Does the MS blog look correct against these fine print Items below:

** All physical cores on the server must be licensed, subject to a minimum of 8 core licenses per physical processor and a minimum of 16 core licenses per server.

** CALs are required for every user or device accessing a server. See the Product Terms for details.

* Software Assurance is required to install and use Nano Server.

**  Pricing for Open (NL) ERP license for 16 core licenses. Actual customer prices may vary.


 

Now if you watched the Video, and saw the chart above, you probably want to see if the chart is right or wrong! Go ahead and get your calculator here !  I found a Microsoft version of the Spread Sheet. Dell and Microsoft look very similar indeed. It does say OEM windows so they may actually be the same. The 2016 Windows Server Core Calculator should be easy to find.

Enjoy reading and have a great Day!

If you want to get into the changes from old until now, or you want to get into the philosophy of why this change, you can look for some opinions and facts online. I found a pretty good one here, which i used for my own knowledge. The link here is from the Tech-net Blog and it is something you should go though and understand. Go ahead and correct it with the Calculator, and you can see how the 2016 License is going to affect how you purchase your Operating System.  Enjoy correcting your price projections!

Louis

 

 

Edge Replication Status is false and the Last Update Creation time stops updating for command get-csmanagementstorereplicationstatus

When it comes to Edge Replication checking, this looks like a false positive below. But I know we all like to see true. So you see where the date says 6/22? That change means the last status report was a few month’s earlier. That missing update creation, is possibly saying the replication is not working. this is not hard to fix, so lets fix it!

 

Perform the steps below:

  1. Go to the Front end server and open Skype Management Shell
  2. Run the command Export-CsConfiguration –Filename C:\filename.zip
  3. Copy the file to the Edge Server.
  4. Open the Skype for Business Deployment Wizard
  5. Choose Install Or Update Skype for Business Server System
  6. Choose install local configuration store.
  7. Browse to the file and finish the wizard.
  8. You can restart the Edge Server or just wait several minutes.
  9. If this fails, you just need to restart the SFB replication service on the FE and Edge Servers.

 

This is the point at which you browse to the configuration Zip file. Its Step 7.

I hope this helps your issue. I have seen this just stop refreshing and this step normally fixes the issue in my experience.

 

Louis

How to repair Software service won’t start on a domain controller or Windows software protection will not start access denied 5. on server 2012 R2.

Good day all,

I had the strangest activation issue today. I decided to detail the issue If I ever see it again. So I must admit, the whole idea came from searching the core team blog. My issue was the Software activation service would not start. This resulted in all of the activation related items failing from the customer perspective.

My particular error code was a little different, but the error verbiage was the same. I considered the verbiage enough to try a few things, and I found success. Its always important to share success.

The Core Team can solve your issue without me, so feel free to consult their article. I am just showing the folder and registry locations I needed to add the SPPSVC to, for my protection service to start. This is apparently only on a Domain Controller, Hence the name.

The Key was to add the NT Service\SPPSVC to some specific locations, both in the Folder system and the Registry. There was a little trick here. You have to deselect the domain, when choosing the account. This caused me to wallow along for much longer, as I never thought of doing this on my own power. That is where the Core Team Saved me. Thank you guys.

Screen shots of my 42DC Server look like:

 

 

Just to be clear what I am saying, you are to not use active directory groups when making your search. If you do, the NT SERVICE\SPPSVC will not be

there. So select your local machine and add your group. It should be there.

 

So now we understand how to make an otherwise mysterious user account show up on a Domain controller, here we go with the folder and registry locations.

  1. The Store Folder Located:

C:\Windows\System32\spp\ – Right click and chose the store folder permissions

2. The SPPSVC registry folder located at the  SPPSVC folder

Regedit\Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SPPSVC

3. The SoftwareProtection Registry Folder located at

Regedit\Computer\HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Software Protection

4. This one, I don’t know if this was necessary, so I would not do this unless it was your last gasp:

  • Take ownership of C:\Windows\System32\SPPSVC.EXE
  • Make sure you screen shot original permissions and put them back
  • Add SPPSVC as needed.

Those were the locations I found. Now I did not find this all on one MS KB, so I certainly don’t recommend this is a true fix for an issue. I simply found this in the moment of trying to get a customer back into functionality. Please look to the core team for updated information. This will supersede anything I have here today.

 

Update 6/22/2017

 

So in my one particular case, there was an additional location that changed. I had to find it with PROCMON by sysinternals. Using Procmon, I followed the MS blog to capture the traffic. I did not even need to filter the traffic. It clearly showed the WPA folder in the registry was missing a permission. Since the Service stared, after I made this change, It was definately the network service that was missing:

Here is the Key Location in regedit:

Computer\HKEY_LOCAL_MACHINE\SYSTEM\WPA

 

I hope this may help get you out of a Jam, and I hope your licensing functions well.

Louis

Das Cache – Help Support Das Cache Sandisk

Good day,

I wanted to write a little something about DAS Cache.  You may not have heard of it. In fact, Unless you purchase a Dell Server, you may not ever be exposed to it. Apparently, When you purchase a Dell Server, you may purchase an application called Das Cache.

Please be advised the support for this product is available only if you have a Pro Support Contract with Dell. See the sales information on this product here.

“San Disk DAS Cache software is fully supported by Dell Services and available with the purchase of a next-generation Dell Power Edge server”

 


Tell Dell Support you have Das Cache

After Spending a little time with this product, the main support incident happens when the support person is not told that Das Cache is installed on the system. The fact is this product is simple and will not likely fail. But, the key point is when Das Cache is Deployed, It takes the Place of windows, in terms of manageability and control.

What happens is either the IT person or the support agent treats this disk as a Windows Disk, trying to run operations on the disk directly. You can’t treat a DAS Cache Disk as a windows disk. I am thinking, this first fact is the cause of Support-ability problems with the product.


Use the Das Cache GUI

The Gui of Das Cache is simple. You simply select a Disk to use as the scratch disk, and a volume, which serves as your data volume. Das Cache keeps copies of the most used data, on an SSD Drive. I found that it doesn’t have to even be SSD. It could be just two Sata Jbod Disks.

The way you set this product up Is you select “add Cache” at the right side of the application window. You select disk 1, then disk 2, and finish. The product is now using Disk1 as a cache for the data volume Disk2.

If you want to do anything to that data disk, or the cache disk, the only option you have is to use the GUI, and assign or un-assign cache from the cache tab:

1

Or you may stop or start acceleration from the volumes tab:

2

The only other thing you can really do is reset performance statistics or Build an Incident report. This menu is off to the right hand side, of the GUI interface.

3


What troubleshooting vs. recreate Cache

In all honesty, the amount of troubleshooting that can be done on this simple product, is very limited. The danger of this product is if you try to go into windows and do things to this disk. Instead of doing that, you can just come into the DAS Cache GUI, stop accelerating, and then removing caching. Then you can come in and create the same cache, and choose start accelerating. That should be the extent of troubleshooting.

Most Common errors

My understanding is the most common errors with this technology is File Server Resource Manager errors. For example, here are some known issues with FSRM.

In reviewing File server resource manager, My advice would be to disable it and find out if the errors stop. If they do, then, it would be a question of disabling specific settings in FSRM to find out, what setting is incompatible.

I could see certain aspects of FSRM not being happy with Das Cache. On the other hand, these errors are likely false positives, as the Cache itself is not part of the actual File Storage system. As long as the Cache is working. I would say you just have some erroneous errors to contend with.

In the most extreme, you should just stop caching, stop accelerating, and perhaps re-configure the cache. The only reason this technology would have any trouble is if you made changes to the disk inside windows. Just make sure you use the DAS Cache GUI only, and you will be fine to troubleshoot with that only.

Take Away

To troubleshoot with windows, you should remove caching and acceleration, to troubleshoot the disk, by itself.

In conclusion, I hope this is helpful, if you have to take this your support team, just make sure the first thing you tell them is you have a Das Cache setup. That is an important point. Otherwise, that leaves the support team looking at, what they think is a Windows disk, with some very unusual failures.

Thank you

 

Louis

 

#I work for Dell Services.

Some tips on fixing Warning – Reverse DNS does not match the SMTP Banner

 

 

I have a pretty common error that I get asked about pretty frequently. I wanted to take a moment to hopefully share some information on what the error is, what to focus on, and what tools you need to fix and monitor.

First of all, please understand this paper covers the simplest of scenarios. Multiple sites, Smart Hosts, Bridgeheads, and multiple Accepted Domains will quickly muddy the waters, but for a basic Exchange Server, This Article Applies directly.

 

The Error

Exchange Server 2013 SMTP banner does not match reverse lookup. or

Warning – Reverse DNS does not match the SMTP Banner

 

Disclaim

First be aware, there is a lot of misinformation out there. Stop and read and understand, before you decide which articles are telling you the truth. This error is likely to pop up in a few situations. I wanted to take a minute to clarify this message and what is needed to clear this up.

First you must understand this error  is directional and relative to a point in mail flow. So you really have to nail down your situation before you set out on solving the problem. You risk getting yourself more confused. Speaking of that, let me try to hopefully explain in a simple way.

First let me say the SMTP Banner is more generally a problem for outbound mail. You may still get an error for inbound connectors,  but mail will not usually fail either. Internal mail uses Internal banner (host) and DNS, and external mail uses External Banner and DNS.  An error comes about, generally where you have mail received across the public internet, where a reference is made to an internal FQDN in the SMTP Header.

Inbound Banner

So if you think you have an inbound banner issue, just go into your inbound mail connector, and then try to save it, without making changes. If there is a problem, you should get a pop up message similar to figure A

Figure A. Inbound Banner issues are identifiable

 

Exchange will promptly give you an error when your inbound connector has a banner issue. Why you ask? Because  the Banner is checked by Exchange, against the security settings.  Think of it like a security Guard. They always check you coming in, but once you have cleared security, it is not as difficult to leave.

So I won’t go into the explanation of inbound banners, except to say, by the time your mail hits this server, the lookup is internal, so the Banner should always be internal. In addition, you have a server, with a certificate, matching this FQDN, so it should make sense that these should all be the same name. Do what the error says and set the Banner to the Internal FQDN.

Outbound Banner

Outbound is really the same sort of thing, for any outbound Internal Connectors. Internal connector, Internal FQDN. The change comes when you have an outbound Internet connector. So this connector will be the banner for your reverse look ups by external recipients. That is, unless you have a third party device doing store and forward for you, in which case, you should be able to set the SMTP banner there as well. Assuming you don’t use a smart host, your Send connector header would look like this:

 

Figure B. Send Connector Scoping Tab.

 

This should make sense. You see this is the external facing send connector. Once mail leaves this connector, the mail will be called External Mail. From this point mail will have to rely on MX, DNS or a Smart host to propagate.

So.. What do you think gets queried for the reverse lookup? The mail server at the destination Is going to query public records it finds, against the header and other information it has received, when it looks your mail domain up. So the checks done include reverse lookup, Public MX record, A record, Text Record and SPF record. So all you need to do to is make sure these records contain your correct Public IP address for your Exchange server, the correct resolution of the  Banner to an IP address, and verify the other records contain the same Name and or IP addresses.

A light conversation

So now we get to brass tacks. So I want to focus you to the main things you would need to set correctly. This is:

  1. Public MX record -Domain.com resolves to target mail.domain.com at PUBLIC IP address
  2. An “A Record” that is the value of the Banner “Mail.domain.com”
  3. An “A record” for values for your setup like “auto-discover.domain.com”
  4. TXT or (PTR) record for your Reverse Lookup DNS record. One domain should be assigned to one PTR record- this is what should match the “send” banner
  5. SPF record. – . Special record with special format for Domain verification by Anti-Spam. SPF record tool will help generate your record

Tools you can use to make sure your records are correct:

  1. Install Dig on your client machine for windows- Dig -x Public IP (will find your PTR record)
  2. Dig domain.com will give you your “A” record.
  3. Dig mail.domain.com txt – will show your SPF record.
  4. Dig mx domain.com to query MX record, or Dig @nameserver.domain.com yourdomain.com

So with this Dig tool, you can check and cross check. If you have an IP address in this mix, that you are not aware of, or are not using, then you will need to fix this.

I am not going into too much detail here, but if you have all these records in place, and make sure they point to the public IP address, which sends the exchange server its mail, then you should be happy. Use the web site IPCHICKEN.COM on your Exchange Server. It will tell you your Public IP, normally used for Setting Public DNS records. For non-smart host or bridgehead customers, your value of IPCHICKEN, should be your Public IP values for these records.

In Closing

You have the public information you need to set records above. Set this correctly. Second, go to Exchange Server and set the FQDN correctly and you should no longer have SMTP banner failing to match the reverse lookup:

  • Send Connector Mail Flow -> Send Connector-> Scoping-> FQDN
  • Receive Connector  Mail Flow -> Send Connector-> Scoping-> FQDN

Make sure these FQDN matches its function. Internal connector is internal FQDN.

Send Connector is Public FQDN. Then make the Records match the correct public values and this issue will be resolved.

In closing Here are some tools you can use to troubleshoot:

Exchange Connectivity.

Dig Bind Tool

MX Tool Box

I hope this is helpful and explains what you are seeing, and how you can fix your SMTP banner issue.

Thank you,

 

Louis

 

 

 

Use a Baseline Database Generator Script for reviewing performance of SQL Instance

Use a Baseline Database Generator Script for reviewing performance of SQL Instance

For anyone trying to troubleshoot a Slow SQL server, I wanted to come up with a test that will take the SQL issue and generalize it. Why does this need to be generalized? I have found that a customer or a support team may introduce a bias in all aspects of the tests. Begin with the Data. Data is impossible to to show a unique result. You may say this database does not go as fast as my favorite one, on a separate server. you cannot accurately prove one server is faster or slower then another server. Why?; for a basic Idea, take look at another case, where I lay out some basic testing tenets to go by.I will re-state them here. They sound like car rules, but they are universal testing rules you can apply to any situation.

From Car Rules to Computers

  1. The performance should be documented and repeatable.
  2. More than one test should be run, and simple is usually more realistic.
  3. Tests should be standardized, down to a science, so that if applied to another matching scenario, you would expect similar results.
  4. Keep the time down to a short test. The longer the test, the more variables can be introduced.
  5. Do not focus on two separate car models not functioning the same, find a way to introduce a baseline into what a reasonable car will perform like. Then prove or disprove your baseline.

In order to get a good unbiased test result for SQL, I came of with a dynamically created SQL database, that gets created once. Once Created, you can run some test on this standardize database, and compare with results on, say your laptop, or another machine, where your processor, Memory and disk resources are similar. All you have to do is to follow the method. One simply must not use ones own data.

Read on-Grab the download from here or the top of the page.

The SQL Baseline for Customers who report server A is slow then Server B.

Disclaimer

When a customer claims that one machine is slower than the other, there is always the possibility the customer has an actual baseline. However, when they say one is slower than another, this usually indicates they don’t know what a baseline is.

A Baseline is a collection of metrics, about the server, when it is installed at Greenfield time. When the Server is first Deployed with SQL, a baseline should be taken. Then, future claims as to a slow server, should be taken against itself; not another server.

When a person wants to compare two servers, this is almost an impossible ask. It’s like asking us to compare why two people do not complete a personality test in a similar way. From a support standpoint, it is a fruitless pursuit, and often creates a bad CE, in trying to fulfill their request.

The goal of this process, is to give Support and the customer a way to meet on common ground. The customer claim that the server is slow may as well be translated into, The Data on my servers does not match!! And they are correct. And we don’t support data. The Key word is Data. This question of “SLOW-ER” pulls us into the customers’ data sets.

This process gives us a way to use our own data set. The advantage cannot be understated. We will be telling them one machine is slower or it’s not.

Accepting that generally one machine is slower, do not underestimate this result, as the customer re-introduces his production elements. If the Baseline test show a machine 20% slower, then any difference, more than 20%, will be due to specific workloads introduced by the customer. All of the SQL Subject matter experts have known this, but we all spend weeks trying to find the leverage to prove it. Without an “absolute”, we could not substantiate that claim. This caused these cases to last for months. This method below, should cut these cases into a two day case, at most.

 

The Process

 

In the following test for SQL you will see four files, which compose of a method of base-lining SQL performance, without using Bias Data from the Customer, or a third party company. This Test Is devoid of the implication of using cashing or indexing, so it is a perfectly simple test to illustrate capabilities between two machines.

The reason this test has been devised is due to customer demand. Customers often ask us to compare two adjacent machines. Often times these comparisons can only be done using apples to oranges methods. Often times, these cases end up being a point of contention for the customer and for support teams. The goal of this test is to mitigate that disparity.

 

Here are the files you will need

Figure 1. Files you will need

 

SupportBaseline.xlsx

The Excel Spread sheet is to be filled out and returned to Support. We keep a master copy of this spread to monitor the scripts performance against a variety of machines and situations. Over time, we will have a database of how this script performs, on average, across a multitude of platforms. And the simple measure we are obtaining, is time. How long does it take the baseline query to complete?

 

Test Parameters

The Results of the script, should answer the question, is my “server” really slower than average, or slower than another server? In order to do this, strict adherence to rules must occur. This test must be run, with all other operations terminated on the SQL server. There should be no Antivirus running, there should be no other applications running. Other than a baseline Windows machine, with core applications and services running, the server should be running SQL with no client connections. In other words the SQL machine needs to be out of production.  There are columns In SupportBaseline.xlsx, but it will be noted in the analysis, that the machine was in production, and the results may not reflect a true baseline.

Several baseline runs can be collected with the single variable as the total number of rows, this script will create. The default is set to 1 million. The recommendation is a million rows, on average. However, depending on how powerful the server is, or how much down time you are allowed, you can adjust this variable to fit into your needs CreateSupportTest.sql is the file where this change is made see below.

 

Figure 2. Where to adjust how long the script will run

How long do I run the initial test?

As a general rule, 1 million rows should take less than 15 minutes on a reasonable SQL server. However Performance degrades fast. For example, A SQL VM with only 3 GB of ram, will take 121 minutes to run the query. So the first run should be 100,000. Then multiply the length of time it takes to complete by 10.

This is how long a million rows should take to complete. You can judge how many rows you should choose, depending on the amount of time you want the query to take to complete.

Process

  1. Determine how long you want to run the query. Follow How long do I run the initial test?
  2. Set the value of the # of Rows. Follow Test Parameters
  3. Record the initial values of the server in the XL spread SupportBaseline.xlsx
  4. Run the Query named CreateSupportTest.sql here is a how to if you need it
  5. Record the results in the Excel spread sheet SupportBaseline.xlsx. use start and stop time and it will auto populate the time of execution
  6. Repeat as necessary, populating the spread sheet, and returning to Louis Reeves in Support support. He is keeping the overall list of how the query runs in several different scenarios and can give you more information about how your query results compare to other machines running the same query
  7. When you are finished testing a server, there are two scripts that are cleanup scripts. Run DropSupportTest.SQL. here is a how to if you need it
  8. Then run DropSupportMaster.SQL. here is a how to if you need it

That’s it, Now you can complicate things, by running things like Diskspd against these machines, but, it will be best to just keep it simple and stay with the program laid out. If you desire to look at diskspd, go ahead and read The Fallcy of Performance or; Are you bringing your Support Agent Apples or Oranges? This will help you the plan for running Diskspd commands. So here you really have two ways to testing the claim of a SLOW:

 

I hope this series of articles is helpful in troublsehooting issues with model data.

Louis.