Tag: Disaster Recovery

AlwaysOn for High-Availability in SQL Server

It’s very much the case that many organisations rely on a single SQL Server instance to host mission critical ERP, CRM, finance and stock control systems. In the event of a serious issue with your SQL Server instance your organisation will face a system down incident potentially on a mission critical production system.

In SQL Server there are a number of options for disaster recovery and high availability should the worst happen. AlwaysOn is a term used by Microsoft to refer to a number of options for deploying a highly available SQL Server solutions resilient to single points of failure.

You should check with application vendor(s) to confirm support of AlwaysOn technologies. This isn’t just a technological question but also competency from their implementation and support teams too. If you are in the process of or considering a fresh deployment of an application – for example if you are upgrading to the latest version alongside new infrastructure – you should definitely consider including AlwaysOn as part of the solution requirements.

Prior to the introduction of AlwaysOn DBAs would consider using Log Shipping and Database Mirroring however these have now been deprecated in favour of AlwaysOn. If you are using Log Shipping and/or Mirroring for a current deployment use AlwaysOn for any future deployments.

Broadly speaking there are two AlwaysOn methods by which high-availability can be implemented. Both technologies rely on Windows Server Failover Clustering feature to be configured. AlwaysOn options are:

AlwaysOn Availability Groups (AG) provide database level failover and secondary reads for availability databases that are members of an availability group.

If you are using SQL Server Standard Edition the AlwaysOn feature is known as Basic Availability Groups (BAG) and will be limited to two members of which the secondary is read-only.

AlwaysOn Failover Clustering Instances (FCI) as the name implies provides instance level redundancy via active or passive nodes and as such relies on using a shared storage medium such as a shared disk or SAN.

There are advantages and disadvantages to both AlwaysOn methods. Ongoing maintenance from a DBA is necessary to manage the deployment and monitor performance.

Conclusion

In this post we’ve discussed why your organisation should consider adding high-availability options into your Microsoft SQL Server based solution and briefly mentioned the two key methods of achieving such resilience.

If you organisation needs support for AlwaysOn technologies for SQL Server or disaster recovery following an incident Digital Incite and Matter Ltd are here to help you. Please get in touch with us today to discuss your requirements.

Why Managing Transaction Logs in SQL Server Is Important

This past week I had a situation arise where the SQL Server transaction logs for a particular site grew out of control. This ended up consuming an entire disk volume for the log files which eventually caused the SQL Server to stop processing and by extension caused application downtime. This can be a common cause of unplanned downtime for an application when the transaction log was not managed correctly. Just what is the transaction log in SQL Server for and what do you need to know about it?

What is the transaction log for?

In SQL Server each database consists of two types of files: the first is the data and the second is the log file. Each database may have one of each or multiple data and/or log files.

The database file contains the tables, views, functions, indexes, stored procedures, etc. Basically all the objects in the database. This file normally has the extension .mdf.

The log file on the other hand contains a record of transactions that have occurred in the database. The transaction log file is important to consider for many reasons including performance, data integrity, disaster recovery and high-availability. This file normally has the extension .ldf for primary log files or .ndf for secondary logs.

Note a common myth is that in SIMPLE recovery mode the transaction log is not switched off. All operations are recorded in the transaction log whether that be FULL or SIMPLE mode. The difference is in SIMPLE recovery mode once the transaction is committed into the database then the log is truncated.

Why is it important to manage the transaction log?

The most important answer is that the transaction log forms part of the disaster recovery plan of any SQL Server database. If a disaster occurs then backups of the transaction logs will be required to restore to the last known good point in time.

An immediate unwanted effect of uncontrolled transaction logging is that the log file will continuously grow if autogrowth is enabled. The risk is that this can eventually grow to consume the entire disk volume the transaction log occupies. If SQL Server cannot grow the log file of the database then the transaction it is trying to process will fail.

Why and when should I use SIMPLE or FULL transaction logging?

When deciding on the most appropriate transaction log the decision should be focused around the recovery requirements and acceptable loss window.

For example if you have a live ERP system you should consider FULL transaction logging so that you are able to restore the database using transaction log backups if a failure occurs after a full or differential backup of the database is done.

On the other hand if you had copied the same database into a temporary test system and you are not using the test environment with intention it should be a perfect replica of live then consider using the SIMPLE transaction logging method.

Generally use SIMPLE:

  • Test/training databases.
  • Data warehouses.
  • Settings databases (i.e: lists of usernames, application configuration where the data does not change frequently)

Generally use FULL:

  • Production databases for live OLTP applications.
  • UAT and Pre-Production systems where there is a requirement to mimick the live environment configuration exactly for proving purposes.

You should also consider switching a production system with databases in FULL recovery mode to SIMPLE on a temporary basis whilst you are performing an application upgrade or patch which updates the database. This is to avoid the transaction log growing out of control during the operation. After finishing the upgrade the recovery mode should be switched back to FULL.

Footnote: Switching between recovery modes.

  1. Stop all dependent applications for the databases.
  2. Take a full backup of the databases you are switching the transaction log for. Safety first.
--Set the database to single user mode so that further transactions are stopped.
ALTER DATABASE [ExampleDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Switch the recovery mode
ALTER DATABASE [ExampleDatabase] SET RECOVERY FULL | SIMPLE 

--Finally set the database back to multiple user mode.
ALTER DATABASE [ExampleDatabase]  SET MULTI_USER

You can then start any application services that depend on these databases.