Microsoft

Bulk Bing Maps Geocode Service Encoding using Powershell

/ The Architect Evangelist / 1 Comment

Recently I had the need to geo-encode millions of addresses, after reviewing multiple technologies I found PowerShell provided excellent results. Here is the example solution.

You will need a Bing Maps Maps Account API Key and Microsoft SQL Server

The first step is to create a database with sample data, below is the script:

USE [master]
GO
/****** Object:  Database [LocationDB]    Script Date: 2012-10-20 12:42:43 AM ******/
CREATE DATABASE [LocationDB]
GO
ALTER DATABASE [LocationDB] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [LocationDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [LocationDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [LocationDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [LocationDB] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [LocationDB] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [LocationDB] SET ARITHABORT OFF 
GO
ALTER DATABASE [LocationDB] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [LocationDB] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [LocationDB] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [LocationDB] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [LocationDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [LocationDB] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [LocationDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [LocationDB] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [LocationDB] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [LocationDB] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [LocationDB] SET  DISABLE_BROKER 
GO
ALTER DATABASE [LocationDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [LocationDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [LocationDB] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [LocationDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [LocationDB] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [LocationDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [LocationDB] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [LocationDB] SET RECOVERY FULL 
GO
ALTER DATABASE [LocationDB] SET  MULTI_USER 
GO
ALTER DATABASE [LocationDB] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [LocationDB] SET DB_CHAINING OFF 
GO
ALTER DATABASE [LocationDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [LocationDB] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
EXEC sys.sp_db_vardecimal_storage_format N'LocationDB', N'ON'
GO
USE [LocationDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetLocations]    Script Date: 2012-10-20 12:42:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Nathan Storms
-- Create date: 2012-10-20
-- Description:    Get Location that need encoding.
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetLocations] 
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    
  SELECT TOP 1000 Id, ([Address] + ', ' + [City] + ', ' + [Region] + ', ' + [Country] + ', ' + [PostalCode]) AS Address
  FROM [dbo].[Locations] WITH (NOLOCK) WHERE SRID IS NULL

END


GO
/****** Object:  StoredProcedure [dbo].[usp_UpdateLocation]    Script Date: 2012-10-20 12:42:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Nathan Storms
-- Create date: 2012-10-20
-- Description:    Update locations with geocode
-- =============================================
CREATE PROCEDURE [dbo].[usp_UpdateLocation] 
    -- Add the parameters for the stored procedure here
    @ID int = 0, 
    @Longitude float = 0,
    @Latitude float = 0,
    @SRID int = 4326
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE [dbo].[Locations]
    SET [Latitude] = @Latitude
       ,[Longitude] = @Longitude
       ,[SRID] = 4326
    WHERE [ID] = @ID
END


GO
/****** Object:  Table [dbo].[Locations]    Script Date: 2012-10-20 12:42:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Locations](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Address] [varchar](50) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [Region] [char](2) NOT NULL,
    [Country] [varchar](50) NOT NULL,
    [PostalCode] [char](15) NOT NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [SRID] [int] NULL,
    [Point]  AS ([geography]::Point([Latitude],[Longitude],[SRID])),
 CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED 
(
    [Id] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Locations] ADD  CONSTRAINT [DF_Locations_Latitude]  DEFAULT ((0)) FOR [Latitude]
GO
ALTER TABLE [dbo].[Locations] ADD  CONSTRAINT [DF_Locations_Longitude]  DEFAULT ((0)) FOR [Longitude]
GO

INSERT INTO [dbo].[Locations]
           ([Address],[City],[Region],[Country],[PostalCode])
     VALUES
           ('6258 Amesbury St','San Diego','CA','United States','92114-6717'),
           ('8308 Fenway Rd','Bethesda','MD','United States','20817-2733'),
           ('10 Wall St','Burlington','MA','United States','01803-4749'),
           ('3315 W Greenway Rd','Phoenix','AZ','United States','85053-380'),
           ('1635 Camile Pl','Santa Ana','CA','United States','92703-4401')
GO

Lastly here is the PowerShell Script to geo-encode the addresses in the database:

# Setup Connection To Bing
$key = "{Add Bing Map API Key}"
$ws = New-WebServiceProxy -uri http://dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl
$wsgr = New-Object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.GeocodeRequest
$wsc = New-Object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.Credentials
$wsc.ApplicationId = $key
$wsgr.Credentials = $wsc

# Setup Connection To SQL Database
function ConnectSQL {
    Param ($server, $query, $database)
    $conn = new-object ('System.Data.SqlClient.SqlConnection')
    $connString = "Server=$server;Integrated Security=SSPI;Database=$database"
    $conn.ConnectionString = $connString
    $conn.Open()
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    ,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

function QuerySQL {
    Param ($server, $query, $database = "master")
    $data = ConnectSQL $server $query $database
    while ($data.read() -eq $true) {
        $max = $data.FieldCount -1
        $obj = New-Object Object
        For ($i = 0; $i -le $max; $i++) {
            $name = $data.GetName($i)
            $obj | Add-Member Noteproperty $name -value $data.GetValue($i)
     }
     $obj
    }
}
Write-Host "Getting Records From Database..."
$Locations = QuerySQL "localhost" "EXECUTE [dbo].[usp_GetLocations]" "LocationDB"

$Locations | ForEach-Object {
    $wsr = 0
    $Longitude = 0
    $Latitude = 0
    $ID = $_.ID
    $Address = $_.Address
    Write-Host "Processing record $LocationID for the address: $Address"
    $wsgr.Query = $Address
    $wsr = $ws.Geocode($wsgr)
    $Longitude = $wsr.Results[0].Locations[0].Longitude
    $Latitude = $wsr.Results[0].Locations[0].Latitude
    Write-Host "Location found for record $ID the location is $Longitude, $Latitude"
    QuerySQL "localhost" "EXECUTE [dbo].[usp_UpdateLocation] $ID, $Longitude, $Latitude" "LocationDB"
}

That’s it.

Forefront TMG within Hyper-V Private Clouds

/ The Architect Evangelist / 1 Comment

The following describes a high availability architecture for Forefront TMG within Hyper-V for IaaS Private Cloud supporting an web application tier and data storage tier with optimal path section (OPS).

Scenario

In this scenario I will use 4 identical Microsoft Hyper-V servers each with a LACP LAG tagging all VLANs to the Hyper-V constructing a virtual switch whereby each VM network interface is tagged to a VLAN ID.

Construct a TMG enterprise array placing a virtual TMG server on each Hyper-V host, you will need to provide each TMG a minimum of two network interfaces (LAN & WAN), give each VM 8GB of memory and 4 virtual processors reserving 100% for the CPU reservation. Next configure the TMG enterprise with a unicast NLB on each network interface creating bi-directional affinity. Note: real client traffic (IPs) will need to be able to route to the WAN interface on the TMG enterprise array.

Construct an application tier farm placing a virtual application server on each Hyper-V host, you will need to define the default gateway for each TMG server to be the primary NLB IP address on the LAN interface defined on the TMG enterprise array.

Construct a data tier farm placing a virtual database server on each Hype-V host. I recommend using database mirroring or AlawaysOn availability architecture to obtain application resiliency in favour of clustering, you will need to define the default gateway for each TMG server to be the primary NLB IP address on the LAN interface defined on the TMG enterprise array.

Deploy your IaaS database solution in your data tier farm, for example deploying multiple databases across with mirrors across all of your database servers.

Deploy your SaaS application solution in your application tier farm.

Behaviour

To recap you have deployed a TMG NLB in front of a web farm that uses a database tier backend comprised of multiple SQL servers, but the active database may only exist on one database server.

privatecloud

As you can see an external request is sent to TMG then load balanced out to one of the web application servers which is then routed to a database server. By default traffic is randomly distributed between one or all Hyper-V Hosts.

Problem

How to optimally route requests with affinity in TMG to the Web Application server where the primary database resides so that communication between the application and database don’t transmitting the physical IP network but end up transmitting over the hypervisor network at 10gb with zero latency.

Solution

Configure your web application with application request routing in a workflow to instrument the first request with affinity to sort out the shortest path then set the client origin IP affinity to get routed in the TMG enterprise array to the TMG server that contains the primary database and web farm affinity to send additional requests over time to the web application server that resides on the same server as the primary database.

The result is over time an NLB affinity cache is built where a client request automatically gets routed to the fastest communication path for TMG, web application server and the primary database while reducing traffic on your physical network using Optimal Path Selection (OPS).