How to configure SQL Server 2012 for remote network connections

SQL Server 2012, especially when using a named instance, changes the way us old SQL Server veterans manage connectivity to the server.  We are so used to relying on port 1433 for SQL Server that setting up a new SQL Server 2012 database server might give us some fits.

I have one software engineering team that just did this recently.  Our dev and test environments are in Windows Azure, and then our production environment is in a specialized data center on the east coast of the U.S.

To make a long story short, we are using Azure’s point-to-site VPN so that none of the servers can have any contact from the outside world.  The only way to get to them is to VPN in.  Then, the servers respond to pings, and it’s as if they are on the LAN.  It works great – except connecting SQL Management Studio to a new Windows Server 2012 box running SQL Server 2012 wouldn’t work.  All the old tricks of using SQL Configuration Manager and enabling TCP/IP yielded no fruit.  And opening port 1433 on the firewall yielded no fruit as well.

Just for completeness, we were using a named instance, not a default instance.  A quick look at NetStat revealed that SQL Server wasn’t listening on port 1433 at all.

Notice how port 1434 is listening, however.  This is the SQL Server Browser service.  A search through the documentation reveals that Microsoft has made a bit of a change to the way SQL Server operates regarding ports.  These named instances now use dynamic ports – I think for security.  This TechNet article explains that to connect, the client will send a UDP packet to port 1434 first to resolve the dynamic port.  Then it will connect as normal.

The appropriate Windows Firewall rule to establish is a program rule, not a port rule.

By adding SQL Server’s exe as a program rule in the firewall, it now works.

It’s been several versions since something like this has changed in SQL Server, so hopefully this article will help someone else using SQL Server 2012 for the first time.

GoToMeeting works great–until you add video conferencing

I have been a loyal customer of GoToMeeting for years.  I love the cost, flexibility, screen sharing, and audio quality.  It works very well.

In 2013, I’ve started using the video conferencing feature much more heavily – meaning, I’m using it.  At Clear Measure, we have teams in Austin, Dallas, Toronto, and New York.  On top of that, our clients like to connect to us from wherever they are.  The phone conference dialing is very useful, and the smartphone app is great as well. 

The issue I have found is that the video conferencing takes a remarkable amount of CPU time.  Many team members have difficulty using video conferencing when other process are running on their computers.  Webex, on the other hand, appears to degrade video quality in that case.  GoToMeeting seems to require full video quality and audio quality at all times.  And when the CPU can’t keep up, audio and video just cut out and stop working (dropped packets/words/sentences).

Here is GoToMeeting running without video (call with 5 people – my webcam is off, other 4 webcams are streaming)

GoToMeeting without video

And here is GoToMeeting running with my webcam turned on.

GoToMeeting with video

If I could have my way, when faced with constrained CPU cycles, video quality would degrade first.  Then screen sharing quality, then audio quality, The current behavior has me shopping for other solutions.  I’d be interested knowing other experiences.

Onion Architecture for Distributed Systems at Austin Code Camp, 2013

Kudos to the Austin .Net User Group for organizing another Code Camp!  I have submitted a very interesting talk (in my opinion). It is Onion Architecture for Distributed Systems.

Abstract:

Onion Architecture is an architectural pattern for keeping libraries and dependencies on the extremities of a software system while building a strong and cohesive system core.  Distributed systems like those that leverage NServiceBus and Enterprise Service Bus (ESB) products have characteristics that challenge layering principles of non-distributed systems.  This session will show how to properly structure and layer a .Net/C# software system that makes use of Onion Architecture, NServiceBus, and ASP.NET MVC.  The target audience for this session is:  C#, architect, senior engineer, team leads/lead developers.

_______________________________________________

If you are interested in speaking at the Code Camp, please submit a talk.  http://codecamp13.adnug.org/speakers.html