Scheduled backups for LARGE server installations

Question

Post reply

Scheduled backups for LARGE server installations

hemul

Right there with Babe

Points: 734
More actions
September 30, 2009 at 4:43 am

#385467

Hi people, I need some advice.

We have a very large installation of sql server express, about 200 clients at different geographical locations.

All clients are accessible remotely through our vpn network.

Now I need to set up scheduled backup jobs for every client.

We need full backups every week, and differential backups every day.

The backups are to be uploaded to our servers.

There are plenty of third party tools available, however most of them require a single license for every server.

What backup tools are best suited for large server installations, in terms of licensing and performance?

Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Swarndeep SSCrazy Points: 2347 More actions · Answer 1

There will be no single opinion on this.

Why don't you use SQL's own backup utility, if encryption is not your concern.

However, I would use Red Gate SQL Backup Utility. After comparing Lite Speed and Red Gate backup utilities I have short listed Red Gate on the basis of Price they offer (you may bargain on price), then I compared Red Gate Backup and SQL Server 2008 Backup from both License Price and technical aspects.

Red Gate was winner if you want to encrypt databases as well.

SQL Server 2008 was winner if you don't want to encrypt backup and of course SQL Server backup is free with SQL License.

You may see my detailed analysis on the following blog:

http://talksql.blogspot.com/2009/09/red-gate-sql-backup-54-vs-sql-server.html

Please note this is my personal opinion, I am not promoting any products. 😎

Thanks

Swarndeep

http://talksql.blogspot.com

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

ChiragNS

One Orange Chip

Points: 26137

October 1, 2009 at 1:23 am

#1060304

Any issue with using the maintenance plans in sql server.

"Keep Trying"

philcart SSC-Forever Points: 47794 More actions · Answer 3

hemul (9/30/2009)

Hi people, I need some advice.

We have a very large installation of sql server express, about 200 clients at different geographical locations.

All clients are accessible remotely through our vpn network.

Now I need to set up scheduled backup jobs for every client.

We need full backups every week, and differential backups every day.

The backups are to be uploaded to our servers.

There are plenty of third party tools available, however most of them require a single license for every server.

What backup tools are best suited for large server installations, in terms of licensing and performance?

Thank you.

Firstly, you're using SQL Server Express which has a hard wired limit of 4GB for the maximum database size. Individually, not that big really, but you have alot of them. With the Express edition you don't get any compression or encryption. If this is an issue for you, or your clients, you'll need to upgrade to Enterprse Edition ( 😀 ), or buy a third party product.

Any commercial third-party backup solution is going to cost you. Could be $500, to a couple of thousand per server. Unless you're able to pass that cost onto your clients, your going to be stuck with the basic inbuilt SQL Backup.

Is there a specific reason you need weekly full backups and daily differential backups? Remember, differential backups contain all changes since the last full backup, not just the changes since the last differential backup. Depending on backup size and available bandwidth, I'd go with full backups every 2-3 days and transaction log backups on a regular basis throughout the day.

If you do go down the route of purchasing a third-party backup tool, I'd suggest you take a look at Hyperbac (www.hyperbac.com). While not the most inexpensive out there, this tool is pretty impressive. It runs as a Windows service and works outside of SQL Server memory space and so it doesn't consume valuable data cache like most other backup tools. You use normal every day SQL commands to perform backups, no need to learn any overly complex extended stored procedure syntax. The backup files can also be compressed using a zip compatible algorithm which allows you to simply decompress files without needing to install anything extra.

--------------------
Colt 45 - the original point and click interface

hemul Right there with Babe Points: 734 More actions · Answer 4

Thanks for your inputs.

philcart (10/1/2009)

Firstly, you're using SQL Server Express which has a hard wired limit of 4GB for the maximum database size. Individually, not that big really, but you have alot of them. With the Express edition you don't get any compression or encryption. If this is an issue for you, or your clients, you'll need to upgrade to Enterprse Edition ( 😀 ), or buy a third party product.

I'm aware of the limitations in the Express edition.

But as a matter of fact not even the Enterprise edition of SQL Server includes the kind of feature I'm looking for.

philcart (10/1/2009)

Any commercial third-party backup solution is going to cost you. Could be $500, to a couple of thousand per server.

Yes and I'm willing to pay but not per sql server instance.

Every 3rd party tool I find requires additional license for each client.

That means more or less $20000 in license charges. Not possible 🙁

philcart (10/1/2009)

Is there a specific reason you need weekly full backups and daily differential backups?

Ok let's just say that we need backup files on a regular basis.

We will discuss the backup strategy later.

philcart (10/1/2009)

If you do go down the route of purchasing a third-party backup tool, I'd suggest you take a look at Hyperbac (www.hyperbac.com).

Hyperbac surely gets the job done. But again it comes down to licensing:

HyperBac for SQL Server - Licensing FAQs

How is HyperBac Licensed?

HyperBac offers customers a very simple licensing model. HyperBac is simply licensed per server.

...

It is simply a HyperBac instance per server and nothing more.

philcart SSC-Forever Points: 47794 More actions · Answer 5

philcart (10/1/2009)

Firstly, you're using SQL Server Express which has a hard wired limit of 4GB for the maximum database size. Individually, not that big really, but you have alot of them. With the Express edition you don't get any compression or encryption. If this is an issue for you, or your clients, you'll need to upgrade to Enterprse Edition ( 😀 ), or buy a third party product.

hemul (10/7/2009)

I'm aware of the limitations in the Express edition.

But as a matter of fact not even the Enterprise edition of SQL Server includes the kind of feature I'm looking for.

The features of enterprise edition that I was referring to were the compression and encryption. If you're not concerned over either then stay with Express edition.

If you were able to use the same Windows Crendentials for connecting to each of the servers, then I'd suggest you look at using the Central Management Server feature in SQL Server 2008 Management Studio. Then again, that may not be available as you're using the Express edition.

Not really what I would do, but as you have VPN connectivity, maybe you could look at backing up to a UNC path that points to a shared folder on a server in your office.

Why not talk to the third-party backup vendors such as Red-Gate, Idera, Hyperbac, etc... and see if they have any insights.

--------------------
Colt 45 - the original point and click interface

EdVassie SSC Guru Points: 60312 More actions · Answer 6

Don't just look at the headilne price for 3Party backup tools. As you say, paying on a per-server basis could cost you $20,000. If you have a few hundred Enterprise Edition servers you should be able to justify this money, but the economics are different for Express Edition.

I suggest you go to your 2 or 3 favourite vendors and negotiate a one-off license that will allow you to deploy on a few hunderd SQL Express instances. Some vendors may not budge from the per-server cost, but it would not surprise me if others reckon they have a choice between getting 5K or 8K for a site license limited to Express Edition or getting nothing, and taking the money.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

j.a.c SSCrazy Points: 2283 More actions · Answer 7

j.a.c

SSCrazy

Points: 2283

October 8, 2009 at 1:13 pm

#1063684

Why not execute native backups via sqlcmd and schedule them using the Windows Task Scheduler? You could probably run a Powershell/Python/VB script to upload the backups to your servers.

Very low capital cost but you would need to write some scripts yourself.

Andy

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 8

Agree with the above. I use SSIS to automate my nightly backup and offsite FTP process, but really it's just a nice wrapper for a few different scripts. You are going to have to use the Windows scheduler anyway unless you have third party software to handle that part, and there are numerous FTP processes that will work for free from the command line - I use FileZilla (it can handle FTPS and SFTP).

Edit to add that you will want to push the backup through a compression tool (7zip is good and free) before you FTP.