Category Archives: SQL Server

Automating encryption tasks with PowerShell

Recently I answered a question on how to automate a task that runs a DTS package, encrypts the result with PGP and then transfers the encrypted file to a FTP site. I became interested in this subject since I heard the same request from 2 people in less than a month and realized that this is a standard pattern for a business process. Extract.Encrypt.Transfer.

First person asking me this found the solution in having a .NET application designed to perform these task using existing .NET components.

I think the is a easier solution is a PowerShell script.

All ww have to do is script the following:

#execute DTSPackage
DTSRun /S "(DBServer)" /N "DTSPackage" /A "StringPar":"8"="String Value" /A "IntPar":"3"="1" /W "1" /E
#Encrypt result file
gpg -E Datafile.csv --options
#transfer file over to FTP
function Open-FTPConnection($ftphost, $username, $password) {

[void][Reflection.Assembly]::LoadFrom("C:\IndyNet-Daily-20051007\Indy.Sockets.dll")
$ftp = new-object Indy.Sockets.FTP
$ftp.Disconnect()
$ftp.Host = $ftphost
$ftp.Username = $username
$ftp.Password = $password
$ftp.Connect()
$ftp.Passive=$true;
return $ftp
}
function Close-FTPConnection($ftp) { $ftp.Disconnect(); } function Upload-FTPFile($ftp, $sourceFileName, $targetDir) {     "Uploading {0} into {1}.." -f $sourceFileName, $targetDir;     $ftp.Put($sourceFileName, ($targetDir + $sourceFileName), $false);     "Uploading of {0} into {1} is complete" -f $sourceFileName, $targetDir; } $f = Open-FTPConnection "localhost" "foo" "bar" Upload-FTPFile $f "C:[\Path]\Datafile.pgp" (Get-FTPCurrentLocation $f) "Datafile.pgp" Close-FTPConnection $f

Source code found here:

http://groups.google.com/group/microsoft.public.windows.powershell/msg/6d9b5a4541160b25

Command line GNUPG can be downloaded  from here:

http://www.gnupg.org/

Of course this needs some polishing but … this is can be easily reused


SQL Server 2008 Failover Clustering with High Availability

I’m heading home from the August session of Chicago PASS and SSUG meeting.
First off, this was the biggest meeting in Chicago. Over 80 people signed up and for a very good reason.
After a very interesting Microsoft SQL Server 2008 Administration with Windows PowerShell presentation by The MAK and Yan Pan,  Allan Hirt managed to cram the essentials of the new MS Failover Cluster Manager and he made it in less than an hour!
One very interesting and long awaited feature is Live Migration. Basically for the first time you can failover a MS Cluster without dropping any transaction. This is only happening for planned failovers of course but this is a phenomenal achievement for the MS team. Until now any time you would failover a cluster, the operation was the equivalent of stopping the SQLServer service an restarting it on a different machine. Obviously any pending transactions were dropped. With Live Migration the when a planned failover is initiated data from memory is copied over to the new active node and all transactions are seamlessly ported over as well. There might be some performance decrease but no transactions are dropped achieving true High Availability.
And last but not least all this is free with Windows Server 2008 Enterprise or Datacenter
For more information read this blog from Microsoft.
http://blogs.technet.com/virtualization/archive/2009/05/06/microsoft-hyper-v-server-2008-r2-release-candidate-free-live-migration-ha-anyone.aspx

Thanks Allan!

How (not) to design a SQL Server table

I recently saw the worst example of  how not to design a table and indexes in SQL Server.

So here it is:

Table1(UserID,ActivityID,ActivityType,AddDate)

Role : Log any activity that a user is invited for.

PrimaryKey: … Wait for it … tada : UserID,ActivityID,ActivityType,AddDate … Wait you say …. YES … all table columns are used for the primary key and of course by default this will be a clustered index. Which means that if you want to insert 100K records in this table … it will split a lot of pages to reorganize the index beside actually adding the table records.

Wait … it gets better…

Someone created a non clustered index on ActivityID,UserID,,ActivityType,AddDate … yes in that order … and NO it does not help but I guess not everybody knows that a non clustered index will contain the clustered index besides the actual index.

So let’s recap:

Table : 4 Columns

PK Clustered Index: Same 4 Columns

Non clustered index: Same 4 columns x2

Insert efficiency -> extremely poor.

Why did SQL Server cross the road?

I posted a comment on Kevin Kline’s blog regarding:

Microsoft Marketing Throws SQL Server Under the Bus

“A spokesperson for Microsoft said that the problem was not bandwidth but that its SQL Server database had reached excessive fragmentation levels caused by the tremendous surge of queries”.  Read about it here:

http://www.itnews.com.au/News/102445,windows-7-rc-download-crashes.aspx

and also here:

http://blogs.zdnet.com/Bott/?p=910

Why didn’t anyone involved bother to think thru the fact that competitors and various elements of anti-Microsoft factions can make a lot of hay about how Microsoft’s own product?!?

Just as politicians only ever admit to “wanting to spend more time with family”, a technology company shouldn’t point to their own product as the culprit of a major technology failure, especially when there are thousands of other reasons that the meltdown might’ve happened.

What are your thoughts?

Here are my thoughts.

I think the truth is somewhere in the middle. I have worked with both Oracle and SQL Server and while I agree that in a lot of regards Oracle is a superior product it is also because of their experience.
Let’s face it …Oracle started in 1979 while Microsoft bought Sybase in ’89.
In my opinion the first great product they had were Oracle 8 in 1997 and SQL Server 2005 in  2005. Right now Oracle has a stable multi tiered scalable clustered architecture (including app server) whereas Microsoft is still discussing about deployment dates.
Also … Oracle query optimizer is IMO one of the best in the industry since Oracle 8. Microsoft has finally developed a good (should I say … comparable)query optimizer in 2008.

So while SQL Server is a good product  we should compare apples to apples because IMO Oracle and Microsoft play in different leagues.