Hello there
If i use transactional replication on one server, can i set the recovery
mode to be simple?
Yes - it has no impact.
HTH,
Paul Ibison
|||It should be set to bulk logged on the subscription database while the
snapshot is being applied. After that it makes no difference.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eR$g%23s1zHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Hello there
> If i use transactional replication on one server, can i set the recovery
> mode to be simple?
>
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Wednesday, March 28, 2012
recovery model and replication
does the recovery model also change in a replication enviroment when you change a database from simple to full?
regards
Johan van der Wiel
Johan.vanderWiel@.getronics.comNo, database settings are not replicated, and that's something that many people were asking for from MS, and I don't think it is going to be part of the new release.sql
regards
Johan van der Wiel
Johan.vanderWiel@.getronics.comNo, database settings are not replicated, and that's something that many people were asking for from MS, and I don't think it is going to be part of the new release.sql
Recovery Mode and Replication
Is there any requirement for either the publisher or subscriber to have
full recovery model enabled when using transaction replication.
No, but put your subscriber in bulk copy mode when you are applying the
snapshot for a faster deployment.
I normally put my publication database in full recovery model and dump
frequently to make life easier for the log reader.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul" <sueme.sucker@.gmail.com> wrote in message
news:1138896281.822489.160290@.g47g2000cwa.googlegr oups.com...
> Is there any requirement for either the publisher or subscriber to have
> full recovery model enabled when using transaction replication.
>
|||Nope. Replication will still work in any recovery model.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Paul" <sueme.sucker@.gmail.com> wrote in message
news:1138896281.822489.160290@.g47g2000cwa.googlegr oups.com...
> Is there any requirement for either the publisher or subscriber to have
> full recovery model enabled when using transaction replication.
>
|||Thanks for the quick reponses
full recovery model enabled when using transaction replication.
No, but put your subscriber in bulk copy mode when you are applying the
snapshot for a faster deployment.
I normally put my publication database in full recovery model and dump
frequently to make life easier for the log reader.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul" <sueme.sucker@.gmail.com> wrote in message
news:1138896281.822489.160290@.g47g2000cwa.googlegr oups.com...
> Is there any requirement for either the publisher or subscriber to have
> full recovery model enabled when using transaction replication.
>
|||Nope. Replication will still work in any recovery model.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Paul" <sueme.sucker@.gmail.com> wrote in message
news:1138896281.822489.160290@.g47g2000cwa.googlegr oups.com...
> Is there any requirement for either the publisher or subscriber to have
> full recovery model enabled when using transaction replication.
>
|||Thanks for the quick reponses
recovery mode and log size
hi friends,
after setting merge replication my log file is incresing continuouly.its going in gbs.what should be the optimum log file size for a database.what are the microsoft recommendations regarding this
what should be the best recovery mode to implement.it is simple,full,bulklogback.
which should be the efficient .
what is the microsoft recommendations for this
please help
thanks
reddy
Reddy,
the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size, ru
n DBCC SHRINGFILE.
HTH,
Paul Ibison
|||paul,
thanks for your solution
regards
reddy
"Paul Ibison" wrote:
> Reddy,
> the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
> The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
> If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size,
run DBCC SHRINGFILE.
> HTH,
> Paul Ibison
after setting merge replication my log file is incresing continuouly.its going in gbs.what should be the optimum log file size for a database.what are the microsoft recommendations regarding this
what should be the best recovery mode to implement.it is simple,full,bulklogback.
which should be the efficient .
what is the microsoft recommendations for this
please help
thanks
reddy
Reddy,
the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size, ru
n DBCC SHRINGFILE.
HTH,
Paul Ibison
|||paul,
thanks for your solution
regards
reddy
"Paul Ibison" wrote:
> Reddy,
> the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
> The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
> If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size,
run DBCC SHRINGFILE.
> HTH,
> Paul Ibison
Monday, March 26, 2012
Recovery from a Restore without replication info
Can anyone point me to a set of documented steps on how to clean up the
following mess?
Three days ago a developer (not me) deployed some changes to a production
database by restoring from a backup created on another server. The problem
is the production database was set for replication. Now there is an
inconsistency between the replication metadata stored in Master and
Distribution vs. the production database. The type of replication is
snapshot and the servers are all version 2005 SP1.
I've recovered from this exact same situation in SQL 2000 but never in 2005.
Unfortunately I didn't bother to document the steps. I dimly recall however
a lot of false starts and direct editing of the contents of the Master
database. I'd really love to avoid experimentation this time around.
Hi, Kevin,
To let me better understand your issue, coulder you please let me know the
follwing questions:
1. Who is the publisher?
2. Who is the distributor?
3. Who is the subscriber?
4. What is the type of your replication?
5. Could you please describe me more detailed of the inconsistency that you
mentioned?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||As it is snapshot replication, what I'd do is to remove the publication, run
sp_removedbreplication on the relevant databases (assuming no other
publications there) and then readd the publication. If you have been backing
up your replication scripts (http://www.replicationanswers.com/Script7.asp)
this is pretty straightforward to do.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks for the suggestion. That's what I am going to try to do this morning.
I spent all part of yesterday setting up a test environment that reproduces
the problem. I know that on 2000 the system stored procs that delete
replication data from the Master and Distribution databases fail if the
publishing database does not contain replication metadata. My hope is that
the procs have been improved in 2005 and will no longer fail.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e5JCCbGbHHA.4176@.TK2MSFTNGP02.phx.gbl...
> As it is snapshot replication, what I'd do is to remove the publication,
> run sp_removedbreplication on the relevant databases (assuming no other
> publications there) and then readd the publication. If you have been
> backing up your replication scripts
> (http://www.replicationanswers.com/Script7.asp) this is pretty
> straightforward to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Hi, Kevin,
I am sorry that I missed reading that your replication type was snapshot.
Did you mean that your publication database was polluted?
In this case, you can just restore your publication database from the
latest dabase backup file since it is a snapshot replication; however there
may be data loss between the backup time to now.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
following mess?
Three days ago a developer (not me) deployed some changes to a production
database by restoring from a backup created on another server. The problem
is the production database was set for replication. Now there is an
inconsistency between the replication metadata stored in Master and
Distribution vs. the production database. The type of replication is
snapshot and the servers are all version 2005 SP1.
I've recovered from this exact same situation in SQL 2000 but never in 2005.
Unfortunately I didn't bother to document the steps. I dimly recall however
a lot of false starts and direct editing of the contents of the Master
database. I'd really love to avoid experimentation this time around.
Hi, Kevin,
To let me better understand your issue, coulder you please let me know the
follwing questions:
1. Who is the publisher?
2. Who is the distributor?
3. Who is the subscriber?
4. What is the type of your replication?
5. Could you please describe me more detailed of the inconsistency that you
mentioned?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||As it is snapshot replication, what I'd do is to remove the publication, run
sp_removedbreplication on the relevant databases (assuming no other
publications there) and then readd the publication. If you have been backing
up your replication scripts (http://www.replicationanswers.com/Script7.asp)
this is pretty straightforward to do.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks for the suggestion. That's what I am going to try to do this morning.
I spent all part of yesterday setting up a test environment that reproduces
the problem. I know that on 2000 the system stored procs that delete
replication data from the Master and Distribution databases fail if the
publishing database does not contain replication metadata. My hope is that
the procs have been improved in 2005 and will no longer fail.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e5JCCbGbHHA.4176@.TK2MSFTNGP02.phx.gbl...
> As it is snapshot replication, what I'd do is to remove the publication,
> run sp_removedbreplication on the relevant databases (assuming no other
> publications there) and then readd the publication. If you have been
> backing up your replication scripts
> (http://www.replicationanswers.com/Script7.asp) this is pretty
> straightforward to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Hi, Kevin,
I am sorry that I missed reading that your replication type was snapshot.
Did you mean that your publication database was polluted?
In this case, you can just restore your publication database from the
latest dabase backup file since it is a snapshot replication; however there
may be data loss between the backup time to now.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
Saturday, February 25, 2012
Records lost on database
One of our clients set up merged replication between two SQL Servers.
Replication occurs every ten minutes. A problem occurs when they capture
employee information through a web application affecting several tables that
the information in some of the tables "disappears". What would be the best
way for me to investigate what the problem could be?
It depends on how the info was added. If it is a bulk insert, or a fast load
from DTS, then the replication triggers won't fire by default, and the data
will be left on one side only. Is that what you're seeing? If it is instead
that data is successfully added then disappears, it might be that you have
compensating changes occurring
(http://support.microsoft.com/default.aspx?scid=kb;en-us;828637&Product=sql2k).
Cheers,
Paul
Replication occurs every ten minutes. A problem occurs when they capture
employee information through a web application affecting several tables that
the information in some of the tables "disappears". What would be the best
way for me to investigate what the problem could be?
It depends on how the info was added. If it is a bulk insert, or a fast load
from DTS, then the replication triggers won't fire by default, and the data
will be left on one side only. Is that what you're seeing? If it is instead
that data is successfully added then disappears, it might be that you have
compensating changes occurring
(http://support.microsoft.com/default.aspx?scid=kb;en-us;828637&Product=sql2k).
Cheers,
Paul
Subscribe to:
Posts (Atom)