26 Nov 2013
I use Azure for my demo machines and needed a way to easily shut all VMs down and also to start my BI demo machines. What better way to do this other than with PowerShell!
In order to be able to talk to Azure with Powershell you will need to install Windows Azure Powershell (download link: http://www.windowsazure.com/en-us/downloads/ ).
Also you will need to get your Azure Publish Settings file which is specific to your subscription. You can get it from the Azure portal once you are logged in: https://windows.azure.com/download/publishprofile.aspx .
Let’s start with stopping all VMs. Start the PowerShell ISE (search for powershell_ise on Windows 8) and create the following script:
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"
Import-AzurePublishSettingsFile "PathToYourPublishSettingsFile"
echo "Stopping ALL VMs"
get-azureSubscription | ForEach-Object {
get-azureVM | Where-Object {$_.Status -like "Ready*"} | ForEach-Object {
echo $_.Name
Stop-AzureVM -ServiceName $_.ServiceName -Name $_.Name -Force }
}
Make sure to enter the path to where you stored your publish profile that you have downloaded.
Basically what this script does is iterate over all your subscriptions if you have more than 1 and look for VMs that are in a running state using get-AzureVM. Then for each VM that is running it will echo its name and then stop the VM using stop-AzureVM.
Save the script and then you can just run it and all of your VMs will be turned off. Pretty easy huh?
For my BI demos I use a maximum of four VMs and I made another script that starts them in the correct other (first the domain controller, then the SQL server and then finally the two SharePoint servers I need):
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"
Import-AzurePublishSettingsFile "PathToYourPublishSettingsFile"
function StartVM($serviceName, $name) {
get-azureSubscription | ForEach-Object {
$vm = get-AzureVM |Where-Object {($_.Name = $name) -and ($_.ServiceName = $serviceName)}
if($vm -ne $null) {
if($vm[0].Status = "StoppedDeallocated") {
Start-AzureVM -ServiceName $serviceName -Name $name
Write-Host "$name started"
}
else {
Write-Host "$name already running"
}
}
}
}
echo "Starting BI Demo VMs"
StartVM -serviceName "VMServiceName" -name "VMName"
StartVM -serviceName "VMServiceName" -name "VMName"
StartVM -serviceName "VMServiceName" -name "VMName"
StartVM -serviceName "VMServiceName" -name "VMName"
This script defines a function that wraps a check if the VM is already running and otherwise starts it. The bottom part of this script uses that function to specify which VMs to start in which order. I replaced the original names for security reasons.
This saves a lot of time. It saves me from logging into the Azure portal and starting / stopping each VM by hand. The best part is I can let this run in the background while presenting and nobody sees it J
19 Nov 2013
I was hired as FTE at Microsoft in November 2008 so recently celebrated five years at Microsoft! Still going strong and enjoying every day J
Below is the glass award I received:
12 Nov 2013
Config files (web.config, app.config) are a great way to store configuration settings outside of your programs’ code. If the configuration changes (for example you move from development to test or from test to production) you do not need to go and change your code and re-compile. You simply change the config file and you are good to go. In SQL CLR you can also use this concept, however there are some gotcha’s.
First create your SQL Server CLR project. Note that you will have to have the correct versions of SQL Server Data Tools. You can download what you need from here: http://msdn.microsoft.com/en-us/data/hh297027. I have SQL 2012 installed and am using Visual Studio 2012, so I installed the SQL Server Data Tools for Visual Studio 2012 from the link above.
To start create a new database project and add a new SQL CLR C# User Defined Function (you can also use VB if you like). Add a reference to System.Configuration to your project. Make sure you include the following line in your cs file: ‘using System.Configuration;’.
Now, the config file needs to be stored in the \Binn folder under the root path of the SQL Instance. To get the root folder run the following code in SQL Server Management Studio:
declare @SQLRoot varchar(1024)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath',@SQLRoot OUTPUT
select @SQLRoot
Add ‘\Binn’ to the result of this query. My path was: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn. There create a file named ‘sqlservr.exe.config’ (make sure to get the name right!) You can use Visual Studio for this. A sample config file would be as follows:
xml version="1.0" encoding="utf-8" ?>
Of course you can specify anything you want in the config file. To keep things simple I chose only to configure a sample connection string.
In your cs file all you need to do is create a function that retrieves your configuration for the config file (in my case just the connection string).
Here is the code you will need to put in your CS file:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Configuration;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString getConfigurationConnectionString()
{
return new SqlString(ConfigurationManager.ConnectionStrings["MyConnString"].ToString());
}
}
As you can see this code retrieves the value of the configuration value MyConnString from the config file.
If you, after the code has been deployed, made any changes to the configuration file that you would like to be picked up you will have to either restart the SQL Instance (not really an option in most cases) or free the system memory caches which will unload the CLR memory clerk by running the following in SQL Server Management Studio:
DBCC FREESYSTEMCACHE('ALL')
This is unlike normal .NET applications where configuration file changes take effect immediately.
When done right-click your solution in the solution explorer and choose ‘Publish’ and publish your solution to the SQL Server. Then in Management Studio you should be able to find your function in the database you deployed to:
Now we can test our function by executing it:
select dbo.getConfigurationConnectionString()
Be sure to execute this against the database you deployed to.
Your connection string should be returned:
There you have it: you can now use configuration files from SQL Server CLR.
05 Nov 2013
A while ago a whitepaper was published on how to work with Azure and HDInsight (Hadoop on Azure) from SSIS. In that whitepaper some code samples were given. That code is also available here (including some components): http://code.msdn.microsoft.com/SSIS-Packages-Sample-for-2ffd9c32 . After you have downloaded the zip make sure you have Visual Studio installed, start the Developer Command for Visual Studio as Administrator and run the ‘deploy_SSIS_packages_and_components.bat’ file which is included in the zip. This will install some of the DLLs included into the GAC. Then, you can open the solution in Visual Studio.
The solution includes the following sample SSIS packages:
- PigSqoopPackage: shows how to work with Pig and SQOOP tasks
- HadoopJobAutomation: shows how to start jobs on Hadoop and how to consume results
- ComplexSourceDestination: shows how to get data from Azure Blog Storage and save the results into various targets, including Azure Blob Storage.
- BlobSourceTestPackage: sample package showing how to read data from Azure Blob Storage.
- BlobDestinationTestPackage: sample package showing how to write data to Azure Blob Storage.
The first two packages essentially contain some script tasks with complete samples on how to work with Piq, SQOOP and Hadoop jobs respectively. The other packages use the components provided and provide a quick start on getting data from Azure Blob Storage and getting data into Azure Blog Storage using SSIS.
01 Nov 2013
Just a quick post: I will be speaking at SharePoint Connections Amsterdam 2013 (19th-20th November 2013) on Power BI for Office 365.
More info on: www.nccomms.com/sharepoint_connections.
I can get you a 10% discount, just reach out to me.
Dutch Data Dude