Batchscript with VMFS driver

This is as-is, and AWSOME...ref http://code.google.com/p/vmfs/


THANK YOU http://www.fluidops.com/

@echo off
:: This was just something thrown together to semi-automate the copy of vm's to local disk
:: BLAZING SPEEDS
:: With a little more effort this would save so much time and can automate the backup of all vms on an ISCSI share.
SET DirList=E:\VMBackups\dir.txt
SET CopyFolder=WinXPIE7
SET Destination=E:\VMBackups
:: This assumes you already connected Iscsi and its Disk 1
SET iDrive=\\.\PhysicalDrive1
SET vmfscmd=java -jar C:\vmfs_r95\fvmfs.jar

%vmfscmd% %iDrive% dirall / >%DirList%

CALL:GOGETEM Copper
CALL:GOGETEM Silver
CALL:GOGETEM Gold

GOTO:EOF
:GOGETEM
FOR /F "tokens=4 delims= " %%G IN ('findstr %1 "%DirList%"') DO call:copyvmdata "%%G" "%1"

GOTO:EOF

:copyvmdata
SET vPath=%~1
SET vFolder=%~2
SET vPath=%vPath:/=\%

IF "%vPath%"=="/%vFolder%" GOTO:EOF
IF NOT EXIST "%Destination%/%vFolder%" MD "%Destination%/%vFolder%"
%vmfscmd% %iDrive% filecopy %1 %Destination%%vPath%

GOTO:EOF


~Enjoy

Open Source VMFS Driver --AWSOME Orz

Open Source VMFS Driver

ESXI + openfiler(iscsi) + trying to copy/move files = shoddy lamb, various reasons, moreover the copy/move bandwidth limitations cause is esxi(free) vs Paid, IMHO.

Anywhoo, stumbled across an open source project, "Open Source Virtual Machine File System (VMFS) Driver". Necessity to move data, faster than a speeding snail got the best of me.

If you are suffering from weak sauce data tranfers of 1-2MBs or less you got to check this out.

I connected openfiler iscsi share used by ESXi to windows XP via microsoft iscsi initiator. fiddled with command line a bit, and BAM, 1gb/min transfer speeds. DAMN!!!

So this proved to me the hardware and network was sound and its just some mickey mouse limitation being imposed on the free version. This even blew Veeams FastSCP out of the water.


~Enjoy

monitor 3ware on Openfiler

Got openfiler? Got 3ware raid controller? Got no monitoring setup? Go here and prosper.


THANK YOU openfiler forum

And more specifically:Kahlid74 and lvincent

Got it all working on the first shot, thought the email setup was tricky


SSMS - Right Click Server Properties, error

Another WTM moment: Cannot show requested Dialog.


An itch told me to profile and see what going on behind the scenes:

CREATE TABLE #SVer(ID INT, Name SYSNAME, Internal_Value INT, Value NVARCHAR(512))
INSERT #SVer EXEC MASTER.dbo.xp_msver

DECLARE @SmoRoot NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

SELECT
(SELECT Value FROM #SVer WHERE Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(
SELECT Value FROM #SVer WHERE Name = N'Language') AS [Language],
(
SELECT Value FROM #SVer WHERE Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS SYSNAME) AS [Edition],
(
SELECT Internal_Value FROM #SVer WHERE Name = N'ProcessorCount') AS [Processors],
(
SELECT Value FROM #SVer WHERE Name = N'WindowsVersion') AS [OSVersion],
(
SELECT Internal_Value FROM #SVer WHERE Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@SmoRoot AS [RootDirectory],
CONVERT(SYSNAME, SERVERPROPERTY(N'collation')) AS [Collation]

DROP TABLE #SVer

-----
(0 row(s) affected)
Msg 0, LEVEL 11, State 0, Line 0
A severe error occurred ON the CURRENT command. The results, IF ANY, should be discarded.

Bah, first 2 lines. BUT, thats not all. Hunting and pecking as the norm goes.

Son-Of-Monkeys!!!

DECLARE @SmoRoot NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
SELECT @SmoRoot
-------
c:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\

WTM!!!

SQL, I KICK YOUR DOG!!!

I can only surmise that Reporting services lacks the proper resources to execute extended stored procs. And in addition, this may be benign for most, but in my case SQL was not installed in the default location, which would have been in same path, c:\Program Files\Microsoft SQL Server.

No one was using reporting services, so hey, uninstall it. And what do you know. It all works again. Meh, it was a bit of troubleshooting with the registry, prior to pulling the trigger on the uninstall, I change the name of the MSSQL.3\Setup key and renameing to Setupx. Changing that produced the expected query results.

Hope this helps someone other than myself.

~Enjoy

Follow-up to Track Field/Column Level Changes.

This just a follow-up post to: Is This Too Much? An Attempt To Track Field/Column Level Changes.


Here's an example of how to make use of your new Tracking.

Run these sample updates
--// Sample updates
UPDATE
Customer
SET
FirstName = 'Bob'
, LastName = 'Mihada'
, WorkPhone = '808-808-808'
, Email = 'fake@uout.com'
, STATE = 'HI'
WHERE
CustomerId = 566

UPDATE
Customer
SET
LastName = 'Beans'
WHERE
CustomerId = 566

UPDATE
Customer
SET
MiddleName = 'K'
WHERE
CustomerId = 566

UPDATE
Customer
SET
MiddleName = ''
WHERE
CustomerId = 566

UPDATE
Customer
SET
FirstName = 'Bob'
, LastName = 'Mihada'
, WorkPhone = '808-808-808'
, Email = 'spambot@die32123.we3w.34.com'
, STATE = 'HI'
WHERE
CustomerId = 566

Then run this to view changes(Note this limited to LastName, FirstName, MiddleName)
--// Retrieve history
DECLARE @CustomerID INT
SELECT
@CustomerID = 566;
WITH CTE
AS (
SELECT
A.CustomerID
, ROW_NUMBER() OVER (PARTITION BY A.CustomerFieldId ORDER BY CustomerFieldAuditDateRecorded) Interval
, CASE WHEN A.CustomerFieldId = 2
THEN A.CustomerFieldAuditValue.value('(/)[1]',
'nvarchar(100)')
END LastName
, CASE WHEN A.CustomerFieldId = 3
THEN A.CustomerFieldAuditValue.value('(/)[1]',
'nvarchar(100)')
END FirstName
, CASE WHEN A.CustomerFieldId = 4
THEN A.CustomerFieldAuditValue.value('(/)[1]',
'nvarchar(100)')
END MiddleName
, A.CustomerFieldAuditDateRecorded
FROM
CustomerFieldAudit A (NOLOCK)
WHERE
A.CustomerID = @CustomerID
)
SELECT
Interval
, CASE WHEN MAX(C.LastName) IS NOT NULL THEN MAX(C.LastName)
ELSE P.LastName
END LastName
, CASE WHEN MAX(C.FirstName) IS NOT NULL THEN MAX(C.FirstName)
ELSE P.FirstName
END FirstName
, CASE WHEN MAX(C.MiddleName) IS NOT NULL THEN MAX(C.MiddleName)
ELSE P.MiddleName
END MiddleName
, MAX(CustomerFieldAuditDateRecorded) CustomerFieldAuditDateRecorded
FROM
CTE C
JOIN Customer P ON P.CustomerID = C.CustomerID
WHERE
P.CustomerID = @CustomerID
GROUP BY
C.INTERVAL
, P.LastName
, P.FirstName
, P.MiddleName

Should end up with something like this:
IntervalLastNameFirstNameMiddleNameCustomerFieldAuditDateRecorded
1GreenFrankE2011-02-02 21:33:40.743
2MihadaBobK2011-02-02 21:33:40.750
3BeansBob 2011-02-02 21:33:40.750

~Enjoy.

Is this too much? An attempt to track field/column level changes.

Not to spoon feed you an example or anything (it was hard for me to find a complete one). But the following is a self-contained proof of concept, that will track specific column level changes.

I was surprised and impressed with the results.

--------------------------------------------------------------------------------
--// 1. Create/populat dummy table
CREATE TABLE [dbo].[Customer] (
[CustomerID] int IDENTITY(100000, 1) NOT NULL,
[LastName] varchar(35) NULL,
[FirstName] varchar(25) NULL,
[MiddleName] varchar(25) NULL,
[AddressLine1] varchar(55) NULL,
[AddressLine2] varchar(55) NULL,
[City] varchar(30) NULL,
[STATE] varchar(2) NULL,
[zip] varchar(15) NULL,
[HomePhone] varchar(15) NULL,
[CellPhone] varchar(15) NULL,
[WorkPhone] varchar(15) NULL,
[WorkPhoneExt] varchar(10) NULL,
[Fax] varchar(15) NULL,
[Email] varchar(60) NULL,
[datecreated] datetime NULL
)
GO

SET IDENTITY_INSERT [dbo].[Customer] ON
GO
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (123, 'Hall', 'Kira', 'A', '1 Big RD APT 29', '', 'PHOENIX', 'AZ', '85018', '123-654-1501', '--', '--', '', '--', '', '2011-02-01 20:59:17.563')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (322, 'Smith', 'Alan', 'B', '3 Water park wy', '', 'Big Rock', 'AR', '72205', '808-335-0003', '--', '--', '', '--', '', '2011-02-01 20:58:17.353')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (443, 'Lee', 'Eric', 'C', '5 Blue Lane', '', 'Portland ', 'OR', '97219', '--', '--', '--', '', '--', '', '2011-02-01 20:57:46.863')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (544, 'Marsh', 'Ben', 'D', '99 Flag ST', '', 'Orange', 'CA', '94014', '904-808-5433', '--', '--', '', '--', '', '2011-02-01 20:54:38.623')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (566, 'Green', 'Frank', 'E', '1 Disney Way', '', 'Anaheim', 'CA', '92886', '808-695-1314', '--', '949-808-3400', '', '--', '', '2011-02-01 20:51:54.997')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (577, 'Ronalds', 'Bob', '', '95 Cap St ', '', 'ARLINGTON', 'TX', '78582', '--', '--', '--', '', '--', '', '2011-02-01 21:50:33.833')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (588, 'Tool', 'Jennifer', 'G', '48 Bridges wy', '', 'Forest', 'AR', '71602', '555-808-0006', '--', '555-808-0000', '', '555-808-0100', 'fake@beans.com', '2011-02-01 20:51:29.257')
GO
SET IDENTITY_INSERT [dbo].[Customer] OFF

RETURN

--// Check table and new records
SELECT * FROM Customer

--------------------------------------------------------------------------------
--// 2. Created Fields Table
CREATE TABLE [CustomerField](
[CustomerFieldId] [int] IDENTITY(1,1) NOT NULL,
[CustomerFieldName] [varchar](128) NOT NULL,
[CustomerFieldDateCreated] [datetime] DEFAULT(getdate()) NOT NULL,
CONSTRAINT [PK_CustomerField] PRIMARY KEY CLUSTERED
(
[CustomerFieldId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--// Load Fields
INSERT INTO [CustomerField]( CustomerFieldName)
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Customer'
AND COLUMN_NAME IN (
'CustomerId'
,'LastName'
,'FirstName'
,'MiddleName'
,'AddressLine1'
,'AddressLine2'
,'City'
,'STATE'
,'zip'
,'HomePhone'
,'CellPhone'
,'WorkPhone'
,'WorkPhoneExt'
,'Fax'
,'Email'
,'datecreated')
ORDER BY ORDINAL_POSITION
RETURN
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--// 3. Create Audit table
CREATE TABLE [dbo].[CustomerFieldAudit](
[CustomerFieldAuditId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[CustomerFieldId] [int] NOT NULL,
[CustomerFieldAuditValue] [xml] NULL,
[CustomerFieldAuditDateRecorded] [datetime] DEFAULT (getdate()) NOT NULL,
CONSTRAINT [PK_CustomerFieldAudit] PRIMARY KEY CLUSTERED
(
[CustomerFieldAuditId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerFieldAudit] WITH CHECK ADD CONSTRAINT [FK_CustomerFieldAudit_CustomerField] FOREIGN KEY([CustomerFieldId])
REFERENCES [dbo].[CustomerField] ([CustomerFieldId])
GO
ALTER TABLE [dbo].[CustomerFieldAudit] CHECK CONSTRAINT [FK_CustomerFieldAudit_CustomerField]
GO
RETURN
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--// 4. Create table trigger
CREATE TRIGGER [dbo].[trg_Customer_UPDATE]
ON [Customer]
FOR UPDATE
AS
IF
(SELECT HASHBYTES('SHA1',
UPPER((
SELECT CustomerId
,LastName
,FirstName
,MiddleName
,AddressLine1
,AddressLine2
,City
,State
,zip
,HomePhone
,CellPhone
,WorkPhone
,WorkPhoneExt
,Fax
,Email
,datecreated
FROM
INSERTED
FOR XML PATH('')
)))
) <> (SELECT HASHBYTES('SHA1',
UPPER((
SELECT CustomerId
,LastName
,FirstName
,MiddleName
,AddressLine1
,AddressLine2
,City
,State
,zip
,HomePhone
,CellPhone
,WorkPhone
,WorkPhoneExt
,Fax
,Email
,datecreated
FROM
DELETED
FOR XML PATH('')
)))
)
BEGIN

INSERT INTO CustomerFieldAudit(CustomerId, CustomerFieldId,CustomerFieldAuditValue )
SELECT
CustomerId
,CONVERT(INT, RIGHT(FD_X,3)) FieldID
,FieldValue
FROM
(
SELECT
C.CustomerId
,CASE WHEN C.LastName <> N.LastName THEN (SELECT C.LastName FOR XML PATH('')) END FD_002
,CASE WHEN C.FirstName <> N.FirstName THEN (SELECT C.FirstName FOR XML PATH('')) END FD_003
,CASE WHEN C.MiddleName <> N.MiddleName THEN (SELECT C.MiddleName FOR XML PATH('')) END FD_004
,CASE WHEN C.AddressLine1 <> N.AddressLine1 THEN (SELECT C.AddressLine1 FOR XML PATH('')) END FD_005
,CASE WHEN C.AddressLine2 <> N.AddressLine2 THEN (SELECT C.AddressLine2 FOR XML PATH('')) END FD_006
,CASE WHEN C.City <> N.City THEN (SELECT C.City FOR XML PATH('')) END FD_007
,CASE WHEN C.State <> N.State THEN (SELECT C.State FOR XML PATH('')) END FD_008
,CASE WHEN C.zip <> N.zip THEN (SELECT C.zip FOR XML PATH('')) END FD_009
,CASE WHEN C.HomePhone <> N.HomePhone THEN (SELECT C.HomePhone FOR XML PATH('')) END FD_010
,CASE WHEN C.CellPhone <> N.CellPhone THEN (SELECT C.CellPhone FOR XML PATH('')) END FD_011
,CASE WHEN C.WorkPhone <> N.WorkPhone THEN (SELECT C.WorkPhone FOR XML PATH('')) END FD_012
,CASE WHEN C.WorkPhoneExt <> N.WorkPhoneExt THEN (SELECT C.WorkPhoneExt FOR XML PATH('')) END FD_013
,CASE WHEN C.Fax <> N.Fax THEN (SELECT C.Fax FOR XML PATH('')) END FD_014
,CASE WHEN C.Email <> N.Email THEN (SELECT C.Email FOR XML PATH('')) END FD_015
,CASE WHEN C.datecreated <> N.datecreated THEN (SELECT C.datecreated FOR XML PATH('')) END FD_016
FROM
INSERTED N
LEFT JOIN DELETED C ON N.CustomerId = C.CustomerId
) F
UNPIVOT
(
FieldValue FOR FD_X IN(
FD_002
,FD_003
,FD_004
,FD_005
,FD_006
,FD_007
,FD_008
,FD_009
,FD_010
,FD_011
,FD_012
,FD_013
,FD_014
,FD_015
,FD_016
)
) as UPV_FD_Value

END
RETURN
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--// Test out updates
--// Select top 10 * from Customer
UPDATE Customer
SET
FirstName = 'Bob'
,LastName = 'Mihada'
,WorkPhone = '808-808-808'
, Email = 'fake@uout.com'
,STATE = 'HI'
WHERE
CustomerId = 566

--//Review Audits
SELECT
A.CustomerId
,A.CustomerFieldID
,F.CustomerFieldName
,A.CustomerFieldAuditDateRecorded
,A.CustomerFieldAuditValue
,F.CustomerFieldName
,A.CustomerFieldAuditValue.value('(/)[1]', 'nvarchar(1000)') AS CustomerFieldAuditValue --Expensive
FROM
CustomerFieldAudit A
JOIN CustomerField F ON F.CustomerFieldID = A.CustomerFieldID

ORDER BY
A.CustomerId
,A.CustomerFieldID
,A.CustomerFieldAuditDateRecorded

/*
--// Clean up your mess
--// SELECT 'DROP TABLE ' + NAME FROM sys.objects WHERE type = 'U' and Name like '%Customer%'
DROP TABLE Customer
DROP TABLE CustomerFieldAudit
DROP TABLE CustomerField
*/

~ENJOY