Steve Spencer's Blog

When Logic Apps first came out I wrote a blog post explaining how to convert a CSV file into XML.A lot of this is still relevant, especially the integration account and the schemas and maps that are in my github repo. This post will show how Logic Apps are now even simpler to use with flat file decoding and also show how to insert the CSV data into a SQL server. The SQL part of the blog was adapted from this post: https://pellitterisbiztalkblog.wordpress.com/2016/11/14/upload-flat-file-on-azure-sql-database-using-azure-logic-app/

Logic Apps has evolved since I last wrote about this topic and you now no longer need to create a function to transform our csv to xml.

The Transform XML connector is used now with the same maps we used in the previous post

In order to add the individual rows to the database there are a number of things you need to do. We will use an XML schema mapping in a stored procedure to extract the data from the transformed xml.

In your SQL database you will need to add a stored procedure, table and an XML schema, The SQL Scripts to create the table, stored procedure and xml schema have been added to the github repo. The stored procedure takes the xml file that has been transformed and uses the xml schema to extract the firstname, middlename and surname from the xml and then store the data in the employees table. In the logic app you need to add a SQL server connector and configure the connection to your Azure SQL database and also add in the stored procedure with the parameter as the output from the Transform XML.

The only other thing I needed to do to get this working was to remove the first row of the csv file as it contained the header fields and I didn’t want that inserted into the database.

The “length” expression is:  length(variables('csvdata'))

The “indexOf” expression is: indexOf(variables('csvdata'),'\r\n')

However if you add this in the editor the back slash will be delimited and you will end up with \\r\\n which will not work. To fix this you will need to click the View Code button, search for the \r\n and remove the extra back  slash

The “substring” expression is: substring(variables('csvdata'),add(variables('firstnewlineposition'),2),sub(variables('csvlength'),add(variables('firstnewlineposition'),2)))

The trigger for my Logic App was when a new file was added to OneDrive, so click the run button and then drop a file into the configured OneDrive location and the csv entries should be added to your database.

I’ve just finished presenting my talk on “Successfully Adopting the Cloud: TfGM Case Study”and there were a couple of questions that I said I would clarify.

1. What are the limits for the numbers of subscriptions per service bus topic. the answer is 2000. further details can be found at:http://msdn.microsoft.com/en-us/library/windowsazure/ee732538.aspx

2. what are the differences between Windows Azure SQL database and SQL Server 2012. The following pages provide the details:

Supported T-SQL: http://msdn.microsoft.com/en-us/library/ee336270.aspx

Partially supported T-SQL: http://msdn.microsoft.com/en-us/library/ee336267.aspx

Unsupported T-SQL: http://msdn.microsoft.com/en-us/library/ee336253.aspx

Guidelines and Limitations: http://msdn.microsoft.com/en-us/library/ff394102.aspx

3. Accessing the TfGM open data site requires you to register as a developer at: http://developer.tfgm.com

Thanks to everyone who attended I hope you found it useful.

One of our projects involves a web service that implements both SignalR and Web API and we were looking at the quickest and most cost effective way to get it deployed so that one of our customers could run a Windows 8 application as a demo away from the office. The application works well internally as we have the service deployed on one of our servers on IIS. The options we were considering were:

1. Package the application up in an install package, ship this to our customer and then provide them with instructions and support to allow them to deploy and configure their application
2. Deploy it on one of our servers and then publish the service through our firewall
3. Deploy as a Cloud service in Windows Azure
4. Deploy as a website in Windows Azure

We considered the fact that the first option would probably take us a fair amount of time to make a deployment package, test it and provide enough documentation and support to allow our customer to deploy it on their servers. The other 3 options involved us doing a smaller amount of work, but at least we could get everything working well before shipping the demo out. Option 2 would mean using our internal resources for something that would not be used that often but we would not necessarily know whether and when it was being used so the resources would need to be kept running limiting our capacity internally. Windows Azure was a good fit for this application and the choice was really between setting up a cloud service or use a web site, I guess we could have set up a virtual machine hosted in Windows Azure, but this was a bit excessive just for a simple web service. The two remaining options were to set up a cloud service by creating a web role in deploying to Windows Azure or to use Websites. The cloud service would involve more work for us as we would need to change the project to add in the cloud service project and web role and then do a full PaaS deploy to Windows Azure. This would then utilise a whole virtual machine (although we would have used an Extra Small instance), but the web sites seem a sensible option especially as we already have a number of them available for free. How easy was this going to be and will both Web API and SignalR work with Windows Azure Websites, especially as we were using preview software. I was surprised about how easy this was to deploy and I’ll walk through the process we went through.

Step 1: Make sure that the service runs locally,

Step 2: Our service uses Code First Entity Framework using a local SQL server. Create a database using Windows Azure SQL Server via the Windows Azure Management portal (https://manage.windowsazure.com), the copy the ADO.NET connection string.

Paste this into your web config file of the web api service. You will need to make sure that the Windows Azure SQL Server firewall has your public IP address configured and you will need to make sure that your firewall will allow connections through port 1433. Now run your application and make sure that you can connect to the Windows Azure SQL database. As we are using Code First Entity Framework, the database tables were created for me so I didn’t need to do any database deployment. The only issue I had with this approach was that I had to create the database first in Windows Azure.

Step 3: With our service running locally but with the database in Windows Azure we are now ready to deploy to the cloud. In the Windows Azure Management portal, click the “New” button

The “Quick Create”, enter the url you want to use and click “Create Web Site”

Step 4: We now need to deploy our service. In the Azure management portal, navigate to the web site you just created and click “Download Publishing Profile”. Save this to your computer.

In Visual Studio 2012, open your web api project, right click on the project in Solution Explorer and click publish.

This will display the publish dialog.

Click the import button and navigate to the folder where the publish profile was saved. This should then allow you to complete the wizard

Click Next and check to make sure the correct connection string is displayed, click Next then Publish. This should then start to upload your web api project to the Windows Azure Website. The deploy should be relatively quick and no where near the time it takes to deploy a cloud service. When completed, your deployed website should start in the browser and you can carry out whatever tests you need.

Step 5: With your website deployed you should just need to change the url of your service in the Window 8 application.

This whole process took less than 10 minutes to setup and deploy. One of the nice features of using websites is that changes are quick to deploy.

We had a number of issues to get this all working fully:

1. As I mentioned earlier we had to ensure that the database was created before the EF code would create the correct tables
2. When we first ran the Windows 8 application we were getting an error each time we tried to use SignalR. We received an “Incompatible protocol version”. This was because I installed the latest SignalR libraries on the server side code but the client was using an older version. You need to make sure that both the client and server are using the same version of SignalR
3. We also had an issue when deployed to Windows Azure where it looked like the SignalR hubs were not being created correctly. It looked like the hub creation was hanging and not returning. This is a known issue that has been fixed but not yet deployed to Azure. There is a work around which is to configure SignalR to use long polling (https://github.com/SignalR/SignalR/issues/510). We did that with the following code:

   1: hubConnection = new HubConnection(App.SignalRUrl);            

   2: proxy = App.hubConnection.CreateHubProxy("statushub");

   3: App.hubConnection.Start(new LongPollingTransport()).Wait();

Windows Azure Web Sites is not just for web sites, using it also for services can make a lot of sense as the scaling model will allow a lot of flexibility and can provide a cost effective way to host your services, especially if they are not heavily loaded at the start. They are also easy and fast to deploy which is always a bonus

Costing a system for running on Windows Azure seems complex at times and sometimes it is easy to calculate and other times it is not. However there have been a number of times in recent months where I have experienced higher than expected costs for Windows Azure. The first thing to check is the actual bill. The bills are pretty comprehensive and will itemise the usage pretty well. You should easily be able to identify overspend. The following areas are where we have seen unexpected expenses appear.

Compute

The compute prices are straight forward in Windows Azure and you pay for each instance hour you use. The minimum duration is 1 hour and is metered in blocks of hours that means if you have an instance running for 1 minute or 59 minutes you will be charged for 1 hour, similarly if you had 2 instances running at the start of the hour and then turn one off you would still be charged for 2 hours. It may also get complicated if you then put the second instance back up within the same hour. You will probably be charged for 3 hours as the second instance reappearing would be treated as a new deployment and would most likely be on a different machine (although I’ve never checked this) thus being seen as different to the first time the instance was active at the beginning of the hour. This in reality won’t cost you too much as these examples are not likely to happen. What is likely to happen and could cost a significant amount are the following

· Staging & Production

· Instance count

When you upgrade your system using the VIP swap mechanism, you will deploy your new software to the Staging area, do your testing and then VIP swap Production for Staging. During the period where you have something deployed in Staging you will be charged for the number of instances you have running in Production plus the number of instances you have running in Staging. For example I have deployed 2 instances to Production and want to upgrade my software using VIP swap. I then deploy my software to Staging configured to have 2 instances running and carry out the tests. This process takes 30 minutes. I then VIP swap Staging to Production. I will be charged for 4 instances for that hour. If I then do not undeploy the software that is currently in Staging (i.e. the old software that was previously in Production) I will continue to be charged for 4 instances. You may want to leave the old software there so that you can swap back easily if there is an issue, but remember you are being charged twice still.

Another area where you could be getting billed is if you don’t bring the instance count back down after your peak loading has subsided. An example of this is that you increase your instance count on your website just before a big marketing campaign kicks in. Once the campaign is complete it is likely that the load of your website will drop. At this point it would make sense to reduce the instance count of your website otherwise you are paying more that you need to for your site.

Data

With Windows Azure storage you get charged for the space needed to store the data, the cost to transfer the data out of the data centre and the cost of transactions on the data storage API. Just to clarify a storage transaction is effectively a call to the Storage API, whether that is to retrieve data, query queues, delete data etc. Each is at least one transaction (Some data retrievals might be multiple SDK calls). Data storage costs are straightforward, but bandwidth and transaction costs could take you by surprise if you are not careful.

Bandwidth charges cover the cost to extract data from the data centre, that could be the presentation of a web page to a user, extraction of a blob extracting data from a queue outside of Windows Azure etc. unless you are regularly moving data outside of Windows azure the bandwidth costs are relatively small (when compared to the compute costs) e.g. $0.12 per GB. Another thing to bear in mind is that data transfer within the data centre is free and also data transferred into the data centre is also currently free. This means you can put a lot of data into a data centre, process it and then store it without incurring any bandwidth charges. You will only be charged for bandwidth when you pull the data out of the data centre. It is therefore important to make sure that if you are transferring data around your system that all your compute and data storage (Storage and SQL Azure) are all in the same data centre. If you don’t then you will be charged for bandwidth to get the data from one data centre to another. For example, in the diagram below we put our web site in Dublin and SQL Azure in Amsterdam.

In the simple scenario of putting some data into our database from a website and then doing a single retrieve you can see that the data will incur 3 bandwidth charges when it should only have 1. With a lot of data transfers the costs will soon mount up.

Transactions charges also appear to be relatively low when compared to compute costs and most of the time they are, but as you are charged each time a method on the Storage API is called you could be getting charged even when there is no data being processed. This would happen when you are processing a queue. Each time you call the SDK to see if there is something to process you would be registering a transaction, therefore if you had this code in a loop that regularly checked the queue you could start to rack up hidden costs. For example a queue is polled once per second this would cost around $300 per year, however if you then reduced the polling time to check it every 100ms then the cost would be around $3000 per year, now multiply that by the number of instance you have running and the costs significantly rise. Do you really need to poll every 100ms, can you redesign to an event driven system or poll less often. Polling the queue every minute for example reduces the costs from $3000 to $5 per year

The charging concept needs to be understood by your developers so that they you don’t end up with surprise bills. You also need to be careful not to engineer a solution that is overly complex just to save transaction costs when the costs could be negligible in the scheme of things. Sensible calculations up front can help you to balance the costs effectively but watch out for the charges mentioned above as these are often done by mistake.

If you attended the Black Marble Architecture Event yesterday you would have seen a number of talks around Azure and the Windows Azure Platform Training Kit was mentioned a number of times.

The latest update to the training kit is here:

http://www.microsoft.com/download/en/details.aspx?id=8396

This update includes the changes for the Azure 1.6 SDK plus updates and new demos.

The training kit is a free resource that provides a good introduction to Azure and covers a large amount including Windows Azure, SQL Azure, AppFabric (Service Bus, Caching, Access Control) plus a load of other stuff.

AppFabric

Microsoft have announced two new Azure AppFabric CTPs.

May 2011 CTP will include Service bus enhancements including

• A more comprehensive pub/sub messaging
• Integration with the Access Control Service V2
• Queues based upon a new messaging infrastructure backed by a replicated, durable store.

See here for more details.

June 2011 CTP will include tooling to help with building, deploying and managing Windows Azure Applications including:

• AppFabric Developer Tools
• AppFabric Application Manager
• Composition Model

See here for more details

SQL Azure

The SQL May 2011 update contains the following:

• SQL Azure Management REST API – a web API for managing SQL Azure servers.
• Multiple servers per subscription – create multiple SQL Azure servers per subscription.
• JDBC Driver – updated database driver for Java applications to access SQL Server and SQL Azure.
• DAC Framework 1.1 – making it easier to deploy databases and in-place upgrades on SQL Azure.

See here for more details

During my Azure talks in Ireland we were discussing issues with migrating SQL to SQL Azure.

At PDC 2010 in Redmond there was a good talk about migrating TFS to Windows Azure and covers the issues they found. It details well the SQL Server to SQL Azure issues and discusses how they solved them. Here is a link to the presentation:

http://player.microsoftpdc.com/Session/be3ad63f-74dc-4283-b70d-817b27226175