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 thesp_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 andsp_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.
- The application requires low latency between
the time changes are made at the Publisher and the changes arrive at the
Subscriber.
- 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 theMSmerge_contents system table; if column-level tracking
is enabled for an article, theCOLV1 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 proceduresp_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.
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
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.
