Re: SQL 2005 --> Replicate Production DB to Development DB

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Terry,

Not sure if I understand your scenario correctly (typically folks will like
to keep their changes at the subscriber), but you can definitely schedule a
T-SQL job to execute sp_reinitsubscription followed by
sp_startpublication_snapshot at the publisher on a daily basis. If your
distribution agent is running continuously, it should pick up the new
snapshot and remove all your subscriber data once the snapshot has been
generated.

-Raymond

"Terry Wahl" <TerryWahl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:70456384-45CC-4C15-A582-1C28E284B60A@xxxxxxxxxxxxxxxx
Hi Raymond,
I have a follow-up question. What I would like to happen is schedule a
task
such that once a day the production database replicates to the development
database. As part of this replication the contents of the development
tables
would equal the contents of the production database.

While testing I have inserted new rows into a table on the development
database. If I manually initiate the replication process I receive a
status
message stating "No replicated transaction are available". The newly
inserted rows still remain in the development database table.

If I first select 'Reinitialize subscription(s)' and then select 'Mark For
Reinialization' in the dialog and then manually initiate the replication
process the inserted rows in the development database table are removed.

How can I configure the publisher to instrut the subscriber to
reinitialize
from the snapshot? I'm surprised that the inserted rows in the
development
table persist after replication.

Thanks again for your help,
Terry




"Raymond Mak [MSFT]" wrote:

Hi Terry,

SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you
described so I would encourage you to check that out. Or, you can work
around the problem by performing a dumming insert that triggers the PK
violation and then run DBCC CHECKIDENT to reseed the identity value.

-Raymond

"Terry Wahl" <TerryWahl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:854D386A-E82D-4948-BC03-47BB3F8E8E15@xxxxxxxxxxxxxxxx
Hi Raymond,
We are using push replication so it looks like I will have to execute
the
command by hand. But I am having problems with the 'DBCC CHECIDENT'
command.
The command that I am running after the data is replicatated is the
following:

DBCC CHECIDENT('dbo.TcmLatestReport', RESEED)

I receive the following message:
Checking identity information: current identity value 'NULL', current
column
value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

The table contains entries so I'm not sure why the command is not
setting
the identiy seed to be the max value of the current value?

Thanks again for your help,
Terry





"Raymond Mak [MSFT]" wrote:

Hi Terry,

Unfortunately the only solution that I can think of is to manually run
DBCC
CHECKIDENT on your subscriber tables with identity columns (you can
simply
put that in a post-snapshot script) to reset the identity seed values
to
be
the max values of the identity columns. While it may be worthwhile for
replication to automatically do this for you, I can think of at least
one
case where doing so is not desirable (rolling up data with different
identity ranges on a central subscriber). In any case, you may want to
log a
feedback item about this @ http://connect.microsoft.com so we can
consider
doing something about this in a future release.

-Raymond

"Terry Wahl" <TerryWahl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DAD169CE-0E70-40FB-97C5-51314DE515F0@xxxxxxxxxxxxxxxx
Replication of the tables is working but I am having an issue when
adding
rows to the replicated tables. There seems to be a problem with
dealing
with
idenity columns. For instance I have a table with three columns one
of
which
is the primary key column. When a new row is added I think the
system
is
generating an already present value for the primary key. I am
receiving a
duplicate key error. Is there a configuration setting that I need
to
set?

Thanks again for your help,
Terry


"Raymond Mak [MSFT]" wrote:

Hi Paul,

Actually SQL2005 snapshot processing for transactional\snapshot
replication
should be able to handle FKs that exist only on the subscriber
reasonably
well. Consider the following .pre file generated from the snapshot
agent
in
SQL2005 when the pre-creation command is 'drop':

SET QUOTED_IDENTIFIER ON



go



if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec
sys.sp_MSdropfkreferencingarticle @destination_object_name =
N'sometable',
@destination_owner_name = N'dbo'



go



drop Table [dbo].[sometable]

go

The magical line here is the call to sp_MSdropfkreferencingarticle
if
sp_MSrestoresavedforeignkeys exists. The
sp_MSdropfkreferencingarticle
system procedure has been substantially enhanced in SQL2005 to save
off
meta-data of FKs referencing a published object that we are about
to
drop
(so we can drop the table a few lines below) and at the end of the
snapshot
delivery session, the distribution agent will call the new
sp_MSrestoresavedforeignkeys procedure to restore the dropped FKs
based
on
meta-data we saved off. Now, it is very possible that I am
misreading
the
whole thing or there are cases not handled by the new mechanism
although
I
will encourage you to test it out :)

-Raymond





"Paul Ibison" <Paul.Ibison@xxxxxxxxxxxxx> wrote in message
news:%23Qe0tjYnHHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
Raymond - I reckon we are talking at cross-purposes, or I might
be
off-track - anyway let me explain my thinking....
In Terri's case there are tables in the Dev environment that
don't
exist
in the Prod environment, so when the other tables are
reinitialized
using
snapshot replication from Prod to Dev, if there are any
relationships
that
exist purely in the Dev environment then there would be FK
issues.
Rgds,
Paul Ibison












.

Relevant Pages

  • Re: Replication over FTP
    ... My publisher, when setting up the replication, ... The subscriber, a pull merge subscription, has all the ... > place over port 1433 except for the application of the snapshot which is ... >> Can I, when setting up the database, export all objects to the server. ...
    (microsoft.public.sqlserver.replication)
  • Merge Replication: invalid column name rowguidcol on adding new articles?!?!?
    ... I've been running Merge replication successfully now for about 6 months. ... Table 'C_ClaimStatus' does not exist in the Subscriber database. ... (Source: MARIAAN\CC (Data source); Error number: 21078) ... The process could not deliver the snapshot to the Subscriber. ...
    (microsoft.public.sqlserver.replication)
  • Re: Schema Changes in Transactional Replication
    ... publisher is the distributor. ... It is a push replication. ... change the schema on the subscriber so it's the ... table, or if I have to recreate the entire snapshot, or re-init the ...
    (microsoft.public.sqlserver.replication)
  • Re: Transactional Replication Advice
    ... use a seperate publication. ... This will only generate a snapshot for the new ... Looking for a SQL Server replication book? ... > subscription from the subscriber. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 --> Replicate Production DB to Development DB
    ... snapshot and remove all your subscriber data once the snapshot has been ... As part of this replication the contents of the development ... process the inserted rows in the development database table are removed. ... SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you ...
    (microsoft.public.sqlserver.replication)