This article is half-done without your Comment! *** Please share your thoughts via Comment ***
How do I drop a table that is being replicated?
First drop the article from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties – <Publication> dialog box, and then drop it from the database using DROP <Object>.
How do I remove a table from a publication?
Remove a table from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties – <Publication> dialog box.
Does replication work in conjunction with clustering?
Yes. No special considerations are required because all data is stored on one set of disks on the cluster.
Why can’t I run TRUNCATE TABLE on a published table?
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
Does replication resume if a connection is dropped or do we need to reinitialize the replication?
Yes. Replication processing resumes at the point at which it left off if a connection is dropped. If you are using merge replication over an unreliable network, consider using logical records, which ensures related changes are processed as a unit.
What options are available to delete rows on the publisher and not on the subscriber?
One option is to replicate stored procedure execution instead of the actual DELETE command. You can create two different versions of the stored procedures one on the publisher that does the delete and the other on the subscriber that does not do the delete.
Another option is to not replicate DELETE commands.
What should be our ultimate steps for Enhancing Replication Performance?
- Setting a minimum amount of memory.
- Using a separate disk drive for the transaction log for all databases involved in replication.
- Consider adding memory to servers used in replication.
- Using multiprocessor computers.
- Setting a fixed size for the distribution database.
- Publishing only the amount of data required.
- Running the Snapshot Agent only when necessary and at off-peak times.
- Placing the snapshot folder on a drive not used to store database or log files.
- Using a single snapshot folder per publication.
- Consider using compressed snapshot files.
- Reducing the distribution frequency when replicating to numerous Subscribers.
- Consider use of pull or anonymous subscriptions.
- Reduce the verbose level of replication agents to ‘0’ except during initial testing, monitoring, or debugging.
- Run agents continuously instead of on very frequent schedules.
- Consider using the –UseInprocLoader agent property.
Why backup of Publisher, Distributor and Subscriber is very important?
Publication databases are the primary, or central source, of data in a replication topology; therefore, even the most basic recovery plan should include regular backups at the Publisher. Back up the publication database and then make transaction log backups and/or differential database backups.
Backing up the Distributor involves backing up the distribution database, the msdb database, and the master system database. This allows you to recover from almost any type of failure without having to re-create publications or reconfigure replication.
Backing up the Distributor preserves the snapshot of the publication as well as the history, error, and replication agent information for your application.
Backing up the Subscriber involves backing up the subscription database and, optionally, the msdb and master system databases. The msdb and master databases need to be backed up only if it is a Subscriber that uses pull subscriptions and only if there is a need to be able to restore after a total system loss.
How can grants on the subscription database be configured to match grants on the publication database?
- Execute GRANT statements at the subscription database directly.
- Use a post-snapshot script to execute the statements.
- Use the stored procedure sp_addscriptexec to execute the statements.
What happens to permissions granted in a subscription database if a subscription is reinitialized?
By default, objects at the Subscriber are dropped and recreated when a subscription is reinitialized, which causes all granted permissions for those objects to be dropped.
What is a Local Subscriber?
A local subscriber is one that is defined with a priority setting used in conflict resolution. The priority setting is from 1 to 99.
What is a Global Subscriber?
A global subscriber has no priority and uses the distributor as the proxy for determining priority for conflict resolution.
I am running out of disk space for one of my SQL Server replicated databases. I would like to move the database files of my publication database, which is part of transactional replication, to a drive on the same server, which is has more free space.
What are the different options to do this task?
Option 1: sp_detach_db
Before using this option, we need to disable the publishing option using the sp_replicationdboption stored procedure.
if this stored procedure does not work, we need to use the sp_removedbreplication stored procedure, which removes all the replication objects in the database.
After that, we can use sp_detach_db option.
Option 2: ALTER DATABASE MODIFY FILE
Using this option we can modify the file path, but before that we have to make database offline and after changing the new path we have to make database online.
We are using replicated Subscriber databases for reporting and other purposes in our environment. As such, we have differing index needs based on the user activity on the publisher tables vs. the subscriber tables.
Unfortunately, whenever we reinitialize replication, we have missing indexes on the tables. Since the index needs are different between the publisher and subscriber, I do not also want to replicate all indexes from the publisher using the schema options that I wrote previously. How can I address these issues?
SQL Server Replication has a feature at the snapshot level called “Run additional scripts” which can be setup before and/or after the snapshot is refreshed. These options provide an opportunity to address our differing index needs between the publisher and subscriber.
T-SQL script to read Replication error log.
|
1 |
EXEC sys.sp_readerrorlog 0,1,'Replication','-' |
We set up replication initially by using the GUI and accepting all defaults and now due to growth we need to change our default replication snapshot folder location to another drive since the current location is a local disk and cannot be expanded to accommodate our growth.
How it is possible?
For more details, You can visit this article.
