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 |
|
ichbindev
Starting Member
4 Posts |
 Posted - 2007-10-16 : 17:19:19
|
| Using SQL Server 2000, is it possible to create off-site linked servers over the Internet? We want a remote, offsite standby server which is geographically a long way from the original server. We would need to first create linked servers and then start log shipping. A simpler way to explain is that the main server is in city A. We would like to create a linked server to a server in city B. Then the server in city A will do log shipping to the server in city B. How can this be accomplished? |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-16 : 17:31:17
|
| You don't need linked servers for log shipping. For log shipping you just backup the db, restore it to the remote site, then start backing up your logs (say every hour) and restoring them to the remote site...linked servers aren't involved....AFAIK |
 |
|
|
ichbindev
Starting Member
4 Posts |
Posted - 2007-10-16 : 20:38:34
|
| I understand your point. Since the goal is to have geographically far apart backups, is this scenario possible:The DB server has two backup/restore plans. One will do daily full backups and store it locally. It will also do transaction log backups at regular intervals throughout the day.In addition, we have another simultaneous plan: do a monthly full backup with transaction log backups at regular intervals throughout the month.In other words, is it possible to have more than one maintenance and backup/restore plans associated with a single database which occur simultaneously without affecting each other? |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 09:41:10
|
| I think having two maintenance plans will cause you some trouble with the log backups. You'd have to apply the log backups from both maintenance plans to your remote server in order for it to work. You are better off having one maintenance plan and have your monthly process simply use files generated from the one maintenance plan. |
|
|
|
ichbindev
Starting Member
4 Posts |
Posted - 2007-10-18 : 20:41:12
|
| Yours is an infinitely better idea than mine. I suppose this should work: take one daily backup as the starting point and then keep backups subsequent transaction logs off-site. Repeat this process after certain periods of time where you take a daily full backup, take it off-site, and also take transaction logs off-site regularly. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-19 : 10:29:12
|
| Yea, that's what I've set up in the past. As a side note, zipping backups generally makes them 1/10 the size and much faster to copy offsite. Then you just unzip them and restore offsite. You can automate the whole process in a bat file. I kept the tlog backups local and just restored them each hour to the standby server over the line.You could also look at SQL Lite Speed which zips them as they backup for even more of a time saver...but you have to have it installed at the remote site in order to use the files it generates. I needed to use the backup files for numerous other things than just the standby server and we couldn't put lite speed on every server (5 dev servers, 8 test servers, 5 qa servers, prod, dr) so I didn't use lite speed. |
|
|
|
ichbindev
Starting Member
4 Posts |
Posted - 2007-10-19 : 13:33:30
|
| The DB I have inherited has close to 40 GB of full backup, plus about 1 GB of transaction log backups daily. Can you recommend any compression software that would not take a lot of system resources, such as cpu cycles and memory, to compress this much data in a reasonable amount of time? We have tested WinRAR and 7-zip but both hog a lot of the processor and some amount of memory.Good suggestion on SQL Lite Speed. We will definitely look at it and see if it can be incorporated in our environment.As you said, a bat file may be used to automate the process. It would be really helpful to me and others in such situations if you could guide us how to write such a bat file. |
|
|
|
|
|
|
|
|
Set up linked server over the Internet