Now lets get a deep insight about another very interesting feature of SQL Server, The Replication.

It is
simple, It is bit tough and It is complex at the same time depending on the mode of replication you are
using. While it is highly useful for Reporting purpose, It can also be described as cheapest solution for
high availability and disaster recovery as you can move object(Say Most important objects of your
environment) from one server to another server. You don’t need to move whole database from one node
to another if there are only few tables which are important from HA\DR point of view. Lets start the
reading most frequently asked QA series on Replication. Here you go.

1) What is replication?
Replication is subset of SQL Server that can move data and database objects in an automated way from
one database to another database. This allows users to work with the same data at different locations and
changes that are made are transferred to keep the databases synchronized.

2) What are types of replication?

 Snapshot replication – As the name implies snapshot replication takes a snapshot of the published
objects and applies it to a subscriber. Snapshot replication completely overwrites the data at the
subscriber each time a snapshot is applied. It is best suited for fairly static data or if it’s acceptable to
have data out of sync between replication intervals. A subscriber does not always need to be connected,
so data marked for replication can be applied the next time the subscriber is connected. An example
use of snapshot replication is to update a list of items that only changes periodically.
 Transactional replication – As the name implies, it replicates each transaction for the article being
published. To set up transactional replication, a snapshot of the publisher or a backup is taken and
applied to the subscriber to synchronize the data. After that, when a transaction is written to the
transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution
database and then to the subscriber. Only committed transactions are replicated to ensure data
consistency. Transactional replication is widely applied where high latency is not allowed, such as an
OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or
stocks.
 Merge replication – This is the most complex types of replication which allows changes to happen at
both the publisher and subscriber. As the name implies, changes are merged to keep data consistency
and a uniform set of data. Just like transactional replication, an initial synchronization is done by
applying snapshot. When a transaction occurs at the Publisher or Subscriber, the change is written to
change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the
distribution database where it gets propagated. The merge agent has the capability of resolving
conflicts that occur during data synchronization. An example of using merge replication can be a store
with many branches where products may be centrally stored in inventory. As the overall inventory is
reduced it is propagated to the other stores to keep the databases synchronized.
3) What are various Agents of replication?
 Snapshot Agent- The Snapshot Agent is used with all types of replication. It prepares the schema and
the initial bulk copy files of published tables and other objects, stores the snapshot files, and records
information about synchronization in the distribution database. The Snapshot Agent runs at the
Distributor.
 Log Reader Agent – The Log Reader Agent is used with transactional replication. It moves transactions
marked for replication from the transaction log on the Publisher to the distribution database. Each
database published using transactional replication has its own Log Reader Agent that runs on the
 Distributor and connects to the Publisher (the Distributor can be on the same computer as the
Publisher)
 Distribution Agent – The Distribution Agent is used with snapshot replication and transactional
replication. It applies the initial snapshot to the Subscriber and moves transactions held in the
distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push
subscriptions or at the Subscriber for pull subscriptions.
 Merge Agent – The Merge Agent is used with merge replication. It applies the initial snapshot to the
Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription
has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The
Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull
subscriptions.
 Queue Reader Agent – The Queue Reader Agent is used with transactional replication with the
queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber
back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the
Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

4) Why is primary key needed in Transactional replication?

The reason is in the subscriber, rows are updated/deleted one-by-one using primary key.

For example:
If you delete 100 rows in the publisher using a single DELETE statement, in the subscriber 100 DELETE
statements would be executed.

— on publisher
DELETE FROM dbo.tbAddress WHERE City = ‘LONDON’

— on subscriber
DELETE FROM dbo.tbAddress WHERE pk = @pk

5) Which all database objects can be included in replication?

Tables
Partitioned Tables
Stored Procedures – Definition (Transact-SQL and CLR)
Stored Procedures – Execution (Transact-SQL and CLR)
Views
Indexed Views
Indexed Views as Tables
User-Defined Types (CLR)
User-Defined Functions (Transact-SQL and CLR)
Alias Data Types
Full text indexes
Schema Objects

6) What are prerequisites of transactional replication?

Primary Key:
 This is a basic rule that every article should have a Primary Key to be a candidate table for Transactional
Replication. Primary keys are used to maintain uniqueness of records and to maintain referential
integrity between tables, and that is why it is recommended for every article to have a primary key.

Securing snapshot folder:

Schedule:

Network bandwidth:

Enough disk space for database being published:

We need to make sureWe need to make sure that we have ample space available for the transaction log
for the published database, as it will continue to grow and won’t truncate the log records until they are
moved to the distribution database. Please note that even in simple recovery model, the log fle can grow
large if replication breaks. That is the reason it is recommended to set T-log’s auto grow option to value
“true”. We should also make sure that the distribution database is available and log reader agent is
running.

Enough disk space for distribution database:

It is necessary to have enough disk space allocated to the distribution database. This is because
the distribution database will store the transactions marked for replication until it is applied to the
subscriber database within the limit of retention period of distribution (which is 72 hours by default), or
it will retain the transactions until the snapshot agent re-runs and creates a new snapshot. re-runs and
creates a new snapshot.

Use domain account as service account:

We should always use the domain account as a service account, so that when agents access the shared
folder of snapshot fles, it won’t have any problem just because they are local to the system and do not
have permission to access network share. While mentioning service account, we are asked to choose from
two built-in accounts including Local System account, Network Services, and this account, wherein we
have to specify the domain account on which the service account will run.

7) Difference between push and pull replication.

 Push – As the name implies, a push subscription pushes data from publisher to the subscriber. Changes
can be pushed to subscribers on demand, continuously, or on a scheduled basis.
 Pull – As the name implies, a pull subscription requests changes from the Publisher. This allows the
subscriber to pull data as needed. This is useful for disconnected machines such as notebook
computers that are not always connected and when they connect they can pull the data.
8) Define Distributor, Subscriber & Publisher
Publisher
The Publisher is a server that makes data available for replication to other servers. In addition to being
the server where you specify which data is to be replicated, the Publisher also detects which data has
changed and maintains information about all publications at that site. Usually, any data element that is
replicated has a single Publisher, even if it may be updated by several Subscribers or republished by a
Subscriber. The publication database is the database on the Publisher that is the source of data and
database objects to be replicated. Each database used in replication must be enabled as a publication
database either through the Configure Publishing and Distribution Wizard, the Publisher and Distributor
properties, by using the sp_replicationdboption system stored procedure, or by creating a publication
on that database using the Create Publication Wizard.
Distributor
The Distributor is a server that contains the distribution database and stores meta data, history data,
and/or transactions. The Distributor can be a separate server from the Publisher (remote Distributor), or
it can be the same server as the Publisher (local Distributor). The role of the Distributor varies depending
on which type of replication you implement, and in general, its role is much greater for snapshot
replication and transactional replication than it is for merge replication.

Subscribers

Subscribers are servers that receive replicated data. Subscribers subscribe to publications, not to
individual articles within a publication, and they subscribe only to the publications that they need, not
necessarily all of the publications available on a Publisher. If you have applications using transactional
replication built with Microsoft® SQL Server™ version 6.5 or later, and those applications subscribe
directly to articles instead of to publications, the applications will continue to work in SQL Server 2000.
However, you should begin to migrate your subscriptions to the publication level where each publication
is composed of one or more articles.

9) Define Article, Publication & Subscription.

Article
An article identifies a database object that is included in a publication. A publication can contain different
types of articles, including tables, views, stored procedures, and other objects. When tables are published
as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.

Publication
A publication is a collection of one or more articles from one database. The grouping of multiple articles
into a publication makes it easier to specify a logically related set of database objects and data that are
replicated as a unit.

Subscribe
A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription
defines what publication will be received, where, and when. There are two types of subscriptions: push
and pull.

10) Can we add or drop a single article from a publication. If so, How?
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 – <Publication> dialog
box or the stored procedures sp_addarticle and sp_addmergearticle.
 Remove a table from the publication using sp_droparticle, sp_dropmergearticle, or the Publication
Properties – <Publication> dialog box. You cannot drop articles from snapshot or transactional
publications after subscriptions have been added; you must drop the subscriptions first.

11) Define sp_replcounters

Returns replication statistics about latency, throughput, and transaction count for each published
database. This stored procedure is executed at the Publisher on any database.

12) Can we use replication to replicate data across different RDBMS i.e. SQL to Oracle
Oracle and DB2 can subscribe to snapshot and transactional publications using push subscriptions.
Subscriptions are supported for the two most recent versions of each database listed using the most
recent version of the OLE DB provider listed.

However, Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is

deprecated. To move data, create solutions using change data capture and SSIS.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new
development work, and plan to modify applications that currently use this feature.

13) Explain Latency in replication. How can you monitor Latency of particular publication
Transactional replication provides the tracer token feature, which provides a convenient way to measure
latency in transactional replication topologies and to validate the connections between the Publisher,
Distributor and Subscribers. A token (a small amount of data) is written to the transaction log of the
publication database, marked as though it were a typical replicated transaction, and sent through the
system, allowing a calculation of:

 How much time elapses between a transaction being committed at the Publisher and the
corresponding command being inserted in the distribution database at the Distributor.
 How much time elapses between a command being inserted in the distribution database and the
corresponding transaction being committed at a Subscriber.

14) What permissions are needed to a user to monitor replication.

The replmonitor database role in the distribution database. These users can monitor replication, but
cannot change any replication properties.

15) Name some commonly used Replication DMVs and their use.
There are four replication related DMV’s in SQL Server.

sys.dm_repl_articles

sys.dm_repl_schemas

sys.dm_repl_tranhash
 sys.dm_repl_traninfo

16) What are the advantages and disadvantages of Snapshot replication over Transactional
replication.
Snapshot Replication would be good to use if:
1. if you are sure that you would synchronize only once in a day and your business requirements do not
include replicating transactions as and when they are comitted on the publisher
2. If the size of the replicating articles is small – may be a few MBs/GBs
3. If it is does not matter that for some time the replicating articles would be locked (till the snapshot
would be generated)

Transactional Replication would be good to use if:

1. You want incremental changes to be propagated to Subscribers as they occur.

2. The application requires low latency between the time changes are made at the Publisher and the
changes arrive at the Subscriber.
3. The application requires access to intermediate data states. For example, if a row changes five times,
transactional replication allows an application to respond to each change (such as firing a trigger), not
simply the net data change to the row.
4.The Publisher has a very high volume of insert, update, and delete activity.

15) What is peer to peer replication.

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data
across multiple server instances, also referred to as nodes. Built on the foundation of transactional
replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This
enables applications that require scale-out of read operations to distribute the reads from clients across
multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication
provides data redundancy, which increases the availability of data.

16) What is conflict resolution in merge replication.

Merge replication allows multiple nodes to make autonomous data changes, so situations exist in which a
change made at one node may conflict with a change made to the same data at another node. In other
situations, the Merge Agent encounters an error such as a constraint violation and cannot propagate a
change made at a particular node to another node.

The Merge Agent detects conflicts by using the lineage column of the MSmerge_contents system table; if
column-level tracking is enabled for an article, the COLV1 column is also used. These columns contain
metadata about when a row or column is inserted or updated, and about which nodes in a merge
replication topology made changes to the row or column. You can use the system stored
procedure sp_showrowreplicainfo (Transact-SQL) to view this metadata.
As the Merge Agent enumerates changes to be applied during synchronization, it compares the metadata
for each row at the Publisher and Subscriber. The Merge Agent uses this metadata to determine if a row
or column has changed at more than one node in the topology, which indicates a potential conflict. After a
conflict is detected, the Merge Agent launches the conflict resolver specified for the article with a conflict
and uses the resolver to determine the conflict winner. The winning row is applied at the Publisher and
Subscriber, and the data from the losing row is written to a conflict table.

Conflicts are resolved automatically and immediately by the Merge Agent unless you have chosen
interactive conflict resolution for the article.

17) What are datatype concerns in transactional replication.

Transactional replication supports publishing LOBs and performs partial updates on LOB columns: if a
LOB column is updated, only the fragment of data changed is replicated, rather than all the data in the
column.

If a published table includes any LOBs, consider using the following Distribution Agent parameters: -
UseOledbStreaming, -OledbStreamThreshold, and -PacketSize. The most straightforward way to set these
parameters is to use the Distribution Agent profile titled Distribution Profile for OLEDB streaming.

The process of replicating text, ntext and image data types in a transactional publication is subject to a
number of considerations. It is recommend that you use the data types varchar(max), nvarchar(max),
varbinary(max) instead of text, ntext, and image data types, respectively.

18) Which all SQL editions provide replication functionality

Business
Feature Name EnterpriseIntelligenceStandardWeb Express
SQL Server change
tracking Yes Yes Yes Yes Yes
Yes (Subscriber Yes (Subscriber
Merge replication Yes Yes Yes only) only)
Transactional Yes (Subscriber Yes (Subscriber
replication Yes Yes Yes only) only)
Yes (Subscriber Yes (Subscriber
Snapshot replication Yes Yes Yes only only)
Heterogeneous
subscribers Yes Yes Yes
Oracle publishing Yes
Peer to Peer
transactional
replication Yes

19) Can we rename a database used in Publication or subscription.

No. we would need to drop the publications, rename the database and re-configure replication all over
again. So there is no easy way to do this.

20) Are logins and passwords replicated?

 No. You could create a DTS\SSIS package to transfer logins and passwords from a Publisher to one or
more Subscribers.

21) Please underline the complications involved in using replication on SQL Cluster.
No special considerations are required because all data is stored on one set of disks on the cluster.
22) Are tables locked during snapshot generation?
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.

23) What recovery model is required on a replicated database?

Replication is not dependent on any particular recovery model. A database can participate in replication
whether it is in simple, bulk-logged, or full. However how data is tracked for replication depends on the
type of replication used.
24) Can the same objects be published in different publications?
Replication supports publishing articles in multiple publications (including republishing data) with the
following restrictions:

 If an article is published in a transactional publication and a merge publication, ensure that the
@published_in_tran_pub property is set to TRUE for the merge article.
 An article cannot be published in both a merge publication and a transactional publication with queued
updating subscriptions.
 Articles included in transactional publications that support updating subscriptions cannot be
republished.
 Transactional replication and unfiltered merge replication support publishing a table in multiple
publications and then subscribing within a single table in the subscription database (commonly
referred to as a roll up scenario). Roll up is often used for aggregating subsets of data from multiple
locations in one table at a central Subscribe
25) 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.
 26) Does replication encrypt data?
No. Replication does not encrypt data that is stored in the database or transferred over the network.

27) What is the effect of running a bulk insert command on a replicated database?
For transactional replication, bulk inserts are tracked and replicated like other inserts. For merge
replication, you must ensure that change tracking metadata is updated properly.

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

29) What is NOT FOR REPLICATION option for table constraints

In some cases, it is desirable for user activity in a replication topology to be treated differently from agent
activity. For example, if a row is inserted by a user at the Publisher and that insert satisfies a check
constraint on the table, it might not be required to enforce the same constraint when the row is inserted
by a replication agent at the Subscriber. The NOT FOR REPLICATION option allows you to specify that the
following database objects are treated differently when a replication agent performs an operation:

 Foreign key constraints : The foreign key constraint is not enforced when a replication agent performs
an insert, update, or delete operation.
 Check constraints : The check constraint is not enforced when a replication agent performs an insert,
update, or delete operation.
 Identity columns : The identity column value is not incremented when a replication agent performs an
insert operation.
 Triggers : The trigger is not executed when a replication agent performs an insert, update, or delete
operation.

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

31) How do I move or rename files for databases involved in replication?

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 on Replication

Replication is a vast topic and there are many questions and scenario based issues which a DBA can face.
So, today’s series of Interview Questions on Replication presents 31 more questions to make you a highly
knowledgeable professional on replication. You can refer to Part I of Interview Questions series on
Replicationhere. Happy learning!

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