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 |
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
 Posted - 2006-02-02 : 15:25:14
|
| Hello,I was reading articles about database replication and found that one important point was that users can continue their work during replication unlike back-ups where they would have to log off.Question:Is replication done on servers as well as databases?How different or better is it over back-ups?Lastly, is database dump better than database replication?Is there a link that I can access which might answer these qustions?Thanks in advance!sqlnovice123 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-02 : 15:27:55
|
| You still need to perform backups on your databases! Replication isn't a backup strategy. And users do not need to logoff during backups, not sure where you read this.So are you referring to using replication for a secondary server instead of log shipping?Replication is done on objects inside a database.Tara Kizeraka tduggan |
 |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2006-02-02 : 17:12:34
|
| Tara,Thanks for your reply. I apologise, cause I mis-intepreted the article. I should have said:"Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online"Is it true that replication can also supplement disaster-recovery plans by duplicating the data from a local database server to a remote database server?Thanks!sqlnovice123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-02 : 17:15:27
|
| Traditional backups do NOT prevent users from getting access to a database during backups. The article is wrong. Is it even referring to Microsoft SQL Server?I certainly wouldn't use replication for disaster recovery plans. We use log shipping for this. However, some people use replication. I just wouldn't recommend it.Tara Kizeraka tduggan |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-02-03 : 14:23:00
|
| SQLNovice,What one uses as a backup strategy depends on what their particular needs would be and how fast they would want to be able to recover. There is really 1 question to ask: "If your server went down how fast would you need to be back up and running?" Second question would be "If your server went down at 4:55 on the busiest day of the week, how much work from that day is your company willing to lose?" Using Replication or Log Shipping, or mirroring your server offers you some things that a traditional backup nightly and doing log dumps hourly or every other don't offer you. That is why you choose to use them.As a "Backup" approach log shipping is 1 way to go. However, the other server just sits there completely useless (per my understanding) until a crash occurs and then is ready to be brought online and made usefull. If you go the Replication route you can replicate to that second server, so you do have a "ready to be live" copy of the data, but you can also then use the server anytime as a reporting server. You'd have nearly live data if you used transactional replication so your reports would be up to the minute. Then you have the best of both worlds, you have "a" backup copy of your data, that you can also use to reduce the volume of queries against your production server. So why wouldn't you want to use this as your "backup/disaster recovery" plan then since it sounds so good. Well, assume that some well meaning developer is in Query Analyzer and deletes some test data from the customer table. Problem is, he forgot to switch the database to "TEST" and they did it in the production database. You hear the uh-oh, but SQL Server didn't hear their "uh-oh" and their deletes of half of your customers data was replicated instantly. Suddenly the rest of the office hears you scream "I sure wish I had a Backup and some log files that I could restore from to recover from this problem." Another issue to consider (yes it happened to me that's why I mention it) is that Replication will not replicate tables that are over 255 columns. Just flat out not an option until you move to SQL Server 2005. Tara is absolutely right regarding the fact that you can still use your system while a backup is being performed. But in practicality I've found that response time gets impacted during that time period. But I think that could be environment dependant because our backup is done to another server so our network is being hit kind of hard by that movement, and our storage is a single RAID-5 array (before my time) that is hammered hard all day long to begin with.Our policy is:Backups nightly with log backups hourly to another physical serverTape backups are then taken off of the storage server and cycledThe server that the files are backed up to is actually used as a reporting server, so after the backup is complete we do a RESTORE to bring that days backup online for reporting. (Yes its day old the next day)We also installed Double Take software to mirror our production servers to an offsite location for 'serious' disaster recovery. We went that route instead of log shipping because we also needed to protect non - sql image/document files.Hope it helps,Dalton |
|
|
|
|
|
|
|
|
Is replication more advantageous over back-ups