Erika loves having fresh flowers around the house. Every Saturday morning, I pick up a bouquet at a farmer’s market or grocery store and put it in a vase for her. I’m slowly upping my game by learning more and more about the art of arranging flowers.

When I say flowers, I bet you think about the English Garden style: a big, complex vase with all kinds of flowers crammed into every nook and cranny. It’s an explosion of color and life.

That’s way too stuffy for us. We’re into minimalism, clean lines, and letting materials speak for themselves. I like plucking one or two of the more beautiful or unusual flowers and putting them in their own vase. This leans toward the Ikebana style of Japanese flower arrangement, specifically the Nageire type. (I don’t even want to think about how badly I’m going to mispronounce these if I ever have to say them out loud.)

Writing database code is like arranging flowers.

If you show someone your bouquet, they might not like it. They might give you a million reasons about why it’s not right or why another way is better. That’s not the point – does it produce the results you want?

If your goal is to get to market quickly and cheaply, just buy a premade bouquet from the grocery store, throw the flowers in the vase and be done with it. Use LINQ, Entity Framework, NHibernate, or whatever code tools make your job easy.

If you translate your app code into SQL code, you’re building an English Garden. You start by declaring variables at the center, then populating those variables by checking configuration tables, then spin out to more and more other tables, getting your results in loops and setting values one at a time. This is exactly how developers have always been taught to arrange their flowers, and it works just fine. Once you’re used to doing it, you can bang that code out quickly, and the results are attractive.

But if you need it to be beautifully fast, you need Ikebana. You need very clean, very minimalist code that gets the job done in as few statements as possible. In a database environment, this means set-based code that avoids cursors and loops.

While clean, Ikebana-style database code is simple to behold, it’s deceivingly complex to build. The first step is moving as much logic as possible from the database server to the application server – starting with the ORDER BY statement. If you’re not fetching just the TOP X rows, then do all sorting in the application server. Removing just that one line from a query will often cut its cost dramatically. Your development platform (.NET, Java, Cobol, whatever you kids are using these days) is really good at scaling out CPU and memory-intensive work like sorting, and you’re already really good at splitting out your work into multiple application servers. Leverage that capability.

Think of it like pruning your code – remove all the things that database servers don’t do beautifully, and what you’re left with will be gorgeous.

...
Just check the boxes to see Bad DBA Job Detector, Top 3 Indexing Mistakes, and the DBAReactions Guide to Database Administration.

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

THE GRANT question

The third question in the list is:

Can you please expound on the difference between “Grant” and “With Grant?”

When you GRANT something within SQL Server, that provides the permissions required to access the object, or securable, that is included in the statement.  For instance, a GRANT of read permissions on a tables allows the person to read from the table.

The WITH GRANT option, requires that Grant Fritchey (Blog | @GFritchey) show up for any of your permissions to work.  No, that’s not right.

In all seriousness, the WITH GRANT option allows the person who has been granted access to an object to GRANT that same access to other principals.  It is basically a method for delegating permissions when you have someone you can trust to manage those permissions.

Generally,  I’m not a big fan of this option.  The main reason is that, as a DBA, I’ve tended to be more concerned with who has permissions within a database compared to others.  The frequency in which I’ve seen others over grant permissions has been disheatening, especially when I was an FTE DBA and working on trying to constrain all of the run-away permissions.  That said, as a team member, we can’t always hold all of the cards and granting a junior DBA or team lead in another department the option to grant permissions through delegation can be especially handy.

Demo

To demonstrate how WITH GRANT works, let’s run a couple GRANT statements in AdventureWorks2012.  We’ll start by creating two new logins, WithGrantLogin1 and WithGrantLogin2, using the script in Listing 1.  The last part of the script includes a GRANT statement for WithGrantLogin1 provide access to Sales.SalesOrderHeader.  At the end of the GRANT, notice that “WITH GRANT OPTION” is included.  That is the clause that allows WithGrantLogin1 to have the permissions required to assign that GRANT to other users.

--Listing 1. Create new login
USE [master]
GO
CREATE LOGIN [WithGrantLogin1] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

CREATE LOGIN [WithGrantLogin2] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

USE [AdventureWorks2012]
GO

CREATE USER [WithGrantLogin1] FOR LOGIN [WithGrantLogin1]

CREATE USER [WithGrantLogin2] FOR LOGIN [WithGrantLogin2]

GRANT SELECT ON Sales.SalesOrderHeader TO [WithGrantLogin1] WITH GRANT OPTION

To test this out, open two query windows and login to each of them with one of the two new users.  After doing so, run the script in Listing 2 in each of the two query windows, once for each of the users.  As you might expect, the query will succeed for WithGrantLogin1, but fails for WithGrantLogin2.

--Listing 2. Query Sales.SalesOrderHeader
USE AdventureWorks2012
GO

SELECT * FROM Sales.SalesOrderHeader

In the query window for WithGrantLogin1, run the script in Listing 3.  This script; which does not include the WITH GRANT OPTION, will GRANT read permissions to WithGrantLogin2 on Sales.SalesOrderHeader.  You should now be able to successfully run the query in Listing 2 for WithGrantLogin2.

--Listing 3. Create new login
GRANT SELECT ON Sales.SalesOrderHeader TO [WithGrantLogin2]

For the DBAs reading this, one of your first concerns with using the WITH GRANT clause for the GRANT statement is auditing how permissions have been assigned.  Fortunately, through SQL Server’s catalog views, this information is easy to discern.  With the catalog view sys.database_permissions, there are columns that identify both the grantee and the grantor for permissions.  Joining the view to sys.database_principals provides the names associated with the principal_ids and the names associated with accepting and granting permissions.  Using the query in Listing 4 and output in Figure 1, you can see that there is an audit trail of both who has and who granted permissions.

--Listing 4. Review permissions and the grantor
SELECT dp.class
,dp.class_desc
,dp.permission_name
,dp.state_desc
,OBJECT_NAME(dp.major_id) AS object_name
,dp2.name AS grantee_principal
,dp1.name AS grantor_principal
FROM sys.database_permissions dp
left JOIN sys.database_principals dp1 ON dp.grantor_principal_id = dp1.principal_id
left JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id

Figure 1. Sys.permissions output

Summary

When it comes to granting permissions, using the WITH GRANT OPTION might not be my favorite security command.  But it is a very useful option for delegating permissions, and potentially freeing DBAs from mindless tasks to more fun tasks, like performance tuning.  Properly leveraging this option is something that definitely should be a consideration.  What do you think?  Do you use the WITH GRANT OPTION in your environment?

You can make decisions as you design and deploy your Group Policy Objects (GPOs) that will have an impact on how quickly your Windows desktops start and become usable to your users. Some of these decisions are obvious, while many are not. This post will guide you through some of the ways Group Policy can impact performance as well as some of the improvements we made for Windows 8.

This post (including the introduction) is a partial reproduction of a white paper written by one of our Group Policy MVPs, Darren Mar-Elia, with some additional comments and information. Darren has also written a free tool to help identity if your configuration might be susceptible to long logons.

This post covers:

•          Foreground vs. background processing.
•          Synchronous vs. asynchronous processing.
•          GPOs with WMI filters.
•          GPOs with scripts.
•          Group Policy Preferences.
•          Actions you can take in Windows 8.

Foreground vs. background processing

Foreground and background processing are key concepts in Group Policy. Foreground processing only occurs when the machine starts up or when the user logs on. Some policy areas (also called Client Side Extensions (CSEs)) can only run during foreground processing. Examples of these include Folder Redirection, Software Installation and Group Policy Preferences Drive Mapping. In contrast, background processing is that thing that occurs every 90 or so minutes on Windows workstations, where GP refreshes itself periodically. Background processing happens, well, in the background, while the user is working and they generally never notice it. While background processing doesn’t impact performance, foreground processing can extend start and login times. This post will be focusing on foreground processing.

Synchronous vs. asynchronous processing

Foreground processing can operate under two different modes—synchronously or asynchronously. The default foreground processing mode for Windows clients since Windows XP has been asynchronous. Asynchronous GP processing does not prevent the user from using their desktop while GP processing completes. For example, when the computer is starting up, GP asynchronous processing starts to occur for the computer, and in the meantime, the user is presented the Windows logon prompt. Likewise, for asynchronous user processing, the user logs on and is presented with their desktop while GP finishes processing. The user is not delayed getting either their logon prompt or their desktop during asynchronous GP processing. When foreground processing is synchronous, the user is not presented with the logon prompt until computer GP processing has completed after a system boot. Likewise the user will not see their desktop at logon until user GP processing completes. This can have the effect of making the user feel like the system is running slow. In short, synchronous processing can impact startup time, where asynchronous does not.

Foreground processing will run synchronously for two reasons:

1)      The administrator forces synchronous processing through a policy setting. This can be done by enabling the Computer Configuration\Policies\Administrative Templates\System\Logon\Always wait for the network at computer startup and logon policy setting. Enabling this setting will make all foreground processing synchronous. This is commonly used for troubleshooting problems with Group Policy processing, but doesn’t always get turned back off again.

2)      A particular CSE requires synchronous foreground processing. There are four CSEs provided by Microsoft that currently require synchronous foreground processing: Software Installation, Folder Redirection, Microsoft Disk Quota and GP Preferences Drive Mapping. If any of these are enabled within one or more GPOs, they will trigger the next foreground processing cycle to run synchronously when they are changed.

Action: Avoid synchronous CSEs and don’t force synchronous policy. If usage of synchronous CSEs is necessary, minimize changes to these policy settings.

GPOs with WMI filters

If you must use a policy setting that triggers synchronous processing, another area to take note of is the use of WMI filters. A WMI filter does not automatically slow down GP processing appreciably, but if a WQL query implemented in that filter is time consuming, it could slow down the startup or logon  process, This is true especially if that workstation is running Group Policy processing synchronously. A few long-running queries can extend the amount of time it takes to finish processing Group Policy. Costly WMI filters include those that rely heavily on network traffic, such as LDAP queries.

Action: Avoid costly WMI filters such as LDAP queries

GPOs with scripts

As with WMI filters, long-running scripts might impact performance during synchronous processing. Startup or logon scripts are not by themselves always a problem. But too many scripts running for a given user or computer, or scripts that hang or are no longer really in use, can add to startup and logon times. Frequently, people don’t recognize the number of scripts that are present. Try looking through your environment for startup or logon script policies and reviewing them to ensure they are responding (scripts that aren’t responding will not timeout for 10 minutes, by default) or otherwise taking a long time to execute.

Action: Look for costly scripts by running them in isolation and work to improve the scripts’ performance or avoid these scripts

Group Policy Preferences

GP Preferences settings that use Item- Level Targeting (ILT) are not inherently harmful. However, certain kinds of Item Level Targeting queries can take more time to run. You can find these targets in any Group Policy Preferences setting under the Common tab. Costly ILT evaluations include all of the ILT types that must work over the network against AD to be evaluated: OU, LDAP Query, Domain, Site and Computer Security Group filters.

Actions:

•          Don’t run ILT evaluations such as OU, LDAP Query, Domain, Site, or Computer Security Groups.
•          If you need to use security group filters, consider this KB article.

Similarly, Group Policy Preferences Printers can take some time to install a printer driver. If a printer item is set to “replace,” it will re-install the printer driver every time it runs. If you are deploying several printers at once, this can add up quickly. Instead of “replace,” consider using “create” or “update.”

Action: Don’t use “Replace” with Group Policy Preferences Printers.                 

Actions You Can Take in Windows 8

We made changes in Windows 8 to give greater control over synchronous processing. Synchronous processing takes longer over slow networks, because Group Policy makes many requests to domain controllers when applying Group Policy. To mitigate this effect, we added an option to allow computers or users on a slow network connection to avoid processing any CSEs that require synchronous processing. All other policy settings will apply as normal. Those synchronous policies that were skipped while on a slow network will take effect the next time a user or computer is on a fast network (as usual).

You can find this policy setting in Computer and User configurations under Policies -> Administrative Templates -> System -> Group Policy -> Change Group Policy Processing to run asynchronously when a slow link is detected

 

The Configure slow link detection policy setting in the same path allows you to configure the threshold at which Group Policy determines a link is slow. Slow link detection raises event 5314 in the Group Policy Operational Log (Located in the Event Log Viewer under Applications and Services Logs -> Microsoft -> Windows -> Group Policy) . Checking this event will show if Group Policy has detected a fast or slow link, as well as the detected bandwidth, and the threshold that has been set. 

Action: Disable synchronous logins when on a slow link

Summary

Windows 8 provides new mechanisms to help reduce the impact of synchronous processing. By utilizing available tools and being aware of the necessary difference in the design and deployment of GPOs you can substantially improve your user’s desktop startup experience.

Actions you can take to improve Group Policy performance:

•          Avoid synchronous CSEs and don’t force synchronous policy, or if CSE usage is necessary, minimize changes to these policies.
•          Avoid costly WMI filters such as LDAP queries.
•          Look for costly scripts by running them in isolation, and work to improve the scripts’ performance or avoid these scripts.
•          Don’t run ITL evaluations such as OU, LDAP Query, Domain, Site, or Computer Security Groups.
  •    If you need to use security group filters, consider this KB article.
•          Don’t use “Replace” with Group Policy Preferences Printers.
•          Disable synchronous logins when on a slow link.

 

Summary: Microsoft Scripting Guy, Ed Wilson, talks about using Windows PowerShell to create and to add new virtual hard disks while configuring new virtual machines on Hyper-V.

Microsoft Scripting Guy, Ed Wilson, is here. Yesterday was a holiday in the U.S. People in Charlotte, North Carolina were outside cooking hot dogs on their barbeque qrills, and on the north side of the city, it was a NASCAR race weekend. Personally, I thought it was a great time to spend at home and play around with Windows PowerShell. The Scripting Wife was also working with Windows PowerShell for her entry for the Scripting Games. So, at least around our household, it was a Windows PowerShell holiday.

Modifying virtual machine storage

One of the things that I like to do is to create a small virtual disk and attach it to a newly created virtual machine on Hyper-V. (Luckily, I can do all this on my laptop running Windows 8). In my previous blog post, Use PowerShell to Build Multiple Virtual Machines, I talked about using New-VM, Set-VMMemory, and Set-VMDvdDrive to perform the initial build of a series of virtual machines.

Today I am going to use three more cmdlets: New-VHD, Add-VMHardDiskDrive, and Set-VM. I will create a new VHD for my virtual machine to use for storage, add it to the virtual machine, and then while I am playing around, I am going to change the default snapshot storage location and change the smart paging file location. In the end, I will provide a link to my modified Create-VM script in the Scripting Guys Script Repository.

Create a new VHD

To create a new VHD, I use the New-VHD cmdlet. I need to specify the path to the VHD (including the name for the newly created VHD, in addition to the size of the disk and if I want it to be a dynamic disk. In my command (because I contain it inside a loop that creates the initial virtual machines), I do not have a specific name for the newly created VHD. I will use the name of the virtual machine, and append disk2.vhdx to the end of the file name.

The thing that is perhaps a bit cool is the use of the escape ( ` ) character to separate the variable name $n from the rest of the text used to create the file name. Here is the command I use:

New-VHD -Path "F:\VM\$n\Virtual Hard Disks\$n`Disk2.vhdx" -Dynamic -SizeBytes 50MB

Add the newly created VHD to the virtual machine

To add the newly created VHD to the previously created virtual machine, I use the Add-VMHardDiskDrive cmdlet. I need to provide it with the name of the virtual machine and the path to the VHD. In reality, it is pretty simple. Here is the command:

ADD-VMHardDiskDrive -VMName $n -Path "F:\VM\$n\Virtual Hard Disks\$n`Disk2.vhdx"

Configure snapshot and smart paging locations

To configure the locations for the snap shot storage and for smart paging, I need to use the Set-VM cmdlet. When I do this, I simply provide the locations for the appropriate parameters as shown here:

Set-VM -SnapshotFileLocation "F:\VM\$n\Snapshots" –SmartPagingFilePath "F:\VM\$n\paging"

Finishing up

So that is it for the modifications to my Create-VM.ps1 script. Not bad for 30 lines of code, including blank lines for spacing. The completed script is shown here:

$name = "Client1","Server1","Server2"

$name |

Foreach {

    new-vm $_ -MemoryStartupBytes 512MB -SwitchName InternalSwitch `

    -BootDevice cd -NewVHDPath "F:\VM\$_\Virtual Hard Disks\$_.vhdx" `

    -NewVHDSizeBytes 127GB }

 

Foreach($n in $name)

{

 if($n -match 'client')

    {

     Set-VMMemory -VMName $n -DynamicMemoryEnabled $true -MinimumBytes 512MB -MaximumBytes 4096MB -StartupBytes 2048MB -Buffer 20

     Set-VMDvdDrive -VMName $n -Path $((Get-Item F:\winblue\*client*).fullname)

     New-VHD -Path "F:\VM\$n\Virtual Hard Disks\$n`Disk2.vhdx" -Dynamic -SizeBytes 50MB

     ADD-VMHardDiskDrive -VMName $n -Path "F:\VM\$n\Virtual Hard Disks\$n`Disk2.vhdx"

     Get-VM -Name $n |

      Set-VM -SnapshotFileLocation "F:\VM\$n\Snapshots" -SmartPagingFilePath "F:\VM\$n\paging"

    }

 if($n -match 'server')

  {

   Set-VMMemory -VMName $n -DynamicMemoryEnabled $true -MinimumBytes 512MB -MaximumBytes 2048MB -StartupBytes 1024MB -Buffer 20

   Set-VMDvdDrive -VMName $n -Path $((Get-Item F:\winblue\*server*).fullname)

   New-VHD -Path "F:\VM\$n\Virtual Hard Disks\$n`Disk2.vhdx" -Dynamic -SizeBytes 50MB

   ADD-VMHardDiskDrive -VMName $n -Path "F:\VM\$n\Virtual Hard Disks\$n`Disk2.vhdx"

   Get-VM -Name $n |

    Set-VM -SnapshotFileLocation "F:\VM\$n\Snapshots" -SmartPagingFilePath "F:\VM\$n\paging"

  }

 

}

 

Get-VM $name | Start-VM

The complete script is uploaded to the Scripting Guys Script Repository: Create and Configure a New Virtual Machine.

Join me tomorrow when I will talk about more cool Windows PowerShell stuff.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

The Microsoft Malware Protection Center (MMPC) is committed to protecting our customers from malicious software and disrupting the malware ecosystem.

To achieve these goals, we forge partnerships with internal and external teams.  One such team, Microsoft Digital Crimes Unit (DCU), has expanded their partnerships to include the National Institute of Communication Technologies (INTECO) of Spain. 

This organization is one of the first to utilize live botnet data feeds from the new Azure-based Cyber Threat Intelligence Program (C-TIP).   Data from C-TIP will allow INTECO to better protect machines and fight cybercrime in Spain.

At MMPC, our day-to-day work involves understanding and neutralizing malware families.  This includes how they infect victims, how they monetize, how they evade detection, how their command and control is structured, and how to remediate their destructive effects. 

We author signatures that uniquely identify the malicious characteristics of such software to prevent them from harming our customers.  Our security products are updated with these signatures to give our customers a baseline secure computing experience. We invest heavily in emerging technologies that automate our detections as much as possible as well as discover asymmetric opportunities to make it difficult for cybercriminals to exploit our customers.  

This includes leveraging the scale and speed of the cloud to deliver protection solutions, using big data approaches to calculate areas of needed improvement from our vast collection of telemetry, and using state-of-the-art tools and techniques to respond quickly and globally to cyber threats.

However, at the end of the day, we can never solve cybercrime by addressing the issue solely on a technological level. 

An actual human being is behind each and every cybercriminal activity. A human being that is actively causing this malicious activity.  A human being that cannot grasp the idea of proper exchange, between themselves and the rest of society. In our quest to protect our customers, we must also address the individuals perpetrating these crimes.

We are investing in partnerships that better address the actual cause behind cybercrime.  This will complement current MMPC strategies to provide comprehensive protection to our customers. 

We are continuing our commitment to bring about a safe and secure computing environment for everyone.

You can read more about our partnership with INTECO on the Microsoft on Safety and Defense Blog.

MMPC

Yesterday I was at a site where they decided to change the service account for the SQL Server services on a set of systems. After changing the service accounts, SQL Server restarted just fine on all machines except one.

I had used the SQL Server Configuration Manager to make the changes (important to not just use the Services applet in Administrative Tools) but I got the typical error telling me that the service wouldn’t start in a timely fashion. The server was running SQL Server 2008 R2 SP2.

Looking in the system event log produced the following errors:

The SQL Server (MSSQLSERVER) service terminated with service-specific error %%-2146885628.

A fatal error occurred when attempting to access the SSL server credential private key. The error code returned from the cryptographic module is 0x8009030d. The internal error state is 10001.

I spent a while looking for info on the last error and found a site where they discussed that it was generated when the service account could not read the machine keys that were stored in the  C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys folder. The article then discussed how to add read permissions for the keys one by one.

It suddenly dawned on me that it was probably a problem with the permissions on the folder instead. Checking the permissions on that folder made me realize that the local Administrators account should have the ability to read it. The service account was meant to be a member of the local Administrators account but had not been added to that group on this machine.

Adding the service account to the local Administrators group on the machine (note: not the domain administrators account) fixed the issue and the service started again, until I could get the correct account permissions set in the morning when other staff came back. (In the comments I've added a list of what's actually required).

Hope this helps someone else. (And helps me the next time I see this and have forgotten what it was J)

Scenario: You need to configure a Power View report to show data for a dynamic date, such as the current date or the last date with data. However, as it stands Power View doesn't support expressions.

Workaround: Add a Boolean calculated column to the Date table that returns TRUE for the date of interest. For example, if you want the report to show data for today's date, the expression might be:

=if([Date]=TODAY(), True, False)

where [Date] is the column with date data type. Then, use this column as a filter in Power View.

Unfortunately, this workaround has a significant limitation. If the report needs another filter on the Date table, such as to allow the user to overwrite the default filter on the current date, this approach won't work because filters on multiple attributes are interpreted as an AND condition (Date is 1/1/2013 AND TodayDate=True). Inability to specify OR filter condition between attributes is another Power View limitation. So, users needs to be trained to use either the TodayDate or Date filter but not both.

Looking ahead, it will be nice if Power View supports VB or DAX expressions as regular SSRS reports do.

Many thanks to Darren Gosbell for suggesting the workaround.

Anyone who has gone through configuring Kerberos knows that it's not fun. After having done a few installations, my personal record for configuring Kerberos for SharePoint, SSAS, SSRS, SQL Server, and PerformancePoint was 4 hours but I had all parties including the AD administrator in the same room. In an attempt to facilitate troubleshooting Kerberos, Microsoft released Microsoft Kerberos Configuration Manager for SQL Server. It's a simple diagnostic tool for troubleshoot Kerberos issues related to SQL Server.

Once you install and start the tool, you connect to the desired instance (requires SQL Server standard authentication to a remote server). Then, the tool checks the service account and discovers what SPN's are registered for that account and what delegation options are configured in Active Directory. If it finds inconsistencies, it is capable of generating a script that your AD administrator can run or applying the fix interactively.

As a side note, be aware that if Kerberos doesn't work, SSRS, Power View, and PerformancePoint 2013 supports SSAS authentication using EffectiveUserName.

During my career I’ve been a part of dozens of projects. Some I was on from the start, most I came in to help bail out. Some went smooth and were a pleasure to build and maintain and some projects failed (failed being broadly defined as projects that...(read more)

When using ApplicationIntent=ReadOnly to access a readable secondary via the Availability Group Listener, the login SIDs have to be the same between primary and secondary. The connection attempt generates an error saying the login does not exist. This happens because the restored database on the secondary has a defined user, but no associated login.

Two scenarios can cause this behavior:

1. Logins exist on primary but not on secondary

2. Logins exist on both primary and secondary but have mismatched SIDs

Both scenarios give same results.

Reproducing the problem:

1. Create several logins and users on a standalone SQL instance.
2. Create a simple RS report in VS2010 while connected as one of the new users
3. Click Preview and the report runs fine.
4. Create an Availability Group with a readable secondary, restore, and make sure everything is active and synchronized.
5. Create a Listener.
6. In the Visual Studio report, change the connection string to connet to the listener and add ApplicationIntent=Readonly.
7. Click Preview. A login error is generated on the screen. And an error gets generated in the SQL Server log.
8. Add the logins and users to secondary server, but in a different order than on the primary.
9. Click Preview on the report. Again, the login error is generated.
10. Sync the SIDs using either method in the links below.
11. Click Preview. The report is generated immediately.

Fixing the problem:

Either of the following methods can be employed to synchronize Login SIDs:

1. Create the logins on the secondary with the SID=sid option in the CREATE LOGIN command.  In order to do this one, you will have to get the SIDs and logins from the primary before you create them on the secondary.  Or you can get them from the user record in the secondary database.
2. Follow the instructions and use the script from KB 918992.  Even though it says it is used to transfer logins from SQL Server 2005 to SQL Server 2008, it can also be used to create a script from your primary server to be run on the secondary to create the same logins with the same SIDs.

 

 

Author: Kevin Cox

Contributors:  Min He,Steve Lindell

Reviewers: Sanjay Mishra, Juergen Thomas, Jimmy May

 

Creating an availability group needs pings and status checks across the different servers involved. This accounts for approximately 500 bytes per database in the group.  The PerfMon counter used to track the activity is “Bytes Received from Replica/sec”.  Books Online has recently been changed to reflect a high level description.  The purpose of this blog is to provide a bit more detail.

Books Online   http://msdn.microsoft.com/en-us/library/ff878472.aspx

Counter Name	Description
Bytes Received from Replica/sec	Number of   bytes received from the availability replica per second. Pings and status updates will generate network traffic even on databases with no user updates.

 

The key to understanding this counter is that the traffic is per database.  On one customer project with 40 databases in one availability group, this counter was showing about 8k/sec when there was no user traffic.  This consists of bi-directional
ping and status checks.

What does the primary do with this data it gets back from pings and status checks?  It displays the information on the Always On Dashboard. There are two main sources for the dashboard data. 

1. sys.dm_hadr_database_replica_states.  This runs every second unless the primary is too busy, then it will run as soon as it can. This is used to display the information from the latest status checks, but does not drive any network traffic.
2. sp_server_diagnostics. It is called by the Availability Group (AG) Resource DLL, which is only run on the AG primary node and only connects to the local instance.  It does not contribute to the network traffic mentioned above, but is useful to know that it is running periodically according to the HEALTH_CHECK_TIMEOUT setting.   All the Availability Groups on one instance share a single resource DLL.

Hopefully, with this information you will be able to understand why the Bytes Received from Replica/sec counter is showing activity even when there is no user activity.

A new version of the Partition Management Utility for SQL Server is now available on Codeplex at http://sqlpartitionmgmt.codeplex.com/.   This is a tool that helps you create necessary staging tables and indexes and associated check constraints, to support partition-switch operations against existing partitioned tables.   This solves the problem of keeping staging table scripts in synch when a permanent partitioned table evolves to contain new boundary values or column attributes.

The latest version adds some significant new capabilities:

• Support for SQL Server 2012 Columnstore Indexes
• Option to create TSQL scripts for staging stables, rather than executing DDL
• Date constraints are now specified in a locale-independent format better supporting globalized deployments
• Compatible with a broader range of partitioning column types

The utility is a command line tool but it also can be integrated into an SSIS process or used to generate SQL scripts that then become part of a regular "sliding window" partition management scenario.   The new version is backward-compatible with SQL Server 2005, 2008 and 2008 R2.

One of the great enhancements in Microsoft's HDInsight distribution of Hadoop is the ability to store and access Hadoop data on an Azure Blob Store. We do this via the HDFS API extension called Azure Storage Vault (ASV). This allows you to persist data even after you spin down an HDInsight cluster and to make that data available across multiple programs or clusters from persistent storage. Blob stores can be replicated for redundancy and are highly available. When you need to access the data from Hadoop you point your cluster at the existing data and the data persists even after the cluster is spun down.

Azure Blob Storage

Let's start with how your data is stored. A storage account is created in the Azure portal and has access keys associated with it. All access to your Azure blob data is done via storage accounts. Within a storage account you need to create at least one container, though you can have many. Files (blobs) are put in the container(s). For more information on how to create and use storage accounts and containers see: http://www.windowsazure.com/en-us/develop/net/how-to-guides/blob-storage/. Any storage accounts associated with HDInsight should be in the same data center as the cluster and must not be in an affinity group.

You can create a container from the Azure portal....

Read the complete blog post at: http://blogs.msdn.com/b/cindygross/archive/2013/04/25/access-azure-blob-stores-from-hdinsight.aspx

If you are involved in Business Intelligence, then the books by Ralph Kimball are required reading:

The Data Warehouse Lifecycle Toolkit: Practical Techniques for Building Data Warehouse and Business Intelligence Systems, Second Edition, 2008, 636 pages (Amazon).  Subject: Implementation guide.  Primary Audience: Good overview for all project participants; key tool for project managers, business analysts, and data modelers.  See Tools and Utilities.  Walks you through the detailed steps of designing, developing, and deploying a DW/BI system: Introduces the Kimball Lifecycle, managing the project, collecting the requirements, introducing the technical architecture, creating the architecture plan and selecting products, introducing and designing the dimensional model, designing the physical database and planning for performance, introducing and designing/developing ETL, introducing and designing/developing BI applications, deploying/supporting/expanding the DW/BI system

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, Second Edition, 2002, 436 pages (Amazon).  The third edition was published July 1, 2013 (Amazon).  Subject: Dimensional data modeling.  Primary Audience: Data modelers, business analysts, DBAs, ETL  developers.  Complete library of dimensional modeling techniques.  Starts with fundamental design recommendations and progresses through increasingly complex scenarios..  Has guidelines for designing dimensional models using real-world data warehouse case studies from retail, accounting, inventory, CRM, HR, financial, etc

The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data, First Edition, 2004, 491 pages (Amazon).  Subject: ETL system architecture.  Primary Audience: ETL architects and developers.  Shows you how to plan and design your ETL system, choose the appropriate architecture from the many possible options, build the development/test/production suite of ETL processes, build a comprehensive data cleaning subsystem, and tune the overall ETL process for optimum performance

The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence, First Edition, 2010, 744 pages (Amazon).  Subject: DW/BI system design and development.  Primary Audience: A topical reference book for all project participants. A collection of recommended guidelines for data warehousing and business intelligence. Compiles the best articles written by Ralph Kimball as his team of colleagues. Covers the complete lifecycle—including project planning, requirements gathering, dimensional modeling, ETL, and business intelligence (BI) and analytics

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset, Second Edition, 2011, 696 pages (Amazon).  See Tools and Utilities.  The “bible” if you are using the Microsoft BI stack.  Think of it as a combination of the four previous books, taking parts of each, but making it specific to the Microsoft BI tool-set, where the other books are tool independent. The goal of the book is to guide the reader down the best path toward designing and building a successful business intelligence system and its underlying data warehouse databases using the Microsoft SQL Server product set

Kimball’s Data Warehouse Toolkit Classics: The Data Warehouse Toolkit, 2nd Edition; The Data Warehouse Lifecycle Toolkit, 2nd Edition; The Data Warehouse ETL Toolkit, 2009, 1628 pages (Amazon).  This is just a way to save money: instead of buying the three books separately you can make one purchase at a cost savings

My next guest is a person I have known for years, and have worked with on several occasions, Jason Strate ( @stratesql ). Jason is a very active writer and speaker (at my first SQL Saturday event, he spoke four times!), and always seems very busy. His...(read more)

Have you ever heard of these types of requests? True story! I have had each of these and many more: A customer needed to grant a business user the rights to issue a KILL command – without giving them sysadmin or CONTROL SERVER. A customer wanted to grant...(read more)

Hot off the virtual press, the SQL Server Separation of Duties Whitepaper is now available! SQL Server 2008 R2 provides the full breadth of tools to support restrictive compliance and security requirements. This whitepaper will associate the features...(read more)

I have recently had several requests from people asking for resources to learn about Big Data and Hadoop. Below is a list of resources that I typically recommend. I'll update this list as I find more resources. Let's crowdsource this... Tell me your favorite...(read more)

Distribution statistics are one of the most important sources of information that the Query Optimizer uses to determine a good query plan. In this post, I’m not going to tell you everything about distribution statistics. I’m just going to show you a few...(read more)

I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more info about distribution statistics. It returns info about the last statistics update date (which is also available with a function STATS_DATE())....(read more)

Data Explorer will speed up and simplify your data analysis by at least an order of magnitude. It makes data just work for me. No fighting or struggling, it just works. Step 1. Go here and download the Data Explorer Preview for Excel 2013 or Excel 2010....(read more)

The BETWEEN operator is a handy SQL construct, but it can cause unexpected results when it isn’t understood. Consider the following code snippet: where x between .9 and 1.10 One of the questions you should ask is this: What is x? What if x has a float,...(read more)

We all know that CURRENT_TIMESTAMP is ANSI and carries the Celko stamp of approval while GETDATE() is proprietary. So now run the following line of code

 

No problem right? Let's take a look at what is actually created, run the query below

 

 SELECT object_definition(id) FROM sysobjects WHERE name = 'df_duh'

Interesting, the object definition for the constraint shows getdate() not current_timestamp.

Let's verify that by using sp_help, run the query below and scroll all the way down, look at the constraint_keys column

 

 

I've given a couple of security presentations recently where I talked about how you must keep track of how security is being managed. For instance:

 

1. The best practice recommendation is that we use Windows authentication vs. SQL Server authentication.
2. In an enterprise environment, we typically want to use Windows groups over Windows users.
3. This means the DBAs only have to worry about assigning permissions to the groups.

 

And for the most part this is fine. AD admins (or security admins, if you have a separate group) are responsible for security already. It makes sense to compartmentalize the overall management of security groups this way, allowing them to parcel out permissions based on the organization's needs. There's one big issue that comes to mind: they also manage the Windows group for the DBAs.

 

They should. However, the issue is that a person with such permissions could add himself/herself to the security group, make use of the elevated rights, and then remove himself/herself from the security group. As a DBA, there isn't anything I can do to detect this. And as a DBA, my options for detecting the active use of the elevated rights isn't exactly straight-forward. To be able to detect this scenario I'm going to need help. I'm going to need help from the AD admins.

 

Group membership changes will record an appropriate security event in the security event log on a DC. That is, if the DCs are configured right. Those events could be forwarded to a collection server and they can also be picked up by some log management device. However, none of these are within the control of the DBA. This is an example where IT needs to cross teams to protect the SQL Servers. There is some work on the DBA side and there is some work on the AD side. If you have a security team that monitors logs, there's some work on their side, too. Quite simply, crossing teams is a must.

 

How is your relationship with the other infrastructure teams? Are you all working together towards the same goals with respect to security?

This blog outlines a new twist to my previous blog outlining issues with 4K sector sizes.

SQL Server - New Drives Use 4K Sector Size: http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

In the previous post I discussed that it was unsafe for the I/O subsystem to present a sector size that was smaller than the actual, physical sector size.   This leads to unsupported, Read-Modify-Write (RMW) behavior.

I was doing testing on a Windows 2012 Server - Storage Space setup and found that both Storage Spaces and the VHDx format can report a 4K sector size to the SQL Server.   This allows the various drives setup in the pool for Storage Spaces to be of disparate sector sizes (Drive 1 = 512 bytes, 1K, 2K, and Drive 4 = 4K.) 

Is this safe for SQL Server?

The answer is yes.  An I/O subsystem can return a larger sector size than actual, physical sector size as long as all reported values can be evenly divided by 512 bytes.

As the diagram below shows, SQL Server maintains parity on 512 byte boundaries, for the log, regardless of the reported sector size.   This allows SQL Server to detect a partial write (torn behavior.)   For example, if the system reported a sector size of 4K but the physical sector size was 512 bytes, the I/O subsystem is only guaranteed to flush to a 512 byte mark.   If the first 4, physical sectors are flushed (2K of the 4K aligned block) and a power outage occurs, SQL Server will be able to detect the entire 4K was not properly flushed.

 

Without the logical parity every 512 bytes SQL Server would be unable to detect the torn situation, leading to unexpected recovery and logging behavior(s).

WARNING:  While SQL Server protects your data against such a failure the reporting of sector size, larger than physical sector size, can lead to unwanted/unexpected space usage.   SQL Server will align the log writes to the reported sector size (4K in this example.) 

SQL Server packs records within the log blocks and then aligns/pads the writes on the reported sector boundary.  Lots of small transactions, leading to many log flushes, can result in wasted log space for a system reporting larger sector sizes.   Moving the scenario to an I/O subsystem reporting smaller sector sizes can reduce space usage.

The easiest way to see this in action is a single worker doing tiny transactions.

while(1=1)

begin

   insert into tblTest values (1)   // Each insert is a transaction and a log flush

end

Each insert is a separate commit transaction, causing the log to be flushed for each iteration.   In this example each insert will require at least 4K of log space to properly align during the flush.    Wrapping a transaction around the while loop or only committing at reasonable boundaries (say 10,000 inserts) reduces the log flushing behavior and uses the log space more effectively.

Bob Dorr - Principal SQL Server Escalation Engineer

I’m always amazed that issues usually come in batches.  I was looped into a few cases that had the following symptoms.   They were running SharePoint 2010 and Reporting Services 2012 SP1.  When they went to use a data source with Windows Authentication, they were seeing the following error:

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)   

This caused me to raise an eyebrow (visions of Spock as the new Star Trek movie is opening today <g>).  A lot of thoughts were floating in my head that all told me that this error didn’t make sense, for a bunch of reasons.

1. The default protocol order for connecting to SQL from a client is TCP and then Named Pipes.  So, because we failed with a Named Pipes error, that meant something was either wrong with TCP or someone changed the Protocol order (which I have never seen in a customer case – so very unlikely)
2. This is RS 2012, which means we are a Shared Service and rely on the Claims to Windows Token Service (C2WTS).  This forces Constrained Delegation.  Pretty sure most people would not have created the delegation requirements for the Named Pipes SQL SPN as most people go down the TCP route.  You can read more about SQL’s SPNs being Protocol based here.  Also more on this related aspect in a later post as I found some interesting things about this as well.
3. This error tells me that we couldn’t establish a connection to SQL via Named Pipes.  Think of this as a “Server Not Found” type error.  I immediately tossed out any Kerberos/Claims related issue due to that thinking – again more on the kerb piece of this in a later post.
4. This is really the first time I’ve had someone hit me up with a Named Pipes connection failure from an RS/SharePoint Integration perspective ever.  And I just got hit with 3 of them within the same week.  Something is up.

Being this told me we had an actual connection issue via Named Pipes, I started down the normal connectivity troubleshooting path.  With any connectivity issue, I started with a UDL (Universal Data Link) file.  Basically just a text file renamed with an extension of UDL.  It’s important to run this from the same machine that is hitting the SqlException.  In my case it was my SharePoint App server, not the WFE server.

You’ll notice the “np:” in front of the server name.  This forces the Named Pipes Protocol and ignores the default protocol order.  And this worked.  I also tried “tcp:” to force TCP in the UDL and this worked to.  I went back to my data source and tried forcing TCP there.

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.)

This made no sense.  I even made sure I was logged in as the RS Service Account as that is the context in which we would have been connecting to SQL.  Same result.  Also, within a network trace, I saw nothing on either the TCP or Named Pipes side of the house in the trace that related to this connection attempt.  Which meant we never hit the wire. 

As I was going to collect some additional diagnostic logging (Kerberos ETW tracing and LSASS Logging) I ended up doing an IISRESET and a recycle of the C2WTS service.  We went to reproduce the issue, but got a different error this time.

System.IO.FileLoadException: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)  File name: 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' ---> System.Runtime.InteropServices.COMException (0x80070542): Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)   

This error I did know and can work with.  I had blogged about this error last July here.  Checking the “Act as part of the operating system” showed that the C2WTS service account in fact was not given that right.  Adding that account to that policy right and restarting the C2WTS Windows Service and performing an IISRESET then yielded the following:

The connectivity errors were clearly related to the lack of the Policy Setting.  It was unexpected and didn’t line up with normal connectivity related issues and also wasn’t very helpful with regards of where to go look for more information as all of the normal paths didn’t show anything useful.

Of note, I tried reproducing this on SharePoint 2013, but only got the FileLoadException.  I think this is partly a timing issue with how IIS AppPools are started and the C2WTS service is started.  Doesn’t mean you won’t see this on SharePoint 2013 necessarily.  Even on SharePoint 2010, the first time I hit the FileLoadException.

 

Adam W. Saxton | Microsoft Escalation Services
http://twitter.com/awsaxton

I made a huge mistake that to be honest I am embarrassed to admit to. Yet for some unknown crazy reason I have determined it is ok to blog it. What did I do? I didn’t test a new piece of software before I installed it on a server where it could impact my monitoring tool.

The whole mess started when a company contacted me about testing some new software they had just released about 6 weeks ago. The software is supposed to be an auditing product that will allow you to provide a solution that is somewhat close to the feature sets that are offered with SQL Server. There are some added features that made it somewhat interesting for a test. I installed the product on a server that is not critical for production operations my monitoring server. Within a few hours I started to be alerted to some slow I/O; the good news is that I was able to put one and one together and determine that the Audit product I installed was the cause of the I/O issue.

Finding this behavior completely unacceptable I took actions that I thought would resolve the issue. I uninstalled the product… I should be all good, right? For good measure I sent an email over to the company, I was going to do a product review and I wanted them to know what I was seeing, the email was pretty short and clear.

Do you have any added information on the impact that the tool had on the server? 

I could not say at the time what caused the performance issue exactly, I knew it was the new software because of a couple items, first all the issues stopped the second I removed the tool, and the most telling was the fact that pre software install my SQL Server was running fine, after the install I was looking at 10 second write times.

I received an email back that said:

Hi Chris,

 I have talked to my technical team about this issue. They told me that our tool doesn’t impact server’s performance.

I knew immediately that this was not going to work, there was nothing more to the message and I realized that it is obvious they have not done enough testing. I was going to send them a few emails and see what we could do to reach across the table and see if I could help, but as life goes life got busy. As time went on and I forgot about the problem, that was until last week. I ended up having a different issue that caused me to open my performance tool to see what clues I could gather, this is when I found that my monitoring to was struggling to do anything. I had completely forgot about the Audit tool that I had removed before, I had removed it so why should I even consider it…

One would think that there were some obvious signs that the tool is either not ready for prime time, or does not meet the standards of the tools that I want to use:

• The tool wanted elevated security access.
• The supporting documents included all of about 5 pages of screen shots. I asked about this and was told more is coming, however I don’t see them on the site yet.
• I have never heard of the tool, and had not seen other reviews on it.

Now let’s move a few weeks forward…

Performance issues struck again.

Well, to sum up a couple of really frustrating days not only for myself but for the poor guys that run the support desk for my monitoring tool. We stumbled on to a few things.

First of all the support guys at SQL Sentry thought that we should take a look just to verify that there were not any traces running, easy enough…

SELECT
*
FROM
sys.traces

 

The results had two that I had expected, one was a performance trace that is filtered and expected. The second one was the default trace, but for the life of me I could not figure out what the third one was. I admit my age is starting to reach the point where I can’t remember what I ate for breakfast most days, but I had no clue where or when I created a trace called mytrace.trc. In addition to not knowing the trace it had just started the night before we found it based on the creation date, now granted the server was rebooted. I looked for what I thought could start this trace, then it occurred to me to look for a startup procedure. Found a great post here where I found this quick query.

SELECT
name

FROM
sys.objects

WHERE
type
=
‘P’

AND
OBJECTPROPERTY(object_id,
‘ExecIsStartup’)
= 1

 

And there is was…

The audit tool that I installed did not remove the startup procedures in the database. So when I uninstalled the tool, and more or less considered the issue closed, there were items that were left behind that caused issues weeks later. My point is, be careful on what you install. I tried to do that, but when I determined that I needed to remove the product I found that the same effort the company dedicated to rolling out the product was much more than the effort they used to remove it. I should not have had to learn this lesson the hard way, but we all make mistakes. I can tell you that I will learn from this mistake, and it won’t happen again.

 

 

 

I was at SQL Saturday in Phoenix last week and had a number of sidebar discussions with a solider that is transferring out of the Army lifestyle and over to the civilian world. This is a transition that I have done before, and I recall it well. I have not thought about it much over the last 15 years or so, but I do recall the nervousness that I had when I made the jump from the Marine Corps to the workforce as I know it today. When I made the transition I was lucky enough to have the opportunity to take a class my last week in the corps to help me with the many changes I was about to experience. I debated not taking the class because I didn’t think it was going to be that different, but I sure am glad I did. So as I reflect on this experience I thought I would share a few tips that might make it a bit easier. If you have any that you think would fit well send them over to me and I will see what I can do to add them to the list.

1. Stay away from acronyms on your resume – There is a good chance that when someone is reviewing your resume that they did not serve in the military. With this being a fact, consider how many terms you use on a day to day basis that you consider normal knowledge. If someone is vetting your resume and does not understand all the terms you are using they may disqualify you simply because they don’t understand what you are trying to relay. My tip here is find a friend or a relative who does not know the military life and have them review your resume, if you use terms such as MOS or OD duty chances are they will catch it and help you explain more.
2. Chain of command – This was a difficult one for me. The chain of command is pretty simple to understand; even people in the civilian world use it. However, in the military I was taught that anything and everything in your life was important to your chain of command. In a time of high stress such as a situation where you are in battle and bullets are being fired at you this is completely understandable. Assume you are in a fighting position, and one of your troops had a pretty serious breakup via a “Dear John” letter. This is something you would want to know, but in the civilian world many supervisors get a little creped out when they know so much about you.
3. Your opinion – Many think that the military is a group of young people that are trained to not have and opinion. When the commanding officer says take that hill, he does not need to hear a bunch of people arguing about the right way to do it, or if you should even try to do it. Granted, these discussions on the best way may arise. In the civilian world, most employers want to know if you think that something is not being done in the best way. The secret here is understanding the line between open discussions on the best way to do things and just doing them with no opinion at all. With that being said, it is still critical to know that as a solider you don’t always know all the information that is relevant, in the civilian world that is the often the case as well.
4. Networking – In the military if you wanted to get the best equipment it never hurt to know someone in the supply area, well it’s true in the civilian world as well. Who you know can help you, use your contacts well, and as you make the transition understand you need to make as many contacts as you can.
5. YES SIR – A few years ago I had interviewed someone who was coming out of the Navy, when I talked with him the answer to everything was “Yes Sir“. When the candidate spoke it was not a yell, nor a bark but very direct. It is not critical to speak in such a formal way, however respect should always be paid.
6. Volunteer – I have heard the joke more times than I care to count, in the military you become a volunteer when everyone else steps back faster than you. I was told many times before boot camp that you should never volunteer for anything. In the civilian world, I cannot stress enough how important it is. Not only do you have the opportunity to stand out, but you have a chance to learn something new. Be the person your boss knows they can go to when the need to get something done. Be the person they can count on, the type of person you want on your team.

 

 

This is a quick post that is more for my reference than for anything else. When I had the Compellent installed, Dell passed on a copy of the “Dell Compellent Storage Center Best Practices with vSphere 5.x” document (Dell P/N 680-041-020). One of the interesting points I noted was around modifying the queue depth, and where that should be done. As with any best practice document, there are going to be factors that may influence the outcomes of these activities in a positive or negative fashion. In other words, YMMV, but I found it useful. As always, test it before launching into production.

Firstly, set the HBA queue depth to 255 via the HBA BIOS. The thinking here is that the VMkernel driver module ultimately controls the HBA’s queue depth. Now, set the queue depth on the driver module. I use QLogic HBAs in my environment.

To find the correct driver name for the loaded module, run the following command.

esxcli system module list |grep qla

The output should be something like qla2xxx

Now run the following command.

esxcli system module parameters set -m qla2xxx -p "ql2xmaxqdepth=255 ql2xloginretrycount=60 qlport_down_retry=60"

Note that you can also set it via Disk.SchedNumReqOutstanding (DNSRO), where the default value is 32. Keep in mind that this setting is only enforced when more than one VM is active on the datastore. This is a global setting too, so if you’ve set the DNSRO value to 64, for example and you have two datastores in place, one with 4 VMs and one with 6 VMs, each VM will get 64 as the queue depth value. VMware recommend that this value be set to the same as the VMkernel module driver value.

You can also modify the queue depth in the Windows guest OS my modifying the registry settings of the OS.

In any case, go check out the document. It’s one of the more useful white papers I’ve seen from a vendor in some time.

coondoggie writes "The mantra is old, grant you, but worth repeating since its obvious from the amount of cybersecurity breaches that not everyone is listening. Speaking at the Georgetown Cybersecurity Law Institute this week, Deputy Attorney General of the United States James Cole said there are a ton of things companies can do to help government and vice-versa, combat cyber threats through better prevention, preparedness, and incidence response."

Read more of this story at Slashdot.