REPLICATION - 2

REPLICATION - 2

1) Is it possible to run multiple publications and different type of publications from the same distribution database?

Yes it can be done and there are no restrictions on the number or types of publications that can use the same distribution database. One thing to note though is that all publications from a Publisher must use the same Distributor and distribution database.

2) 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.

3) Data is not being delivered to Subscribers, what can be the possible reasons?

There can be a number of possible causes for data not being delivered to Subscribers:

• The table is filtered, and there are no changes to deliver to a given Subscriber.
• One or more agents are not running or are failing with an error.
• Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.
• A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.
• Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
• The INSERT stored procedure used by a transactional article includes a condition that is not met.
• Data is deleted by a user, a replication script, or another application.

4) 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?

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.

5) How will you monitor replication activity and performance?

The easiest way to monitor replication activity and performance is to use replication monitor, but you can also use the below tools to monitor replication performance:

• T-SQL commands.
• Microsoft SQL Server Management studio.

6)  Is there a need to stop activity on a database when it is published?

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).

7) In Transactional replication, If a table in database ‘A’(Publisher) is dropped, will the table get dropped in ‘B’(Subscriber)?

You cannot drop a table that is replicated. You have to first drop the article.

8) In Transactional Replication, If we drop a column in a table in database ‘A’, what will happen to the column in the same table in database ‘B’?

Issuing ALTER TABLE … DROP COLUMN at the Publisher, will result in the command being replicated to the Subscriber.

9) Do you set the “Replicate Schema Changes” subscription option to false when needed?

New columns being added to a published article shouldn’t be replicated to the subscriber unless they really need to be there. You can turn off the replication of schema changes by setting the ‘Replicate Schema Changes’ subscription option to ‘false’. (It defaults to ‘true’.)

10) Have you considered static row filters?

“Static row filters” allow you to include only certain rows in a given publication. There is overhead to applying the row filter itself: Microsoft only recommends you use the row filters if your replication setup can’t handle replicating the full dataset.

11) What are advantages of Peer-Peer Replication?

Peer-Peer Replication decreases / nullifies the dependency on Distributor. In this Replication topology each node is Publisher, Distributor and Subscriber. This increases availability of the database system and Failure of any node does not impact the health of Replication process. This topology also offers automatic conflict detection and correction. Hence, recommended in Realtime.

12) What Options we use to secure Replication Data?

Ensure that SQL Browser is running and TCP/IP is enabled. Enforce TDE (Transparent Data Encryption) so that every data bit is encrypted.

13) Is it possible to subscribe to specific articles in a publication (ie not all the articles of the publication)?

According to the documentation, you can only subscribe to a publication. However, assuming you have a publication which consists of several articles (tables) it is possible for each subscriber to subscribe to a subset of the publication. You create the publication and then set up the subscribers as per usual. Then you drop the unnecessary articles on a per subscriber basis. This only works if your subscribers exist on separate servers, but if you need to do it, the procedure to run is:

sp_dropsubscription ‘publicationname’, ‘tablename’, ‘subscribername’
Or more simply just run sp_addsubscription on a per article basis!

14) In Merge replication, for the conflict resolver: I have a row guid and a timestamp column on each article in the publication. It was my hope that by having the timestamp, I could avoid the need to manually reconcile the conflicts between publisher and subscriber. However, I see that the conflicts are still there and still require manual intervention to eliminate. Why?

Merge Replication does NOT require a timestamp column (that’s for transactional replication). Merge Replication only requires a ROWGUID (which must be the ROWGUIDCOL for the article). Second, the conflict resolver didn’t work the way I expected it to: the conflict resolver works by using a default rule to resolve a conflict, but it still records the fact that a conflict occurred. The record of the conflict (and the winning and losing records) are stored (by default) at the distributor. Manual intervention is required (opening the conflict viewer) to view and eliminate the conflicts. But if you want to be alerted to the existence of a conflict, you must write a separate monitoring tool.

15) 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.

16) What is a “Global Subscriber”?

A global subscriber has no priority and uses the distributor as the proxy for determining priority for conflict resolution.

17) My replication monitor incorrectly shows a failed publication – how can I remove it?

I have seen this a few times recently. In each case it occurred because of a restored database! Basically, there was an existing published database in the test environment which was correctly configured, worked normally and which showed up correctly in replication monitor. Someone then restored a backup from production on top of this test database without first removing the subscriptions and the publication. Replication monitor at this stage shows the publication still existing but with an error. In this case running sp_removedbreplication, sp_droppublication, restarting the SQL Server service and the like will not solve the issue – still the replication monitor shows an error. I have tried removing some of the system metadata in the distribution database, which also failed to remove the error. Ultimately the only way I found to remove the publication from the replication monitor was to recreate a publication with exactly the same name and then delete it. The dummy publication only needs the same name – the articles can be anything from the database – and once deleted the replication monitor registers the change. No doubt there will be some sort of system proc to do this properly at some stage and I’ll update this entry.

18) What should I do if my system is running out of memory when too many agents synchronize?

You can limit the number of concurrent synchronizations; in merge this is done on the GUI or by setting the @max_concurrent_merge property of sp_addmergepublication. For other agents you’ll need to make the necessary edits to the registry.

19) How can I add a “NOT NULL” column to an existing article?

Basically you need to add a column with a default constraint. After that the column is made nullable and the constraint can be removed. This worked for transactional publications. For merge it worked but I had to rerun the merge agent after it failed once. The code I used is below:alter table tXXX ADD Salary INT NOT NULL Default 0
go
alter table tXXX alter column Salary INT NULL
go
alter table tXXX drop constraint DF__tXXX__Salary__353DDB1D
go

20) How can I have redundancy for the publishing database?

Database mirroring can be used in conjunction with replication to provide availability for the publication database.

Log shipping can also be used in conjunction with replication.

21) How can I see the text for ‘sys.sp_MSrepl_helparticlecolumns’ or any other such hidden replication system stored procedures?

Here’s a nice trick for you! Some of these procedures aren’t accessible using sp_helptext and they also aren’t accessible using the OBJECT_DEFINITION function. However if you use the Dedicated Admin Connection (DAC), you’ll be able to access the real text of the procedure:

SELECT object_definition(object_id(‘sys.sp_MSrepl_helparticlecolumns’))

The trick is to open up a connection using the DAC (when you open a query window to “yourservername” just use “ADMIN:yourservername” instead).

22) How can Replication Alerts be written to Event Viewer?

Open up the replication alerts folder, double click on the alert you are interested, click on the browse button (the three ellipses), click on the edit button, select always write to the Windows Event Log.

23) How can I ensure that triggers fire during initialization?

The setting to enable firing of triggers during the initial bulk load is not exposed in SSMS, but it is configurable through the @fire_triggers_on_snapshot parameter in sp_addarticle and the ‘fire_triggers_on_snapshot’ property through sp_change_article.

24) What are the differences between 32 and 64 bit replication?

The Replication features of SQL Server 2000 (64-bit) are nearly identical to the 32-bit version, with the following features being supported:Snapshot Replication
Transactional Replication
Merge Replication
Immediately updating subscribers
Queued updating subscribersHowever, there are a few special cases:

(1) as the Microsoft Jet engine is not supported, Microsoft Jet push subscriptions for merge replication are not supported
(2) unless the subscriber provides a 64-bit ODBC or OLE DB driver, transactional or snapshot push subscriptions for ODBC or OLE DB subscribers are not supported
(3) because of the unavailability of 64-bit Data Transformation Services (DTS), transformable push subscriptions are not supported.

25) How can I prevent the snapshot agent failing with ‘Server execution failed’?

The value of:
“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\Subsystems\Snapshot”
should be changed to:
C:\Program Files\Microsoft SQL Server\MSSQL\BINN\SQLREPSS.DLL,C:\Program Files\Microsoft SQL Server\80\COM\SNAPSHOT.EXE,ReplStart,ReplEvent,ReplStop,120

26) Is it possible to have 2 publications with one table in common?

In merge replication the same table may be added to each publication. However, one of the publications will need to be set up as a no-sync and if you are using automatic range management you’ll receive a PK error like this:
“Violation of PRIMARY KEY constraint ‘PK__MSrepl_identity___4D5F7D71′. Cannot insert duplicate key in object ‘MSrepl_identity_range’.”. So you’ll need to use manual range management for your table article.

 27)   Can multiple publications use the same distribution database?

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.

If you have multiple publications, you can configure multiple distribution databases at the Distributor to ensure that the data flowing through each distribution database is from a single publication. Use the Distributor Properties dialog box or sp_adddistributiondb (Transact-SQL) to add a distribution database.

28) Does replication work over low bandwidth connections? Does it use compression?

Yes, replication does work over low bandwidth connections. For connections over TCP/IP, it uses the compression provided by the protocol but does not provide additional compression. For Web synchronization connections over HTTPS, it uses the compression provided by the protocol and also additional compression of the XML files used to replicate changes.

29)   How can grants on the subscription database be configured to match grants on the publication database?

By default, replication does not execute GRANT statements on the subscription database. If you want the permissions on the subscription database to match those on the publication database, use one of the following methods:

• 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.

30)   Does replication affect the size of the transaction log?

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. If it is set to run in continuous mode (the default), ensure that it is running.

31)   How far behind is the Distribution Agent? Should I reinitialize?

Use the sp_replmonitorsubscriptionpendingcmds stored procedure or the Undistributed Commands tab in Replication Monitor. The stored procedure and tab display:

• The number of commands in the distribution database that have not been delivered to the selected Subscriber. A command consists of one Transact-SQL data manipulation language (DML) statement or one data definition language (DDL) statement.
• The estimated amount of time to deliver commands to the Subscriber. If this value is greater than the amount of time required to generate and apply a snapshot to the Subscriber, consider reinitializing the Subscriber.