Azure, and DataBase High Availability

Azure, making your business Agile!

To understand how Microsoft Azure(*) can help your DataBase operations, you have to understand some basics about databases.  Database are stores of information populated by transactions. The transactions alter the database structure and the database data. So, at any moment a database has pending transactions; and any copy of a database can have the same transactions sent to it via the ‘transaction log’. DataBase backups are copies of the database or the database log at any point in time.  As you might guess, the amount of data in an hourly transaction log is a lot smaller than an entire database; so, if you were going to REPLICATE (copy) a database you would want to replicate and transfer the transaction log. Several SQL Server technology methods were developed to help with DataBase Replication:

Replication

As stated above, SQL Server is transactional. Replication is the process of sending those transactions to a secondary database. The secondary database may NOT be an exact copy of the primary. This is a good reporting solution, not all the objects need be replicated.

Log Shipping

An SQL Transaction Log Backup is a file, created at set time intervals, which can be restored to a DataBase copy. These DataBase log backups can be sent to other copies of a database and restored. There is an obvious time lag, but the copied database is exactly like the original. Both the backup of the primary and the restore to the secondary require time and machine resources. (Slow, but a 100% copy; also, because the log copy process can include FTP, there is NO limit to where the log file might be copied.)

Clustering

This technology solves the SQL Server machine failure risk; multiple servers are clustered together to make sure if 1 server fails, the other server will respond. This technology assumes the database being unavailable is the critical risk to be avoided. (The other methods above may or may not also be used, clustering is a server level solution.)

DataBase Mirroring

Like replication, Mirroring is based on transaction replication with the added benefit of FAILURE rollover from the primary to the secondary. The rollover requires another SQL Server to act as a witness or monitor of the primary/secondary servers; the rollover also requires that the transactions be shared in a Synchronous method which slows the server performance. The limitations of Database Mirroring are: 1) You are limited to 1 secondary copy of the database and 2) The secondary copy isn’t readable or available for secondary use. 

AlwaysOn Technology (SQL 2012 and later)

In SQL Server 2012, Microsoft introduced AlwaysOn Technology which is DataBase Mirroring with some upgrades: 1) No monitoring server is needed, failover is automatic 2) Up to 4 secondary copies of the primary can exist across different servers 3) All the secondary copies are now readable and available for other uses. What this means with Azure is that a secondary copy could exist is the Cloud and be available to your other applications AND be available for failover of the primary database. Disaster recovery and High Availability combined. 

(*)Azure is Microsoft’s Cloud computing tool solution for Infrastructure (IaaS), Platform (PaaS), and Software Applications (SaaS).  Every information system is made up of 3 levels: Hardware and Network, Programing Platform and Database, and Software Application. Azure allows you to purchase the level of service you need to make your ETL project Agile. 

I hope you have found this information informative and inspiring. If I can help you with your next Azure, ETL, or ERP project, please contact me.

Randall Moore  https://MooreRC.azurewebsites.net/ 615.415.3573