backups | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

backups

hello all, I am a newbie at SQL. My issue is in backing up a fairly large SQL database over a T1 to my location. I use Veritas BackupExec 9.0 and it takes an incredible amount of time. Not only that, the backups choke and eventually die. It seems to be either an issue with latency but I could be wrong. I am trying to find a better way to get a faster and complete backup. my idea was to replicate the sql machine to another server at that location. Then backup from that secondary server to my backup server here. I’m not sure if this will improve things but it looks like it would. Furthermore, I don’t know if the cause of these backup crashes are related to the sql server trying to sync with my tape. If that is the case, I might have to backup to file first before backing up to tape. I’m not sure which way works as I’m unfamiliar with this. It’s also a major headache to try to duplicate 40GB of data from the original machine. But I think as long as I have a secondary machine at that location, I can free up the original machine. Does anyone have any suggestions or tips??
Backups across the network are going to be slower than backups to a local drive. Backups to tape will usually be even slower. If possible, backup directly to your sql server and then copy the backup across the network. Or backup to a server in the same location as the sql server then copy that to tape. Much faster. What version of sql? What version of the operating system? How fast is the network card on the server? How fast is the tape system you are using? All of these things will have an impact on the backup performance. What you are thinking about with replication can work, but I’d look at alternatives first if you can. Chris
Why not think about Log Shipping if you want to implement at DR solution. This can be done by using a standby server for failover has involved manually making database and log backups on the production server and then restoring them to the standby server on a regular basis. I feel Log shipping is easier to maintain than a replication process. Come with the information required by Chris as well to asses the situation for exact solution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Log shipping is ideal in this case as suggested. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
The machine runs Microsoft SQL 2000 on Windows 2000 Server (Pentium 2.4GHZ with about 768MB of RAM). Two drives of 74GB each. MSQL resides on the C: drive and the rest of the SQL data files are on the E: drive. By data files I mean the actual physical files that the database holds are sitting on E: and they come to about 40+GB right now. I have a standard 10/100Mbit NIC on the remote site and a Broadcom netxtreme adapter at my site. Both are running at 100Mbps. Sites are connected through a point to point T-1. My site has the backup server (a Dell Xeon processor server 2.8GHz with 2GB of RAM), and attached is a Dell Powervault 132T autotape loading library with single drive. The tape drive uses 100GB/200GB compressed Ultrium LTO tapes, two cartridges with 7 tapes per cartridge. I think the speed is about 15MB/s or 54GB/hr for the single drive model. So you think log shipping is the best solution for me so far? Basically to have a warm standby machine at that remote location. What if I wanted to have that warm standby here at MY location? Would that be safer? Would the T-1 affect this? Also, I would have to copy every single file (pertaining to the database) over to the warm standby machine before I begin log shipping?
If you’re looking fairly for warm standby then I insist again Log shipping is the correct solution, refer to this linkhttp://www.sql-server-performance.com/sql_server_log_shipping.asp to setup/perform log shipping depending upon the resources availability at your end. Log shipping increases a SQL Server database’s availability by automatically copying and restoring the database’s transaction logs to another database on a standby server. When the original primary server becomes available again, you can make it a new standby server—effectively reversing the servers’ roles. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

be aware though that log shipping requires advanced/enterprise edition, which is quite a lot more expensive. You can back databases/logs up to a network share (which is slower but dives you some resilience) and then restore the databases/logs to another machine. You’d have to create a sql job and a couple of stored procedures to do the restoring bit, since SQL Server backup files change name over time. but this can be done on standard edition software. There amy be third party tools that do this, or even sps available on the Net to do this. One of my tasks is to look at this for my employers, but have too much other stuff going on at the moment to do much about it. So we just back up to a network share, and in the event of a failure we’ll restore databases then Cheers
Twan
If not you can define your own LOG SHIPPING with the help of the article mentioned irrespective of ENTERPRISE edition for SQL server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
ok I will try this out satya. I’ve just placed an order for another server with similar hardware configuration. Now the only headache is copying all the physical files over to the new machine. Would there be a performance problem with log shipping over the T1 though?
Not at all and that too on T1, ensure all the hubs, switches are working properly before commencing the operation. A sneak preivew of event viewer, netmon & SQL error log helps to asses the information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I read through the log shipping document. How exactly would I go about synchronizing the sql id for both machines. Is this through a tsql script?
Which version & edition of SQL you’re having?
Log shipping can be configured using Wizard from SQL Enterprise manager.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I have SQL Server 2000 Standard Edition running.
Then you don’t have a LS wizard along with the edition, follow the link provided and notes by Brad about log shipping. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
satya,
will log shipping provide a way to copy over the actual physical data files? I don’t mean just the data directory under c:msql but the real files. Reason is that the actual files are scanned documents in .TIF format that sit on another drive and directory on the same server. How would that copy over to the warm standby server? Would I need to use replication or some other method to do that?
Log shipping involves copying transactions between primary and secondary server related to the database and if you store any documents/images path on the database and those are required to be copied then you must create another job to copy to secondary server. In the same even replication works and it only relates to SQL server data only. BTW, can you explain bit more about ‘those actual files are scanned documents in .TIF format? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
well the database is of scanned documents and they are saved as TIF picture images. They basically sit in another directory besides the sql ones and are the real bread and butter of it all. A vendor application processes these scanned documents and saves them inside of this directory. There is also a certain hierarchy internal to the vendor application itself but SQL 2000 is the real engine. As you can imagine, over time, the accumulation of these images can become quite large. So you are saying that log shipping will also "ship" any changes/additions/removals of these images as well? All I need to do is create another job for that part?
No LS will not copy the physical files as a part of the process, for reselience you must enable another job to copy those images as a safe side. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>