Do I really need a snapshot (to initialize transactional replication, in SQL2000)?

I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

Set up the distributor, publication, and subscription Turn off distribution agent Set the publisher to "sync with backup" Backup the publisher, full then log Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on) Turn off "sync with backup" Restore the full and tran log backups to new subscriber db Create subscriber stored procs in subscriber Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!

Mike

OK, I just did a search and came across this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;320499

However this method still requires a brief time in single user mode (ie killing all connections), whereas my method doesn't. I just don't like that my method involves deleting the MSrepl_ tables...

distributor agent

Win2003srv
mssql2000
mssql2005
Oracle
I have a mssql2000 database that I Replicate to oracle,
some tables have row filtering.
This was working fine until I create an another publication and subscription
(transaction)
on the same database, for mssql2005 This subscription has the
loopback_detection = N'True',
After this, the Oracle subscription stop to work correctly
Tables with row filter are not replicated. In Repl Monitor everything looks
fine
I try to delete all publication and then re-crate it and start snap-shot for
oracle
The bulk copying are doing the right thing regarding filter-rows.
But the distributor agent will not work properly
I can see the log-reader pushing transaction to the distibutor , but nothing
happens
What is wrong her?
-roger
What do you see if you run a sp_browsereplcmds in the distribution database?
The oracle DML should be showing up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roger Nygrd" <roger@.askit.no> wrote in message
news:11uemak27qnmp5d@.corp.supernews.com...
> Win2003srv
> mssql2000
> mssql2005
> Oracle
> I have a mssql2000 database that I Replicate to oracle,
> some tables have row filtering.
> This was working fine until I create an another publication and
> subscription (transaction)
> on the same database, for mssql2005 This subscription has the
> loopback_detection = N'True',
> After this, the Oracle subscription stop to work correctly
> Tables with row filter are not replicated. In Repl Monitor everything
> looks fine
> I try to delete all publication and then re-crate it and start snap-shot
> for oracle
> The bulk copying are doing the right thing regarding filter-rows.
> But the distributor agent will not work properly
> I can see the log-reader pushing transaction to the distibutor , but
> nothing happens
> What is wrong her?
>
> -roger
>
|||It is empty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23w5ntqyKGHA.2036@.TK2MSFTNGP14.phx.gbl...
> What do you see if you run a sp_browsereplcmds in the distribution
> database? The oracle DML should be showing up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roger Nygrd" <roger@.askit.no> wrote in message
> news:11uemak27qnmp5d@.corp.supernews.com...
>

Distribution Server and snapshot

I have a 120GB database I would like to replicate. I would be doing
transactional replication. I would like to know what amount of disk space I
will need for the snapshot and the distribution database.
What effect on performance does having the distribution database on a
production server? Can distribution database be on the other side of a WAN
from the publication database?
What is performance hit difference between syncronous mirroring verus
transactional replication on the primary database server. I have SE 2005
x64 SQL server so I can only do syncronous mirroring.
Thanks,
You will probably need about 120 Gigs for the snapshot. The size of the
distribution database is a function of the amount of data you push through
it on a daily basis, if your subscribers are named or anonymous (anonymous
means much greater storage), and what your latency is.
Mirroring in general consumes less resources than replication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:FCFE4B7B-69DC-4CAC-A5CD-7E1360374782@.microsoft.com...
>I have a 120GB database I would like to replicate. I would be doing
> transactional replication. I would like to know what amount of disk
> space I
> will need for the snapshot and the distribution database.
> What effect on performance does having the distribution database on a
> production server? Can distribution database be on the other side of a WAN
> from the publication database?
> What is performance hit difference between syncronous mirroring verus
> transactional replication on the primary database server. I have SE 2005
> x64 SQL server so I can only do syncronous mirroring.
> Thanks,

distribution database SUSPECT - HELP! HELP!

We have SQL 2000 server replicate to another box.
I just found replication stopped! and distribution database was marked SUSPECT.
I am a SQL server newby, I checked sysdatabases table in master database and found
the status column is 280 and status2 is 1090519040.
Is there any way I can quickly recover this database and make replication moving?
Thanks in advance
David
David,
have a look in BOL for sp_resetstatus. Here is a brief synopsis:
"sp_resetstatus turns off the suspect flag on a database. This procedure
updates the mode and status columns of the named database in sysdatabases.
The SQL Server error log should be consulted and all problems resolved
before running this procedure. Stop and restart SQL Server after executing
sp_resetstatus.
A database can become suspect for several reasons. Possible causes include
denial of access to a database resource by the operating system, and the
unavailability or corruption of one or more database files."
Regards,
Paul Ibison
|||Thank you very much!
|||i m also in same case.
if u know pls let me know
- delwar
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Delwar,
the main reason for this is faulty hardware - drive array etc. Sp_resetstatus will help if the MDF or LDF file for the database is not available during startup. If there's still an issue, start the database in Emergency mode, Update the Status column in
master..sysdatabases table for that database to 32768. After this database will be usable with out transaction log so you can create a new database and use DTS to transfer objects and data (thanks to Hari Prasad for this). DBCC CHECKDB with REPAIR_REBUIL
D can be used if there is still a problem, and after that it's a PSS call.
HTH,
Paul Ibison

Distribution Agent Process

Hi,
Our replication topology (currently in design) will use transactional
replication to replicate from a OLTP server (publisher) to a Reporting server
(subscriber) in hope of near realtime results. Not all of the tables require
this though and the plan would be to use snapshot replication once a day for
these less updated tables. We’re then going to use log shipping to ship
tranaction log files out to remote servers with the databases originating
from the Reporting server.
My question is what does the distribution agent do to the existing
subscriber tables when it applies the new snapshot files? I guess I’m
looking for information as to whether it TRUNCATES the table, DELETES the
data, or DROPS the table prior to importing the BCP files from the new
snapshot.
SQL 2005 SP1 exclusively will be used in this environment.
Any information would be appreciated.
It depends on what option you set in the article property.
The property you should look for is in the article property under the
destination objects and Action if name is in use
The options, are keep data, drop table, truncate and delete filter data.
"dgcull" <dgcull@.discussions.microsoft.com> wrote in message
news:4A2EAA92-0353-4E83-AAC0-546BD5E0C5B1@.microsoft.com...
> Hi,
> Our replication topology (currently in design) will use transactional
> replication to replicate from a OLTP server (publisher) to a Reporting
> server
> (subscriber) in hope of near realtime results. Not all of the tables
> require
> this though and the plan would be to use snapshot replication once a day
> for
> these less updated tables. We're then going to use log shipping to ship
> tranaction log files out to remote servers with the databases originating
> from the Reporting server.
> My question is what does the distribution agent do to the existing
> subscriber tables when it applies the new snapshot files? I guess I'm
> looking for information as to whether it TRUNCATES the table, DELETES the
> data, or DROPS the table prior to importing the BCP files from the new
> snapshot.
> SQL 2005 SP1 exclusively will be used in this environment.
> Any information would be appreciated.
>
|||Gopal is right, but just to add - the default is to drop the existing table
on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .