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/

Load XML data into SQL 2000

I recently had the need (read "somebody wanted a big favor") to load XML data into a SQL Server 2000 database. I came across an easy way for them (being a developer to push the data into SQL) per the article (http://support.microsoft.com/kb/316005).

After creating the table on SQL, you have to create a mapping file to tell it which fields go with which columns in the SQL database. The article above has a good basic but working example. Modify away.

Basically, I created a VB script as follows:
Note you can use localhost or replace with a SQL instance (default or named). You can also use paths relative to the executable.

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")objBL.ConnectionString = "provider=SQLOLEDB.1;data source=localhost;database=XMLImport;Integrated Security=SSPI"objBL.ErrorLogFile = "error.log"objBL.Execute "XML_Schema.xml", "XML_Data.xml"Set objBL = Nothing

The biggest "gotchas" were the case-sensitivity of the field names and it didn't like spaces either. Also, you had to have the right version to run the VB script. Since I did not, my workaround was to execute the script remotely.

List Table Rowcount and Size

Here's a handy way to get a list of table rowcounts and space used. If you've not used the sp_msforeachtable just plug in the question mark (?) as a token for the name of the table.

--create a temporary table to hold the results
CREATE TABLE #TableStats (
 [name] varchar(128),
 [rows] varchar(128),
 [reserved] varchar(128),
 [data] varchar(128),
 [index_size] varchar(128),
 [unused] varchar(128)
)
GO
--loop through each table and populate the temp table
--NOTE: we can't use table variables with the sp_msforeachtable
sp_msforeachtable "INSERT INTO #TableStats EXEC sp_spaceused '?'"

SELECT * FROM #TableStats
--ORDER BY LEN([rows]) DESC, [rows] DESC --sort by # of rows
ORDER BY LEN([reserved]) DESC, [reserved] DESC --sort by space used
DROP TABLE #TableStats
GO

List of DTS packages

I was recently asked by a fellow DBA if I had "one of my scripts" to give a list of DTS package on a SQL Server 2000 instance. Since the majority of my work is in 2005 and 2008, I just ran the following query.

select * from msdb..sysdtspackages

The results were not helpful because most of the packages had multiple versions stored so instead of listing the 200+ packages, it listed the 7000+ versions of those packages. After a few minutes, I came up with the following query which I've now added to my script library.

SELECT
 pkg.[name],
 pkg.[owner],
 pkg.[description],
 cnt.[CreateDate],
 cnt.[LastModified],
 cnt.[NumOfVersions]
FROM
 [msdb].[dbo].[sysdtspackages] pkg
INNER JOIN
(SELECT
 [id],
 COUNT(*) [NumOfVersions],
 MIN([createdate]) [CreateDate],
 MAX([createdate]) [LastModified]
FROM
 [msdb].[dbo].[sysdtspackages]
GROUP BY
 [id]) AS cnt
ON
 pkg.id = cnt.id AND
 pkg.createdate = cnt.LastModified

SQL Server Profiler Tools

I learned several things about SQL Profiler by watching this video. I found it especially interesting that you can view the ShowPlan and the Perfmon data right from profiler.
You have to setup a free account to view the video but it's worth it. There are dozens of great tutorials like this.
http://www.jumpstarttv.com/using-sql-server-2005-profiler_59.aspx

Move TempDB, Recreate missing TempDB

You can move tempdb by running the following script. If tempdb is missing (due a drive failure, for example), you will need to start the SQL Service with -T3608 for the startup option and then run the script.

/*If tempdb is unavailable, stop and start the SQL service with the
startup option -T3608
This allows you to start SQL without tempdb.
*/
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb.ldf')
GO

Insert Picture into Image Field using T-SQL

Here's an example of how to quickly load a picture into an image field using T-SQL.

INSERT INTO [hr].[NTUsers] (
 [userid],
 [username],
 [title],
 [reportsto],
 [pic]
)
SELECT
 '00001',
 'Chip Porter',
 'Technology Services',
 '00000',
 BulkColumn FROM OPENROWSET(BULK 'c:\noimage.bmp', SINGLE_BLOB) AS ImageLoad
GO