Windows Azure Application Gateway


I’m not sure when it started, but for a while now I’ve found it hard to just sit and watch TV or a movie with my family. I feel like it doesn’t occupy my mind enough, and I need to be doing something else at the same time. My Surface has come in really handy here – usually if I’m watching something, I’ll be doing something with it at the same time. I’m sure I’m not alone in this.

There’s a lot of stuff going on within my organization, and I like to have a general idea of what everyone is doing. Recently I’ve been spending a lot of time reviewing specs, wireframes and power point presentations from other teams. We make heavy use of SharePoint internally, both the corporate and cloud versions, and that’s generally where I go to find the docs I’m looking for.

I thought I could make good use of my “quality family time in front of the TV” by combining these two activities. However, when I’m at home, accessing the documents I need from my tablet can be tricky. When they are stored in SharePoint Online, I can get to them through my browser, but getting to documents in our internal SharePoint sites is harder because they are behind the corporate firewall. I started searching around for solutions, and came across a new service called the Windows Azure Application Gateway. Microsoft has ways for employees to connect to the corporate VPN on a Surface (using our smartcards), but this new service seemed like a light weight approach and looked interesting.

Windows Azure Application Gateway


The interoperability @ Microsoft blog had a great blog post about this service:

For obvious security reasons, connecting to resources that are behind a firewall usually requires a fairly complex infrastructure such as VPN (Virtual Private Network). The AppGateway demo app is designed to make the process simpler yet highly secured. The mobile app connects to a service on Windows Azure that acts as the proxy to an agent that is running inside the network behind the firewall. Using the Windows Azure Authentication service, the proxy can establish a trusted connection to the agent so that the application on the mobile device can browse web sites that would not be normally accessible outside the corporate network.

The site has an easy to understand explanation of what the service does, and how it works. It was actually what caught my eye… I really like the graphic they use. I call him Mr. Firewall.


The usage scenario they described fit what I was trying to do – there were corpnet resources I wanted to access, but couldn’t access them from home.


Many companies will provide a way to connect to their internal corporate network (for example, DirectAccess or connecting through a VPN). These solutions tend to work fine from a laptop, but might not always work so well for a tablet or phone. Surface RT supports VPN connections, but many corporate networks require some form of custom VPN client or smartcard solution that might make it harder (or more awkward) to use. If your company doesn’t offer a VPN solution, or you’re in a reverse scenario where you want to access your home network while you’re at work, then you might be out of luck.


Their walkthrough graphics got me hooked, and I decided to try it out.


To use the service, you need to create login with either Windows Azure Active Directory (WAAD) or Office 365. (You can sign up to WAAD for free).

Once you have a login, you can download the connector and install it on your corporate machine (or your home machine, if you’re trying to create a bridge from work to home).


After install, you’ll see an Application Gateway icon in your notification area, and a browser pops up asking you to login and register your connector.


After registration, you get a webpage dashboard on your connector machine, with live tiles.


I like the detailed status window that lets you test your connectivity:


Once your connector is setup, you can use it to access web sites in a few different ways. There are custom iOS and Android client apps available, and on a windows OS (Windows Phone, Surface, PC) you access the Web Client directly using your browser. Here is a screenshot from my Surface RT:

appgateway from surface

Click on the name of your connector, and it brings up a web browser interface. From here you can enter intranet URLs. The data from the site gets passed back through your connector machine and rendered on your device. It’s as if you were directly connected to your network using a VPN.


I’ve just started using the service, but it seems to work great so far. I’m able to browse internal SharePoint sites, and view (and edit) Word/PowerPoint files on my Surface. It hasn’t been out long (it’s still in Preview), so I expect there will be further improvements before it becomes generally available.

A more detailed explanation of how the service works can be found here, and it looks the source code for their Android demo app is available on Github.

Try it out and let the team know what you think!

New SSIS Whitepapers from the SQLCAT Team

Two new SSIS SQL Server Technical Articles written by the SQLCAT team have been posted to the MSDN Library. They both focus on hybrid/cloud data movement, but they also contain design patterns and guidance that applies to on-premises data flows as well. The SSIS Operational and Tuning Guide has a great section on designing a package for restartability and performance tuning that all SSIS users will find useful.

SSIS Operational and Tuning Guide

SQL Server Integration Services (SSIS) can be used effectively as a tool for moving data to and from Windows Azure (WA) SQL Database, as part of the total extract, transform, and load (ETL) solution and as part of the data movement solution. SSIS can be used effectively to move data between sources and destinations in the cloud, and in a hybrid scenario between the cloud and on-premise. This paper outlines SSIS best practices for cloud sources and destinations, discusses project planning for SSIS projects whether the project is all in the cloud or involves hybrid data moves, and walks through an example of maximizing performance on a hybrid move by scaling out the data movement.


SSIS for Azure and Hybrid Data Movement

SQL Server Integration Services (SSIS) can be used effectively as a tool for moving data to and from Windows Azure SQL Database, as part of the total extract, transform, and load (ETL) solution and as part of the data movement solution. SSIS can be used effectively to move data between sources and destinations in the cloud, and in a hybrid scenario between the cloud and on-premise. This paper outlines best practices for using SSIS for cloud sources and destinations and for project planning for SSIS projects to be used with Azure or hybrid data moves, and gives an example of maximizing performance on a hybrid move by scaling out the data movement.


Did you know that the DQS team also published a whitepaper on using DQS with AlwaysOn? It’s another interesting read.

SQL Saturday #146 | Nashua, New Hampshire


I’ve been invited to speak at SQL Saturday #146 in Nashua in October. I’ll be presenting two talks related to Data Movement:

Efficient On-Premise to Cloud Data Transfer

Thinking about moving some of your operations to Azure? Have multiple remote sites, and want to use the cloud to centralize and share data between them?? Just like hearing talks about data transfer performance?! Have we got the session for you! We’ll cover some common user scenarios, and describe when and how to use the latest Microsoft data transfer technologies, including SQL Server Integration Services (SSIS), SQL Data Sync (a capability of SQL Database), and more.

EIM – Bringing Together SSIS, DQS and MDS

Enterprise Information Management (EIM) is an industry term for managing your data for data integration, quality, and governance. This session revolves around a demo which brings together the EIM functionality in SQL Server, a key part of our Credible, Consistent Data story for the 2012 release. We will show you how SQL Server Integration Services (SSIS), Data Quality Services (DQS), Master Data Services (MDS) and other Microsoft technologies work together to provide a comprehensive EIM solution.

Both are intermediate (200-300) level talks, and will cover multiple technologies. I’ll be using this as a venue to preview of some of the new content I’ll be presenting at the PASS  Summit in November. I’ll most likely end up in SSIS Design Pattern book co-author Andy Leonard’s (Blog | Twitter) two SSIS sessions, as well – I always love to see other people present SSIS topics, and Andy is one of the best SSIS presenters around.

The full schedule is now available on the event site. If you’re at the event, please come by and say hello! I’ll be happy to sign your ebook edition of my two SQL 2012 books.

The timing is perfect, as I’ll be presenting to the New England SQL Server User Group a couple of days before… Hope to see you at both events!

Getting started with SQL Data Sync

Here’s the scenario – you have an on-prem SQL database that you’d like to expose to a number of internet based applications. This could be Windows Azure Reporting Services reports, web applications, Windows Azure based apps, or mobile clients… it’s might even be all of the above. Now, you could make SQL Azure your primary database location, however, chances are you have processes and business apps running on-prem that are accessing (and perhaps generating) the data within this database.

In comes SQL Data Sync – a web service in the Azure platform that allows one-way or bi-directional syncing of on-prem SQL databases, and SQL Azure databases. It also allows SQL Azure <-> SQL Azure syncing. This post provides a step-by-step look at how to get started with the service. For more information, you can also take a look at the Getting Started entry in Books Online.

[warning]SQL Data Sync is currently in a technology Preview state.[/warning]

[warning]This steps and user interface described in this post is current as of 2012-07-03.[/warning]

The first step to using SQL Data Sync is to provision yourself a server. Log into the Windows Azure Platform management portal, and click the Data Sync button on the left hand pane.


In the center of your window, you’ll see the Provision option.


Click on the Provision button to setup your service account. Select your subscription, and the region you’d like to host your hub.


Once you have provisioned the server, you will create your Sync Group.


Step 1 is to specify a name for the sync group. In this scenario, I’d like to sync an on-prem AdventureWorksDW database to the cloud, so I’ll name my sync group accordingly.


Once you’ve set the name, Step 2 is add a SQL Server database to the sync group.


Since this is the first Sync Group we are creating, we’ll be adding a new SQL Server database to the sync group. The sync direction specifies how the data is being moved:

  • Bi-Directional – data flows both ways between the Hub and the client
  • Sync to the Hub – data flows to the hub only. The hub acts as a mirror for changes made on the client.
  • Sync from the Hub – data flows to the client only. The client acts as a mirror for changes made to the Hub.


In this scenario, I’d like to mirror my on-prem database to make it available to Cloud applications, so I select “Sync to the Hub”.

On the next page, I need to select a Client Sync Agent. Since I don’t have one already installed, I select the “Install a new Agent” option.


I now download the agent binary to my local machine, enter a name, and generate an Agent Key.

Agent Configuration

Clicking the Download button brings you to a download page, with the option to download and install the agent msi. During the installation process, you are prompted for an account which has network access. This becomes the account which will run the agent service.


Once the agent is installed, you will see a new “Microsoft SQL Data Sync” service created on your machine. Before you can start syncing, you will need to launch the Agent Configuration Tool (“Microsoft SQL Data Sync Agent Preview”).


Launching the newly installed Agent application brings up the configuration UI.


Back to the Windows Azure Management Portal to generate a key for the agent.


Copy the key from the management portal, and go back to the Agent configuration tool. Click the Submit Agent Key button and enter the key.


Now your Agent is linked to the Sync Group you are creating. We can proceed with making databases available.


Click Register to add a new database. You’ll enter the SQL instance information, and the database name. You have the option of using SQL Authentication (i.e. a user name and password), or to use Windows Authentication and connect to the database using the SQL Data Sync Agent service account.


Once a database has been configured, we can go back to the Management Portal.

On the next step, we will add a SQL Server database to our sync group.


Click the Get Database List button to retrieve the list of databases you registered with the SQL Data Sync Agent. Select your database (in this example I am selecting AdventureWorksDW).

Step 3 will be to configure a Windows Azure SQL Database to use as the Sync Hub.


Click the database icon to enter your Windows Azure SQL database information.

Note, for the Test connection to work, I had to enter:

  • Fully qualified server name (i.e. <server>
  • Database name (i.e. AdventureWorksDW_Sync)
  • Credentials
    • Username without @<server>
    • Password

Step 4 is to enter the configuration details.


For the configuration, you can specify the sync schedule, as well as the Conflict Resolution if you are doing Bi-directional syncing. In this example, I don’t expect to have any conflicts (as it’s a one way push to SQL Azure), but I’ll select Client Wins.

Step 5 is to define the dataset you will synchronize.



When we select our source database (in this case, the on-prem AdventureWorksDW database), we see that we have some tables that can’t be properly synced to SQL Azure. Clicking on the See the Report link brings up details.


For this example, we can ignore the tables that can’t be synced. Clicking Select All will automatically skip tables with incompatible schemas.


At the bottom of the UI, we can see a Row Filter area. Filtering allows us to sync only certain records… for example, we might want to only sync a single year of data from our fact table. This is essentially adding a WHERE clause to our sync query. To do this, we would select the table we want to filter, and click the Filter checkbox next to the column we are adding the WHERE clause on.


Step 6 is to “Deploy” the changes to the server.


Once you click Deploy, SQL Data Sync completes the configuration of the sync group. The selected schema will be created in your SQL Azure database, and the initial set of data will be moved over.


Your newly created Sync Group now appears underneath your Subscription on the left hand side of the Management Portal. On the right, you can see the dataset sync definition, including any filters you’ve defined.