Making a SQL Server Instance Listen on Multiple Ports

Sometimes when upgrading a SQL Server instance to 2005 or 2008 named instance there is still a need to support a legacy application which can only talk to the default instance on port 1433. Normally I handle this scenario in the following way.

(1) Install a named instance and set it a static TCP port (under IP All). (for example, 4532)
(2) Configure a "default" alias to point to the named instance.What I'm calling a "default" alias is an alias named as the server name and assigned to port 1433.

This allows you to access the instance through a number of ways.
ServerName\NamedInstance
ServerName\NamedInstance,4532
ServerName,4532
ServerName
ServerName,1433

This allows SQL to "listen" on multiple ports. Keep in mind based on which options above you choose, you may need Shared Memory enabled as a protocol as well as the SQL Server Browser service enabled. Generally, I use the static ports only and turn the browser service off for a more secure installation. In addition, since I generally remotely connect to a SQL instance, I turned off the Shared Memory protocol.

Get a List of Unique Servers from a list of SQL Instances

Here's an example of pulling just the server name from a list of SQL Server instances.

CREATE TABLE [dbo].[Instances](
 [QID] [smallint] IDENTITY(1,1) NOT NULL,
 [Instance] [varchar](128) NOT NULL
 --additional fields and table options not shown
)
GO
--load the table with data
SELECT DISTINCT --eliminate duplicates
 CASE
  WHEN charindex('\', [instance]) > 0 THEN --if the instance name includes a slash
   substring([instance], 1, charindex('\', [instance]) - 1) --take only the part before the slash
  ELSE
   [instance] --otherwise grab the whole name
 END [ServerName]
FROM
 [dbo].[instances]

Enterprise Software - Scalability, Flexibility and Integration

In yesterday's SSWUG newsletter, Stephen Wynkoop posed the following question.

(excerpt from the SSWUG newsletter 11/10/2008)

What, Exactly, Makes "Enterprise" Software?
The next version of SQL Server takes more steps toward supporting enterprise-class installations and BI solutions. Great!

What is "enterprise" - what does that mean? I'm curious because since even the 7.0 days, and certainly 2000 and beyond, I've seen huge installations of SQL Server - and I know our teams at SQL on Call have worked with large installations and I'm sure you know of them as well. Is it size? Complexity? Type of application?

What exactly is this "Enterprise" thing that is being chased? If someone talks to you about SQL Server being Enterprise-ready... what does that mean to you?

In today's newletter, he posted my answer that I sent in. Read it at http://www.sswug.org/nlarchive.asp?odate=11/11/2008.

"Enterprise Software" Seems that the definition is pretty varied from those that have had a chance to write in about what, exactly, is "Enterprise" software. Specifically, I was curious what this target was that keeps coming up when describing SQL Server - getting it "Enterprise-Ready."

Ben: "Thanks, for the thought-provoking questions. Here are my three cents on this one. Being "Enterprise" ready means scalability, flexibility and integration.

(1) Scalability - To support the load generated by an entire enterprise, an application must be scalable. This means it is a robust platform which can be scaled through hardware upgrades to support the continued growth of the business. The whole infrastructure needs to be manageable (not sprawling) and secure to ensure the long-term strategic goals are achievable.

(2) Flexibility - To service the diverse needs of each business division, an application has to flexible. The exact solution which works in one division may need tweaking to service the needs of another. This agility is also needed to support the ever-changing business landscape. In order to keep pace with its industry, a business needs to make short-term, tactical adjustments which must be implemented quickly.

(3) Integration - Because no single product can meet every need, it must readily integrate with other standard products to deliver a complete solution. This would also include being reverse-compatible with prior versions to provide an easy upgrade path. "

It's interesting that this last point, integration, has come up several times in describing what enterprise-ready software refers to. On the other hand, it seems like enterprise-ready typically refers to scalability, reliability and availability in these two areas in the official descriptions I've seen typically.

SQL 2005 Log Reader - Default Trace

I just read a great article at SQLServerCentral.com about the Default Trace. It's a low-impact trace that's running all the time and writes DDL and other events to a file which can be queried in SSMS. Here's the link. http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/