MS SQL on AWS: Amazon RDS

amazon-rds-ms-sql-server

There are some startups and SMEs hosting their databases on AWS. However, most of them choose to use Amazon EC2 because doing so is similar to running a SQL Server on-premise at data centres. So, to them, it’s something that they are familiar with back in the old days. However, doing so actually increases their cost of hosting services on AWS. The companies also need to hire experts to do database administration such as database backup and recovery and OS patching.

Hence, if I’m given the opportunity, I usually recommend the small companies with limited resources to consider Amazon RDS (or Azure SQL) first. Amazon RDS is a fully managed service which provides cost-efficient and resizable capacity while automating time-consuming database administration tasks.

Multi-AZ Deployments for MS SQL Server

Starting from May 2014, Amazon RDS also provides a highly available database solution with the synchronous Multi-AZ replication for MS SQL. Multi-AZ deployments for MS SQL database instances use SQL Server Mirroring.

Currently, Amazon RDS only supports Standard Edition and Enterprise Edition of SQL Server 2008 R2, 2012, 2014, and 2016. Amazon RDS also does not support Multi-AZ with Mirroring for the following regions yet:

  • US West (N. California);
  • Asia Pacific (Singapore);
  • European Union (Frankfurt);
  • AWS GovCloud (US);
  • Asia Pacific (Sdyney): Supported for DB instances in VPCs only;
  • Asia Pacific (Tokyo): Supported for DB instances in VPCs only;
  • South America (São Paulo): Supported for all DB instance classes except m1/m2.

It’s quite unfortunate that Singapore Region is one of them.

use-multi-az-deployment-for-production-sql-server-se.png

In N. Virginia Region, we’re able to specify to use Multi-AZ Deployment in Production SQL Server SE.

DB Instance Class

We can specify the DB Instance Class that allocates the computational, network, and memory capacity required by planned workload of the database instance.

Standard (db.m4) instances offer a balance of compute, memory, and network resources, and are a good choice for many applications.

Memory Optimized (db.r3) instances are designed to deliver fast performance for workloads that process large data sets in memory. The instances are well suited for the applications, such as high performance relational databases, in-memory analytics, and enterprise applications (for example, Microsoft SharePoint).

Burst Capable (db.t2) instances are instances that provide baseline performance level with the ability to burst to full CPU usage.

Storage Types

Most of the Amazon RDS are using Amazon EBS (Elastic Block Store) volumes for database and log storage. There are currently two main Storage Types available when setting up MS SQL database instances, as listed below.

General Purpose (SSD) storage, aka gp2, offers cost-effective storage which is suitable for a broad range of database workloads. Hence, it’s ideal for small to medium-sized databases. It provides baseline of 3 IOPS/GB and ability to burst to 3,000 IOPS for extended periods of time. Its volume can range from 20GB to 4TB for MS SQL database instances. However, provisioning less than 100 GB of General Purpose (SSD) storage for high throughput workloads could result in higher latencies upon exhaustion of the initial General Purpose (SSD) I/O Credit balance.

Provisioned IOPS (SSD) storage, aka io1, is suitable for I/O intensive database workloads which pay attention to storage performance and consistency in random access I/O throughput. It provides flexibility to provision I/O ranging from 1,000 to 30,000 IOPS. MS SQL can have provisioned IOPS volumes between 100GB (Express/Web edition) or 200GB (Standard/Enterprise edition) and 4TB.

Allocated Storage and I/O Credits

General Purpose (SSD) storage performance is controlled by the volume size. Larger volumes have higher base performance levels and can accumulate I/O Credits faster. The more storage, the greater the base performance is and the faster it replenishes the credit balance.

For General Purpose (SSD) storage, the DB instance has an initial I/O Credits balance of 5.4 million. When the storage requires more than the base performance I/O level, it uses I/O credits in the credit balance to burst to the required performance level, up to a maximum of 3,000 IOPS. If the storage uses all of its I/O credit balance, its maximum performance will remain at the base performance level until I/O demand drops below the base level and unused credits are added to the I/O credit balance at the baseline performance rate of 3 IOPS/GB of volume size. Hence, we can use the formula below to calculate the Burst Duration.

burst-duration-formula.png

burst-duration-tabular.png

Thus, for production application that requires fast and consistent I/O performance, it’s recommended to use Provisioned IOPS (SSD) storage that is optimized for I/O intensive, online transaction processing workloads that have consistent performance requirements. Note that we cannot decrease storage allocated for a DB instance.

For MS SQL Server, Amazon RDS does not currently support increasing storage. Hence, we need to provision storage based on anticipated future storage growth. If we predict it wrongly, then we need to increase the storage of an existing SQL Server DB instance by first exporting the data, creating a new database instance with increased storage, and then importing the data into the new database instance.

Specifying Database Instance Specification

After understanding key concepts above, we can then proceed to setup our database instance.

specifying-db-instance-specifications.png

Although there is Free Tier available but allocating storage > 20GB or adding provisioned IOPS will disqualify the databse instance from being eligible for the Free Tier.

Network and Security: VPC (Virtual Private Cloud)

Amazon RDS database instances can be hosted on either EC2-VPC platform or the legacy EC2-Classic platform, the original platform used by Amazon RDS. Amazon VPC launches AWS resources, such as database instances, into a virtual private cloud.

Nowadays, if we are creating a database instance in a region that we have not used before, we normally are already on the EC2-VPC platform.

rds-supported-platforms.png

We are already on EC2-VPC platform.

There are many scenarios for accessing a database instance in a VPC. Today, I will only focus on having an EC2 web server to access the database instance in the same VPC.

web-server-and-db-instance-in-the-same-vpc.png

A database instance in a VPC accessed by an EC2 instance in the same VPC (Source: AWS Documentation)

In such scenario, Amazon RDS database instance normally needs to be available to the web server, and not to the public Internet. Hence, we can create a VPC with both public and private subnets. The web server will be hosted in the public subnet so that it is accessible by the public. The database instance is hosted in the private subnet so that it won’t be available to the public Internet, providing greater security.

The Security Group used to restrict access to the database instances can have a custom rule that allows TCP access using the port 1433 and an IP address we will use to access the database instance for development or other purposes. In addition, we also need to set the Public Accessible option to Yes first (It is recommended to set the option to No for production database instance to limit the potential thread with no public routes).

Encryption of Database Instances using Key Management Service (KMS)

Amazon RDS for MS SQL supports the encryption of database instances with encryption keys managed in AWS KMS. Once the data is encrypted, Amazon RDS handles authentication of access and decryption of the data transparently without having the need to change our database client applications.

enable-database-encryption.png

Currently, encryption of database instances (Data-in-Rest Protection) is not available for those which are running SQL Server Express Edition.

Backup and Maintenance

Amazon RDS automatically backup our database instances. It creates a storage volume snapshot of our database instance, backing up the entire database instance and not just individual databases. We can setup and modify our preferred Backup Window from time to time. During the automatic backup window, storage I/O might be suspended briefly while the backup process initializes (typically under a few seconds). For SQL Server, I/O activity is suspended briefly during backup for Multi-AZ deployments.

By default, Amazon RDS has a 30-minute backup window randomly selected from an 8-hour block (Singapore region will be 14:00–22:00 UTC).

Periodically, Amazon RDS also automatically does maintenance work such as, updating the databse instance’s or database cluster’s OS. We can choose to manually apply maintenance, or wait for the automatic maintenance process initiated during our preferred maintenance window. There is one thing to take note is that the maintenance window determines when pending operations start, but does not limit the total execution time of these operations.

By default, Amazon RDS also has a 30-minute maintenance window randomly selected from an 8-hour block (Singapore region will be 14:00–22:00 UTC).

maintenance-window-collide-with-backup-window.png

We’re not allowed to make the maintenance window and the backup window overlap.

CloudWatch

Amazon RDS sends metrics to CloudWatch for each active database instance every minute. Detailed monitoring is enabled by default.

cloudwatch.png

Amazon RDS Metrics

When setting up the database instance, there is an option for us to specify whether to enable Enhanced Monitoring or not. Enhanced Monitoring is not exactly like CloudWatch. CloudWatch gathers metrics about CPU utilization from the hypervisor for a database instance, and Enhanced Monitoring gathers its metrics from an agent on the instance.

enable-enhanced-monitoring.png

Enhanced monitoring requires permission to act on our behalf to send OS metric information to CloudWatch Logs.

Conclusion

It’s true that AWS allows us to deploy our MS SQL Server database on either Amazon RDS and Amazon EC2. However, it’s very crucial to analyze our needs and our application before deciding which one to use. In general, it is still recommended to consider Amazon RDS first so that developers can focus on high-level tasks and business logic implementation.

That’s all for my first trip to Amazon RDS. As a frequent user of Microsoft Azure, I never host MS SQL Server on AWS platform. So, if there is any mistake made in this article, kindly feedback to me. Thanks in advance!

Further Reading

Deploying Microsoft SQL Server on Amazon Web Services

Advertisements

Entity Framework and Database

By using Entity Framework, we can save a lot of time on writing SQL ourselves because Entity Framework, a Microsoft-supported ORM for .NET, is able to generate the SQL for us.

I started to use ADO .NET when I was building .NET web applications in my first job. I learnt about how to call stored procedures with ADO .NET. I witnessed how my colleague wrote a 400-line SQL to complete a task which we normally will choose to do it in C#. I also realized the pain of forgetting to update the stored procedure when the C# code is already different.

After that, my friend introduced me Entity Framework when I was working on my first ASP .NET MVC project. Since then, I have been using Entity Framework because it enables me to deliver my web applications faster without writing (and debugging) any SQL myself. I read a very interesting article comparing between Entity Framework and ADO .NET. The author also acknowledged that the performance of Entity Framework was slower than hand-coded ADO .NET. He emphasized that, however, Entity Framework did maximize his productivity.

How I react when I read a 400-line stored procedure submitted by my colleague.

How I react when I read a 400-line stored procedure submitted by my colleague.

What Is Happening in Database with Entity Framework?

The SQL generated by Entity Framework is believed to be pretty good. However, it’s still nice to be aware of what SQL is being generated. For example, I have the following code to retrieve Singapore weather info.

using (var db = new ApplicationDbContext())
{
    var forecastRecords = db.SingaporeWeathers.ToList();
}

In Visual Studio, I can just mouse-over “SingaporeWeather” to get the following query.

SELECT 
    [Extent1].[RecordID] AS [RecordID], 
    [Extent1].[LocationID] AS [LocationID], 
    [Extent1].[WeatherDescription] AS [WeatherDescription], 
    [Extent1].[Temperature] AS [Temperature], 
    [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[SingaporeWeathers] AS [Extent1]

If I have the following code which retrieves only records having temperature greater than 37, then I can use ToString().

using (var db = new ApplicationDbContext())
{
    var query = from sw in db.SingaporeWeathers where sw.Temperature > 37 select sw;
    Console.WriteLine(query.ToString());
}
SELECT
     [Extent1].[RecordID] AS [RecordID],
     [Extent1].[LocationID] AS [LocationID],
     [Extent1].[WeatherDescription] AS [WeatherDescription]
     [Extent1].[Temperature] AS [Temperature],
     [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[SingaporeWeathers] AS [Extent1]
WHERE [Extent1].[Temperature] > cast(37 as decimal(18))

I am using DBContect API, so I can just use ToString(). Alternatively, you can also use ToTraceString(), which is a method of ObjectQuery, to get the generated SQL.

SQL Logging in Entity Framework 6

It is a great news for developer when Entity Framework is announced to have SQL Logging feature added For example, to write database logs to a file, I just need to do as follows.

using (var db = new ApplicationDbContext())
{
    var logFile = new StreamWriter("C:\\temp\\log.txt");
    db.Database.Log = logFile.Write;
    var forecastRecords = db.SingaporeWeathers.Where(x => x.Temperature > 37).ToList();
    logFile.Close();
}

Then in the log file, I can see logs as follows.

...
Closed connection at 6/6/2015 10:59:32 PM +08:00
Opened connection at 6/6/2015 10:59:32 PM +08:00
SELECT TOP (1) 
    [Project1].[C1] AS [C1], 
    [Project1].[MigrationId] AS [MigrationId], 
    [Project1].[Model] AS [Model], 
    [Project1].[ProductVersion] AS [ProductVersion]
FROM ( SELECT 
    [Extent1].[MigrationId] AS [MigrationId], 
    [Extent1].[Model] AS [Model], 
    [Extent1].[ProductVersion] AS [ProductVersion], 
    1 AS [C1]
    FROM [dbo].[__MigrationHistory] AS [Extent1]
    WHERE [Extent1].[ContextKey] = @p__linq__0
) AS [Project1]
ORDER BY [Project1].[MigrationId] DESC
-- p__linq__0: 'MyWeb.Migrations.Configuration' (Type = String, Size = 4000)
-- Executing at 6/6/2015 10:59:32 PM +08:00
-- Completed in 70 ms with result: SqlDataReader

Closed connection at 6/6/2015 10:59:32 PM +08:00
Opened connection at 6/6/2015 10:59:32 PM +08:00
SELECT 
    [Extent1].[RecordID] AS [RecordID], 
    [Extent1].[WeatherDate] AS [WeatherDate], 
    [Extent1].[WeatherDescription] AS [WeatherDescription], 
    [Extent1].[WeatherSecondaryDescription] AS [WeatherSecondaryDescription], 
    [Extent1].[IconFileName] AS [IconFileName], 
    [Extent1].[Temperature] AS [Temperature], 
    [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[Weathers] AS [Extent1]
WHERE [Extent1].[Temperature] > cast(37 as decimal(18))
-- Executing at 6/6/2015 10:59:33 PM +08:00
-- Completed in 28 ms with result: SqlDataReader
...

So, as you can see, even the Code First migration related activity is logged as well. If you would like to know what are being logged, you can read an article about SQL Logging in EF6 which was written before it’s released.

Migration and the Verbose Flag

Speaking of Code First migration, if you would like to find out the SQL being generated when Update-Database is executed, you can add a Verbose flag to the command.

Update-Database -Verbose

Navigation Property

“I have no idea why tables in our database don’t have any relationship especially when we are using relational database.”

I heard from my friend that my ex-colleague shouted this in the office. He left his job few days after. I think bad codes and bad design do anger some of the developers. So, how do we do “relationship” in Entity Framework Code First? How do we specify the foreign key?

I quit!

I quit!

In Entity Framework, we use the Navigation Property to represent the foreign key relationship inside the database. With Navigation Property, we can define relationship between entities.

If we have a 1-to-1 Relationship between two entities, then we can have the following code.

public class Entity1
{
    [Key]
    public int Entity1ID { get; set; }
    public virtual Entity2 Entity2 { get; set; }
}

public class Entity2
{
    [Key, ForeignKey("Entity1")]
    public int Entity1ID { get; set; }
    public virtual Entity1 Entity1 { get; set; }
}

By default, navigation properties are not loaded. Here, the virtual keyword is used to achieve the lazy loading, so that the entity is automatically loaded from the database the first time a property referring to the entity is accessed.

However, there are people against using virtual keyword because they claim that lazy loading will have subtle performance issue in the application using it. So, what they suggest is to use the include keyword, for example

dbContext.Entity1.Include(x => x.Entity2).ToArray();

By specifying the ForeignKey attribute for Entity1ID in Entity2 class, Code First will then create a 1-to-1 Relationship between Entity1 and Entity2 using the DataAnnotations attributes.

For 1-to-n Relationship, we then need to change the navigation property, for example, in Entity1 class to use collection as demonstrated in the code below.

public class Entity1
{
    [Key]
    public int Entity1ID { get; set; }
    public virtual ICollection<Entity2> Entity2s { get; set; }
}

Finally, how about n-to-m Relationship? We will just need to change the navigation property in both Entity1 and Entity2 classes to use collection.

public class Entity2
{
    [Key]
    public int Entity2ID { get; set; }
    public virtual ICollection<Entity1> Entity1s { get; set; }
}

Together with the following model builder statement.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Entity2>()
        .HasMany(e2 => e2.Entity1s)
        .WithMany(e1 => e1.Entity2s)
        .Map(e12 => 
            {
                e12.MapLeftKey("Entity1ID");
                e12.MapRightKey("Entity2ID");
                e12.ToTable("Entity12");
            });
}

The code above is using Fluent API which won’t be discussed in this post.

Database Context Disposal

When I first used Scaffolding in MVC 5, I noticed the template of controller class it generates look something as follows.

public class MyController : Controller
{
    private MyContext db = new MyContext();
    
    protected override void Dispose(bool disposing)
    {
        if (disposing) 
        {
            db.Dispose(); 
        } 
        base.Dispose(disposing);
    }
}

Before using Scaffolding, I have always been using the Using block, so I only create database context where I have to, as recommended in a discussion on StackOverflow. Also, the Using block will have the Dispose() be called automatically at the end of the block, so I don’t need to worry about forgetting to include the Dispose() method to dispose the database context in my controller.

Azure SQL: Database Backup and Restore

Before ending this post, I would like to share about how DB backup and restore is done in Azure SQL Database.

First of all, Azure SQL Database has built-in backups and even self-service point in time restores. Yay!

For each activate databases, Azure SQL will create a backup and geo-replicate it every hour to achieve 1-hour Recovery Point Objective (RPO).

If there is a need to migrate the database or archive it, we can also export the database from Azure SQL Database. Simply click on the Export button in the SQL Databases section of Azure Management Portal and then choose an Azure blob storage account to export the database to.

Finally, just provide the server login name and password to the database and you are good to go.

Export DB from Azure SQL Database.

Export DB from Azure SQL Database.

Later, we can also create a new database using the BACPAC file which is being generated by the Export function. In the Azure Management Portal, click New > Data Services > SQL Database > Import. This will open the Import Database dialog, as shown in the screenshot below.

Create a new database in Azure SQL Database by import BACPAC file.

Create a new database in Azure SQL Database by import BACPAC file.

Okai, that’s all for this post on Entity Framework, database, and Azure SQL Database. Thank you for your time and have a nice day!

Summer 2015 Self-Learning Project

This article is part of my Self-Learning in this summer. To read the other topics in this project, please click here to visit the project overview page.

Summer Self-Learning Banner

Protect Your ASP .NET Applications

ASP .NET MVC - Entity Framework - reCAPTCHA - OWASP - JSON

Here is a just a few items that I learnt on how to protect and secure my web applications in recent ASP .NET projects.

reCAPTCHA in Razor

CAPTCHA is an acronym for “Completely Automated Public Turing test to tell Computers and Humans Apart”.

CAPTCHA is a program to find out if the current user is whether a human or a robot by asking the user to do some challenge-response tests. This feature is important in some websites to prevent machine to, for example, auto login to the websites, to do online transactions, to register as members, and so on. Luckily, it’s now very easy to include CAPTCHA in our ASP .NET MVC web applications.

Register your website here to use reCAPTCHA: https://www.google.com/recaptcha/admin.

Register your website here to use reCAPTCHA: https://www.google.com/recaptcha/admin.

reCAPTCHA is a free Google CAPTCHA service that comes in the form of widget that can be added to websites easily. So, how do we implement reCAPTCHA in our ASP .NET MVC sites?

The library that I use is called ReCaptcha for MVC5, which can be downloaded from Codeplex. With the help of it, I am able to easily plugin reCAPTCHA in my MVC5 web applications.

After adding ReCaptcha.Mvc5.dll in my project, I will need to import its namespace to the Razor view of the page which needs to have reCAPTCHA widget.

@using ReCaptcha.Mvc5;

To render the reCAPTCHA widget in, for example, a form, we will do the following.

< div class="form-group">
    @Html.LabelFor(model => model.recaptcha_response_field, new { @class = "control-label col-md-2" })
    < div class="col-md-10">
        <!--Render the recaptcha-->
        @Html.reCAPTCHA("<public key here>")
    < /div>
 < /div>

The public key can be retrieved from the official reCAPTCHA page after you register your website there.

reCAPTCHA Widget on Website

reCAPTCHA Widget on Website

In the code above, there is a field called recaptcha_response_field, which will be added in our model class as demonstrated below.

public class RegistrationViewModel : ReCaptchaViewModel
{
    ...

    [Display(Name = "Word Verification")]
    public override string recaptcha_response_field { get; set; }
}

To do verification in the controller, we will have the following code to help us.

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Register(RegistrationViewModel registrationVM)
{
    ...

    if (!string.IsNullOrEmpty(checkoutVM.recaptcha_response_field))
    {
        // Verify the recaptcha response.
        ReCaptchaResponse response = 
            await this.verifyReCAPTCHA(registrationVM, "<private key here>", true);

        if (response.Success)
        {
            // Yay, the user is human!
        } 
        else 
        {
            ModelState.AddModelError("", "Please enter correct verification word.");
        }
    }
}

The private key can also be found in the official reCAPTCHA page after you have submitted your website.

After doing all these, you are now be able to have a working reCAPTCHA widget in your website.

XSRF: Cross-Site Request Forgery

In the controller code above, there is one attribute called ValidateAntiForgeryToken. The purpose of this attribute is to prevent XSRF by adding anti-forgery tokens in both a hidden form field and the cookies to the server.

I draw a graph for me to better explain about what XSRF is.

XSRF (Cross-Site Request Forgery)

XSRF (Cross-Site Request Forgery)

Steps are as follows.

  1. The user logs in to, for example, a bank website.
  2. The response header from the bank site will contain the user’s authentication cookie. Since authentication cookie is a session cookie, it will only be cleared when the process ends. Thus, until that time, the browser will always include the cookie with each request to the same bank website.
  3. The attacker sends to the user a link and somehow encourage the user to click on it. This causes sending a request to the attacker’s server.
  4. The attacker website has the following form.
    <body onload="document.getElementById('attack-form').submit()">
        <form id="fm1" action="https://bank.com/TransferMoney" method="post">
            <input name="transferTo" value="attackerAccount" />
            <input name="currency" value="USD" />
            <input name="money" value="7,000,000,000" />
        </form>
    </body>
  5. Because of Step 4, the user will be forced to send a request to the bank website to transfer money to attacker’s account with the user’s authentication cookie.

Hence, the attribute ValidateAntiForgeryToken helps to avoid XSRF by checking both the cookie and form have anti-forgery tokens and their values match.

Mass-Assignment Vulnerability and Over-Posting Attack

Few years ago, Github was found to have Mass-Assignment Vulnerability. The vulnerability allows people to perform Over-Posting Attack to the site so that the attackers can modify data items which are not normally allowed to access. Due to the fact that ASP .NET MVC web application is using Model Binding, the same vulnerability can happen in ASP .NET MVC environment as well.

You want to control what is being passed into the binder.

You want to control what is being passed into the binder.

There are two my personal favourite solutions to avoid Over-Posting Attack.

One is using Bind attribute in the controller method. For example, in order to prevent users editing the value of isAdmin when they update their profile, I can do something as follows.

[HttpPost]
public ViewResult Edit([Bind(Exclude = "IsAdmin")] User user)
{
    ...
}

Alternatively, we can also use “Include” to define those fields that should be included in the binding.

Second solution is using view model. For example, the following class will not contain properties such as IsAdmin which are not allowed to be edited in the form post of profile edit.

public class UserProfileUpdateViewModel
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    ...
}

XSS: Cross-Site Scripting

According to OWASP (Open Web Application Security Project), XSS attacks

…are a type of injection, in which malicious scripts are injected into otherwise benign and trusted web sites… Flaws that allow these attacks are quite widespread and occur anywhere a web application uses input from a user within the output it generates without validating or encoding it.

Kirill Saltanov from NUS is explaining to guests about XSS during 5th STePS event.

Kirill Saltanov from NUS is explaining to guests about XSS during 5th STePS event.

Currently, by default ASP .NET will throw exception if potentially dangerous content is detected in the request. In addition, the Razor view engine protect us against most of the XSS attacks by encoding data which is displayed to web page via the @ tag.

In View, we also need to encode any user-generated data that we are putting into our JavaScript code. Starting from ASP .NET 4.0, we can call HttpUtility.JavaScriptStringEncode. HttpUtility.JavaScriptStringEncode helps to encode a string so that it is safe to display and characters are escaped in a way that JavaScript can understand.

In order to avoid our database to have malicious markup and script, we need to encode the user inputs in the Controller as well using Server.HtmlEncode.

[AllowHtml]

There are some cases where our web application should accept HTML tags. For example, we have a <textarea> element in our blogging system where user can write the content of post, then we need to skip the default checking of ASP .NET.

To post HTML back to our Model, we can simply add the [AllowHtml] attribute to the corresponding property in the Model, for example

public class BlogPost {
    [Key]
    public int ID { get; set; }
    ...
    [AllowHtml]
    public string Content { get; set; }
}

Then in the View, we will need to use @Html.Raw to tell Razor not to encode the HTML markup.

@Html.Raw(post.Content)

Wait… Won’t this make XSS attack possible in our website? Yup, of course. So, we must be very careful whenever we are trying to bypass the Razor encoding. The solution will then be using AntiXSS encoding library from Microsoft.

AntiXSS uses a safe list approach to encoding. With its help, we will then able to remove any malicious script from the user input in the Controller, as demonstrated below.

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult CreatePost(BlogPost post)
{
    if (ModelState.IsValid)
    {
        ...
        post.Content = Sanitizer.GetSafeHtmlFragment(post.Content);
        ...
        db.BlogPosts.Add(post);
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(post);
}

ASP .NET Request Validation

Previously in the discussion of XSS, we know that by default ASP .NET throws exception if potentially dangerous content is detected in the request. This is because of the existence of ASP .NET Request Validation.

However, according to OWASP, Request Validation should not be used as our only method of XSS protection because it does not guarantee to catch every type of invalid input.

HttpOnly Cookies

In order to reduce the risk of XSS, popular modern browsers have added a new attribute to cookie called HttpOnly Cookie. This new attribute specifies that a cookie is not accessible through script. Hence, it prevents the sensitive data contained in the cookie can be sent to attacker’s side via malicious JavaScript in XSS attack.

When a cookie is labelled as HttpOnly, it tells the browser that the cookie should only be accessed by the server. It is very easy to check which cookies are HttpOnly in the developer tool of modern browsers.

Microsoft Edge F12 Developer Tools can tell which are the HttpOnly cookies.

Microsoft Edge F12 Developer Tools can tell which are the HttpOnly cookies.

So, how do we create HttpOnly cookies in ASP .NET web applications? Just add a new line to set HttpOnly attribute of the cookie to true is fine.

HttpCookie myCookie = new HttpCookie("MyHttpOnlyCookie");
myCookie["Message"] = "Hello, world!";
myCookie.Expires = DateTime.Now.AddDays(30);
myCookie.HttpOnly = true;
Response.Cookies.Add(myCookie);

Alternatively, HttpOnly attribute can be set in web.config.

<httpCookies httpOnlyCookies="true" ...>

However, as pointed out in OWASP, if a browser is too old to support HttpOnly cookie, the attribute will be ignored by the browser and thus the cookies will be vulnerable to XSS attack. Also according to MSDN, HttpOnly does not prevent attacker with access to the network channel from accessing the cookie directly, so it recommends the use of SSL in addition of HttpOnly attribute.

HttpOnly Cookie was introduced in 2002 in IE6. Firefox 2.0.0.5 only supported HttpOnly attribute in 2007, 5 years later. However, soon people realized that in Firefox, there was still a bug in the HttpOnly implementation. Firefox allowed attackers to do an XMLHttpRequest to get the cookie values from the HTTP Response headers. 2 years later, in 2009, Mozilla finally fixed the bug. Since then, the XMLHttpRequest can no longer access the Set-Cookie and Set-Cookie2 headers of any response no matter the HttpOnly attribute is set to true or not.

Browserscope provides a good overview about the security functionalities in major browsers.

Browserscope provides a good overview about the security functionalities in major browsers.

SQL Injection and Entity SQL

When I first learned SQL in university, I always thought escaping user inputs helped to prevent SQL Injection. This approach doesn’t work actually. I just read an article written by Steve Friedl regarding how escaping the input strings does not protect our applications from being attacked by SQL Injection. The following is the example Steve gave.

SELECT fieldlist
FROM table
WHERE id = 23 OR 1=1;  -- Boom! Always matches!

When I was working in the Summer Fellowship Programme, I started to use Parameterized SQL.

SqlConnection conn = new SqlConnection(connectionString); 
conn.Open(); 
string sql = "SELECT fieldlist FROM table WHERE id = @id";
SqlCommand cmd = new SqlCommand(sql); 
cmd.Parameters.Add("@id", SqlDbType.Int, id); 
SqlDataReader reader = cmd.ExecuteReader();

This approach provides a huge security performance benefits.

In January, I started to learn Entity Framework. In Entity Framework, there are three types of queries:

  • Native SQL
  • Entity SQL
  • LINQ to Entity

In the first two types, there is a risk of allowing SQL Injection if the developers are not careful enough. Hence, it’s recommended to use parameterized queries. In addition, we can also use Query Builder Methods to safely construct Entity SQL, for example

ObjectQuery<Flight> query =
    context.Flights
    .Where("it.FlightCode = @code",
    new ObjectParameter("code", flightCode));

However, if we choose to use LINQ to Entity, which does not compose queries by using string manipulation and concatenation, we will not have the problem of being attacked by traditional SQL Injection.

JsonResult and JSON Hijacking

Using the MVC JsonResult, we are able to make our controller in ASP .NET MVC application to return Json. However, by default, ASP .NET MVC does not allow us to response to an HTTP GET request with a JSON payload (Book: Professional ASP .NET MVC 5). Hence, if we test the controller by just typing the URL directly in the browser, we will receive the following error message.

This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet.

Since the method only accepts POST requests, unless Cross-Origin Resource Sharing (CORS) is implemented, the browser will be able to protect our data from returning the Json result to other domains.

This is actually a feature introduced by ASP .NET MVC team in order to mitigate a security threat known as JSON Hijacking. JSON Hijacking is an attack similar to XSRF where attacker can access cross-domain JSON data which is returned as array literals.

The reason why “returning JSON data as array” is dangerous is that although browsers nowadays stop us from making cross domain HTTP request via JavaScript, we are still able to use a <script> tag to make the browser load a script from another domain.

<script src="https://www.bank.com/Home/AccountBalance/12"></script>

Due to the fact that a JSON array will be treated as a valid JavaScript script and can thus be executed. So, we need to wrap the JSON result in an object, just like what ASP .NET and WCF do. The ASP.NET AJAX library, for example, automatically wraps JSON data with { d: [] } construct to make the returned value to become an invalid JavaScript statement which cannot be executed:

{"d" : ["balance", "$7,000,000,000.00"] }

So, to avoid JSON Hijacking, we need to

  1. never return JSON array
  2. not allow HTTP GET request to get the sensitive data

Nowadays, even though JSON Hijacking is no longer a known problem in modern browsers, it is still a concern because “you shouldn’t stop plugging a security hole just because it isn’t likely to be exploited“.

By the way, GMail was successfully exploited via JSON Hijacking. =)

Summer 2015 Self-Learning Project

This article is part of my Self-Learning in this summer. To read the other topics in this project, please click here to visit the project overview page.

Summer Self-Learning Banner

Summer 2015 Self-Learning

Summer Self-Learning
It has been about half a year since I started to learn ASP .NET MVC and Entity Framework (EF). In this period of time, I have learnt about not just MVC and EF, but also Azure PaaS, Google Maps API, web application security, cool jQuery plugins, Visual Studio Online, etc.

In the beginning of May, I started to note down useful things I’d learned in my learning journey. Months of bringing together information in this summer has helped me compile my notes about what I’ve learned in the past 6 months. I have currently completed compiling notes for 17 topics that I’ve learnt in this summer.

I listed down the title of the 17 posts below to give you a quick overview about all the 17 topics.

Contents

ASP .NET MVC and Entity Framework

Security

Microsoft Azure

Google APIs

Web Development Tools

Learning After Work

I’m working in Changi Airport. The office working hour is from 8:30am to 6pm. In addition, I am staying quite far from the airport which will take about one hour for me to travel from home to office. Hence, the only time that I can have sufficient time to work on personal projects is weekends.

This summer self-learning project is originally planned to be done by the end of May. Normally, it takes me about one day to finish writing a post. After that, if I find any new materials about the topics, I will then modify the post again. Sometimes, however, I am just too tired and I would not write anything even though it’s weekend. Hence, I end up finishing all the 17 topics three months later.

This summer learning project covers not only what I’ve learnt in my personal projects, but also new skills that I learn in my workplace. I always enjoy having a chat with my colleagues about the new .NET technology, app development, Azure hosting, and other interesting development tools. So yup, these 17 articles combine all the new knowledge I acquire.

I’m also very happy that that I am able to meet developers from both .NET Developers Community Singapore and Azure Community Singapore and share with them what I’ve learnt. That gives me a great opportunity to learn from those experienced .NET developers. =)

Azure Community March Meetup in Microsoft Singapore office.

Azure Community March Meetup in Microsoft Singapore office.

I am not that hardworking to work on personal projects every day. Sometimes, I will visit family and friends. Sometimes, I will travel with friends to overseas. Sometimes, I will play computer games or simply just sleep at home. So ya, this self-learning project takes a longer time to complete. =D

Working on personal projects after work is stressful also. Yup, so here is a music that helps reducing my stress. =)

Journey to ASP .NET MVC 5

When I first worked as web developer after graduation, I used to think what I knew about web development was already enough. However, as I learned more from friends and colleagues, I realized how difficult the field is, even though in Easibook.com we were just dealing with ASP .NET for web development.

New Ideas

Singapore .NET Developers Community meetup

Singapore .NET Developers Community meetup (Photo Credit: .NET Developers Singapore)

I participated in the Singapore .NET Developers Community meetup with my colleagues on 28 January. The theme is about web development. We had the chance to learn about ASP .NET MVC 5, Dependency Injection and how ASP .NET MVC 5 works with Angular JS.

What interested me is the ASP .NET MVC 5 talk given by Nguyen Quy Hy. In work, I was always using ASP .NET Web Forms. When I first started the ASP .NET MVC project in Visual Studio, I was already shocked by new terminologies like Razor, Identity, Scaffold, and all sort of folders, such as Models, Views, Controllers, App_Start, etc. Those are basically not found in my existing Web Forms project.

Working in a startup, there is always more to do and even more to learn, no matter the size of business. In many ways, my job changes frequently. I have to always take time to learn and challenge myself to play with new technology. Hence, learning ASP .NET MVC becomes my new challenge in this year.

I thus decided to write this post to share about what I’ve learned in my ASP .NET MVC 4/5 projects in February.

Bootstrap

Let’s start with simple stuff first. The GUI.

It’s nowadays quite common that people want a website which is responsive and mobile friendly. Luckily, there are frameworks to help. A even better news is that Visual Studio web application template by default is using Bootstrap, a framework providing design and theming features.

Previously we were using VS 2008. There was no such thing as bootstrap in our Web Forms application. Hence, I only started playing with Bootstrap when I did my first ASP .NET MVC 4 project in VS 2012.

ASP .NET web server controls can no longer be seen in ASP .NET MVC project. I was once asked about how GridView and paging were going to be handled in ASP .NET MVC without the use of the web server controls. I found some online discussions and articles which gave good answer to the question.

  1. Grid Controls for ASP .NET MVC
  2. Bootwatch: Free themes for Bootstrap including table and paging themes
  3. Paging, Searching, and Sorting in ASP .NET MVC 5
  4. ASP .NET MVC Paging Done Perfectly with @Html.PagedListPager()

12-Column Grid System is another thing I learnt when playing with Bootstrap. The grid system allows us to easily create complex grid layouts for different devices.

Grid System of Bootstrap 3

Grid System of Bootstrap 3

With the help of Bootstrap, even before I do anything, my web application is already responsive and mobile friendly. It’s true that technology is just a tool but with the right tools, we are able to work more efficiently and productively. =)

Native Support of Clean URL: Good News for SEO

My colleague, who was doing SEO, always received requests to do URL Rewrite in our existing Web Forms applications. Whenever there is a new page created, he has to add a new rule to web.config, sometimes just to get rid of the .aspx thingy.

<urlrewritingnet rewriteOnlyVirtualUrls="true" contextItemsPrefix="QueryString" defaultPage="default.aspx" xmlns="http://www.urlrewriting.net/schemas/config/2006/07">
    <rewrites>
        <add name="RedirectInDomain" virtualUrl="^http\://(.*)/SomethingFriendly"
            rewriteUrlParameter="IncludeQueryStringForRewrite" 
            destinationUrl="~/test.aspx" ignoreCase="true" 
            redirectMode="Permanent" rewrite="Domain" />
        ...
 
     </rewrites>
 </urlrewritingnet>

If there are one thousand pages, then there will be same amount of rules. So in the end, we even need to create separate config file just to keep the rules for URL rewrite.

In ASP .NET MVC 5, with the help of ASP .NET Routing, URLs no need to be mapped to specific web pages. Hence, in MVC web application, we can always see clean URLs which is friendly to not only the web crawler but also sometimes to the users. This is one of the features that I love in ASP .NET MVC.

Identity and Social Network Login

Whenever I visit an online store, I always find it more customer-friendly to accept Facebook or Google login.

Fortunately, ASP .NET Identity is powerful enough to not just accept application-wise user name and password, but also allows the connections from social websites like Facebook, Twitter, and Google+.

I only need to create a Facebook app and then key in the https URL of my website. After that, I put both the application ID and secret key to Startup.Auth.cs. Tada, users can now login to my website with their Facebook credentials.

app.UseFacebookAuthentication(
    appId: "<Facebook app ID here>",
    appSecret: "<Facebook app secret here>");
Localhost HTTPS URL is also accepted! =)

Localhost HTTPS URL is also accepted! =)

Just in case if you also encounter exception saying “Object reference not set to an instance of an object” on the line with AuthenticationManager.GetExternalLoginInfoAsync(), as shown in the following screenshot, please update Microsoft.Owin.Security.Facebook Nuget package.

Facebook Login Exception. Boom!

Facebook Login Exception. Boom!

Update nuget.org - Microsoft.Owin.Security.Facebook

Update nuget.org – Microsoft.Owin.Security.Facebook

Entity Framework Code First

Due to the fact that my project is a new one. So, I used Code First to help me create tables in a new database according to my Model definition.

There is also a video on MSDN Data Developer Center website where they give an introduction to Code First development.

I like how easy it is to have all my tables created auto-magically by just defining model using classes. Then after that, I can create new views and controller by adding Scaffold.

Easily create MVC controller and views with Scafolding

Easily create MVC controller and views with Scafolding

Headache with Migrations

In order to have database scheme updated when the model is changed, I have enabled migration by running the Enable-Migrations command.

Ran Enable-Migrations command in the Package Manager Console

Ran Enable-Migrations command in the Package Manager Console

After that, whenever I changed my model classes, I will run Update-Database to have database schema updated as well. However, soon I encountered a problem.

When I was working on an ASP .NET MVC 4 project with VS2012, the Id in the Users table is integer. So, in VS2013, I assumed it to be the same when I created the model classes and updated the database. Unfortunately, nope. The default web application of VS2013 uses GUID for user ID. There is an online tutorial on how to change the primary key of Users back to integer, if you are interested.

Due to the fact that my project is a totally new project, so what I am going to do is just to change my model classes to use GUID as the type of storing user ID in other tables. However, when I ran the Update-Database command, the console prompted me an error message, saying “Operand type clash: int is incompatible with uniqueidentifier”. To quickly get rid of this problem, I deleted my tables (Don’t do this at home. =P) from the database. Then when I ran Update-Database command again, it complaint the table was missing. Finally, I had no choice but deleting the relevant records in __MigrationHistory table before making Update-Database to work again. =P

Yay, successfully updated database schema after deleting migration history.

Yay, successfully updated database schema after deleting migration history.

Yay with Entity Framework

Before using Entity Framework, I played with stored procedure for few years. My colleagues have always been complaining that sometimes the logic was being hidden in stored procedures and thus made the debugging difficult. Also, having logic in stored procedures means that our business logic is actually split up into both C# and SQL. So, sometimes the developers need to spend a few hours debugging the C# code before realizing the store procedure was actually the culprit.

With Entity Framework, I am now able to modify the table structure and logic all in C# code which helps developers to easily find out where goes wrong.

Still, sometimes it is good to group related functions into one well-defined stored procedure so that the system only needs to call to the database once to get all the work done. However, after reading a 400-line store procedure once, I decided that doing this may not be the best option because no one in my team was interested to debug SQL code.

Review a long stored procedure?

Review a long stored procedure?

There are more related topics online regarding Entity Framework vs. Stored Procedures, as listed below. If you are interested, feel free to check them out.

  1. Entity Framework Vs Stored Procedures – Performance Measure
  2. Stored Procedure or Entities?

Using MySQL Instead of Default SQL Server: I Was Having a Hard Time

By default, the data provider of ASP .NET Identity with Entity Framework is set to be MS SQL in VS 2013. However, MS SQL Server is not free. So, I decided to use MySQL instead. Hence, I need to find ways to configure Entity Framework on my project to work with MySQL.

The first tutorial that I started with is a detailed step-by-step guide on ASP .NET website regarding how to use use MySQL Storage with an Entity Framework MySQL Provider. It mainly involves steps on changing the web.config. Some important steps are listed below.

Change database connection string.

<add name="DefaultConnection" connectionString="Server=localhost;Uid=root;Pwd=password;Database=mediablog;" providerName="MySql.Data.MySqlClient" />

Configure Entity Framework to use MySQL.

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </providers>
</entityFramework>
<system.data>
    <DbProviderFactories>
        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.8.3.0" />
    </DbProviderFactories>
</system.data>

However, if I am not wrong, part of these can be done easily by just including the related MySQL nuget packages. I chose four of them to be installed in my project: MySQL.Data, MySQL.Data.Entity, MySQL,Data.Entities, and MySQL.Web.

Install related NuGet packages to make Entity Framework Code First works with MySQL.

Install related NuGet packages to make Entity Framework Code First works with MySQL.

After changing web.config, I followed the tutorial to introduce two new classes in the project. One is MySqlHistoryContext.cs which will sync the model changes with the database schema using MySQL standard and not MS SQL.

According to an online post, I added extra one line to the OnModelCreating method MySQLHistoryContext.cs. It’s to fix the exception of the famous Error 0040: The Type nvarchar(max) is not qualified with a namespace or alias. Only primitive types can be used without qualification.

modelBuilder.Properties<String>().Configure(c => c.HasColumnType("longtext"));

However, the Error 0040 didn’t disappear because of this line. I will share later the other steps I took to fix this problem.

The famous Error 0040 encountered when doing migrations for MySQL.

The famous Error 0040 encountered when doing migrations for MySQL.

Another new class is called MySqlConfiguration which is used to make sure the Entity Framework will use MySqlHistoryContext, instead of the default one.

Besides, I also made changes to Configuration.cs. Remember the Error 0040? A discussion thread on Github actually suggested to add the following line to fix it.

SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());

This didn’t fix the Error 0040 on my project too.

In the end, I found a Chinese post which said the following.

此时只需要将Data层的Migrations的文件夹删掉即可。因为SqlServer做过一些迁移,有些数据类型与MySql不兼容。

The sentence basically says that due to the fact that the migration earlier done in SQL Server and thus some data types are not compatible with MySQL, we need to delete the Migrations folder. So, after I excluded the 201502231459263_InitialCreate.cs file (which was created when I am still using MS SQL for my project) in Migrations folder from the project, the Error 0040 was gone when I did Update-Database. Yay!

So yup, sometimes it’s very, very useful to know more than one language. And yup, I spent half of my holiday to figure out how to make Entity Framework to work with MySQL. =)

Oh well, half day gone just to make MySQL work in my little project.

Oh well, half day gone just to make MySQL work in my little project.

By the way, the Chinese web page mentioned above was already not available. What I shared with you is actually a link to its Google cached copy. I am not sure if the cache is still around when you visit it.

Self Learning ASP .NET MVC on MVA during Chinese New Year

The talks given during the community meetup are good. However, in order to learn more, I also need to get advice from my colleagues who have more experience with ASP .NET MVC.

In addition, during Chinese New Year period, instead of watching the new year shows, I stayed in front of my computer to complete the introductory series of ASP .NET MVC delivered by two Microsoft experts, Christopher Harrison and Jon Galloway. It’s definitely a good starting point for beginners. And yup, the two speakers are very good at explaining the key concepts and they also tell good jokes so you shouldn’t find the course to be boring. =P

Yup, people from Malaysia are watching the live too!

Yup, people from Malaysia are watching the live too!

The End of the Beginning

I am now still a beginner in ASP .NET MVC. I always find that there are many new things to learn in just web development. Actually, it’s very challenging. For example, to get Entity Framework Code First to work with MySQL already takes me half day to figure it out.

Anyway, this is just a post sharing how I get started on ASP .NET MVC. In the future, I will do my best to share with you all more about what I learn in this cool technology. =)

500TB Storage for Database Backup

When your database is big, sometimes just its backup will be around 20GB in size already. Hence, keeping them on disk is always not a solution even though Microsoft Azure provides a data disk with 1TB.

Fortunately, Microsoft Azure offers a scalable and larger storage. It is called Microsoft Azure Storage, a storage with 500TB capacity limit. The good thing about it is we only need to pay for the amount we are using in the storage.

Hence, Chun Siong from Microsoft Singapore suggested my company to try out this service to store our database backups. It turns out that it can be easily done in just 3 steps.

Step 1: Create Azure Storage Account and Retrieve Access Keys

To create a new Azure Storage Account, I simply login to the Azure Management Portal and then choose the Quick Create option of the Storage under Data Services section. I am able to specify the affinity group and replication rule for the Storage Account.

Creating a Storage Account.

Creating a Storage Account.

After the Storage is created, I can retrieve access keys which will be used later in SQL Server to access the Storage Account.

Retrieve the access keys to the Storage Account.

Retrieve the access keys to the Storage Account.

Finally, I just need to create a Container in the Storage Account. All the database backup files will be put inside the Container later.

Created a container in the Storage Account.

Created a container in the Storage Account.

Step 2: Create SQL Server Credentials

I then execute the following T-SQL statement to create credentials so that SQL Server later can connect to the Storage Account.

CREATE CREDENTIAL mycredential 
WITH IDENTITY= 'chunlindbbackup', 
SECRET = '<storage account access key>'

The Storage Account access key here can be either Primary or Secondary access key retrieved in Step 1 above.

Step 3: Backup Database

I create a scheduled job in SQL Server Agent to do backup of my database daily. The URL is the URL of the container created in Step 1.

BACKUP DATABASE mydatabase 
TO URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_' + REPLACE(CONVERT(VARCHAR ,GETDATE(),126) ,':','_')+ '.bak'
WITH CREDENTIAL = 'mycredential', INIT, NAME = 'Backup of Database mydatabase'

So yup, now the database backups will be stored on the Storage Account directly.

Restore Database Backup from Azure Storage

To restore a database backup from Storage Account, if the backup file is small, I can just simply execute the following T-SQL statements. The URL of the database backup can be found in the Container in Azure Management Portal.

RESTORE DATABASE mydatabase_test 
FROM URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_2014-10-14T13_16_01.243.bak' 
WITH RECOVERY,
MOVE 'mydatabase_db_Data' TO 'F:\db\mydatabase_test_Data.mdf',
MOVE 'mydatabase_db_Log' TO 'F:\db\mydatabase_test_Log.ldf',
CREDENTIAL = 'mycredential'
GO

Unfortunately, the backup that I have is too big. So, I can only download it from Azure Management Portal to the database server first before restoring the database. The download is quite fast.

Download backup file from Storage Account

Download backup file from Storage Account

In case, you wonder why I do not use tool like Azure Storage Explorer, no, it did not work. It would crash also if the backup file was too big.

Pricing

Oh ya, just in case you would like to know the pricing of Azure Storage, you can check it out here: http://azure.microsoft.com/en-us/pricing/details/storage/.

Pricing of Azure Storage in Southeast Asia.

Pricing of Azure Storage in Southeast Asia.

Database Mirroring in Azure

Not many people that I know like to try things that they are not familiar with because unfamiliar is scary. However, working in startup, like my current company, basically forces one to always learn more and learn faster. Hence, after getting approval from the top management, my senior and I migrated our web applications to Microsoft Azure.

Just when we thought we did everything beautifully, our instances on Azure went down for 72 minutes on 4 August, one month after the migration. The reason given by Microsoft team is that there was an issue in one of the clusters within the DC. 3 weeks later, our database instance on Azure went down again for 22 minutes because of a scheduled system update.

Fortunately, Microsoft Singapore is willing to guide us to make high availability in our web applications possible. I am very happy to have Chun Siong, Technical Evangelist from Microsoft Singapore, to help us out.

Last month, Chun Siong successfully to have the database mirroring setup for our database instances on Azure. Since he did all of the work himself, in order to learn and to master the database mirroring, I had to do everything myself from the beginning again.

In this post, I will share the mistakes I made when I tried doing database mirroring myself so that I won’t repeat the same mistakes again.

Beginning of the Journey

There is an easy-to-follow tutorial available on MSDN about how to implement database mirroring in Azure. I used it as a reference to setup one principle database server, one mirror database server, and one witness server within the same availability set.

Elements in my simple database mirroring setup.

Elements in my simple database mirroring setup.

Mistake #1: Firewall Blocking Remote Access of SQL Server

If I had read the tutorial carefully, I wouldn’t have to make this mistake because it’s mentioned in the beginning of the tutorial.

I found out this mistake only when I tried to connect to the mirror server from the principal database server. It kept throwing me the Error 1418 saying that the mirror server was not reachable. After reading a checklist of the error, I found out that it’s because I never create an inbound rule on Windows Firewall to allow the access of the SQL server.

Thanks Chun Siong for pointing it out also. =)

By the way, on the article about Error 1418 (http://msdn.microsoft.com/en-us/library/aa337361.aspx), there is a checklist to check if everything is done correctly. I copied and pasted it below for quick reference.

  1. Make sure that the mirror database is ready for mirroring.
  2. Make sure that the name and port of the mirror server instance are correct.
  3. Make sure that the destination mirror server instance is not behind a firewall.
  4. Make sure that the principal server instance is not behind a firewall.
  5. Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.
  6. Make sure that the principal server instance is listening on the port assigned to its database mirroring endpoint and that and the mirror server instance is listening on its port. If a partner is not listening on its assigned port, modify the database mirroring endpoint to listen on a different port.

If the items above are not helpful to you, there is also another detailed blog post about this Error 1418 written by Pinal Dave.

There is a need to allow the access of SQL server in three instances.

There is a need to allow the access of SQL server in three instances.

Mistake #2: Typo when Creating Certificates

In the tutorial, the recommended way to deploy database mirroring is to use certificates. After certificates of three servers are created, we need to grant login permission on each server to another two servers. That is when we will use the certificates to create a common login account id called DBMirroringLogin.

I had one typo in the password in one of the certificates.  only realized it at the very end when I tried to connect to my witness server. So, yup. Be careful during the database mirroring configuration steps. One small mistake can waste us time to find out why.

Grant login permissions to other two servers.

Grant login permissions to other two servers.

Mistake #3: Mismatch Edition of Principal and Mirror

I only had time to learn database mirroring using my personal account after work. So I screamed in my room at the moment when I realized that it is not allowed to have mirror server using Standard Edition while the principal is not using Standard Edition.

The mirror server instance cannot be Standard Edition if the principal server instance is not Standard Edition.

The mirror server instance cannot be Standard Edition if the principal server instance is not Standard Edition.

So in the end, I shut down the mirror instance and created another virtual machine which has Enterprise Edition SQL Server installed. Fortunately, it could be done quite fast on Microsoft Azure. I did not want to use back the old name so I named the new mirror server mydb-01-kagami.

Kagami means "mirror" in Japanese. (Image Credit: Lucky Star)

Kagami means “mirror” in Japanese. (Image Credit: Lucky Star)

Mistake #4: Three Virtual Machines Not in Same Availability Set

The principal database, witness, and mirror database instances need to be put inside the same availability set.

When I was deploying the database mirroring, I forgot to have the witness instance in the same availability set as principal and mirror. So end up I couldn’t successfully connect to the witness from the principal.

Three instances need to be in the same available set.

Three instances need to be in the same available set.

Work and Learn

I spent about three days in Microsoft office to learn from Chun Siong. I then took another one month to do it myself. Wait, what? One month, seriously? Don’t be surprised. As usual, I have only little time (about half an hour per day) after work to do my personal projects. Sometimes, once I reached my room from office, I just jumped into bed and fell asleep within minutes. So, in fact, I only spent about 15 to 20 hours on learning database mirroring myself. Hence, I am really glad that I have colleagues as well as friends from Microsoft to be willing to support me in my learning journey.

Finally, some little notes to myself and readers who want to try out database mirroring (on Azure).

  1. Be very careful during the whole database mirroring configuration process. Don’t have typo or set something wrongly. You may need to delete and create a new instance because of the mistakes;
  2. Witness (but not principal and mirror) can use Express Edition of SQL Server. So, to save cost, please use that;
  3. Set database to full recovery model before backing up the database on principal;
  4. Remember to enable named pipes;
  5. Use Database Mirroring Monitor to understand more about the status of mirroring session.
  6. Some good resources to refer to:
It's enjoyable to work in Microsoft Singapore office. You can see the beautiful MBS from there.

It’s enjoyable to work in Microsoft Singapore office. You can see the beautiful MBS from there.