April 27, 2015 at 1:57 pm
DISCLAIMER: This is in no way a criticism of RedGate as I have been an 8 year veteran of their SQL Prompt product and plan to continue using it for the foreseeable future. We’ve also used their SQL Monitor product for a few years and switched to a competitor’s product inly because we needed it to take into account the nuances between virtualized SQL Servers and non-virtualized.
My concern is that in order to use SQL Backup Pro I have to install SQL Server CE (Compact Edition) on each SQL Server that I want to backup from or restore to. I have SQL Backup (the GUI piece of the product) installed on its own Server yet the install of SQL Server CE is something that has to be done on all the servers I want to backup from or restore to. This to me seems like an unreasonable requirement. I know that the reason for installing SQL Server CE is because SQL Backup Pro uses it as a cache of the backup and restore history but why is that done at each SQL Server and not just on the server where SQL Backup Pro is installed?
Personally this a big issue for me because it effectively means installing a second copy of SQL Server on every existing SQL Server that I want Backup pro to work with.
So is this something that’s a standard ion SQL Backup software these days? Anyone have any thoughts on whether or not this requirement is something that they would see as prohibitive (cause them to rule out the use of SQL Backup Pro)? Am I just being unreasonable in not wanting to install SQL Server CE on all my SQL Servers?
THOUGHTS?
Kindest Regards,
Just say No to Facebook!April 27, 2015 at 2:28 pm
I think it's silly....why would I want another install of SQL Server on one of my SQL Server boxes? Either allow it to be 'centralized' and/or give me an option to have the software create a database on my existing SQL Server install.
-SQLBill
April 28, 2015 at 9:23 am
Hi,
Disclaimer: I do work for Redgate, although my time here doesn't go all the way back to the decision to use a SQL CE database. However, I can shed some light on this:
I know that the reason for installing SQL Server CE is because SQL Backup Pro uses it as a cache of the backup and restore history but why is that done at each SQL Server and not just on the server where SQL Backup Pro is installed?
The reason for this is that SQL Backup Pro has two main components:
- an engine, continually running, that’s installed on each SQL Server;
- a UI, that typically isn't continually running, is often installed somewhere else, and that connects to and manages multiple engines.
The application architecture therefore causes this separation of responsibilities: the engine persists the data locally on each SQL Server; then the UI collects and caches a local copy of the data from each SQL Servers on demand. The engine manages any data retention and purging requirements of the SQL CE database, so no user management is required.
[As a side-note, this also means that multiple versions of the UI can be used to manage the environment.]
As to why we didn't put the data into a table: well, as per my disclaimer, I wasn't part of the discussion, but my experience is that people are generally unhappy with applications that write data into their production SQL Server instances, when this can be avoided.
Hope this helps,
Colin.
May 5, 2015 at 1:51 pm
Thanks Collin for taking the time to explain this. I have received some additional clarifying information about this from RedGate's support dept. I've actually re-typed this several times because with each revision I find myself better understanding what's going on with SQL CE. Before I was able to finish typing up an additional question I had for You I received another update from Redgate's support tech about this that I'd like to share with all.
Thanks again for taking the time to answer.
Kindest Regards,
Just say No to Facebook!May 5, 2015 at 2:41 pm
CONCLUSION:
It appears the choice to use SQL CE was a design decision and not a design requirement. The Backup/Restore History that is retained by SQL CE on each SQL Server is cached on that server until its time to purge the backup/restore history. SQL CE does not serve as a temporary store for this information as is implied. AS best as I can tell SQL CE duplicates (and may even expand upon) the backup and restore history that is already stored in the msdb system Database.
While this use of SQL CE may serve as a desirable option for the right customer I can see no reasonable argument for why it has to be a requirement.
Based on the limited number of responses I'm guessing few believe that this requirement to install SQL CE is that big a deal. If that's not true and you believe that this is something that RedGate should not be requiring then take the time to let them know. They have always been very receptive to any input I've given and I believe that this is a chance to correct a mistake taken with what is rated as 2013's #1 SQL backup solution.
Kindest Regards,
Just say No to Facebook!May 6, 2015 at 2:59 am
YSLGuru (5/5/2015)
AS best as I can tell SQL CE duplicates (and may even expand upon) the backup and restore history that is already stored in the msdb system Database.
This is a factor that I didn't cover in my original reply. There's some additional information that is created as a result of SQL Backup Pro's backup and restore information, that we need to store. For eg, the compression and encryption levels; the status of copy-to activities; locations of log files; the log copy queue for network resilience and for upload to the hosted storage service; the results of scheduled backup verification activities; etc.
We do duplicate some of the basic data that's stored in the msdb database, for eg, some of the data from the backuphistory table. One reason for this is that there may be shorter retention periods on these tables than is set for SQL Backup Pro's history.
Hope this helps,
Colin.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply