Replication Q&A

Q. Can you bring replicated database offline?

A. No, We can’t bring replicated database offline as it will throw error as database is being replicate and can’t bring I offline, we must take it off of replication before making it offline.

Q. Can you schedule replication?

A. Yes, using replication jobs schedule can be done where subscriber don’t need recent data.

Q.  Under what circumstances will you re-initialize replication?

A.   1. Replication expires (snapshot expires)
      2. Replication is disabled and needed to be re-enabled.
      3. Adding article to an existing replication. (sometimes only snapshot needed for added article in transactional replication)
      4. Certain structure errors related to replication.

Q. What is orphan replication? How to clean it?

A.  Sometimes we see publication and subscription on database which is not connected to system, it happens because during restore/refresh publication comes with database and gets created.
     SP_removedbreplication ‘’ can remove orphan replication, without parameter there is risk of deleting all replication on database.

Q : Does activity need to be stopped on a database when it is published?

A : No. Activity can continue on a database while a publication is being created. Be aware that producing a snapshot can be resource-intensive, so it is best to generate snapshots during periods of lower activity on the database (by default a snapshot is generated when you complete the New Publication Wizard).

 

Q :Are tables locked during snapshot generation?

 A : The length of time that the locks are taken depends on the type of replication used:

For merge publications, the Snapshot Agent does not take any locks.

For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.
For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications.

Q :Should I script my replication configuration?

A : Yes. Scripting the replication configuration is a key part of any disaster recovery plan for a replication topology

Q : What recovery model is required on a replicated database?

A : Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in metadata tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model.

Q : Why does replication add a column to replicated tables; will it be removed if the table isn't published?

A : To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. To accomplish this:
Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.

If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

A filter must not include the rowguidcol used by replication to identify rows. By default this is the column added at the time you set up merge replication and is named rowguid

Q : How do I manage constraints on published tables?

A : There are a number of issues to consider regarding constraints on published tables:

Transactional replication requires a primary key constraint on each published table. Merge replication does not require a primary key, but if one is present, it must be replicated. Snapshot replication does not require a primary key.

By default, primary key constraints, indexes, and check constraints are replicated to Subscribers.

 

The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints; the constraints are enforced for user operations but not agent operations.

Q: Can multiple publications use the same distribution database?

A: Yes. There are no restrictions on the number or types of publications that can use the same distribution database. All publications from a given Publisher must use the same Distributor and distribution database.

Q :Does replication encrypt data?
A : No. Replication does not encrypt data that is stored in the database or transferred over the network

Q :Does replication resume if a connection is dropped

A :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.

Q: Are logins and passwords replicated?
A :No. You could create a DTS package to transfer logins and passwords from a Publisher to one or more Subscribers

Q: Why can't I run TRUNCATE TABLE on a published table?

A: 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.

Q : What is the effect of running a bulk insert command on a replicated database?

A: For transactional replication, bulk inserts are tracked and replicated like other inserts

Q : Does replication affect the size of the transaction log?

A : Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode 

Q: How do I move or rename files for databases involved in replication?

A : In versions of SQL Server prior to SQL Server 2005, moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached,replication had to be removed from these databases first. Beginning with SQL Server 2005, you can move or rename files without detaching and re-attaching the database, with no effect onreplication.

Q: How do I drop a table that is being replicated?

A: First drop the article from the publication using sp_droparticle, sp_dropmergearticle, or the Publication Properties - dialog box, and then drop it from the database using DROP

Q: How do I add or drop columns on a published table?

A: SQL Server supports a wide variety of schema changes on published objects, including adding and dropping columns. For example, execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers and then executed to drop the column. Subscribers running versions of SQL Server prior to SQL Server 2005 support adding and dropping columns through the stored procedures sp_repladdcolumn and sp_repldropcolumn

Q: How do I determine if the data at Subscribers is synchronized with data at the Publisher?

A: Use validation. Validation reports on whether a given Subscriber is synchronized with the Publisher. For more information, see Validate Replicated Data. Validation does not provide information on which rows if any are not synchronized correctly, but the tablediff utility does.

Q: How do I add a table to an existing publication?

A: It is not necessary to stop activity on the publication or subscription databases in order to add a table (or another object).

Add a table to a publication through the Publication Properties - dialog box or the stored procedures sp_addarticle

and sp_addmergearticle. For more information, see Add Articles to and Drop Articles from Existing Publications.

 

Q : Does replication work in conjunction with log shipping, database mirroring and clusering?
A : Yes

Q: What options are there to delete rows on the publisher and not on the subscriber?

A: 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.

Q: Explain what stored procedure sp_replcounters is used for?

A: Sp_replcounters is a system stored procedure that returns information about the transaction rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is acting as the distributor or subscribing to publications from another server will not return any data

Q: If I create a publication with one table as an article, and then change the schema of the published table (for example, by adding a column to the table), will the new schema ever be applied at the Subscribers?

A: Yes. Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table and since you cannot drop a published objects, the schema change will fail.

Q. how to configure alerts if replication fails?
A.  Set up replication monitor to configure alerts or from replication jobs as well,

Easy way would be : replication->launch replication monitor -> click on publication-> warnings tab-> configure alerts and select type of alert as needed.

Q. Can you setup replication with always ON?
A. Yes, but with certain limitation, please follow the MS site for detailed info.