USEFUL SITES :
Write for Us
Recommended by SQL-Server-Performance.Com
Product: SQLsafe 3.1.
Publisher: Idera.
Pricing: Per SQL Server Instance … $995.
Key Benefits:
Microsoft has always made it easy to perform database backups and restores. In fact, SQL Server was the first major database that had a GUI-based option for backups and restores.
While backup and restore tasks are easy to perform, the tools provided by Microsoft aren't necessarily the most efficient. Database backups aren't particularly fast, they tend to be very large in physical size, and there is no central way to manage backups and restores over multiple SQL Servers scattered throughout an enterprise.
For example, it might take five hours to back up a large database, during which time SQL Server applications take a small performance hit. Also, you must store this large file somewhere. And should you need to restore it, are you able to easily locate it and restore it? If you have only a handful of servers, this is probably not a problem. But what if you manage dozens, or hundreds of SQL Servers? Then yes, this can become a problem, one not easily remedied with the backup and restore tools that are native to SQL Server.
To help remedy this problem, Idera has developed SQLsafe, an enterprise-class backup and restore tool for SQL Server. SQLsafe has the ability to significantly reduce backup time, reduce restore time, reduce backup size, reduce the amount of space required to store backups, and optionally encrypt data. And it allows you to manage the backup and restore process on all of the SQL Servers in an enterprise, all from one central console. Each of these has the potential to reduce the cost of administering SQL Server in your enterprise.
In this review, we take an in-depth look at how well SQLsafe meets its stated goals.
In this review, here is what we are going to look at:
All of these questions will be answered in this extensive review.
In this section, we look at some of the key features the software publisher claims that SQLsafe does. Throughout this review, we verify each one to see if the feature does exist, and if it performs as expected.
Before we look at each of the above features, one by one, let's learn a little more about how SQLsafe works. This will give us the perspective we need to better understand how all of these features fit together.
SQLsafe in an n-tiered, .NET 2.0 application that includes many components, which can be broken down into three major categories:
Let's look at each of these major components and see how they work together.
Interface Components
The interface components refer to the many different ways you can interact with SQLsafe. While this list is long, you will most likely spend most of your time with the Management Console. All the other interfaces are optional. They include:
Operational Components
These components are what do the SQLsafe heavy lifting. They include:
External Components
Technically speaking, these aren't SQLsafe components, but they are important parts of the SQLsafe architecture.
How the Components Work Together
To make this section a little easier to follow, we are only going to focus on these SQLsafe components:
SQLsafe is designed so that the components can reside on a single server, or multiple servers. In most cases, you will want a SQLsafe configuration similar to the one below, although this is only one of many options available to choose from.
Let's make these assumptions:
As a DBA, let's say you decide you want to back up a particular database on a particular server. (In the real world, you would schedule this to happen automatically, but I want to use this manual example to show you how SQLsafe works.) To do this, you start up the SQLsafe Management Console from your desktop.
From the Management Console, you specify which database on which server you want to back up, and that you want to perform the backup now. When you do this, the Management Console communicates with the appropriate Backup Agent on the requested SQL Server and the backup begins, with the backup being stored on the hard disk on the SQLsafe server, which has been designated for storing SQL Server backups. Once the backup is done, the backup information (including the path of the backup file itself) is stored in the SQLsafe repository, which is also located on the SQLsafe server.
If the backup needs to be restored, then the DBA can go to the Management Console, locate the backup, and then specify that it should be restored. At that point, the information is sent from the Management Console to the Backup Agent on the server to perform the restore, and this is then logged in the SQL Repository.
As you can see, the process is very simple.
Installing and configuring SQLsafe is easy and straightforward, although it can take multiple steps, depending on what options you want to install. For example, there are several different install programs, and you must run each one separately, depending on which options you want to install.
The install options include:
The most important install program is the core SQLsafe setup program, and it needs to be installed first. The other install options can be installed at any time after the core program has been installed.
Hardware and Software Requirements to Run SQLsafe
SQLsafe can back up the following SQL Server database versions:
Required hardware and software for the SQL Server computers to be backed up:
Required hardware for the SQLsafe computer:
Required software for the SQLsafe computer:
An Overview of Installing SQLsafe
Here are the typical steps you would follow when installing SQLsafe:
At this point, you are ready to begin using SQLsafe to back up your SQL Servers.
In this part of the review, we are going to take a brief look at how you use SQLsafe to backup and restore a database. This section is not intended to be all-inclusive. Its goal is to introduce you to the basic interface so that you become familiar with it.
How to Back Up a SQL Server Database File
The first step to backing up a database on a remote SQL Server is to register it with the SQLsafe Management Console. This not only registers the server, but also installs the Backup Agent service to the remote server. Once this is done, you can perform manual backups, restores, create backup scripts, or schedule backup jobs (using either SQL Server's SQL Server Agent or Idera's SQLschedule).
Now I am going to show you how to perform a manual backup using SQLsafe, step-by-step.
Below is the main view of the SQLsafe Management Console when it is first started.
Figure 1: SQLsafe Management Console.
At the left is a context-sensitive area that changes depending on what you are currently doing in SQLsafe. In the center is where registered SQL Servers and their databases are displayed. In the above example, two different SQL Servers are registered, and you see the databases of one of the servers. On the right hand side is a list of major tasks you can perform from within the SQLsafe Management Console.
Now, let's say we want to manually back up a single database. To do this (there is more than one way to perform this task in the SQLsafe Management Console, but we will only look at one method in this article), we can click on Backup in the above screen.
Once we click on Backup, we are presented with this new screen.
Figure 2: Backup Database screen.
Here, we can specify which group of servers we want to work with (groups are artificial groupings of SQL Servers you create for organization purposes) along with which server you want to back up. You also specify if you want to back up one or multiple databases at the same time.
In our example, we are only going to back up a single database. When I select a database and click OK, I get the following screen (this is actually part of a larger screen, but is the part of the screen we need to focus on now).
Figure 3: General tab.
This screen has four tabs, which offer us several options. Let's look at the options on each of the tabbed screens.
Under the General tab, we can choose the name of the backup set (what we want to call this backup), an optional description, the type of backup to be performed (full, differential, log, or file), the location where the backup is to be placed, whether we want to append or overwrite any preexisting backup set, and what we want to do (perform a manual backup, generate a script we can use to perform the backup, or schedule the script with the SQL Server Agent). At this point, we could go ahead and click on Backup to begin the backup, but before we do, let's look at some additional options available to us from the remaining three tabs.
The Encryption tab, shown below, is used to specify if we want to encrypt the backup or not. (The following screen is abbreviated.)
Figure 4: Encryption tab.
We will talk more about encryption options later in this review. For now, I am going to choose None.
The Compression tab, shown below, is used to specify what type of compression you want to use to compress the backup. (The following screen is abbreviated.)
Figure 5: Compression tab.
We will talk more about compression options later in this review. For now, I am going to choose the default IntelliCompress, Optimize for Speed option.
The Advanced tab, shown below, gives us several more options. (The following screen is abbreviated.)
Figure 6: Advanced tab.
Here, we can choose if we want to verify the backup after the backup is complete (always a good idea), whether we want to remove the backup agent from the server after the backup, and how many threads to devote to the backup. For now, I am only going to choose Verify Backup.
To perform the backup, I click on the Backup button (not shown in the screen above), and the backup begins. If you like, you can watch the live progress of the backup on the following screen. (The following screen is abbreviated.)
Figure 7: Status screen.
This screen let's you watch, live, the progress of any backups currently running. In this case, we only see our single backup. This screen also allows you to filter what is going on so you can drill down to specify servers, or show them all at once, if you like. You can also choose to show only backup jobs that are queued, processing, complete, in error, or cancelled. Think of this screen as your window into the backup and restore process of your entire enterprise-wide SQL Server backups.
Once the backup is done, it will show as complete, like you see below. (The following screen is abbreviated.)
Figure 8: Status screen.
Notice that after the backup is complete, you get a variety of details and statistics at the bottom of the screen.
Manually Restoring a SQL Server Database
Now that you see how a backup is made manually, let' look at how you restore a database.
When I choose from the Management Console to restore a database, I am presented with the following screen. (The following screen is abbreviated.)
Figure 9: Restore screen.
The top portion of the screen is used to locate the backup you want to restore. You can choose by searching from SQLsafe's history, or by examining the file system. Once you have located the backup, you use the lower part of the screen to choose where you want to restore the database, along with the name you want to restore it under. You can restore using the same name, or a different name. You also choose the Recovery option. At this point, you can choose to perform a manual restore now, or to create a script to perform the restore. In my case, I am going to choose a manual restore. When I do, the restore begins and you can see the status of the restore very similar to the status of a backup. In fact, the same screen is used for both. You just have to choose either Backups or Restores from the status screen (see Figure 8) to view the statuses of backups and restores.
As you can see, manually backing up and restoring databases is very easy. Of course, this example is oversimplified.
In this section, we test the key features of SQLsafe. The features tested include:
Reduces Backup Times by at Least 50%, and as Much as About 70%
One of the main reasons to consider purchasing SQLsafe is its ability to reduce the amount of time it takes to perform backups. Idera claims that backup times are reduced by at least 50%, and as much as 70%. Fortunately, this is a fairly easy experiment to perform. As you might expect, my test hardware will most likely be different than the hardware you use. But in this experiment, we don't really care about hardware; the goal for our test is to find out if backup times are reduced by the amount claimed by Idera.
For my experiment, I tested backups of two different databases containing typical accounting data. One was fairly small and one fairly large. I first performed backups using SQL Server 2000's native backup feature three times (to get a good average), and then performed the same database backups using SQLsafe three times.
Below are the results:
Native Backup
SQLsafe Backup
% Faster
Small Database (81.3 MB)
:21
:06
71%
Large Database (5.51 GB)
22:21
5:56
74%
Table 1: Backup speed comparison (minutes:seconds).
As we can see from this first test, SQLsafe does indeed meet its expectations on my test hardware. The actual time it takes to perform backups depend on the size of the databases, the contents of the data in the database, the type of compression used, the type of encryption used (if any), the hardware, and the current load on the server.
The test above was performed using SQLsafe's "IntelliCompress, Optimize for Speed" option. This is the default compression option for SQLsafe, but not the only option. SQLsafe has five additional options. Below, I have tested each to see how they compare.
Small
Large
IntelliCompress, Optimize for Speed
:063
IntelliCompress, Optimize for Size
:072
6:00
Type 1 (high speed, less compression, minimal server load)
:066
5:58
Type 2 (good compression, high speed, moderate load)
:059
5:12
Type 3 (high compression, moderate speed, high load)
:064
5:40
Type 4 (highest compression, slower speed, high load)
:157
16:29
Table 2: Backup speed comparison (minutes:seconds) - SQLsafe backup modes.
Each option above has different pros and cons. You will want to choose the option that best meets your needs. To determine this, you may have to perform some experiments with SQLsafe to determine which backup option is best for your databases. Of course, you can just choose the "IntelliCompress, Optimize for Speed" option, and that option will analyze the various options, and based on what if finds, it will automatically determine the optimum backup option for you.
Reduces Storage Requirements up to 95%
Besides the speed of the backup, the size of the backup is another key feature of SQLsafe, and one of the main justifications used for its purchase. In the following series of tests, we look at how well it compresses our two test databases. As before, this test is specific to the hardware used, along with the databases used for the testing. Your results will vary.
For my experiment, I tested backups of two different databases containing typical accounting data. I first performed backups using SQL Server 2000's native backup feature three times (to get a good average), and then performed the same database backups using SQLsafe three times.
% Smaller
Small Database
81.31 MB
15.84 MB
80.55%
Large Database
5.51 GB
783.2 MB
86.11%
Table 3: Backup size comparison.
As we can see from this test, SQLsafe does indeed meet its expectations on my test hardware.
The test above was performed using SQLsafe's "IntelliCompress, Optimize for Speed" option. Below, I have tested each of the other backup modes to see how they compare.
15.51 MB
776.4 MB
15.71 MB
781.82 MB
12.10 MB
547.2 MB
9.31 MB
479.7 MB
7.81 MB
353.5 MB
Table 4: Backup size comparison - SQLsafe backup modes.
The last backup option, Type 4, produced a savings of over 90% in disk space. SQLsafe does indeed meet its database compression claims.
Recover Data Very Fast; Quickly Find Backups and Perform the Restore
When you need to restore a database, you don't want to have to spend a lot of time locating it. One of the features of SQLsafe is that all the information on the SQL Server backups is stored in a central database repository, which makes it easy to locate and then restore the backup.
When you need to restore a backup, you can choose to find it through the history stored in the repository, or if you know where the backup file is, you can just point to it to perform the restore. Once you have told SQLsafe where the backup is, you can perform the restore using all of the same options available with SQL Server's native restore. While SQLsafe does not make any specific claims about restore speed, as you can see below, it is faster than a native restore, getting you back into production faster.
Native Restore
SQLsafe Restore
Small Database (81.3MB)
:46
:26
43%
Large Database (5.51GB)
32:50
16:25
49%
Table 5: Restore speed comparison (minutes:seconds).
As we can see from the results, SQLsafe restores are faster than native SQL Server restores. This means that you will be able to get your backup restored sooner, resulting in getting your users up and running sooner.
Includes Optional User-Selectable Encryption, Including DES, Triple DES, RC2, and Rijndael
More and more often, DBAs are being tasked with protecting their data, especially their backups. More than once in the past year, there have been reports of unencrypted database backups being lost when sent through the mail. This opens a lot of liability for organizations, and because of this, more and more organizations are requiring that backups be encrypted.
SQLsafe offers database backup encryption in several different flavors, including DES, Triple DES, RC2, and Rijndael. Each encryption method has its own pros and cons, which won't be discussed here. Instead, the focus here is how much turning on encryption affects backup speed. In this experiment, we perform an "IntelliCompress, Optimize for Speed" backup on our large test database.
Encryption
Backup Time
Backup Size
No Encryption
783 MB
DES
5:59
786 MB
Triple DES
782 MB
RC2
6:01
785 MB
Rijndael
Table 6: Encrypted backup comparison (minutes:seconds).
As you can see, turning on encryption does not add much to backup time or size.
Automates Backup Management Across the Enterprise
Another strong feature of SQLsafe is that it allows you to manage backups for all of the SQL Servers throughout your organization from a single Management Console. This is much more convenient than using Enterprise Manager or Management Studio to do the same. All backup information is stored in one central location, making it much easier to locate and restore databases, as needed. It also makes it easier to check on the current status of backups (past and current), as you don't have to go from server to server to see what is going on.
While I don't have the ability to test dozens or hundreds of servers with SQLsafe, its design and architecture are definitely suited for enterprise-wide SQL Server backup management.
Allows Backups to Be Performed Across Large Numbers of Servers Simultaneously
Like the above feature, I am unable to directly test this claim of SQLsafe. But I do want to mention that the design and architecture are well suited for backing up many SQL Servers at the same time. Of course, there are some physical limitations to making many, many backups at the same time, such as the networking hardware used, including network cards, speed, capacity, and current load on network segments, switches, and routers. In addition, the ability of your backup devices to write files, along with current load, will affect how many backups can be made simultaneously.
DynamicDeployment Technology Dynamically Deploys a Lightweight Backup and Restore Agent to each SQL Server to Be Backed Up
The agent runs as a service and may be configured to remain on the server all the time, or to be installed and uninstalled as needed. This permits quick deployment. As long as you have admin rights on the SQL Server where you want to install the agent, you can deploy the agent to any SQL Server, remotely, from the Management Console, making deployment easy.
When running, the agent on my test system took from 48 MB to 76 MB of RAM. CPU utilization by the agent varies with the task at hand, but didn't seem much of a problem. See more about CPU use just a little later in this article.
One of the unique features of SQLsafe is that if you choose, you can have SQLsafe install the backup agent service immediately before the backup, perform the backup, and then remove the backup agent service. This way, the RAM used by the agent won't be taking away RAM from SQL Server when it is not being used. Personally, I would only recommend this if your SQL Server is very short on RAM. And ideally, if you are short on RAM, you should purchase more RAM.
Runs as a Separate Process Outside of the SQL Server Process Space
When you install the backup/restore agent on a SQL Server, it does not install any components, DLLs, scripts, SPs, or tables in the SQL Server instances being backed up. It runs as a separate service. The only exception to this, and this is fully optional, is if you want to install the extended stored procedures provided by SQLsafe in order to integrate your existing backup and restore procedures with SQLsafe.
Includes Catalog of Customizable Reports Using Reporting Services
One of the optional components you can install is a set of customizable reports that run from SQL Server Reporting Services. The reports include:
If these reports don't meet all your needs, you can create your own custom reports using Reporting Services.
After having installed SQLsafe, and using it over a period of several weeks during testing, I have found SQLsafe more than easy enough to install and use. The Management Console's design does not fit the Microsoft design standards 100%, but once you get used to using the console, it is easy to get around so that you can perform your tasks and get them done quickly and easily.
When testing SQLsafe, I kept a close eye on how much CPU was being chewed up by the backup/restore agent. While the amount of CPU usage varies depending on which type of compression you are using, and if you are using encryption, I typically noticed about a 3% to 7% increase of CPU utilization over using the native SQL Server backup. With the exception of the very busiest of servers, this added CPU load during backups should not negatively affect your application's performance when backups or restores are going on.
Virtually every DBA, at one time or another, has run out of backup space using SQL Server's native backup. Traditionally, this means that you must delete some files, or expand the available disk space in order to continue making backups. With SQLsafe, you can immediately begin saving 80% to 90% of your current space, whether you have a single SQL Server, or hundreds of SQL Servers. This kind of disk savings can directly reduce the cost of storing your SQL Server backups.
In addition, the time saved making SQL Server backups can be a godsend, especially if your maintenance windows are very tight and you want to minimize any impact backups have on your production servers.
So very definitely, SQLsafe meets the needs of the average DBA, no matter how many SQL Servers he or she manages.
After extensive testing, SQLsafe meets all if its major specified benefits and goals, and is recommended by SQL-Server-Performance.Com. To find out if it meets your specific needs, download a trial copy of it today and check it out for yourself.
