November 18, 2009 at 4:41 pm
Hello room,
Can SQL 2000 with Simple recovery model participant in Backup and Recovery of Snapshot transaction replication?
Thanks,
TJ
November 18, 2009 at 5:45 pm
Edwin-376531 (11/18/2009)
Hello room,Can SQL 2000 with Simple recovery model participant in Backup and Recovery of Snapshot transaction replication?
Thanks,
TJ
Actually your single line question does not convey the complete sense of your question / doubt.
So I am answering based on what I understood from it.
A database in Simple recovery model can participate in Snapshot replication and also transactional replication.
I am not sure what do you mean by "Backup and Recovery of Snapshot transaction replication?"
Replication does not have backup and recovery, a database has backup and recovery.
Moreover there is nothing like Snapshot transaction replication, it is Snapshot replication and transaction replication.
Correct me if I did not understand your question.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 18, 2009 at 10:04 pm
I wanted to replicate a 100 GB database over WAN.
The database instance is setup with simple recovery model and it run on SQL Server 2000.
I research some articles on web sites and it explained how to setup a transaction replication use a backup database.
During my testing, I run into some issues that indicated the publisher database is in Simple recovery model.
November 18, 2009 at 10:22 pm
Edwin-376531 (11/18/2009)
I wanted to replicate a 100 GB database over WAN.The database instance is setup with simple recovery model and it run on SQL Server 2000.
This is understood....
I research some articles on web sites and it explained how to setup a transaction replication use a backup database.
During my testing, I run into some issues that indicated the publisher database is in Simple recovery model.
This is confusing.....
Let me explain my confusion.. In the very first sentence itself you have mentioned that your database is in simple recovery model, and in the last sentence you reiterate that during testing some issues indicated the publisher is in simple recover model.
Please explain
What testing you have done?
Your replication topology (which system is the publisher, distributor and the subscriber)
What issues you ran into?
It is highly unlikely to know what trouble you are facing without any technical information.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 19, 2009 at 3:17 am
Edwin-376531 (11/18/2009)
During my testing, I run into some issues that indicated the publisher database is in Simple recovery model.
Transactional replication does not depend on recovery model. http://sqlinthewild.co.za/index.php/2008/12/05/a-new-sql-myth/
What issues did you run into?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2009 at 8:26 am
I execute the following db script on publisher:
EXEC sp_replicationdboption @dbname = 'TestDB', @optname = N'Sync with backup', @value = N'true';
Errors on:
Msg 20622 - Replication database option 'sync with backup' cannot be set on the publishing database because the database is in Simple Recovery mode.
November 19, 2009 at 1:10 pm
Then you just can't use the 'sync with backup' option. The replication itself will work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2009 at 4:00 pm
the sync with backup option is used in replication to ensure you have a consistent recovery point with the distribution database if you have to restore the publication database. It does this by not transferring any data to the distributor from the publication database until they are backed up. Hence you need log backups, hence you cannot have the db in simple mode.
So if you want this feature (and you don't have to have it) the db cannot be in simple mode, but transactional replication itself will work with the db in simple mode.
having this feature turned on is good practice in terms of recoverability but it does affect latency, so you have to decide which is more important to you.
see http://msdn.microsoft.com/en-us/library/aa237094(SQL.80).aspx
---------------------------------------------------------------------
November 19, 2009 at 9:00 pm
Thanks for your clarifications. It answers and clear up my questions.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply