|
|
|
Why is a complete snapshot being
generated when a new article is added (SQL 2005)? |
|
|
|
This is expected behaviour if you have a
merge or snapshot publication. If you have a transactional publication,
a snapshot of all articles will always be generated if the
immediate_sync publication property is set to true. Typically, the
immediate_sync publication property is set to true if you allowed
anonymous subscriptions while creating the publication through the
CreatePublication wizard. To prevent the complete snapshot, run the
script below:
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
|
|
|
|
What are the Pros and cons of restarting
the log reader agent? |
|
|
|
Sometimes under extreme high load you will
get deadlocking between the log reader agent and the distribution clean
up agent. In this case stopping the log reader agent to let the
distribution clean up agent do its job will alleviate the problem. It is
recommended that in this case you use a remote distributor. You also can
bounce the log reader agent when you want to switch profiles. (Hilary
Cotter).
If you stop it, the latency will increase and if you stop for a
significant time, the commands' age might exceed the retention period.
Also, the log can't be backed up fully (and therefore truncated) unless
the log reader agent has marked it as read.
|
|
Log reader agent failed and its history
shows message: "No such interface". |
|
|
|
You need to re-register your log reader
agent. Try regsvr32 logread.exe; you might also have to register the
entire contents of C:\Program Files\Microsoft SQL Server\90\Com (Hilary
Cotter)
|
|
|
|
Not all my logreaders start up - what can
I do? |
|
|
|
Increase the max_worker_threads setting in
the syssubsystems table of the msdb database.
|
|
|
|
(SQL 2000/2005) I
receive the error 14100: Specify all
articles when subscribing to a publication using concurrent snapshot
processing |
|
|
|
If you add a new table to an existing
publication using sp_addarticle when you try to subscribe to that newly
added article from an existing subscription, using sp_addsubscription,
the error above may be received. This applies when the existing
publication set up with concurrent snapshot option and means that you
can't synchronize subscriptions for such publications without a complete
resynch. There are 2 unofficial
workarounds: (a) you can circumvent the check by specifying @reserve =
'internal' when you add the subscription for the new article and the
snapshot agent should generate snapshot for the new article after that
and (b) you could change the immediate_sync property in syspublications
to 0 (see sp_changepublication).
Other more official workarounds including
changing the sync_method from 'concurrent' to either 'database snapshot'
(enterprise edition only) and 'native' (which locks table during
snapshot generation). Change the sync_method will force a
reinitialization of all your subscriptions at this point. Alternatively
you could create another publication and use this instead.
|
|
|
|
(SQL 2005) I have a replication setup
which works fine in SQL Server 2000 but when the same setup is used in
SQL Server 2005 I get "Incorrect syntax near ')'" |
|
|
|
In some cases, this is due to a scripting bug in the snapshot agent. It is specific to timestamp
fields that are primary keys. MS has said that they don't have a fix currently, but the work around is to
change the primary key to a non timestamp field.
The problem that you encountered really
has to do with a change of default behaviour from SQL2000 to SQL2005 to
replicate timestamp column as timestamp instead of binary(8), and the
fact that the custom procedure generation logic simply cannot handle the
case where a timestamp column is part of the primary key when it is
replicated as timestamp. Taking a few steps back to look at the
situation at a higher level, transactional replication simply cannot
provide any reasonable behaviour if a timestamp column in a primary key
is replicated as a timestamp column since the timestamp values will be
different between the publisher and the subscriber and transactional
replication is based on the assumption that primary key values are the
same at the publisher and the subscriber. Luckily, you can work around
the problem by disabling the 0x08 schema option (or setting the 'Convert
timestamp to binary' option to true in the article property sheet), this
will basically give you the same behaviour as you got from SQL2000.
|
|
|
|
How to..... truncate the transaction log?
After restoring a database to another server, when I subsequently try to shrink the log I get the
following error: "The log was not truncated because records at the beginning of the log are
pending replication" |
|
|
|
Before truncating the log, you can execute sp_repldone.
In cases where this is not enough, you might have to set up this database as a transactional publisher
before executing sp_repldone, then remove the publication afterwards.
|
|
|
|
How to..... use DBCC OPENTRAN? |
|
|
|
This is my take on the DBCC OPENTRAN:
If your log reader is keeping up with what's in the transaction log, the
'oldest non-distributed LSN' is (0:0:0):
Oldest distributed LSN : (10:384:4)
Oldest non-distributed LSN : (0:0:0)
If it's not able to keep up or is disabled, the output will be in the
following format:
Oldest distributed LSN : (10:388:4)
Oldest non-distributed LSN : (10:390:1)
If you have an open transaction, the format of the output will include a
section at the top:
Oldest active transaction:
SPID (server process ID) : 55
UID (user ID) : 1
Name : mytran
LSN :
(10:391:1)
Start time : May 16 2005 10:10:28:920AM
Replicated Transaction Information:
Oldest distributed LSN
: (10:388:4)
Oldest non-distributed LSN :
(10:390:1)
Using this, you can use dbcc inputbuffer (55) to find the open
transaction'sTSQL, and then decide to kill it if necessary.
The numbers in brackets -(10:388:4) - are
the log sequence numbers. You can run select * from ::fn_dblog(null,null)
to have a look at the list of these, or for more details you can see
them in LogExplorer.
|
|
|
|
How to..... find out which commands are
waiting to be downloaded? |
|
|
|
Use this to get the timestamp of the latest
command to be replicated:
select transaction_timestamp
from subscriberdatabasename..MSreplication_subscriptions
Then run this in the distribution database (replace the value with the
one returned from above:)
sp_browsereplcmds @xact_seqno_start = '0x000000AF00000043000B00000001'
|
|
|
|
How to..... avoid this error "Violation
of Primary Key constraint 'PK__@snapshot_seqnos__{UniqueNumber}'. Cannot
insert duplicate key in object '#{UniqueNumber}'" |
|
|
|
You need to apply a new
patch. There are reports that this step alone is not sufficient, and
because a parameter subscription_seqno is actually type varbinary
(16) and not varchar(16), you need to change the column datatype to
varbinary(16) then it works.
|
|
|
|
How
to.......
tell
which subscriber isn't getting the data? |
|
|
|
Subscribers not performing well:
select agent_id from msdistribution_status
group by agent_id
having
sum(undelivCmdsInDistDB) >1000
order by sum(undelivCmdsInDistDB) desc
Join this against the
msdistribution_agent table to get a list of the subscribers.
Articles generating a lot of activity:
select article_id,
max(undelivcmdsindistdb)
from Msdistribution_status
group by article_id
|
| |
|
|
How
to.......
safely backup transactional replication? |
|
|
|
Have a look in BOL for "Strategies for
Backing Up and Restoring Transactional Replication" and "Backing Up and
Restoring Replication Databases". The key for standard transactional
replication is whether you use 'sync with backup' or not. If you do, you
are ensured that your distribution backup can never get ahead of the
publisher backup (ie no transactions enter the msrepl_commands which
haven't already been backed up on the publisher), and all will be well.
However this will introduce latency (even using log shipping you can
only backup the logs once per minute at the highest frequency, and this
is clearly not ideal). If you don't use this option, after disaster
recovery you'll have to ignore some transactions and treat errors
manually (using -SKIPERRORS). As for the subscriber backups which are to
be restored, this is not usually seen as being so crucial. As long as
they are restored to a time before the distribution restore, then
commands can be sent down by the distribution agent - details in BOL for
this. Alternatively you could of course reinitialize.
|
| |
|
|
How
to....... dynamically
partition data? |
|
|
|
Use a transformable
subscription. Transformable Subscriptions use DTS packages to transform
the data, and a separate package may be created for each subscriber.
When creating the publication, be sure to select 'Show Advanced Options'
and 'Yes, Transform the Data'.
Once the publication is created, create a DTS package to do the
transformation. Due to a peculiar quirk of Enterprise Manager, this
option is available under the publications folder of the published
database, but not the Replication Monitor, Publishers folder. So,
right-click on the publication and select Define Transformation of
Published Data. In this wizard, a Data Driven Query is created. Make
sure this query uses VBScript and modify the script to filter the rows:
if DTSSource("RegionID") = 1 then
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if
|
| |
|
|
How
to.......replicate
transactions? - What happens when a
transaction fails at the publisher - does it still run at the
subscriber? |
|
|
|
If you have a transaction on the publisher,
you may check @@error and then call rollback but whether you rollback or
not, the sp is still executed on the subscriber. This situation is
altered (no subscriber call) if you set the transaction isolation level
to serializable. This is important to do because even if you trap the
same error in the transaction on the subscriber and rollback there, the
error is registered and the distribution agent will fail. SkipErrors
would avoid this problem but ideally the call shouldn't be sent from the
publisher to the subscriber if it has already failed once.
|
| |
|
|
How
to....... read the
transactions for TR in non-binary format? |
|
|
|
These transactions exist in
the transactions table MSrepl_commands: use sp_browsereplcmds to view
them. In the case of a queue, use
sp_replqueuemonitor to read the MSreplication_queue table and sp_browsereplcmds
to look at the compensating commands when there is conflict resolution.
|
| |
|
|
How
to....... have
an update replicated as an update? - When I update a record, the
transactional replication implements a delete followed by an insert on
the subscriber -why? |
|
|
|
This applies to updates of
columns which are part of a unique index and is known as a deferred
update. You'll know when it's happening as the procedure sp_MSupdtablename never
executes on the subscriber, only the sp_MSdeltablename and sp_MSinstablename
procedures (although this could also occur if the snapshot commands tab shows 'NONE' for the update
command!). Implementing trace flag 8207
will replicate the update as an update on the subscriber, but only if it
is a singleton update. See KB Article
302341.
|
| |
|
|
How
to....... replicate users? |
|
|
|
Users, and other such administrative objects
can't be replicated because we can't replicate system tables. If this is
a requirement, you could (a) schedule a DTS package or (b) to initialise
the subscriber before a snapshot - eg to create a filegroup or user-defined
datatype - you could use a pre-script.
|
| |
|
|
How
to....... prevent deletes from going to
the subscriber? |
|
|
|
If you're setting up the publisher then it's
fairly straightforward - just change the command in the table article
properties to NONE. If your publisher is already set up, this will cause
a reinitialisation of all subscribers. If this is just a temporary
measure and you don't want to reinitialise, you could alter the delete
stored procedure on the subscribers to prevent the delete, although this
will obviously result in non-synchronised data.
|
| |
|
|
Error
Messages.......When I create an immediate updating
subscriber, it fails with the error message "Login failed for 'sa'" -
why? |
|
|
|
Subscribers use remote
procedure calls to connect to the publisher. By default, the login is sa
and a blank password, which rarely works! Use sp_link_publication with @security_mode
= 2 to set this up correctly. The other args for this sp are in BOL.
This is now in a MS
article.
|
| |
|
|
Error
Messages.......What does this error
message mean: Could not find stored procedure 'sp_MSupd_Region'? |
|
|
|
If you have used @sync_type = None in
sp_addsubscription or in the subscription wizard, Initialize
subscription page, you select 'no, the subscriber already has the schema
and data' you may get this error. The system tables for replication will
be created on the subscriber in either case but you'll need to manually
create the stored procedures on the subscriber if you use this option
otherwise you'll have the above error when you change a row on the
publisher. Use sp_scriptpublicationcustomprocs (assuming you have >=
sp1) to create a script which creates the procedures and run this script
on the subscriber.
|
| |
|
|
General.......I have a disaster recovery
box and am using standard transactional replication to maintain it from
the production server. When I test it, the identity columns aren't
working - why? |
|
|
|
If you do a nosync initialization, you can
have the column attribute set to Identity - Yes (not for replication)
but the identity value isn't incremented by the replication process and
DBCC CHECKIDENT doesn't always work to reseed these columns. This is one of the reasons
why it may be more appropriate to use queued updating subscribers.
|
| |
|
|
General.......What schema changes
are allowed to a publication? |
|
|
|
(1) Changing the width of
a column: Not allowed - needs reinitialisation or the combination of sp_repladdcolumnsp_repldropcolumn
mentioned in the general section.
(2) Dropping a constraint on the published table - allowed for a check
constraint this can be dropped at the publisher but is not replicated.
(3) creating a new constraint on the published table - allowed but not
replicated
(4) dropping an index on the published table, would it be replicated to
the subscriber - Allowed but not replicated.
(5) creating a new index on a published table, will this be replicated
or an index would need to be created on the Subscriber database -
allowed but not replicated.
For those actions allowed on the publisher but not replicated, you could
use On-Demand Script Execution (or linked servers etc). |
|
|
