For many companies, Microsoft SQL Server is an enabling component of their business operations, and the information they retain can reside on anything from one to hundreds of servers. No matter how large data grows, every last byte must be backed up in anticipation of corruption or loss – ready to be restored at a moment’s notice. The sheer size of modern databases is pushing legacy backup and restore technologies to their limit. In this article, we look at common backup problems, emerging solutions to address them, and the option of outsourcing your backups entirely.
Common Backup, Restore & Administration Problems
Backup Problems
- A full maintenance window. Not only must larger, longer backups be run but other important processes, like data warehousing or security patching, encroach on available downtime.
- Demand for uptime. In an ever connected world, the demand for access to data around the clock means the system maintenance window is shrinking.
- Cost of storage. Backups require local disk space to house them until they are transferred to tape or the Cloud. When they become too large, more disks must be purchased.
- Long lead times. Backup tapes or other removable media must be transported to a protected storage location, and could take the entire day to retrieve in the event of an emergency.
- Human error. Where manual backups and restores are carried out, a single mistake can cause the process to fail entirely or, even worse, let an unrestorable backup go unnoticed.
Restore Problems
- Longer recovery. Larger backups take longer to restore, hindering speedy recovery from database failure and prompt resumption of business.
- Productivity delays. In scenarios like software development, users must wait for a copy of the production database to be restored at not one but multiple points in the development pipeline.
- Faulty media. Many companies still do not test backups to ensure that the physical tape or media is not faulty, only to discover that a restore is not possible when disaster strikes.
Administration Problems
- Poor backup policies. If strong backup policies and schedules are not set, backups will be carried out according to the whims of individual administrators. This puts the integrity of the backup process itself in danger.
- Inefficient procedures. There is limited time to carry out backups so they need to start promptly and finish as fast as possible. The only way to achieve this is through automation.
- Inferior automation. Ad hoc scripts that are written to automate backups, but are poorly designed or forgotten somewhere on the server, could cause mysterious backup failures.
- Decentralised administration. In a group of companies, head office IT management has no visibility into individual business units to confirm that backups are being carried out properly.
- Weak auditing. There is no centralised system from which reports can be drawn to seek patterns of consistent backup failures at sites, or develop more efficient policies.
- Ineffective cataloguing. If full, incremental, differential or log backups are not properly catalogued and sequenced in their correct order, expeditious recovery is impossible.
Solutions
Backup to the Cloud
Studies have shown that the modern Cloud is more secure than onsite systems. The lead time for restores from the Cloud is enormously improved over the retrieval of tapes from protected storage.
Use the backup
It is now possible to provision databases or extract data for warehousing directly from a backup set. So as soon as the backup is complete, business can resume immediately without waiting for secondary processes.
Better compression
Superior compression technology exists that performs high-speed compression faster than traditional algorithms, so less disk space and time are expended on backups. Some proprietary methods can achieve up to 95% smaller backup files than built-in SQL Server backup utilities.
Maximum resource utilisation
Newer backup solutions leverage features like multi-threading and multi-core processing to perform discrete backup operations in parallel to achieve faster completion times. They avoid using the same memory as SQL Server so that their processes do not clash when reading or writing data. As such, backups run faster, and databases that are still in use will not appear to users to run any slower.
Instant restore
The technology exists to allow backups to be used as substitute databases at the same time they are being restored. Effectively, the database becomes immediately available to users and any data entered during that time is pushed to the live database once it is completely restored.
Virtual databases
Virtual databases give business solution developers immediate access to the same data at different points in their programming and testing pipeline. This is accomplished by presenting text-based backups as if they were real databases. For all intents and purposes, this method is ideal for situations where data must be refreshed frequently to facilitate such projects.
Managed Backup Services
For many companies, trying to keep up with the latest SQL Server backup technologies is a necessity that proves too costly or detracts from their core strategic goals. They inevitably turn to a managed backup services provider, allowing them to offload backup technology and management concerns to a third party who specialises in that field. The vendor assumes the responsibility of procuring state-of-the-art, high-performance backup tools and management software, as well as providing adequate hardware, systems, skills, labour, Cloud storage, security and business processes to facilitate the service.
In addition, they take over the centralised administration and monitoring of backups to ensure that they are performed in compliance with information protection standards. Backup policies and schedules are automated in accordance with customers’ specified maintenance procedures. It is also the provider’s duty to ensure that restores are carried out promptly and swiftly, especially in cases of disaster recovery. Lastly, they provision databases for special needs such as points in the software development pipeline.
For most organisations, managed backup services are an ideal solution to their backup problems. For a set monthly or per-use fee, they achieve high-speed backups and restores, get the convenience of outsourcing a non-core business service, and enjoy the latest backup and restore technologies without the associated cost of ownership.
Onpro’s Managed Cloud Backup & Restore service is a 360-degree solution for the backup, restore and administration of corporate databases.
