Monthly Archives: January 2015

Reflections as a consultant

Let’s just start with the last three years have been fantastic! This blog post is a slight deviation from the technical content on my blog. We’re going to focus on career and professional development for a minute.

In 2011 I was thrust into the world of consulting…accidentally. Accidentally you ask, how can that happen? Well, at the time I worked remotely for a large health care practice doing system design and software development on the Microsoft stack. Fun and innovative stuff, with great leadership in fun environment. Learned a lot! Well, they were acquired by a much larger organization and the new corporate policy didn’t allow for remote workers. So rather than terminate me they asked if I would like to be a consultant. Well, who wouldn’t right? I had dabbled in consulting for years in the off hours, and always wanted to make the jump. This was the push that I needed. That left me with one huge question…

How do you run a consulting firm?

Certainly at the time I knew some technical stuff, I had been in the industry for 10 years and I was working on a PhD in computer science (still am :). But what I didn’t know is how to run a consulting firm. Client satisfaction, finances, marketing, professional development and that’s just the short list. Missteps occur, but as long as you learn you’ve made some progress.

Some successes include:

  • Client satisfaction – Have a decent set of clients where I’m working on cool problems…still have 100% client retention :)
  • Marketing – The web site went live, added a blog this year and a video
  • Professional Development – Attended 2 SQLSkills training classes and the Fall SQLIntersection, not to mention numerous books read
  • Business Network Development – Meeting people in the SQL community
  • Technical Community Involvement – Completed a book review for Ben Navarez, working on one more for Kelan Delaney

Where do we go from here?

Well, here are some goals for this year

  • Refine the marketing message – easily and concisely communicate what Centino Systems does  
  • Continue to develop blog content – with an emphasis on SQL Internals and database system architecture, speaking to client’s business and technical needs
  • Deliver a public presentation – build, refine and deliver a top notch presentation(s) at a SQL Server User Group or SQL Saturday
  • Process Optimization – develop repeatable processes. This is key for my success and my client deliverables. It will save me time and clients will get better and consistent services.

…and with that all laid out, why do I think I need to be mentored?

The answers are simple:

  • Collaboration – There’s a lot in the road ahead and having someone to collaborate with, exchange ideas both technical and non-technical is invaluable. 
  • Learning – Computer systems are incredibly complicated, if you stop learning you will be left behind. This would in an incredible chance to have someone guide me through the deep technical nuances of relational databases systems and system design with excellent insight.

Thank you for reading and good luck!

Follow me on Twitter: @nocentino




Availability Group Read-only Routing

This morning at a customer site I was researching an issue where Availability Group read-only routing was not working correctly. Quickly I was able to determine the issue was a misconfigured read-only routing URL list. In this blog post I’ll show you the requirements for read-only routing in Availability Groups, how I determined the URL list was the issue and what to do to fix the situation. The requirements for Read-only routing in Availability Groups are:

  1. A configured listener
  2. At least one replica is configure for read-only access
  3. Each secondary is configured with a URL
  4. Each replica has a configured routing list
  5. The replica being routed to must be synchronized or synchronizing.

Now, let’s explore the requirements and then what happens with condition 5 is not met and the steps to find and remedy the issue.

Is there a listener configured?

	, agl.dns_name
	, agl.ip_configuration_string_from_cluster
FROM	sys.availability_group_listeners agl
INNER JOIN sys.availability_groups ag on ag.group_id = agl.group_id

Screen Shot 2015 01 25 at 12 16 23 PM This shows us that there is one AG listener, requirement 1 met! Review the current access configuration and read only routing URL configuration of each replica

SELECT replica_server_name
	, read_only_routing_url
	, secondary_role_allow_connections_desc
FROM sys.availability_replicas

Screen Shot 2015 01 25 at 12 14 38 PM1

So we can see each is configured for read-only access, indicated by secondary_role_allow_connections_desc = ALL and that each replica has a read only routing url, excellent requirements 2 and 3 are met, let’s move on.

Let’s explore the routing lists for the Availability Group

SELECT	  agr1.replica_server_name		--the source replica
		, agr2.replica_server_name		--the read only replica for the source replica
		, agr2.read_only_routing_url	—the configured read-only routing URL for the read-only replica
		, agr.routing_priority			--the routing priority for that read-only replica
FROM sys.availability_read_only_routing_lists agr
INNER JOIN sys.availability_replicas agr1 ON agr.replica_id = agr1.replica_id
INNER JOIN sys.availability_replicas agr2 ON agr.read_only_replica_id = agr2.replica_id
INNER JOIN sys.availability_groups ag ON ag.group_id = agr1.group_id
ORDER BY agr1.replica_server_name, agr.routing_priority

Read only routing listsFrom this output we can see that when SQL14-A is a read-only replica of SQL14-B and SQL14-B is a read-only replica of SQL14-A. Requirement 4 met, right? Yes, but let’s dig deeper. 4. Let’s test out connectivity under the condition when the current read only replica does not meet the routing requirements listed above. Stop data movement to SQL14-B, which changes the database state to Not Synchronizing


Screen Shot 2015 01 25 at 11 53 19 AM Now let’s test connectivity with SQLCMD, which now has ApplicationIntent as a command line parameter. You’ll need the -d parameter which sets the database context for read only routing to work.

sqlcmd -S AGL1 -d TestAG1 -K ReadOnly -Q "Select @@servername"

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Unable to access the 'TestAG1' database because no online secondary replicas are enabled for read-onlyaccess. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

Yikes, that didn’t work. Shouldn’t it have just failed back to SQL14-A. It didn’t. Why not? Well our only configured read-only replica SQL14-B no longer meets the connection requirements. We need to tell the system what to do in this state. 4. Let’s change our read-only routing URLs to include both the primary and the read-only replica

What we need to to configure the read-only routing URL list to point to all possible read-only replicas, including the primary. 



Let’s check our routing list again, as you can see each server now has two entries and the priority is set for the entries relative to their position in our routing list

Screen Shot 2015 01 25 at 12 19 45 PM So let’s test with SQLCMD again

sqlcmd -S AGL1 -E -d TestAG1 -K ReadOnly -Q "Select @@servername"

Excellent, the AG is back up and running for applications configured with ApplicationIntent=ReadOnly, but we’re not quite there yet, we still are pointing to the Primary. We need to restart data movement on SQL14-B to get our database state back to Synchronized so the workload can use the read-only secondary.


Screen Shot 2015 01 25 at 11 58 30 AM And let’s try with SQLCMD once more

sqlcmd -S AGL1 -E -d TestAG1 -K ReadOnly -Q "Select @@servername"

Success! The key take away here is that the read-only routing URL needs to list all possible read-only replicas, including the primary not just the servers that are current secondaries.  Please feel free to contact me with any questions regarding Availability Groups or other SQL Server related issues at: References: Configure Read-Only Routing for an Availability Group (SQL Server)     

Shout out to Jared Karney for picking up an error in this post! Thanks!

Building Debug Symbols – Troubleshooting symbol building

Recently,I have been working with the Debugger Symbols for SQL Server to generate call stacks and learn more about the internals of SQL Server. 
I approached one of our clients about doing this on a non-production system and they thought it be great. They would get better insight on their workload, I would get access to a real workload., right? Even in their stage environment they have a pretty heavy workload so it would be a good candidate for generating call stack data.
When I sat down to build the debug symbols on their non-production system I could not get it to work for all the binaries that I needed for this test. Not having all the symbols means the function call name resolution will not work for the calls defined in the missing symbol files. Nobody likes spotty data and I wanted to find out why the symbol files did not build.
The system is a SQL Server Enterprise 2012 SP2 + the online index rebuild hotfix 2969896 (build 11.0.5522). So in my test lab I was able to build symbol files for 2012 SP2, then I installed the hotfix and was able to reproduce what I saw on the staging system…missing symbol files.
So I needed an explanation for this and a plausible one is, Microsoft is not providing data on the symbol servers for hotfixed build and likely not for other non-mainstream builds.
These are the binaries that fail once the online index hotfix is applied…likely the binaries affected by the hotfix right?.
SYMCHK: sqlaccess.dll        FAILED  – SqlAccess.pdb mismatched or not found
SYMCHK: sqldk.dll            FAILED  – sqldk.pdb mismatched or not found
SYMCHK: sqllang.dll          FAILED  – sqllang.pdb mismatched or not found
SYMCHK: sqlmin.dll           FAILED  – sqlmin.pdb mismatched or not found
SYMCHK: sqlos.dll            FAILED  – SQLOS.pdb mismatched or not found
SYMCHK: sqltses.dll          FAILED  – sqltses.pdb mismatched or not found
Please feel free to contact me with any questions regarding performance or other SQL Server related issues at:

Tracing call stacks in SQL Server – Installing the Debugging Tools for Windows (WinDbg) on Windows 8.1 and generating debug symbols for SQL Server binaries

Where to get the debugger tools

To generate the needed symbols you will need the “Windows Software Development Kit (SDK) for Windows 10” download here: and click “Download the standalone SDK”

  • I like to select the Download option and pick “Debugging Tools for Windows” this will download all of the installation files

How to install the debugger tools 

The installation of this software is very straight forward, Then find in the downloaded files \Windows Kits\10\StandaloneSDK\Installers\X64 Debuggers And Tools-x64_en-us.msi and install the debugger and install the tools with the default settings. 


Generating the symbol files for SQL Server

Once installed you will need to generate the symbols, below are two commands one for the SQL Server executable and one for all of the dlls that are in the SQL working directory. Paul Randal @PaulRandal recommends a tip in his blog for using *.dll to gather symbol data on all the dlls in the directory at the expense of time and a small amount of disk space.

  • Change into the BINN directory
    • cd C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
  • “C:\Program Files\Windows Kits\10\Debuggers\x64\symchk.exe” sqlservr.exe /s SRV*c:\symbols*
  • “C:\Program Files\Windows Kits\10\Debuggers\x64\symchk.exe” *.dll  /s SRV*c:\symbols*

Please feel free to contact me with any questions regarding performance or other SQL Server related issues at: