Service Broker Protocol Transport Cannot Listen on Port 4022

Applies To:

Operating System	Server 2012, Server 2012 R2
SCCM	2012, 2012R2
SQL Server	2008 R2, 2012 and 2014

As part of the morning checks, I generally check SCOM for any errors or look at SCOM for any new alerts.

Saw the below error and started working to resolve it:

The alert was spitting chips about our SCCM database server.

Logged on to the SCCM database server.

Ran netstat –aon | find “4022” on a command prompt on the server to show me all the connections using port ‘4022’.
Looked for any sign that the port ‘4022’ was in use by anything else.

Checking the SQL Server Management Studio Logs, I could reaffirm that there was a conflict on the port:

If we expand Server Objects then Service Broker.
Right click on the object and select Create. This will create the object into a new query window so we can see what settings have been set:

You can see in the Red box above that the Endpoint is set to use port 4022.

Decided to double check what TCP Port was setup, and low and behold, it is set to the same Port as the TCP Endpoint hence why we are having conflict issues.

Solution

Change the TCP Port within SQL Server Configuration Manager to something other than 4022, like 1433 or 14331.

SAN vs SAS Disks

 

I always thought that all harddrives were the same. The only difference was capacity and brand reliability. That all changed when I got into reviewing SQL Server disk performance and accumulating statistics over the past several years, I started getting interested into the different types of storage and testing these different types of storage.

I came across the following spec comparison chart, comparing SAS vs SATA. Incase you didn’t know, SATA is what you typically buy for your desktop PC or laptop. SAS is enterprise grade and usually found is NAS and SAN systems. Bit of a price difference, with SAS hard-drives about 70% more expensive than SATA hard-drives.

In the below table, I have highlighted in RED the major differences that I believe make SAS hard-drives are worth the extra $$$.

Requirement		SAS	SATA
Workload		100%	10-20%
Cost Sensitivity		Moderately sensitive to cost	Sensitive to low cost
Performance	Latency and Seek	5.7 msec @ 15K rpm	13 msec @ 7200rpm
	Command Queuing and Reordering	Full	Limited
	Rotational Vibration Tolerance	Up to 21 rads/sec/sec	Up to 5 to 12 rads/sec/sec
	Typical I/Os per sec/drive	319	77
	Duplex Operation	Full	Half
Reliability	Bad Sector Recovery	Typical time out 7-15 sec only	Time outs up to 30 sec
	Misalignment detection	Dedicated Servo and data path processors	Single combined servo/data path processor or none
	Vibration Sensors	RV Compensation Feedback Mechanism	No RV Compensation
	Variable Sector Size	Utilizes a 528-byte sector and allow the I/O controller	Does not utilize a variable sector size (locked at 512 bytes)
	MTBF	1.2M hours at 45 degrees C	700K hours at 25 degrees C
	Internal Data Integrity Checks	End to End	Limited, none in memory buffer
	Maximum Operating Temperature	~60 degrees C	~40 degrees C
	Warranty	~5 years	~ to 3 years
Features	Spindle Motor	Higher RPM Tighter run-out Spindle anchor at both ends	Moderate to lower RPM Lower specification for run-out Spindle anchored at one end
	Media	Full media cert	Lower media specification and density
	Head Stack Assembly	Structural rigidity Lower inertial design	Lighter weight design Higher inertial design
	Actuator Mechanics	Larger magnets Air turbulence controls RV sensors and closed loop RV suppression	Smaller magnets No air turbulence compensation No RV sensors or suppression – limited to servo wedge track alignment
	Electronics	Dual processors (dedicated servo and data path processors) Performance optimization Advanced error handling Advanced firmware algorithms	Single processor No performance optimization Standard error handling Standard Firmware algorithms
Customization	FW Code	Extensive	Limited
	Variable Sector Sizes	Yes	No
	LEDs	Yes	No

Fast disks can make a big difference in performance.

If you don’t have the ability to purchase fast disks then increase the memory on the database server to offset the slow disk. More memory means that SQL Server can cache more into its memory rather than have to go to disk to get the data.

Source: http://www.intel.com/content/www/us/en/support/server-products/000005782.html

 

Full-Text Index Searches On A Read-Only Replica

You learn something new every day.

Client wanted to offset the Full-Text Index searches onto the Read-Only Replica of an Always-On Cluster.

Wasn’t too sure exactly how to go about it, but did some research and came across the below MSDN article which mentioned the exact thing I was after:

“full-text indexes are synchronized with the secondary databases”

https://msdn.microsoft.com/en-us/library/ff878253.aspx

So all I need to do is install the Full-Text Search feature on the Read-Only replica and Full-Text Searches will be conducted on the Read-Only replica with no further configuration needed.

Interview Tips for SQL Server

Having recently changed companies and working on a new and exciting project, I have played a big part in secure colleagues roles in other organisations for more money.

One of the main things I strongly enforce is to be yourself.

Most recruiters or interviewers want people that have a sense of humour and good people skills. People who work well within in a team and who can adjust to a new environment easily.

There is always a first time for everything. This time being behind the other side of the desk conducting an interview.

So where do you start.
I would look at every inch of this person CV. Looking at timelines, how long they were at each workplace, as well as the way they write and communicate through their CV. Any relevant certifications. How recent are they? Is there newer certifications out there?

Here are some of the questions that I thought weren’t too technical but would separate DBAs from someone who dabbles with SQL:

1. What motivates you?
2. Who is Ola Hallengren?
3. Fastest way to get a 1000 records in a database?
4. How to tell if a query will scale for PROD.
5. Always-On cluster build instructions.
6. What do you use for SQL Server monitoring?

With the questions above, there is no right or wrong answer. What is interesting is to hear the answers. You generally can spot the Developers from the DBAs from the answers. For example, question 4. Most of the time DBAs will look at the execution plan as well as the statistics IO to see if the query is ready for PROD. Developers will generally say that the appropriate indexes are there. Both answers are right but it gives you an idea of how the person thinks

.