Microsoft SQL Server 2012 – AlwaysOn

Microsoft SQL Server 2012 – Always On

As technology advances, so does the need (and want) for instantaneous information. The goal is always “How can we get information as quickly as possible?” The same is the standard for disaster recovery. How quickly can we recover in the event of failure? The answer in many cases is seconds. One of the newest options for High Availability is Microsoft SQL Server’s AlwaysOn technology. The name is self-explanatory–AlwaysOn is designed to have data and information always available, even in the event of disaster. Disaster Recovery time is minimized with SQL’s AlwaysOn ability to sync multiple databases across multiple database servers.

I had the recent experience of using SQL’s AlwaysOn technology to set up a redundant application environment. The purpose of this blog post is to summarize my experience with the technology. By no means is this a start-to-finish coverage of the technology.

Overall, the perception of AlwaysOn amongst users has been very positive (simply Google “SQL Always On” and you’ll see other posts stating its benefits). I, too, was impressed with its simple setup and configuration and how well it handles failover scenarios (verified during our pre-launch testing). With all technology, there were some downsides, but I felt that the positives of AlwaysOn far out-weigh its drawbacks.

Availability Groups

  • Replication in AlwaysOn is no longer thought of as individual databases to individual servers. Complex systems can be replicated as a group, not as individual databases, minimizing setup and configuration time for replication. The one downside is that the replicas are limited to four sets, so one group can only be replicated to 4 different locations.
  • Synchronous and Asynchronous Setup
    • Synchronous Replication – Transactions are not completed until committed to all databases in the Synchronous Availability Group
      • The first thought I had when hearing this process was that it may be performance impacting since SQL will need to commit the transaction to multiple databases on multiple servers.
      • We tested this process to ensure synchronous replication to multiple databases would not cause performances degradation.
      • We used SQL’s query performance statistics to ensure that the performance of data read/writes were as efficient with the new environment as they were in the old environment.
      • As a side note, there were other factors that affected query performance times (for the better – new environment, new OS), so our test wasn’t a true comparison of SQL performance with and without AlwaysOn replication. For the purpose of our test, we needed to ensure that the AlwaysOn replication was not a detriment to application performance.
    • Asynchronous – Transactions are queued and committed to this Availability Group at a later time. Transaction completion is not dependent on committing to this database group. This is similar to Database Log Shipping in older versions of SQL Server.
      • In previous versions of SQL Server, Log Shipping would require the database to be inaccessible during restoration of the transaction logs. With AlwaysOn, the database is always online and accessible.
      • This allowed us to use the Asynchronous process for other purposes, in this case Read-Only reporting. We took long-running reporting queries and calculations and removed that processing need from the transactional database and server altogether, providing an indirect performance benefit from AlwaysOn.

Automatic Synching

  • One of the most frustrating aspects of SQL Log Shipping is when the process gets out of sync and the transactions are not being properly applied to the replicated database. This would generally result in manually copying and restoring a database to the replication server and restarting the transfer process
  • This headache is alleviated with the Asynchronous Replication of AlwaysOn. AlwaysOn polices itself for the data replication, automatically re-synching the database if a network connectivity loss or other event disrupts the replication process

Licensing

  • Unfortunately, AlwaysOn is only available with the Enterprise Edition of the SQL 2012 License, so you’ll need justification to spend the extra money for the Enterprise license. This may not be the best DR solution for smaller applications.
  • However, licensing is setup to use Active vs. Passive server. This means that you don’t need an additional license for your replication servers if it is only being used in a failover scenario, which allows you to limit the number of licenses you will need.

Again, this was just an overview of my experience with Microsoft SQL Server’s Always On functionality. In the event that you are in the process of setting up a new environment using Microsoft SQL Server, I highly suggest considering AlwaysOn to handle the High Availability/Disaster Recovery requirements. See Microsoft’s overview of AlwaysOn here: http://msdn.microsoft.com/en-us/library/ff877884.aspx

Advertisements

NoSQL Databases

In a software developer’s world, the word “database” usually means “SQL.” SQL stands for Structured Query Language and, for years, this language has been the de facto language of relational databases. This includes Microsoft SQL Server, PostgreSQL, MySQL, and Oracle.

These database have served us well for literally decades, but there’s something new on the horizon. These days, all the cool kids are using NoSQL databases. NoSQL databases are document based, which means that they don’t have a schema or rely on the time-tested Structured Query Language. This is somewhat difficult for many veteran programmers to process. A big question that’s immediately raised is, “How would one communicate with a database that doesn’t use SQL?”  The answer can vary between the many different databases out there, but we’ll use one in particular as an example: Mongo DB.

Mongo DB uses the popular Object Oriented language JavaScript to interact between the software and the developers. This gives the developer the powerful advantage of using JSON to pass data between the Mongo DB collections and the application interacting with the database.

Developers that have a basic grasp of Object Oriented programming should be able to pick up NoSQL databases very quickly. For example, the SQL query to create a database which looks like this: “CREATE TABLE User (Ident INT IDENTITY, FirstName VARCHAR(50), LastName VARCHAR(50))” can be written simply as this: “db.CreateCollection(‘User’).” Since there’s no schema to worry about, any data points can be added at-will within the Mongo DB collection.  For more SQL to Mongo DB translations check out this mapping chart on the MongoDB site.

NoSQL databases are already in use in many popular applications that are familiar to almost everyone. Facebook uses a NoSQL database called Cassandra and FourSquare uses the aforementioned MongoDB. The list is growing with the popularity of these databases increasing on a daily basis. And NoSQL databases like MongoDB are available for every major system, so it’s possible to put these into production today.