The Structured Query Language (SQL) is a standard method of conveying information to and from relational databases. In many instances, these databases are centrally located, requiring SQL transactions to take place across a wide area network (WAN). In addition, enterprises often replicate and / or backup databases across the WAN to ensure maximum data protection in the event of a disaster. Jeff Aaron looks at how to improve database access and management using WAN acceleration.

When database tasks are performed across the WAN, they are subject to a variety of factors that can adversely affect performance, including limited bandwidth, high latency and packet loss. These challenges can be partially addressed by modifying application behaviour. However, this is often expensive and time-consuming when performed across many database systems, and can sometimes lead to slower server performance due to increased CPU requirements.

In addition, it does not address many WAN-specific limitations that can affect performance, and does not take into account the fact that SQL traffic typically shares the WAN with other business-critical applications. As a result, mechanisms need to be put in place to ensure appropriate service vis-à-vis all traffic types.

WAN acceleration uses various optimisation techniques to address the unique requirements of SQL over the WAN - from accelerating reads and writes to real-time replication and recovery. By delivering LAN-like performance with minimal latency and no application-specific tweaking, WAN acceleration is a strategic solution for improving the performance, management and protection of all database applications.

SQL transactions across the WAN

SQL is used to create, retrieve, update and delete data. This can involve a multitude of queries, coupled with the transfer of large volumes of data. This is not a problem when the database is in the same physical location as the users and administrators. However, when SQL access is performed across a WAN, performance can be excruciatingly slow.

SQL transactionsFigure 1. SQL transactions

There are numerous application-specific optimisations that can be performed to improve the performance of individual database applications. For example, query results can be cached or prefetched, some data can be compressed, packet sizes can be increased, SQL statements can be combined into batch requests and queries can be bounded to limit the amount of data sent.

While these adjustments can provide some benefits when communicating across the WAN, they do not address all of the challenges inherent to wide area networking, such as latency and packet loss due to router over subscription. In addition, given the many 'flavours' of SQL available today, it can be complicated and time-consuming to implement application-specific modification across an entire enterprise. Furthermore, a separate solution is required to ensure that SQL services receive the quality of service (QoS) they deserve with respect to other network traffic sharing the same WAN connection.

WAN acceleration is an important tool for ensuring fast and reliable SQL transactions between geographically dispersed locations. Performance improvements will vary greatly depending on the type of SQL transaction. Data manipulation and unique queries, for example, may result in a 5-20x improvement. Frequent retrievals or updates and transfers of large data sets will show greater improvements - 30x on average. The performance range will vary depending upon the size of the data set, the amount of duplicate data being transferred and WAN conditions that can affect performance.

Database backups across the WAN

Given the mission-critical nature of many databases, most enterprises will implement processes to protect against a mishap. Database backups are an easy way of accomplishing this goal as they typically only involve the transfer of .bak files between systems. This makes recovery easy as well, as these files can be restored using native SQL commands.

Database backups look and feel very similar to other large file transfers; in many instances, standard file copy utilities, such as FTP and Microsoft CIFS, are used to get the backup files from one location to the other. This means that WAN acceleration can have a significant impact on SQL backups.

The amount of improvement provided by WAN acceleration will depend upon the type of backup being performed. For example, when performing a complete backup there is a significant amount of repetitive data being sent across the WAN, which can be eliminated with the right optimisation tools. As a result, WAN acceleration will lead to significant performance improvements - up to 100x in some instances. While the same techniques can be applied to 'differential' and 'file' backups, these data sets contain less repetitive patterns. Therefore, it is more likely to see performance improvements in the 10-30x range (which is still quite substantial).

The greatest performance gains are experienced when backups are sent across the same WAN connection that is used for other enterprise traffic, such as email and web access. That is because a WAN acceleration device learns a network as it is used. For example, if a file is sent via email, it will be stored in a WAN acceleration device's local network memory. When that file is then sent across the WAN as part of a backup, it can be immediately treated as a 'warm pass', whereby the information is delivered locally from network memory instead of re-transmitted across the WAN.

In other words, even though it is the first time the data is being sent as part of a backup process, it is not the first time it is being sent across the WAN. Therefore it can be delivered locally using references, providing immediate benefits that otherwise would not be had without a WAN acceleration solution in the equation.

SQL replication

One of the best ways to protect against database failures is to replicate data to a remote standby server, preferably in a separate geographic region. This provides failover options that protect against hardware and software issues, as well as network connectivity failures and catastrophic disasters. The result is lower recovery times and better recovery point objectives.

SQL backup and replicationFigure 2. SQL backup and replication

From a WAN perspective, SQL replication is very similar to storage replication. As a result, WAN acceleration provides similar benefits. For example, by implementing data reduction, compression, latency mitigation and loss correction, WAN acceleration results in faster replication times, better WAN bandwidth utilisation and faster host server performance - all across wider geographic distances.

Some replication techniques, such as snapshot replication, involve the transfer of large amounts of repetitive data. As a result, it is common to see 50x improvements when using WAN acceleration in these environments. Transactional replication, on the other hand, eliminates some (but not all) data repetition. As a result, WAN acceleration typically provides a 10-30x average improvement in these environments.

The business case for WAN acceleration

WAN acceleration is an important tool for enterprise database management. By overcoming the ill effects of low bandwidth, high latency and packet loss, WAN acceleration improves the performance and reliability of most SQL operations. In addition, enterprises can experience the following business benefits:

Minimise the cost of exposure

According to Gartner Group, the average cost of network downtime for larger corporations is $42,000 per hour ($700 per minute). If a database recovery can be reduced from two hours to four minutes using WAN acceleration, than the investment can save approximately $81,000 using these figures.

Similarly, a Pepperdine University report in 2003 estimated that it costs the average enterprise $10,000 for each MB of data lost. With 15x data reduction, 93 per cent less traffic is sent across the WAN using WAN acceleration. This enables enterprise to recover more data in the event of a disaster. If just 10 additional MB are recovered using WAN acceleration, that is $100,000 in estimated cost savings.

Avoid IT costs associated with database synchronisation issues

By eliminating backup synchronisation problems, WAN acceleration can help recoup man-hours spent troubleshooting their disaster recovery processes. If a typical man-hour costs approximately $35 and the company eliminates 20 hours per week in disaster recovery problems, then close to $30,000 can be saved per year in operational expenditures.

Increase geographic distances between data centres

By reducing the impact of latency, enterprises can extend the distances between data centres. This can be the difference between recovering from a catastrophic disaster, such as a hurricane or terrorist attack, and losing everything. Given the above cost metrics, WAN acceleration can save tens of millions of dollars by enabling enterprises to geographically disperse business critical data for DR purposes.

Bandwidth savings

As the volume of data that is being protected in the enterprise continues to increase, the natural desire is to increase WAN bandwidth to accommodate this growth. WAN acceleration can avoid or delay these upgrades, providing significant cost savings, as discussed above.

With WAN acceleration, enterprises have better control over their data, ensuring fast and accurate information retrieval and reliable updates. As a result, WAN acceleration plays an important role in enterprise database performance, management and protection.

Jeff Aaron is director of product marketing at Silver Peak Systems.