In part one of this article I outlined the need for a proper solution to protect SQL Server. In this part I’ll cover some of the main ways in which you can go about achieving different measures of protection and as a consequence, increased availability.

Which option works best for you is very much dependant on what version/edition of SQL Server you’re running. The level of protection and availability you attain is then largely governed by the needs of your company, and especially by the size of your budget. The bottom line is this; the more you spend, the greater the degree of availability.

- SQL Server in Microsoft Cluster
- Replication and fail-over software
- Replication software
- Backup software

The ultimate method of providing availability with the minimum of impact on users and client applications has to be by using at least two copies of SQL Server Standard or Enterprise running in a Microsoft Cluster. A RAID array shared between the nodes gives both access to the data stores irrespective of which machine is hosting the resources groups. “Virtual servers” are created that all SQL Server instances are accessed through by users and applications. Now it doesn’t matter which physical SQL Server is alive, the user or application only ever sees the virtual server. If one server is down all transactions are automatically processed by the other server, completely transparently. This method does though involve a lot of expenditure, and server node location is limited by the available network link speed to the attached 2+ nodes and the shared storage. So this option may not be ideal or even possible in a very distributed environment.

If budgets really cannot run to total availability, third-party software is a very viable alternative. The end result from their use is varying degrees of availability from minutes to hours. The choice as to which is the most appropriate is likely to be driven be how much you are prepared to compromise availability in the environment in which the protected systems must run. However, the one element that should never be compromised is the quality of storage used; RAID-5 or RAID-10 should always be used to give the best balance of redundancy and speed.

A product like EMC’s RepliStor doesn’t give you perfect 100% availability of all servers, transactions and data stores, but with its ability to replicate all data in real-time, across WAN or LAN networks, interact with the Virtual Shadow Service (VSS) and self validate the replicated data, it manages to come very close. A product like RepliStor doesn’t have any of the limitations of distance that you’d get in a cluster. By using aliases it is able to provide not just replication over local and wide area networks but also fail-over. The result is a backup server could be operational within a a very short period of time, and with just about the latest transactions.

Alternatively you could use a product like PeerSync to take scheduled “snapshots” of SQL Server every 30 minutes, hour, whatever you want. With its utilisation of St Bernard’s Open File Manager built into the product it’s able to take fully functional copies of logs and data stores, with little likelihood of any repairs being needed prior to them being put live from another system. By adding the “ByteReplicator” module to the standard offering, only the changes in a snapshot are replicated not the whole thing all over again. This saves time, network resources, and permits the target of the replication to be just about anywhere in the country/world. The down side; on failure you’d lose all transactions since the last snapshot.

Lastly there’s good old backup software. Traditionally not really an availability application, but like PeerSync can be made to provide a very workable solution. By running a backup periodically during the day to online media like a NAS box you get the equivalent of PeerSync “snapshots”, but all the data at that point in time rather than a consolidation of just the differences. The draw back with a lot of backup solutions is having to go back to the most recent full, restore the incrementals, then you’re back in business. By using EMC Retrospect though every backup is an incremental. The result, upon restore you choose the date and time of the restore point and Retrospect builds the image ready for restore. Much faster and efficient. The downside is the time to recover and the loss of all transactions after the last backup was taken.

In conclusion, a company needs to evaluate its business needs and the degree to which it is prepared to compromise its own data stores and inputs. Whatever the result, there is more often than not a solution to meet requirements. Purple Rage is well placed to supply those solutions through its network of partners and consultants in the UK and Europe. For more information we can be reached on +44-(0)871-2500058, or via email at enquiries@purplerage.com.