Getting Drive Info Part 2, DTS

In this installment of the drive info series we will gather the drive information via a DTS package. Part 1 of this series used the File System Object from the Windows Scripting Host via the sp_OA suite of stored procedures to gather the drive information. The File System Object will be used again to accomplish this task, but this time from an ActiveXScript task. Using this method can only collect drive information from the server where this DTS package is run. The drive information can be stored on any SQL Server.

Open a new DTS package in the DTS Designer and create a new Connection object that points to a SQL Server where the drive information is going to be stored. Set the database to tempdb or the database that will hold the DriveInfo table which will be created in the package.

Create a new string global variable named DriveInfoXml.

The next thing that needs to be done is create the table to hold the data. Drag an Execute SQL task into the DTS Designer and add the following SQL statement.

IF NOT EXISTS(SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_NAME = 'DriveInfo')
CREATE TABLE dbo.DriveInfo
( Drive VARCHAR(255)
, DriveSize FLOAT
, FreeSpace FLOAT)

Here is a screen shot of the Execute SQL task set up.

Now we need to collect the drive information. Collection of the drive information is accomplished using the File System Object from an ActiveX Script task. The script accomplishes the same thing as the sp_OA sql script, but looks less like dynamic sql and doesn’t use xp_fixeddrives. The script stores the drive information in XML formatted data to be used in the later steps which insert the data in the table. Drag an ActiveX Script task into the DTS Designer, name it “Get Drive Info” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Create DriveInfo Table Execute SQL task to the Get Drive Info ActiveX Script task.

'**********************************************************************
'  Get Drive Size
'
'**********************************************************************

Function Main()

	Dim FSO
	Dim Drives
	Dim Drive
	Dim XML

	' Set up global data.
	Const DriveTypeRemovable = 1
	Const DriveTypeFixed = 2
	Const DriveTypeNetwork = 3
	Const DriveTypeCDROM = 4
	Const DriveTypeRAMDisk = 5
	NewLine = Chr(10)
   
	Set FSO = CreateObject("Scripting.FileSystemObject")

 	Set Drives = FSO.Drives

  	'Create an XML doc to use in SQL
	XML = "<drives>"
	For Each Drive In Drives
		If Drive.DriveType = DriveTypeFixed Then
			XML = XML & "<drive DriveLetter=""" & Drive.DriveLetter & """ TotalSize=""" & Drive.TotalSize & """ FreeSpace=""" & Drive.FreeSpace & """/>"
		End If
	Next
	XML = XML & "</drives>"
	DTSGlobalVariables("DriveInfoXml").Value = XML
	MsgBox(XML)
	Main = DTSTaskExecResult_Success

End Function

Here is a screen shot of the Get Drive Size ActiveX Script Task.

We use an ActiveX Script task for the next step that builds the sql command for the Execute SQL task to follow. The XML data created previous step is used in a sql script that utilizes the XML features of SQL Server. There are many ways to get this data into a table in a DTS package, but I choose XML just to show another example that didn’t fall back on Ado. I also think it’s kind of silly to use Ado in DTS when there are already database connections to use. It’s probably a little easier to debug as the output of this task can be copied into a query tool and tested.

Drag another ActiveX Script task into the DTS Designer, name it “Build Put DriveInfo” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Get Drive Info ActiveX Script task to the Build Put DriveInfo ActiveX Script task.

'**********************************************************************
'  Build Put DriveInfo
'************************************************************************

Function Main()

Dim SQL
Dim oPkg
Set oPkg = DTSGlobalVariables.Parent

SQL = "DECLARE @hdoc int"  & VbCrLf &_
"DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher"  & VbCrLf &_
"SET @doc ='" & DTSGlobalVariables("DriveInfoXml").Value & "'"  & VbCrLf &_
"--Create an internal representation of the XML document."  & VbCrLf &_
"EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc"  & VbCrLf &_
"-- Execute a SELECT statement that uses the OPENXML rowset provider."  & VbCrLf &_
"INSERT INTO dbo.DriveInfo"  & VbCrLf &_
"SELECT DriveLetter"  & VbCrLf &_
"     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)"  & VbCrLf &_
"     , FreeSpace / POWER(1024, 2)"  & VbCrLf &_
"  FROM OPENXML (@hdoc, '/drives/drive',1)"  & VbCrLf &_
"          WITH (DriveLetter varchar(255)"  & VbCrLf &_
"               ,TotalSize float"  & VbCrLf &_
"               ,FreeSpace float)"

oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = SQL
	Main = DTSTaskExecResult_Success
End Function

Here is a screen shot of the Build Put DriveInfo ActiveX Script task.

The final step in the DTS package is to add the Put DriveInfo Execute SQL Task. The initial sql command is SELECT GETDATE() just to make the parser happy. The actual sql command to execute was generated by the previous step. Here is a sample of the sql command generated by a test run.

DECLARE @hdoc int
DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher
SET @doc ='<drives><drive DriveLetter="C" TotalSize="500000878592" FreeSpace="50790670336"/></drives>'
--Create an internal representation of the XML document.
EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
INSERT INTO dbo.DriveInfo
SELECT DriveLetter
     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)
     , FreeSpace / POWER(1024, 2)
  FROM OPENXML (@hdoc, '/drives/drive',1)
          WITH (DriveLetter varchar(255)
               ,TotalSize float
               ,FreeSpace float)

Here is a screen shot of the completed package.

sp_help_partition

If you are using partitioned tables, this may come in handy. I’ll keep it short and just post the code. Let me know if there are errors or you have improvements.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_help_partition]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_help_partition]
GO

CREATE PROCEDURE dbo.sp_help_partition @OBJECT_NAME sysname = NULL
AS
-------------------------------------------------------------------------------
-- 
-- Proc: sp_help_partition
-- Author: Norman Kelm, Gerasus Software, (C) 2010
--
-- Like sp_help, but for partitioned table partition info.
--
-- Usage:
-- Return a list of table that are partitioned
-- EXECUTE sp_help_partition
--
-- Return the partitioning information for a table
-- EXECUTE sp_help_partition 'Production.TransactionHistory'
--
-- Revision History:
-- 08/06/2010 - 1.0 - Original version
--
-------------------------------------------------------------------------------
DECLARE @schema sysname
      , @TABLE_NAME sysname
      
SELECT @schema = CASE WHEN PARSENAME(@OBJECT_NAME,2) IS NULL THEN 'dbo' ELSE PARSENAME(@OBJECT_NAME,2) END
     , @TABLE_NAME = PARSENAME(@OBJECT_NAME,1)

IF @OBJECT_NAME IS NULL
BEGIN
  -- List all paritioned tables when no paramters
  SELECT DISTINCT SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId')) + '.' + OBJECT_NAME(si.object_id) AS [Name]
    FROM sys.partition_schemes AS ps
   INNER JOIN
         sys.indexes AS si
      ON ps.data_space_id = si.data_space_id
END
ELSE
BEGIN
  IF EXISTS(SELECT 1 
              FROM sys.partition_schemes AS ps
             INNER JOIN
                   sys.indexes AS si
                ON ps.data_space_id = si.data_space_id
               AND OBJECT_ID(@OBJECT_NAME) = si.[object_id])
  BEGIN
  
    -- Borrowed from sp_help
    SELECT [Name]  = o.name
         , [Owner] = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid'))
         , [Schema] = SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId'))
         , [Type]  = substring(v.name,5,31)
         , [Created_datetime] = o.create_date  
     FROM sys.all_objects o
        , master.dbo.spt_values v  
    WHERE o.object_id = OBJECT_ID(@OBJECT_NAME)
      AND o.type = substring(v.name,1,2) collate database_default
      AND v.type = 'O9T'  
 
   SELECT COUNT(*) AS NumberOfPartitions
     FROM sys.partitions  p
    INNER JOIN
          sys.indexes i
       ON p.object_id = i.object_id
      AND p.index_id = i.index_id
    WHERE OBJECT_ID(@OBJECT_NAME) = p.[object_id]  
      AND i.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
       
    SELECT [Scheme] = ps.name 
      FROM sys.partition_schemes AS ps
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
      
    SELECT [Function] = pf.name
         , [Type]     = pf.type_desc
         , [fanout]   = pf.fanout 
         , [boundary_value_on_right] = pf.boundary_value_on_right
         , [create_date]             = pf.create_date
         , [modify_date]             = pf.modify_date
      FROM sys.partition_functions AS pf
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Parameters] = pf.name
         , [parameter_id] = pp.parameter_id
         , [Type] = st.name
         , pp.max_length
         , pp.precision
         , pp.scale
         , pp.collation_name
      FROM sys.partition_parameters AS pp
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Range Values] = pf.name
         , prv.boundary_id
         , prv.value         
      FROM sys.partition_range_values AS prv
     INNER JOIN
           sys.partition_parameters AS pp
        ON prv.function_id = pp.function_id
       ANd prv.parameter_id = pp.parameter_id
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]  
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
     ORDER BY
           prv.boundary_id
           
     SELECT tc.CONSTRAINT_NAME
          , cc.CHECK_CLAUSE
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
       JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
         ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
        AND tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
      WHERE tc.TABLE_SCHEMA = @schema
        AND tc.TABLE_NAME   = @TABLE_NAME
        AND tc.CONSTRAINT_TYPE = 'CHECK'
   END
   ELSE
   BEGIN
     DECLARE @error NVARCHAR(255)
     SELECT @error = @OBJECT_NAME + ' is not partitioned!!!'
     RAISERROR (@error, 16, 1)
   END
END
GO

Getting Drive Info Part 1, sp_OA

This blog series will cover several different methods to collect drive information from a Windows server that is running SQL Server. I’ve answered several requests in forums, news groups and twitter in the past about how to get the drive size of all of the hard drives on the server that houses a SQL Server instance. These people found the undocumented system stored procedure xp_fixeddrives which returns a result set of the drives on the server along with free space. They were looking for a way to get the drive size as well. This would be a useful addition to xp_fixeddrives to create a disk growth monitoring system. This blog series will also show methods that are specific to SQL Server 2000 as well as methods that utilize the newer features available in versions 2005 and 2008 in future posts. The techniques presented can be modified to get just about any information about the Windows server that houses the SQL Server instance.

Retrieving Drive Size Using sp_OA

The first method discussed is old to say the least as I have been using it since SQL Server 7.0 and there are still plenty of SQL Server 2000 instances running that can make use of this method. It utilizes the sp_OA suite of stored procedures. I have used this method to gather data for an automated SQL Server status report daily for years on SQL Server 7.0, 2000 and 2005. Although there are reports that the sp_OA stored procedures can have memory problems and are a security risk, I have yet to have an issue. They are also set to be deprecated at some point. These points are something that needs to be considered before using this solution. The sp_OA suite of stored procedures allows the use of COM objects from within Transact-SQL. Yes, in order to get the drive size you need to do a little bit of object oriented programming. Much like xp_cmdshell, this suite of store procedures has security risks. Microsoft recognized this and disabled them by default in SQL Server 2005 and 2008. They need to be enabled by running the script (the following is not required on SQL Server 2000):

-- For SQL Server 2005 and 2008 only
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE

If you are really worried about the security aspects of having these stored procedures enabled continuously, the above script could be made part of the entire drive info script. This would enable the store procedures for only as long as necessary to get the drive information. The Ole Automation Procedures configuration option could be disabled at the end of the drive size script.

In order to get the drive size a new instance of the Windows Scripting Host File System Object has to be created. The GetDrive method has to be called and then the property TotalSize is retrieved. Here is the script for the C: drive.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)

-- Create a new Windows Scripting Host File System Object
EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

-- Get the drive information for the C: drive
IF @rs = 0
  EXEC @rs = sp_OAMethod @fso, 'GetDrive("C:\")', @drv OUTPUT

-- Get the size of this drive
IF @rs = 0
  EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
IF @rs<> 0
  SET @drivesize = '0'

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

SELECT CAST( @drivesize AS FLOAT ) / 1024.0 / 1024.0

The next step in the process is to include xp_fixeddrives to get a list of all the drives on a server and iterate through the result set of drives to get the drive size. A table is populated with all the fixed drive info. This data is iterated and the drive size for each drive is collected from the file system object. Here is the script.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)
      , @drive VARCHAR(255)
      , @GetDrive VARCHAR(255)

DECLARE @DriveInfo TABLE(Drive VARCHAR(255), DriveSize FLOAT, FreeSpace FLOAT)

-- Get all the drives and free space in MB
INSERT INTO @DriveInfo(Drive, FreeSpace) EXECUTE xp_fixeddrives
-- Convert Free Space to GB if applicable to the environment
UPDATE @DriveInfo SET FreeSpace = FreeSpace / 1024.

SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

WHILE @drive IS NOT NULL
BEGIN

  -- Create a new Windows Scripting Host File System Object
  EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

  -- Get the drive information for the drive
  SELECT @GetDrive = 'GetDrive("'+ @drive + ':\")'
  IF @rs = 0
    EXEC @rs = sp_OAMethod @fso, @GetDrive, @drv OUTPUT

  -- Get the size of this drive
  IF @rs = 0
    EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
  IF @rs<> 0
    SET @drivesize = '0'

  UPDATE @DriveInfo SET DriveSize = CAST(@drivesize AS FLOAT) / 1024.0 / 1024.0 WHERE Drive = @drive
  -- Convert Drive Size to GB if applicable to the environment
  UPDATE @DriveInfo SET DriveSize = DriveSize / 1024. WHERE Drive = @drive

  SELECT @drive = NULL
  SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

END

SELECT * FROM @DriveInfo

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

The data in the @DriveInfo table can be stored permanently in a persisted table for size monitoring and historical reporting.

This concludes the first part of Drive Info series. The sp_OA stored procedures can be used to gather all kinds of information. There are many more properties to look at in the Drive object, go here <add link to Drive docs> for more info. Other COM objects could be instantiated via sp_OA and used to gather server information.

In the next installment of the drive info series, I will show another method supported by SQL Server 2000 and collect the same information using DTS.

SSIS-DTS Package Search 2.0.18 released

SSIS-DTS Package Search 2.0.18 has been released. This is a bug fix release that addresses a problem with the search of the parameter and ResultSet bindings in the Execute SQL task.

Living with DTS on SQL Server 2005 and 2008

The DTS support in SQL Server 2005 and 2008 is excellent (almost). Typically the packages and jobs do not need any modification in order to run on SQL Server 2005 or 2008. Unfortunately, I have read far too many news group posts that indicate a complete conversion to SSIS MUST take place during a SQL Server 2000 upgrade. It is not mandatory. I am not saying to avoid SSIS. The existing DTS packages can be reviewed for candidates to convert to SSIS during the upgrade. The old DTS packages could also be converted as time permits after the upgrade.

DTS Packages can be run and edited in SSMS as long as the Backwards Compatibility and DTS Designer components from the Sql Server 2005 Feature Pack have been installed. It is available for SQL Server 2005 SP2 here, SQL Server 2005 SP3 here and SQL Server 2008 SP1 here.

The two features that are missing from SSMS are the ability to create a new package from scratch and the detailed list of DTS packages which are available in Enterprise Manager. The lack of these two features in SSMS requires a copy of Enterprise Manager to be available and the continued use of Windows XP since Sql Server 2000 is not compatible with Vista and later versions of Windows.

In order to address these missing features, I have created a new utility and two custom reports for SSMS.

The utility is Create DTS Package. It will create an empty DTS package in a structure storage file or SQL Server.

Create DTS Package creates a blank DTS package and saves it in as a structured storage file or in a SQL Server.

Enter the name of the new package in the Package name text box.

If the package storage destination is a structured storage file, enter the full path in the File Name text box and click on the Save To File button.

If the package storage destination is a sql server, choose the authentication, enter the server name in the Server text box, provide the credentials and click on the Save To Sql Server button.

Create DTS Package is an HTA application.

Create DTS Package has eliminated the need for the Enterprise Manager installation in a SQL Server 2005/2008 environment that still needs to support DTS packages.

Create DTS Package will run on both 32(x86) and 64(x64) bit installations.

Create DTS Package could also be modified to add a standard group of objects for your organization to create a package template.

The source code for Create DTS Package is available for download here (right click the link and click Save Target As…\Save Link As..) or here for a listing of the source code (trying to save an hta is causing security violations for some people).

The Package Summary custom report recreates the Local Packages view from Enterprise Manager in SSMS.

In order to support mixed DTS/SSIS environments, the report shows both types of packages.

Since this custom report does not require any object inputs, it can be run from anywhere in the SSMS Object Explorer.

The SQL Server 2005 version is available here.

The SQL Server 2008 version is available here.

Here is a sample output of the report.

Update: If you are running 32-bit Windows 7, there is quite a bit more to do to get the environment set up correctly. See Jason Brimhall’s excellent post SQL 2008 DTS

Update: In order to get DTS working on 32-bit Windows XP with SQL Server 2008 R2, the above steps will need to be followed as well.

Which version of SSIS-DTS Package Search is right for you

The download page for SSIS-DTS Package search presents three choices:

• SSIS-DTS Package Search for SQL Server 2000 on Windows x86
• SSIS-DTS Package Search for SQL Server 2005 on Windows x86
• SSIS-DTS Package Search for SQL Server 2008 on Windows x86

Although there are really only two different versions of the executable, I wanted to give a SQL Server 2000 choice to make it clear that DTS is still supported. Actually both versions support DTS packages. The reason for the two versions is to match the SQL Server client tools installed for the newer SQL Server releases, 2005 and 2008 respectively.

SSIS-DTS Package Search for SQL Server 2000 on Windows x86 is the version that supports SQL Server 2000 DTS packages. There is not a version that only supports DTS packages. This choice will download SSIS-DTS Pacakge Search for SQL Server 2005. It will install and run with out issue on a computer that only has the SQL Server 2000 client tools installed.

SSIS-DTS Package Search for SQL Server 2005 on Windows x86 is the version that supports SQL Server 2000 DTS packages and SQL Server 2005 SSIS packages. This version can be installed on a computer that has both the SQL Server 2000 client tools and the SQL Server 2005 client tools. It can also be installed on a computer with only the SQL Server 2005 client tools and support DTS as long as the Backward Compatibility components are installed.

SSIS-DTS Package Search for SQL Server 2008 on Windows x86 is the version that supports SQL Server 2000 DTS packages, SQL Server 2005 SSIS packages and SQL Server 2008 SSIS packages. This version can be installed on a computer that has both the SQL Server 2000 client tools and the SQL Server 2008 client tools. It can also be installed on a computer with only the SQL Server 2008 client tools and support DTS as long as the SQL Server 2005 Backward Compatibility components are installed.

Moving to Hyper-V

My primary development machine for the past 3 years was a 32 bit Toshiba Satellite notebook running XP named PROBE-DROID. I was very happy with the machine. It went through several upgrades, more memory and a new 7200 RPM Hitachi hard drive. The purpose of PROBE-DROID was a portable virtual development and testing computer lab. I used VMWare Server 1.x for clean build and test machines. I was very happy with VMWare Server 1.x. The user interface integrated well with Windows. The only major problem I had was with one of the point upgrades breaking the network in some way so that the host OS could no longer access the internet. After a few hours of troubleshooting and research, I decided it would be better to go back to the previous version and get on with the development of DTS Package Search. A few months later VMWare Server 2.0 was released and I upgraded. I was not very happy to see Java and Tomcat with a web based interface. Of course the inevitable Java security update caused Tomcat stop the VMWare web interface from working. Uninstalling the security update had no effect. Instead of wasting precious development time trying to fix this, I reinstalled VMWare Server 2.0 and all was good again. I refrained from installing any Java updates after that.

I had no need or interest in moving to Vista, so I carried on. As PROBE-DROID aged the battery life degraded to the point where it couldn’t make it an hour with out a recharge. I needed to replace the battery or move on to something new. With the release of Windows Server 2008 and Sql Server 2008, it became clear that I needed to support the newer platforms and features. I stumbled across several web sites that discussed running Windows Server 2008 as workstation. They claimed that it performed much better than Vista yet with all the bells and whistles. I upgraded the 64 bit PC at home to from XP to Windows 2008 “Workstation” to test this configuration. http://www.win2008workstation.com/ was particularly helpful with its automated tool to turn on all the Vista goodness. I also wanted to take a look at the Hyper-V role, but the PC lacked the Virtualiztion Technology feature that Hyper-V required.

At that point rather than buy a new battery for PROBE-DROID, I decided to get a new 64 bit notebook. Initially I research Intel’s web site to find the CPUs that supported Virtualization Technology. I purchased an HP notebook with a CPU the met the requirements. After installing Windows Server 2008, I had no luck with the Hyper-V role. Virtualization Technology needs to be handled by the BIOS as well. After bouncing around HP support and sales, I found someone that knew the answer. I needed a notebook that was branded vPro.

The first HP was returned. In order to get a vPro branded notebook, I needed to move to a business class machine. The HPs and Dells were all around $3000 for an Intel T9400/4GB/200GB 7200 RPM configuration which was much more than I wanted to spend. I decided to take a look at Toshiba model and found a Tecra A10 with the right parts for less than half. Since the new notebook was the much more potent 64 bit son of PROBE-DROID, I christened it PROBE-DROID64.

PROBE-DROID64 came with Vista. The plan was to wipe and install Windows Server 2008, but with the beta release of Windows Server 2008 R2 I decided to go that direction. That decision turned out to be a rough road as the dreaded BSOD would appear randomly will running a Hyper-V VM. The performance of the terminal service sessions was also poor with lots of screen flashing while navigating through Visual Studio 2008. I was almost ready to move off R2 beta when the RC was announced. I went the upgrade path to move from beta to RC. The RC release fixed everything and now I have a stable and well performing portable virtual development and test computer lab. I look forward to the RTM of Windows Server 2008 R2. I will discuss my experiences using Hyper-V in another post.

Anti-Virus Adventures

Denny Cherry wrote a blog post recently discussing whether to run anti-virus software on Sql Server machines. He said you should and I agree. It is just too risky not to run anti-virus with all of the notebook computers coming in and out of the office. The post did remind me of something that happened to a Sql Server cluster a few years back was an “if it can happen, it will happen.” The anti-virus vendor pushed out a bad virus signature file that flagged sqlservr.exe as a virus. They realized the mistake and push out a corrected file after the bad one was out for a only short period. Of course our cluster had pulled bad file. Of course sqlservr.exe was found on the passive node and quarrentined. Of course the cluster failed over, or tried to. We were a bit perplexed why sqlservr.exe had disappeared, but figured out where it went and got things running with out too much down time.

SQL This, Not That – Episode 2

When creating a table with a column that will hold the name of a stored procedure that feeds an EXEC command, make sure the data type of this column is sysname. The data type of this column should not be VARCHAR(50) or some other arbitrary data type that should just do the job. This will also be true for columns that hold table and column names. Any column in a user table that holds meta data from a Sql Server system table should match the data type of the source system table column.

Chicago relocated to Florida!