Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nathan.russell
Starting Member
13 Posts |
 Posted - 2004-10-07 : 10:17:55
|
| Another newbie dba type question I'm afraid !OK, I've just joined this company whose whole business is based around MS SQL (and IMHO its a right mess !)Our main production servers are based at our ISP. We have no tape facility there. There are 3 SQL servers (a mix of v7, 2K, std edition and enterprise edition); all running on W2K Server. There is no 2K domain structure - each server is in a workgroup with different Administrator passwords. File sharing - in fact, all NetBios traffic is turned off on each server (for 'security' reasons don't you know !!! - despite the fact they are all behind a firewall !!)The current 'backup' procedure is that a backup job runs each Sunday using sqlmaint triggered by the 2K task scheduler. The resultant .bak file is stored on the server in questions local disk.Once a month, someone from support (lucky old me !!) has to copy the last backup from the servers, and copy it using a ridiculously convoluted method to a server in our office which does have a tape drive. Then, we write to tape.I am arguing that we actually only backup monthly; and that given that our business is 100% dependant on these machines, we need to take backups a little more seriously.Now, I'm no SQL dba - I've only been doing this job for a month; so I may well be barking up the wrong trees..... but ......I'm looking at replication - but I think this relies on file shares ??I'm also thinking that perhaps the underlying architecture / build of 2K Server is wrong !Should these boxes be in a domain ? and should file sharing / NetBios be enabled ? Will it help me with any of this ?And what about SQL versions ? Should they all be consistant ?(If I were an outsider looking in (which in a way I am), I'd say that our server environment has been bodged together by people who dont really know what they are doing; and with no view as to the bigger picture)So, what would you guys do for backups ?How would you actually do it given that there is no local tape drive ?(not too worried at this stage about frequency etc) The current sqlmaint/2K task scheduler job seems reasonably sensible, but how is it different to using SQL's Agent Manager and a backup plan ?And how would you get the .bak files off of these servers, back to the ranch to the machine with the tape drive ?Any help with this would be very much appreciated,ThanksNathan |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-07 : 10:26:31
|
quote:
Originally posted by nathan.russell
So, what would you guys do for backups ?How would you actually do it given that there is no local tape drive ?(not too worried at this stage about frequency etc)
i have several servers configured to perform backups on local drive, then at the end of the day, copies these backup files to a network drive (with tape drive). we keep the files on this network drive for a week, then perform tape backup. most of the stuff is done automatically to synchronize the process except for changing the tape when it becomes full  each backup file is named with the databasename + dateOfBackup and saved in a folder named after from where server it came from.if you're interested, use cmdexec as type of step in your job.IMHO, you can do without replication if your worry is backup. |
 |
|
|
nathan.russell
Starting Member
13 Posts |
Posted - 2004-10-07 : 10:47:31
|
Hi jen,Thanks for your reply.quote:
then at the end of the day, copies these backup files to a network drive (with tape drive)
I could really do with the ability to use network file sharing  quote:
IMHO, you can do without replication if your worry is backup.
I was looking at replication as I was hoping it would effectivly 'trickle' the data back to my server here with the tape drive.At the moment, I am struggling copying approx. 10GB of .bak files from the 3 servers back to the office. As mentioned, my servers are at my ISP, and I need to get the data back here to put it on tape. Our internet connection is a pretty basic ADSL line (we are only a small company), so I am 'uploading' into this office at 256K assuming no other line contention within the office, or at the BT exchange.You can imagine that 10GB takes some while to copy !My thoughts were replication, based around transaction replication. I know that to start with I would need to do a snapshot, and I will have the same issues I have today; but thereafter the updates that have to be transfered across the internet are transactions rather than the whole data ??Is this viable ?Anyone else have any other techniques or ideas for backup ?CheersNathan |
|
|
|
biged123456
Starting Member
4 Posts |
Posted - 2004-10-07 : 11:01:58
|
| Wow, you have a lot on your plate. I would not recommend replication if you don't absolutely need it because it can be difficult to setup/monitor/maintain. Log Shipping is a bit cleaner, but requires all Enterprise class. I guess in you suituation, my primary concern would be to get the .bak files off the machine to another machine on a much more frequent basis. You can first do a backup to a network share (yes, you would need netbios). You have to setup the SQL service account and possibly SQL agent account to run as a user and setup this user on the share. Either use a domain account or setup an indentical account and password on both boxes. Having this account in the admin group would make it easier. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-07 : 11:13:52
|
for replication to work properly you need a reliable connection, if you do run snapshot, expect that data transferred will be large, use bulk copy instead.if backup is too large, i suggest, you take 1 full backup daily, then just backup the log files (this file will contain all changes made to the database). schedule the log to backup every 15 minutes depending on how large it'll grow, then just restore in your network drive.i am guessing this will chop down the size of your backup file.simple suggestion: since someone is going to your isp once a month to copy the files, just let him continue his work... just joking... |
|
|
|
nathan.russell
Starting Member
13 Posts |
Posted - 2004-10-07 : 11:32:15
|
quote:
since someone is going to your isp once a month to copy the files
That someone is me ; and I dont go to the ISP - I do this convoluted copy over the network. To give you an idea, this week is the beginning of the month, so we are due to write to tape. I started copying the data from the servers at the ISP to the server at my office on Monday. Its now Thursday afternoon and its still going.'nuff said  If it takes this long to copy the data to perform the backup, presumably it will take the same amount of time to do the reverse - IE. a restore. Thats a whole lot of downtime !Anyway, how about this for an idea.So, we know I have a tape drive here. What if I shipped the tape drive and a hosting server to the ISP. And also convinced the Company that re-enabling NetBios and file sharing on the servers wasn't so bad, and in fact could help us.Could I do anything there ?Could I run some backup software on the 1 server with the tape drive (something like Veritas Backup Exec ??), and configured it to backup the databases on the other servers ?I'm sure I could negotiate with the ISP to maintain a tape cycle for me - they'll do anything for money !Anyone done anything like that ?CheersNathan |
|
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-10-07 : 11:35:14
|
| If size is a concern, you might give our product, MiniSQLBackup, a try. It ought to be able to compress your backup files somewhat, making the transfers back to the office faster. Benefits the ISP too, since you would occupying less space on their servers. Comes with an encryption option too.Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
|
|
|
nathan.russell
Starting Member
13 Posts |
Posted - 2004-10-08 : 07:00:28
|
Peter,Thanks for your comments.quote:
Benefits the ISP too, since you would occupying less space on their servers.
Not an issue for us (or our ISP), as the servers are ours - we simply co-lo them at the ISP. Might be of use to others though.quote:
It ought to be able to compress your backup files somewhat, making the transfers back to the office faster.
We already do compression. Once SQL has backed up the database to a .bak file, we use WINrar to create a compressed archive of the .bak file, and then transfer that back to our office.The 10GB mentioned in the thread that I need to copy is the compressed rar files, rather than the original .bak files !!The more I think about this, the more I think that attempting to copy this much data over such a low bandwidth connection is a bad idea. Getting it offsite is great in principle, but I just dont see that its practical in this case.(Unless anyone else has any ideas ???)So, Backup Exec to a local tape drive, and getting the ISP to manage a tape cycle - sounds favourite so far.Anyone had any experience of Backup Exec and the SQL Server agents ?CheersNathan |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-10 : 10:18:54
|
from experience, the backupexec failed to restore a backup  so i was able to convince management to purchase local tape drives do a full backup at the beginning of day then just backup tlogs from there on, while you're waiting for the next tlog to arrive, restore the previous files with norestore option until the last tlog. IMHO, this will decrease the amount of time for restoring each file rather than the entire database. if you're not using the "backup" database, just retain a read-only so that you can still restore tlogs. |
|
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-10-10 : 23:17:25
|
| Have you considered performing differential or log backups instead? It might reduce the size of your backups, if your database is updated infrequently.Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
|
|
|
|
|
|
|
|
Whats a good backup strategy ?