How to tell if your Azure SQL database performance issues are data center related

I am the founder of a startup called Cotega and also a Microsoft employee where I work as a Program Manager. This is a series of posts where I talk about my experience building a startup outside of Microsoft. I do my best to take my Microsoft hat off and tell both the good parts and the bad parts I experienced using Azure.

Working on a monitoring service for SQL Azure Azure SQL Database allows me to get a really good idea of the common issues DBA’s have when working with their database. If I had to choose the most common area of frustration I hear from DBA’s is whether their performance issue is related only to their database or to the entire data center where their database is hosted? It has been a goal of mine from the very early days of Cotega to help solve this problem. Today I am happy to announce that within Cotega, you are now able to see your performance data overlayed with that of the data centers performance. As you can see below, here is an example of connection latency done from outside the Azure data centers. It shows the connection latency of my database (in blue) along with the average latency of all other customers in this datacenter (in orange).

connection_latency

This has taken me some time to build because in order to get a true picture of the data center you are in, I needed to have enough customers in each of the data centers such that I could get an aggregated average of all the data gathered from the monitoring of these database without exposing any information about any one customers performance data.

As of right now, I have a really good picture of all the major Azure data centers (with the exception of East Asia). In fact, even without a Cotega account, you can see the status of each of the data centers here. Please note that this data is related only to that of SQL Database and does not reflect that of other Azure services.

I hope that all of you will consider creating an account to help everyone create an absolutely perfect picture of the health of SQL Azure database data centers. To learn more about how to create an account, please visit www.cotega.com.

Liam

Check out the new look of Cotega – SQL Azure Monitoring Service

I am the founder of a startup called Cotega and also a Microsoft employee where I work as a Program Manager. This is a series of posts where I talk about my experience building a startup outside of Microsoft. I do my best to take my Microsoft hat off and tell both the good parts and the bad parts I experienced using Azure.

It has been some time since I have posted an update, but the past few months have been quite busy. One of the things that I have been working on is a major update to the Cotega service. I heard a lot of feedback from people on the v1 version of Cotega and I think one of the biggest things I heard was in the area of ease-of-use. There are a lot of things that people want to be able to monitor for their database and I just made it too hard to add all of the things needed. So I decided to step back and try to come up with a new way to set up your database monitoring. Now, you simply point Cotega to your database, provide an email to send notifications and you are ready to go.

In addition, there is now a “Free” subscription that does not require a credit card and allows you to try out the service before making a commitment. The key difference between the Free plan and a paid one is that monitoring is only done once every 2 hours (as opposed to every 10 min for a paid account). In additions paid accounts will send email notifications when database issues occur.

cotega_dashboard

One Response to “Check out the new look of Cotega – SQL Azure Monitoring Service”

How to Host a Website on Azure and Link it to a Domain like .IO

.COM addresses are becoming harder and hard to come by. It seems each time you look, it is harder to get the name you want. Many startups have become creative in the way they name their site, for example if you wanted to create a site called score.com, you might try calling it scorable.com or scorability.com. Another option that seems to be getting more and more popular is to use non .COM domains such as instant.ly or ordr.in or ginger.io. Many of these domains are easier to get, but the downside for many is that they are typically more expensive and the process of configuring them is often harder. I want to talk about how I managed to create URL using the io domain which used a site hosted on Windows Azure (where all the html pages are hosted). I am going to assume you already know how to create a web site on Windows Azure.

Getting the Domain Name

In the past, I have used GoDaddy as the place that I set up most of my domains. For .COM’s this is incredibly cheap (most of the time you can find a promo code for just about anything) and I have found them to be extremely supportive and easy to work with. However, in this case I wanted to get a .IO domain which is actually a Indian Ocean domain because the .COM was owned by someone else trying to sell it. To do this, I used http://nic.io where it cost ~$93 for the year (as opposed to ~$8 for a .COM domain).

DNS Server Configuration

After I purchased the .io domain from nic.io, I learned that they needed to be provided with DNS settings requiring a Primary and Secondary DNS Server. This was kind of frustrating since GoDaddy provided me with this whenever I created a .COM address. So after looking a little more I learned that I could just use GoDaddy for this. In my case, I could just log into my existing GoDaddy account and configure this for no extra charge. For you, you could either look at another DNS Server provider or create an account with GoDaddy to do this.

GoDaddy DNS Configuration

Within my GoDaddy account, I launched the DNS Manager and chose to create a new Offsite Domain. This was pretty hidden which is why I am adding a screen shot of where I went.

dns_config1

In the Domain Name text box I entered my .IO domain name, clicked Next and copied the 2 DNS servers that were provided.  Copy these as you will need them in a minute.

Create A Record

The next step is to configure the GoDaddy DNS servers so that when a request for my domain .io is requested, it know where to point the user to.  In my case it is a Windows Azure website with a .cloudapp.net name.    To do this config, in the GoDaddy DNS Dashboard you click on “Edit Zone” under the domain you just added.  In the A Host section, choose “Add Record” and for “Host” use @, and for “Points To” add the IP address to your Azure hosted site.

Choose Save

a_record

Add DNS Servers to NIC.io.

The final step is to configure NIC.io so that it knows to use the GoDaddy domain servers you just configured. Log in to the admin panel for NIC.io and choose to Manage the DNS Settings. In the “DNS Servers or Mail & Web Forwarding Details”, for the primary server enter the first DNS server you copied from the previous steps and in the Secondary Server enter the second. If you only have one DNS server, I think that is probably ok. At the bottom choose “Modify Domain”.

Give it an hour or two

It usually takes an hour or two for these configurations to fully get updated. However, after that you should be all set to go.

How to use PayPal with ASP.NET MVC

I talked previously about why I chose to use Stripe with Cotega to allow me to accept credit card payments. In it I talked about how excellent their support was and how easy it was to take their samples and implement it into my service. I am still extremely pleased with my choice of using Stripe and I have no intention of moving away from it. I have however always wanted to investigate using PayPal as an alternative payment option because occasionally International customers will have credit cards that are not acceptable by Stripe or they just plain prefer to use PayPal. For this reason, I decided to take some time and understand what it would take to implement PayPal into my MVC based service.
Unfortunately, I did not find this to be quite as simple to implement as it was with Stripe. Although PayPal has a number of examples, none of the ones I could find were targeted for MVC and there did not seem to be any good tutorials on this subject to get me started. I hope this blog post will help anyone thinking to implement PayPal with MVC. I do want to caveat this with the fact that I am far from a PayPal expert and I do not claim that this is the “PayPal preferred” way to implement this other than to say that I have tried to convert other examples as best I can and it has worked well for me so far. If you have any comments or suggestions, I am very interested to hear your feedback.

Step 1 – Choosing PayPal IPN vs. PDT vs. Express

One of the first things I needed to decide on was the appropriate “PayPal Responder” to use. A responder is like a callback that PayPal does when the transaction is complete (successful, unsuccessful, invalid, etc.). It can also return custom variables that you set when the user first clicks the “Pay Now” button.
Since we are using MVC, we first need to create a View that initiates the transaction to PayPal. This is where the user clicks the “Pay Now” button and is re-directed to PayPal where they enter their credit card or PayPal account information. After this is complete, PayPal can initiate a responder which calls your service with the results of the transaction. The flow of the process looks something like this:

paypal_payment_process

You really only need to use a responder in the cases where you need to continue with some process after that transaction completes. For example, in my case I want to be able to enable a user subscription once the transaction completes. If you are simply accepting payments, there may be no need to go to the complexity of using responders.
There is lots of information on responders but I found that Instant Payment Notifications (IPN) was the best choice for me, and probably yourself as well if you simply want a callback from PayPal to your MVC controllers.

Step 2 – Implementing the View and Form

The next step is to create a payment page using a View where the user can choose to start the PayPal process. Here is an example of a HTML form that I used.

There are a couple of things you should notice in this form:

  • The commented out section allows me to either submit the payment request to the PayPal sandbox (test) environment or to the actual live PayPal url.
  • Notice all the input type=”text” types. These are values that are returned to my controller after the transaction is complete. When I load the page, I set the “device-id” input type to a custom value that I want PayPal to return when the transaction completes. I can use this value to update the user’s status in my SQL database (assuming the transaction is successful).
  • The “return” input type URL defines where the user will be redirected to after the transaction is complete. It is important that you don’t rely on this as a method for completing a transaction because most users will never click on this link. Don’t forget to create this page.
  • The notify_url input is the IPN URL that PayPal will call after the transaction completes.
  • Remember to associate the business input type email address with the PayPal address you used for your sandbox or live account
  • There are a lot more custom values you can use and learn more about here.

Step 3 – Creating an IPN Controller in MVC

Next we will want to create a new ActionResult controller that will receive the responder request from PayPal after the transaction completes. I want to give credit to this page where I was able to leverage a lot of code for this controller. You should also remember to actually create an IPN view (IPN.cshtml) page because PayPal will continue to call this controller until it gets a successful page returned.

public ActionResult IPN() { // Receive IPN request from PayPal and parse all the variables returned var formVals = new Dictionary(); formVals.Add("cmd", "_notify-validate"); // if you want to use the PayPal sandbox change this from false to true string response = GetPayPalResponse(formVals, false); if (response == "VERIFIED") { string transactionID = Request["txn_id"]; string sAmountPaid = Request["mc_gross"]; string deviceID = Request["custom"]; //validate the order Decimal amountPaid = 0; Decimal.TryParse(sAmountPaid, out amountPaid); if (sAmountPaid == "2.95") { // take the information returned and store this into a subscription table // this is where you would update your database with the details of the tran return View(); } else { // let fail - this is the IPN so there is no viewer // you may want to log something here } } return View(); } string GetPayPalResponse(DictionaryformVals, bool useSandbox) { // Parse the variables // Choose whether to use sandbox or live environment string paypalUrl = useSandbox ? "https://www.sandbox.paypal.com/cgi-bin/webscr" : "https://www.paypal.com/cgi-bin/webscr"; HttpWebRequest req = (HttpWebRequest)WebRequest.Create(paypalUrl); // Set values for the request back req.Method = "POST"; req.ContentType = "application/x-www-form-urlencoded"; byte[] param = Request.BinaryRead(Request.ContentLength); string strRequest = Encoding.ASCII.GetString(param); StringBuilder sb = new StringBuilder(); sb.Append(strRequest); foreach (string key in formVals.Keys) { sb.AppendFormat("&{0}={1}", key, formVals[key]); } strRequest += sb.ToString(); req.ContentLength = strRequest.Length; //for proxy //WebProxy proxy = new WebProxy(new Uri("http://urlort#"); //req.Proxy = proxy; //Send the request to PayPal and get the response string response = ""; using (StreamWriter streamOut = new StreamWriter(req.GetRequestStream(), System.Text.Encoding.ASCII)) { streamOut.Write(strRequest); streamOut.Close(); using (StreamReader streamIn = new StreamReader(req.GetResponse().GetResponseStream())) { response = streamIn.ReadToEnd(); } } return response; }

Step 4 – Enabling Sandbox and IPN

I highly recommend that you first test this with the PayPal sandbox. You need to sign up separately for a sandbox account from your live PayPal account from the developer page here.
For sandbox accounts, you do not need to enable IPN, but for live accounts you do. Once you are ready to switch to live, log in to your PayPal account and go to Profile | More Options | My Selling Tools | Choose Update for “Instant Payment Notifications” and then enable IPN and set the URL that you wish PayPal to call (which is the IPN controller we created above).

Summary

At this point you should be ready to go. One of the real pains of this system is that there is no easy way to debug your IPN controller if you are running the MVC app on a remote machine that does not have Visual Studio installed. For this reason, I chose to log each of the steps to a “Log” table that I created in my database. That way I could log all the variables that PayPal returned and still get an idea of what was happening or determine if there were any issues.
I am still testing this and have not yet implemented this into my Cotega service for monitoring SQL Azure databases. For now, if you would like to see this working I am using it in a Windows Phone Baby Monitor app that I wrote.
If you have any suggestions or problems I look forward to hearing from you.