January 18, 2011 at 11:29 am
As fate would have it most of the SQL servers I have inherited have been configured to write the backups on the same local disk that the databases reside on. Now multiple that problem by 10 fold.
Thinking long term (note: I have a mixture of 2000 and 2005 backups) would it be possible to purchase a dedicated backup server and have all our SQL servers start sending their backups to this one server in which we will have Tivoli backing up as well?
The first issue that comes to mind is network bandwith. However if I schedule all these backups to run over night I hope it will alleviate that issue.
The second issue I am curious about is whether or not specific versions are capable of this? For example could only enterprise edition support this idea/plan to write the backups to a remote server over the network while standard edition would not?
If this is not possible, I welcome advice.
Thanks!
January 18, 2011 at 11:40 am
Vertigo44 (1/18/2011)
As fate would have it most of the SQL servers I have inherited have been configured to write the backups on the same local disk that the databases reside on. Now multiple that problem by 10 fold.Thinking long term (note: I have a mixture of 2000 and 2005 backups) would it be possible to purchase a dedicated backup server and have all our SQL servers start sending their backups to this one server in which we will have Tivoli backing up as well?
The first issue that comes to mind is network bandwith. However if I schedule all these backups to run over night I hope it will alleviate that issue.
The second issue I am curious about is whether or not specific versions are capable of this? For example could only enterprise edition support this idea/plan to write the backups to a remote server over the network while standard edition would not?
If this is not possible, I welcome advice.
Thanks!
I know that it is possible in SQL 2005 to write backups to a shared folder on another server providing the account running the SQL Agent has permission to read and write there. I don't recall if SQL 2000 offers the same functionality, but in any case SQL 2000 is at its EOL support anyway. Time to move on.
If you have Tivoli as an Enterprise Backup Solution, why not employ the Tivoli Agent to just write the backups created on the local disk to the Enterprise Media (I'm guessing tape)? Having Tivoli backup existing files from a Server does not require a specialized Agent (that I am aware of).
Another alternative is have SQL create the backups on the local disk and then running PowerShell or a good ol' DOS batch to copy the file from the Database Server to the storage location and then having Tivoli grab it from there. It adds a layer of redundancy as you would have multiple copies available in the event of a hardware failure.
Bandwidth is always going to be an issue when moving backups. Unless you have a dedicated network for that sole purpose it is going to bite you in the end.
I hope this has helped.
Regards, Irish
January 18, 2011 at 11:58 am
We are experiencing some issues with the Tivoli backup client tool on some of our servers and I get the impression that fixing Tivoli is not a major priority as of now since the admin I was talking to said they were currently shopping for another DR product. Be that as it may, in the event a server crashes and I can't get to the backups that were being stored on the server drive then I am at the mercy of the Tivoli administrator. I am pretty new here and have never XX been through a fire drill yet and really don't feel like that would be the best time to see what my guys are made of. I do not want to rely on someone else in order to do my job. Especially in an event such as that when management will be likely sitting over my shoulder.
I realize that this idea may be expensive upfront but I think...have read... that this backup model is best practice and will only help once we start getting more SQL 2008 databases in the door. Its just more organized and I won't be at the mercy of someone else.
So is this DR method called Mirroring? I am only wanting to create a single server in which all sql server will be sending their backups to. It sounds like I may want to look into getting our 2000 databases upgraded to 2005 or 2008.
Thanks!
January 18, 2011 at 12:13 pm
Vertigo44 (1/18/2011)
So is this DR method called Mirroring? I am only wanting to create a single server in which all sql server will be sending their backups to. It sounds like I may want to look into getting our 2000 databases upgraded to 2005 or 2008.Thanks!
Mirroring requires 3 Servers. The 2 main Database Servers and then a third Server called the "Witness". The closest I can come to comparing it would be an Active/Passive Failover Cluster. However, in theory, it is better than that.
While I have implemented a few Failover Clusters, I've not yet done anything with Mirrored Database Servers. I imagine it is similar in ways and different in others. However, the big advantage is the near millisecond fail-over.
However, at issue here is that all 3 Servers have to be at the same version of SQL. In your case with 2000, 2005, and 2008 you're not going to get everything from all of those Servers onto one other server. Backup files created on 3 different versions of SQL sent to another Server, no problem.
You can't mirror a SQL 2000 Server because it does not have the ability. SQL 2005 added mirroring, but it was never supported. SQL 2008 I have heard supports mirroring fully, but have yet to see a live implementation of it.
I understand your apprehension on relying on someone else to ensure that you've got backups.
Regards, Irish
January 18, 2011 at 3:47 pm
All versions of SQL Server have the capability of backing up to a UNC share. As long as the service account running SQL Server has access to that share, then it can backup to that location.
The problem with this solution is making sure you not only have the bandwidth, but that you do not have any latency that will cause the backup process to fail. SQL Server backups are very sensitive to network issues and will fail if anything happens.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 18, 2011 at 6:50 pm
Jeffrey Williams-493691 (1/18/2011)
All versions of SQL Server have the capability of backing up to a UNC share. As long as the service account running SQL Server has access to that share, then it can backup to that location.The problem with this solution is making sure you not only have the bandwidth, but that you do not have any latency that will cause the backup process to fail. SQL Server backups are very sensitive to network issues and will fail if anything happens.
Thank you, Jeffrey. It has been many years since I have used SQL 2000 and I tend to forget some of the finer points.
Regards, Irish
February 7, 2011 at 8:01 am
Another thing to consider about transferring large SQL backups over the network is.... compression. SQL 2005 doesn't support compressed backups, 2008 does.
I'm running 2005 and was amazed at the compression ratios I got when I simply ZIP'd up a SQL backup file, something around 1:10. That's a lot less data to move over the network.
I don't really care about compression for normal, on-site backups, because the enterprise tape backup does compression on the fly via hardware, which should be faster than doing it with software. But, we also have off-site backups that copy off our mission-critical backups over the internet. For that, we compress first.
So you could either upgrade to 2008 or use a regular ZIP utility. Try it out beforehand to see what throughput benefit you gain.
Rich
February 7, 2011 at 9:34 am
First, do you have database files and log files separate? If so, you would typically want to back up to the log file disk, not the data file disk.
If not, I would still back up locally, and then use some script to move the files. There are lots of scripts here and on the Internet to help.
You can also log ship from all versions, which is really copying the log backups to another server and restoring them WITH NORECOVERY and available in case of an issue. Mirroring also works, and only requires two instances. The witness is optional, but log shipping is simpler, IMHO, to manage and administer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply