Benjamin Wright-Jones

Using PowerShell to deploy Windows Azure Virtual Machines and Windows Azure SQL Databases

superlatch — Thu, 21 Mar 2013 20:44:30 GMT

Author: Benjamin Wright-Jones (Microsoft)
Contributors: Karthika Raman (Microsoft)
Technical Reviewers: Guy Bowerman (Microsoft), Sanjay Nagamangalam (Microsoft)

I have recently been exploring the use IaaS (Infrastructure as a Service) to provide cloud-based virtual machines (VM’s) as opposed to laptop-based VM’s and also PaaS (Platform as a Service) for SQL databases. I like the idea of carrying around a lighter more portable laptop and using cloud services to help me day to day, in contrast to carrying a heavy weight workstation for Hyper-v usage.

I know we can deploy VM’s through the Azure Portal but I prefer an automated approach. Fortunately the new PowerShell cmdlets support Azure VM provisioning and also Azure SQL database provisioning (plus some other nice interfaces). This enables me to quickly spin up a SQL Server VM in Azure or SQL database in Azure. I am actually quite amazed what is possible with PowerShell and it is my new best friend. PowerShell ISE in Windows 8 is superb, I highly recommend this as a development environment due to the intellisense and cmdlets search pane integration.

Below is the PowerShell script I wrote to provision a VM. Unfortunately some manual steps are still required if you wish to manage the SQL Server instance remotely through Management Studio e.g. opening firewall ports, enabling TCP etc. This is all documented here http://www.windowsazure.com/en-us/manage/windows/common-tasks/sql-server-on-a-vm/. Fortunately it is a lot easier to connect to an Azure SQL database and we can automate the registration of the provisioned instance in Management Studio.

Azure VM Provisioning (SQL Server 2012 Evaluation Edition)

Step 1. Download and register the Azure publishing certificate (one time only event).

In order to use PowerShell with the Azure VM and SQL Database services you will need to download and import the publishing file. Fortunately, this is a simple process. I also store my certificate on Skydrive so I can access it everywhere I go in case I need it again.

Get-AzurePublishSettingsFile
Import-AzurePublishSettingsFile C:\...

If you don’t import the publishing file then you may see an error similar to below when attempting to access the Azure services.

An error occurred while making the HTTP request to https://management.core.
windows.net/ae81ecb1-a8af-4fb7-87c5-4418babb4ff2/services/sqlservers/servers
/<server>?op=ResetPassword. This could be due to the fact that the server 
certificate is not configured properly with HTTP.SYS in the HTTPS case. This 
could also be caused by a mismatch of the security binding between the client 
and the server.

Step 2. View available subscriptions and set the correct Subscription

Get-AzureSubscription | Select SubscriptionName
Select-AzureSubscription –SubscriptionName

Incidentally, you may also need to associate a specific Azure Storage Vault (ASV) Account with your subscription, for some reason the default value was null so I had to allocate a specific account. The Azure Storage account is required to host the VM disks which are provisioned during the creation of an Azure VM image.

Get-AzureStorageAccount | Select Label
Set-AzureSubscription -SubscriptionName "Windows Azure MSDN - Visual Studio Ultimate"

-CurrentStorageAccount "<storageaccount>"

Step 3. View the available Azure VM images and locations

Get-AzureVMImage | Select ImageName
Get-AzureLocation | Select DisplayName

Step 4. Create an Azure VM

I like my VM’s big! ExtraLarge! You can use the New-AzureQuickVM syntax, New-AzureVM or New-AzureVMConfig syntax, The New-AzureQuickVM automatically creates and provisions the VM which does not require any additional steps.

New-AzureQuickVM -Windows -ServiceName "<azureservice>" -Name "<vmname>" 
-ImageName "b83b3509582419d99629ce476bcb5c8__Microsoft-SQL-Server-2012-
Evaluation-CY13Feb-SQL11-SP1-CU2-11.0.3339.0" –Password <password> -Location "North Europe" 
–InstanceSize “ExtraLarge”

You should see something like this below.

If you see a DNS error then you may be trying to provision a under a duplicate service name e.g. Error "DNS name already exists" is misleading as it refers to a duplicate service name so change this in the parameters. This servicename refers to the VM service by which you reference/connect e.g. <vmservice>.cloudapp.net

Step 6. Start the Azure VM

Start the Azure VM using the command below.

Start-AzureVM -ServiceName "<servicename>" -Name "<vmname>"

You can view the properties of your Azure VM’s by using Get-AzureVM –ServiceName <ServiceName>. I would also add that additional data disks can be simply added using the Add-AzureDataDisk syntax providing the ability to simply increase the capacity of the provisioned Azure VM instance.

Step 7. Download the remote desktop connection file to your local desktop!

Another great feature is the ability to automatically download the remote desktop file for the provisioned Azure VM.

Get-AzureRemoteDesktopFile -ServiceName "<ServiceName>" -name "<vmname>" -LocalPath "$ENV:userprofile\Desktop\myVm01.rdp"

It is also possible to automatically launch and connect to your VM instance using the standard Remote Desktop client, mstsc.exe, which just eliminates another step in the process to connect to your Azure VM.

mstsc $ENV:userprofile\Desktop\myAzureVm1.rdp

Of course you could parameterise all of this to make life even simpler.

Step 8. Connect!

Simply enter the username and password off you go, simple. You could create multiple PowerShell batch files for each Azure VM image type. I am sticking with the SQL Server 2012 instance for now.

Teardown

Cleaning up the environment is simple too, just a couple of PowerShell commands to stop and remove the provisioned VM.

Step 9. Stop the Azure VM

Stop-AzureVM  -ServiceName "<ServiceName>" -Name "<VmName>"

Step 10. Remove/delete the Azure VM

Remove-AzureVM -ServiceName "<ServiceName>" -Name "<VmName>"

Step 11. Remove/delete the Azure VM disks

The VHD’s associated with the image are not automatically removed so you will need to issue the Remove-AzureDisk command. You can view the existing VHD’s and the associated image and container using the Get-AzureDisk command as shown below. You will notice that I only have one disk (VHD) associated to an image. The other VHD’s were from previous Azure VM deployments.

Removing (deleting) the VHD is simple. The –DeleteVHD parameter is required if you wish to permanently delete the image from ASV so use with caution!

Remove-AzureDisk –DiskName <diskname> –DeleteVHD

Step 12. Remove the allocated cloud service

Remove-AzureService -ServiceName "<servicename>"

Step 13. Remember to save your PowerShell script and parameterise it for one-click deployment!

Azure SQL Database Provisioning

On a related noted, it is also possible to provision a Windows Azure SQL Database using the new PowerShell cmdlets allowing me to rapidly deploy a cloud-based relational data store. You must register the Azure publishing certificate unless this has been done previously (as above in step 1.).

Import-AzurePublishSettingsFile C:\....
Set-AzureSqlDatabaseServer –ServerName <server> –AdminPassword <password>

Creating a new Azure SQL Database instance is also easy, an example is provided below. I chose North Europe due to my geographical location.

New-AzureSqlDatabaseServer -location "North Europe" -AdministratorLogin "<login>" 
-AdministratorLoginPassword "<password>”

You’ll need to create a firewall rule so you can connect to the new Azure SQL Database instance:

New-AzureSqlDatabaseServerFirewallRule -ServerName <server> –RuleName <rulename> 
-StartIPAddress "0.0.0.0" -EndIPAddress "222.222.222.222"

As I was working through this, I discovered a VERY useful command which pops up a dialog with the help options for a specific command, an example is shown below. Omitting the –ShowWindow syntax will output the help details to the console window.

Get-Help Set-AzureSqlDatabase –ShowWindow

The next step was to create a SQL Server authenticated connection to the server hosting the Windows Azure SQL Database. This is an important step as it establishes the context for the connection.

#specify sql auth credential
$servercredential = new-object System.Management.Automation.PSCredential("<username>", 
("<password>" | ConvertTo-SecureString -asPlainText -Force))

#create a connection context
$ctx = New-AzureSqlDatabaseServerContext –ServerName <servername> -Credential $serverCredential

Incidentally, if you are wondering what is stored in the connection context then see below:

ServerName        : <server>
SessionActivityId : 850d5e6f-7201-4bad-8fd5-331086064d4a
ClientSessionId   : e8d82a6d-0ed2-4aa3-9c38-3c3da924ab6a-2013-03-13 15:49:35Z
ClientRequestId   : d2436b67-ecca-453d-8d7c-12619e599784-2013-03-13 16:02:36Z
Databases         : {master}

Wondering what databases you can see? Easy.

Get-AzureSqlDatabase -Context $ctx

Which returns all the databases deployed on the provisioned instance. In the case below, only the master database was listed as no other databases are currently deployed.

Name          : master
CollationName : SQL_Latin1_General_CP1_CI_AS
Edition       : Web
MaxSizeGB     : 5
CreationDate  : 12/03/2013 22:35:57

Want a new database? Easy again.

New-AzureSqlDatabase -Context $ctx –DatabaseName <databasename>-Collation SQL_Latin1_General_CP1_CI_AS 
-Edition Web -MaxSizeGB 5

Interestingly, there are a host of DataServiceContext class options made available under the context of the connection such as ServerMetrics and DatabaseMetrics. This provides some interesting insight into the metadata for your Azure SQL database server such as throttled connections and failures. Unfortunately, the context commands are not documented right now so this is just exploratory and the exposed properties may be removed in the future.

$ctx.ServerMetrics.IncludeTotalCount()
$ctx.DatabaseMetrics.IncludeTotalCount()

Beyond the ability to provision a Windows Azure SQL Database using PowerShell cmdlets, I can also save time by automatically registering the Azure SQL instance in SQL Server Management Studio by invoking the SQL Server 2012 PowerShell command New-Item as below (thereby saving even more time!). The AzureSqlDbServer1 reference is the friendly name which appears in the SQL Server Management console.

Import-Module sqlps
Cd "sqlregistration\Database Engine Server Group"
New-Item AzureSqlDbServer1 -ItemType Registration -Value "server=<server>.database.windows.net; 
integrated security=false; userid=<username>; password=<password>; initial catalog=<databasename>"

Teardown

Removing (or de-provisioning) the Azure SQL database, instance and Management Studio registration is simple. The last command, Remove-Item, is a SQL Server PowerShell command to delete the Management Studio server registration and this must be invoked using sqlps as above.

Remove-AzureSqlDatabase $ctx –DatabaseName "<dbname>"
Remove-AzureSqlDatabaseServer -ServerName "<AzureSqlDbServer>"

Import-Module sqlps
Remove-Item AzureSqlDbServer1

Closing Remarks

The new PowerShell cmdlets for Azure are a fantastic way to easily provision either VM’s or a database in the cloud. I will be parameterising my scripts (and including try.. catch blocks) to quickly create an Windows Azure Virtual Machine or Windows Azure SQL Database as needed (one-click deployment made easy!). PowerShell ISE is also an excellent development environment which can be leveraged for not only Azure VM or SQL database provisioning but also for many more solution scenarios.

What is missing?

Provisioning Azure HDInsight clusters is not currently possible however this should be coming soon http://hadoopsdk.codeplex.com (refer to Programmatic Cluster Management).
PowerShell cmdlets for Azure SQL Reporting are not currently available.
The ability to provision an Azure VM image with the full business intelligence stack deployed i.e. SharePoint 2013, PowerView and Power Pivot integration.
PowerShell remoting to be automatically enabled in the Azure VM.
The ability to invoke SqlCmd and query Azure SQL databases through PowerShell (inc. support for Federations).

References

Managing Windows Azure SQL Databases with PowerShell http://gallery.technet.microsoft.com/scriptcenter/Managing-Windows-Azure-SQL-632acc4b

Windows Azure SQL Database Management with PowerShell(http://blogs.msdn.com/b/windowsazure/archive/2013/02/07/windows-azure-sql-database-management-with-powershell.aspx

Getting Started with SQL Server on a Windows Azure Virtual Machine http://www.windowsazure.com/en-us/manage/windows/common-tasks/sql-server-on-a-vm/

Building a data mart to analyse web log traffic

superlatch — Thu, 19 Jul 2012 19:47:12 GMT

I recently had the opportunity to build a data mart in order to analyse web logs from IIS servers. The solution utilised SQL Server 2012 Database Engine, Integration Services, Analysis Services and Excel 2010 (to slice/dice the data).

Loading IIS logs (text files with fixed width spaces) was fairly challenging due as the column definitions would alter throughout. I therefore used the .NET StreamReader class to read the metadata and detect changes in the fields.

The data flow is shown below.

A snippet of the code in the Load Data (Source Script Component) is presented below

Code Snippet
 // Get variables
strSourceFile = Variables.vCurrentSourceFileName;
intAuditLogFileIdentifier = Variables.vAuditExecutionFileIdentifier;
 
try
{
    // Create an instance of StreamReader to read from a file.
    // The using statement also closes the StreamReader.
    using (StreamReader sr = new StreamReader(strSourceFile))
    {
        String line;
        int intNumberOfFields = 0; 
        string[] strListOfFields = null;
 
        Trace.WriteLine("Log File: " + strSourceFile);
 
        // Output the source file name as the first line (debugging purposes)
        OutputLogFileRawDataBuffer.AddRow();
        OutputLogFileRawDataBuffer.colRawData = strSourceFile;
 
        // Read and display lines from the file until the end of the file is reached.
        while ((line = sr.ReadLine()) != null)
 

Extracting the data from the file was relatively straightforward. I placed the string into an array based on the fixed spacing between fields. From the data, I was able to extract useful information such as browser type i.e. Safari, Chrome, IE and even browser version.

IP addresses were mapped to geolocation using the free GeoLite information http://www.maxmind.com/app/geolite (CSV data imported into the database). I converted longitude and latitude to the spatial geography data type and presented this against a world map (I wanted to validate that the GeoLite data correctly mapped to the IP address e.g. country/city to IP address).

   1:  USE BiKitchen;

2:

   3:  DECLARE @SRID int = 4326

   4:  DECLARE @pLat nvarchar(max)

   5:  DECLARE @pLong nvarchar(max)

   6:  DECLARE @g geography

7:

   8:  -- Check longitude and latitude for London

   9:  SET @pLat = (SELECT CAST(Latitude AS nvarchar(max)) FROM [GeoData].[GeoLiteCity-Location] WHERE locid = 13547)

  10:  SET @pLong = (SELECT CAST(longitude AS nvarchar(max)) FROM [GeoData].[GeoLiteCity-Location] WHERE locid = 13547)

11:

  12:  SET @g =  geography::STPointFromText('POINT(' +

  13:          @pLong + ' ' +

  14:          @pLat + ')', @SRID).BufferWithTolerance(11000, 1000, 0)

15:

  16:  SELECT @pLat AS Latitude, @pLong AS Longitude

  17:  -- SELECT @g.Lat, @g.Long

18:

  19:  -- Map the geography type to base world map data

  20:  -- View the result in the spatial tab to validate coordinates

  21:  SELECT @g AS spatiallocation

  22:  UNION ALL SELECT geog FROM World_Borders

The star schema was built and deployed in SQL Server 2012 Analysis Services (UDM). I found named calculations to be incredibly powerful way of extending the data model and making attributes more meaningful for end-users

The data was presented using Excel 2010, a screenshot is shown below. I found slicers to be extremely useful

I thought it would be interesting to see what Excel 2013 had to offer so I tried to create a Power View report but this is not currently supported against the UDM. There are however some nice enhancements to chart types so I’ll be looking at this in more detail.

Redux: Using an SSIS package to monitor and archive the default trace file

superlatch — Thu, 05 Jul 2012 05:58:58 GMT

I’ve recently been asked for details regarding the SSIS package I wrote to copy and archive the default trace file which is generated by SQL Server. The contents of the file can be quite useful for troubleshooting or diagnosis purposes.

I’ve updated the package to work with SQL Server 2008 R2 and SQL Server 2012.

The detection of a new trace file is implemented using a Script Task which watches the \Log folder for new files. When a new file is detected it copies the previous file to an archive location. The logic is embedded in Visual Basic.NET (not my personal choice although I was limited in SQL Server 2005 when I wrote the package).

The archive process renames the file with the date and time and then copies the file to a chosen location. I should point out that I use expressions on a few variables to alter outputs such as the filename i.e. date_time_filename.

I also noticed that the service needs appropriate permissions to both access the \LOG directory and also copy to the target directory, in my scenario, this was \\server\share. When I was testing, I launch SSDT (SQL Server Data Tools) using Administrator privileges for testing purposes as a quick workround to permission issues).

Here is the code for the Script Task (apologies for the word wrap, the Live Writer plug-in seems to do this to fit it on the page). I have commented out some of the writeline commands I was using to debug the package when it was initially developed.

Code Snippet
 'Disclaimer: 
    'The sample scripts and SSIS package are not supported under any Microsoft standard support program or service. 
    'The sample scripts and SSIS package are provided AS IS without warranty of any kind. 
    'Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. 
    'The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. 
    'In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, 
    'business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
 
    Public Sub Main()
 
        Dim vars As Variables
        Dim strComputer As String
        Dim objWMIService As Object
        Dim colMonitoredEvents As Object
        Dim objEventObject As Object
        Dim strSourceDirectory As String
        Dim strServerName As String
        Dim strSourceErrorLogDirectory As String
        Dim strSourceErrorLogDirectoryWithQuotes As String
 
        Try
            ' Use the SSIS variables in this code for the WMI query
            strServerName = Dts.Variables("v_ServerName").Value
            strSourceErrorLogDirectory = Dts.Variables("v_DT_SourceLogDirectory").Value
 
            Console.WriteLine("Servername: " + strServerName)
            Console.WriteLine("Monitoring \Log Directory: " + strSourceErrorLogDirectory)
            
            ' Replace \ with \\\\ which is needed for the WMI query
            strSourceErrorLogDirectory = Replace(strSourceErrorLogDirectory, "\", "\\\\")
            strSourceErrorLogDirectoryWithQuotes = Chr(34) & strSourceErrorLogDirectory & Chr(34)
 
            'MsgBox("Server Name: " + strServerName)
 
            ' Connect to the WMI source
            objWMIService = GetObject("winmgmts:\\" & strServerName & "\root\cimv2")
 
            ' Monitor the error log folder for instances of ERRORLOG.1 as this is the file we want to archive
            ' The directory name is parameterised and populated from the SSIS variable
 
            ' Monitor the directory for new default trace files
            colMonitoredEvents = objWMIService.ExecNotificationQuery _
                ("SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE " _
                    & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
                        & "TargetInstance.GroupComponent= " _
                                & "'Win32_Directory.Name=" & strSourceErrorLogDirectoryWithQuotes & "'")
 
            objEventObject = colMonitoredEvents.NextEvent()
 
            'MsgBox("A new file was just created: " + objEventObject.TargetInstance.PartComponent)
 
            Dim strReturned, strFilePath As String
 
            strReturned = objEventObject.TargetInstance.PartComponent
            strFilePath = Split(strReturned, "CIM_DataFile.Name=")(1)
            strFilePath = Replace(strFilePath, """", "")
            strFilePath = Replace(strFilePath, "\\", "\")
            'MsgBox("Sliced file: " + strFilePath)
 
            ' strFilePath is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_nnn.trc
            ' Array element (6) is log_nnn.trc, this assumes the above directory structure
            Dim strFilename As String
 
            'strFilename = Split(strFilePath, "\")(6)
            'MsgBox("Split: " + strFilename)
 
            strFilename = System.IO.Path.GetFileName(strFilePath)
            'MsgBox("IO.Path: " + strFilename)
 
            ' If filename like log_ then enter this code path
            ' The default trace filename is always log_ so we can rely on this for filename matching
            If strFilename Like "log_*.trc" Then
 
                Console.WriteLine("A new default trace file was just created in \LOG called " + strFilename)
                Trace.WriteLine("A new default trace file was just created in \LOG called " + strFilename)
 
                ' Archive the previous default trace file
                'MsgBox("Default Trace found, now process the file")
 
                Dim arrContainer As Array
                Dim intTraceFileNumber As Integer
                Dim strArchiveFileName As String
 
                arrContainer = Split(strFilename, "_")
 
                'Console.WriteLine(arrContainer(0).ToString)
                'Console.WriteLine(arrContainer(1).ToString)
 
                ' Split 1111.trc so we only store 1111 to convert to int
                arrContainer = Split(arrContainer(1), ".")
 
                ' This is the active default trace file number
                'Console.WriteLine(arrContainer(0).ToString)
 
                ' Convert the active trace file number to int and decrease by 1
                intTraceFileNumber = CInt(arrContainer(0)) - 1
 
                ' Convert back to string and create the default trace file name
                strArchiveFileName = "log_" + CStr(intTraceFileNumber) + ".trc"
                'Console.WriteLine("Archiving: " + strArchiveFileName + " to " + Dts.Variables("v_DT_DestinationDefaultTraceDirectory").Value)
 
                'MsgBox(strArchiveFileName)
 
                'Write the filename to the SSIS variable
                Dts.Variables("v_DT_ActiveFileName").Value = strArchiveFileName
 
                Console.WriteLine("Archiving: " + strArchiveFileName + " to " + Dts.Variables("v_DT_DestinationDefaultTraceDirectory").Value)
                MsgBox("Output to SSIS Variable: " + Dts.Variables("v_DT_ActiveFileName").Value + " strFilename: " + strArchiveFileName)
 
                ' Indicate success to move on to the next step
                Dts.TaskResult = ScriptResults.Success
            End If
 
            ' Error handling
        Catch ex As Exception
            Console.WriteLine(System.DateTime.Now.ToString + " - SSIS Script Task Error: " + ex.Message.ToString)
            Dts.TaskResult = ScriptResults.Failure
        End Try
 
    End Sub
 

I hope you find this useful.

I will try and attach the SSIS package to this post later.

Redux: Using a C# script task in SSIS to download a file over http

superlatch — Mon, 02 Jul 2012 21:11:25 GMT

A few people have asked for further information about the C# script task which I blogged about (quite a while ago). I mistakenly forgot to add the full source code, sorry everyone. Here is the link to the original blog post: http://blogs.msdn.com/benjones/archive/2009/03/29/using-a-c-script-task-in-ssis-to-download-a-file-over-http.aspx

I have since imported the SSIS package into Visual Studio 2010 (BIDS) and the code compiles without error. Some of the code below is truncated on the right (just a formatting issue I need to resolve) but the core of the code is there.

As always, let me know if there are any problems.

/*
   Download a file over http using Script Task in SQL Server 2008 R2 Integration Services.   
   Two key variables, vSSOReportURL, which is constructed in a prior Script Task e.g. http://www..
   vSSOLocalFileName, which is the fully qualified reference for the downloaded file e.g. c:\myfile.zip

*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.Net.Security;

namespace ST_7e897e41dd5945f3b77366d32f0a97e0.csproj
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 
        public void Main()
        {
            WebClient myWebClient;
            string RemoteURI;
            string LocalFileName;
            bool FireAgain = true;

            Variables vars = null;
            Dts.VariableDispenser.LockForRead("User::vSSOReportURL");
            Dts.VariableDispenser.LockForRead("User::vSSOLocalFileName");
            Dts.VariableDispenser.LockForWrite("User::vSSOReportURLIndicator");
            Dts.VariableDispenser.GetVariables(ref vars);
             
            try
            {
                // Ignore certificate warnings
                ServicePointManager.ServerCertificateValidationCallback = 
                     new RemoteCertificateValidationCallback(delegate { return true; });

                // Initiate webclient download, use default credentials (current login)
                myWebClient = new WebClient();
                myWebClient.Credentials = CredentialCache.DefaultCredentials; 

                RemoteURI = vars["User::vSSOReportURL"].Value.ToString();
                LocalFileName = vars["User::vSSOLocalFileName"].Value.ToString();

              
                // Log provider notification 
                Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", 
                LocalFileName, RemoteURI), String.Empty, 0, ref FireAgain);

                // Download the file 
                myWebClient.DownloadFile(RemoteURI, LocalFileName);

                // Set report URL indicator, this is used to determine the http source of the 
                // download i.e. vSSOReportURL or vSSOReportURLRetry for the message which is 
                // written to the table
                vars["User::vSSOReportURLIndicator"].Value = 0;

                // Return success
                Dts.TaskResult = (int)ScriptResults.Success;
            }

            catch (Exception ex)
            {
                // Catch and handle error 
                Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

        }


    }
}

The files are extracted using an Execute Process Task (with 7-Zip) as shown below:

And the arguments are set using the expression (below). There are probably better ways of doing this but I found this worked well.

The .zip file is then archived using a File System task and the extracted file is renamed to .xlsx.

Are you interested in Data Science?

superlatch — Mon, 02 Jul 2012 14:52:19 GMT

The University of Dundee is now gauging interest in a number of data science focused courses (see below).

You can register interest via this link http://www.dundee.ac.uk/admissions/new_courses/register.php

Testing SQL Server HA using Hyper-V in Windows 8 Release Preview

superlatch — Thu, 21 Jun 2012 16:22:24 GMT

I often find the need to build and test SQL Server clusters to support native two-node or N+1 (multi-instance) scenarios and also validate SQL Server 2012 Always On configurations. I was previously running Windows Server 2008 R2 on my laptop but I a few issues using this as a day to day operating system e.g. no support for standby when hyper-v was enabled, no dual boot (hyper-v on/off) with bitlocker enabled, no Bluetooth support etc. Fortunately, Windows 8 meets all my needs and the experience has been excellent to date.

Note: for rapid provisioning of other images, I created a fully patched base OS image which was sysprep’d (this is incredibly easy). I now just copy the image if I want to create other server roles e.g. System Center 2012.

The only issue I have found to date is that host internet connectivity is affected (delayed) after defining multiple internal network adapters, therefore I switched to using Private network adapters. I only need internal adapters for host to guest connectivity e.g. copying files etc.

My hardware and software is defined below:

Laptop HP 8540w (16GB memory, 4 cores hyper-threaded)
Two internal SATA disks (hybrid)
Windows 8 Release Preview (x64) with Hyper-V enabled

The environment consists of the following virtual guest images:

1 x Domain Controller (also hosts the virtual storage)
1 x Primary Node (node 1)
1 x Secondary Node (node 2)

All servers are running Windows Server 2008 R2 Enterprise Edition x64 with Service Pack 1. The cluster role is enabled on node 1 and node 2.

Storage

The shared storage is provisioned using the iSCSI Software Target which I defined on the domain controller. I didn’t find a need to dedicate a specific storage server. The screenshot of the virtual storage is presented below

Cluster Validation is good, the only warning was inconsistent OS patch levels on both cluster nodes as shown below.

After running cluster validation, I created a cluster and the final configuration is shown below.

Below is a screenshot of the raw cluster configuration.

I’ll blog more following the SQL Server install. I also plan to repeat this for Window Server 2012 and SQL Server 2012.

Transposing Columns onto Rows

superlatch — Thu, 07 Jun 2012 17:38:50 GMT

After a long period of absence, I have returned to posting some blog articles. This one popped up last week from an Oracle DBA while I was onsite with a customer. The conversation went something along the lines of:

Oracle DBA: “How can I place columns values on rows? I have a limited page width and need to alter the output”

Microsoftie “ah.. well, that’s easy..”

Consider the output below:

but we want it to appear like this:

The magic is really in the UNPIVOT function as shown below.

CREATE DATABASE sandbox;

USE sandbox;

CREATE TABLE tblPerson
(
    Email_Address varchar(50),
    First_Name varchar(50),
    Last_Name varchar(50)
);

INSERT INTO tblPerson VALUES
('ben@test.com', 'Ben', 'WJ')

SELECT * FROM tblPerson;

SELECT   
  tblPivot.Property, tblPivot.Value 
FROM   
  (SELECT
     CONVERT(sql_variant,Email_Address) AS Email_Address,
     CONVERT(sql_variant,First_Name) AS First_Name,
     CONVERT(sql_variant,Last_Name) AS Last_Name
   FROM tblPerson) Person
  UNPIVOT (Value For Property In (Email_Address, First_Name, Last_Name)) as tblPivot;

BI Service Applications in SharePoint 2010

superlatch — Tue, 29 Nov 2011 09:29:14 GMT

One of my colleagues, Chris Bailiss, has written a number of great articles describing how BI service applications authenticate in SharePoint 2010. If you are interested then head over here to part 1 (link below) now:

http://blogs.msdn.com/b/mcsukbi/archive/2011/11/26/bi-service-applications-in-sharepoint-2010-authentication-classic-vs-claims-and-identity-delegation-kerberos-part-1.aspx

Two new SQLCAT papers available, Spinlock and Latch Contention

superlatch — Wed, 06 Jul 2011 08:21:41 GMT

Diagnosing and Resolving Spinlock Contention on SQL Server

http://sqlcat.com/whitepapers/archive/2011/07/05/diagnosing-and-resolving-spinlock-contention-on-sql-server.aspx

Diagnosing and Resolving Latch Contention on SQL Server

http://sqlcat.com/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx

Whitepaper Alert: Microsoft EDW Architecture, Guidance and Deployment Best Practices

superlatch — Fri, 06 May 2011 12:49:55 GMT

http://msdn.microsoft.com/en-us/library/hh147624.aspx