Question about sending a large db backup/restore then replicating

OK.
lets say i have a 100gb db. I really just want to replicate 1 table
within that db that is 65gb at like 100,000,000 rows(but who cares, we
will use the entire db for this example). This is a production db that
is constantly being updated. How would I be able to do something like a
no sync or something of that nature on a db that is constantly being
updated?
Wouldn't I need to shut off access of anything entering that db from the
point I started the backup to the point the subscriber does the restore,
and I can then setup the publication with "nosync"?? I guess I could
setup no sync and then manually dts the missing 1 day of information
over some other way and then we can manually sync items from there?
The other option i was thinking about was starting the 100gb snapshot,
disabling the distribution agent from starting to sync. Copy those
..bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
the mean time let the log file for the db grow like crazy since their
are articles marked for replication that cannot be delivered because the
agent is disabled. Then once they do a restore enable the distribution
agent and tell it to skip looking for the snapshot that was once there
and just deliver the transactions that are waiting in the log? Does
that make sense.
The short of this, Is that I am trying to figure out how to replicate a
100gb db without shutting off access to it. This db is updated 24/7.
tia, and sorry to be so long winded
-comb
Ideally you will kick off all of your users and backup and restore, bcp the
data out, or use DTS.
What I do is create a no-sync subscription at lets say midnight. Then I get
my data over, usually using bcp (if you can use snapshot isolation within
SQL 2005), and the deploy it on my subscriber. Then I start up my
distribution agent with the allow data consistency errors and I try to fix
the consistency issues.
Its very difficult but it seems to be the best option.
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
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e3843bad612d2a99896e4@.news.newsreader.co m...
> OK.
> lets say i have a 100gb db. I really just want to replicate 1 table
> within that db that is 65gb at like 100,000,000 rows(but who cares, we
> will use the entire db for this example). This is a production db that
> is constantly being updated. How would I be able to do something like a
> no sync or something of that nature on a db that is constantly being
> updated?
> Wouldn't I need to shut off access of anything entering that db from the
> point I started the backup to the point the subscriber does the restore,
> and I can then setup the publication with "nosync"?? I guess I could
> setup no sync and then manually dts the missing 1 day of information
> over some other way and then we can manually sync items from there?
> The other option i was thinking about was starting the 100gb snapshot,
> disabling the distribution agent from starting to sync. Copy those
> .bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
> the mean time let the log file for the db grow like crazy since their
> are articles marked for replication that cannot be delivered because the
> agent is disabled. Then once they do a restore enable the distribution
> agent and tell it to skip looking for the snapshot that was once there
> and just deliver the transactions that are waiting in the log? Does
> that make sense.
> The short of this, Is that I am trying to figure out how to replicate a
> 100gb db without shutting off access to it. This db is updated 24/7.
> tia, and sorry to be so long winded
> -comb

Question about locks

In short, we are sending several statements to the database from one source
(my application). When this application is running nobody else will be
accessing the database.
If I do a table lock isnâ't it still releasing that lock after each statement?
I ask because each update I send to the database will be effecting only one
row at a time so I donâ't think I would get performance gains because I lock
the table for one record update then lock it again etc etc. The manager
itself could most likely do that faster anyway.What do you mean by 'doing a table lock'? Do you mean using a lock hint?
Which hint? What is the statement that is affecting the data?
Have you started an explicit transaction with BEGIN TRAN?
We need to see exactly what you are doing to lock the table to be able to
answer your questions, but in general, it is better to let SQL Server's lock
manager handle all the necessary locking automatically.
Why are you updating only one row at a time? If you are interested in
performance, you might consider figuring out a way to process all the rows
at once. That's what a relational database is good at.
--
HTH
Kalen Delaney, SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:5C5D6009-F702-4100-BCE1-F194E3695DAC@.microsoft.com...
> In short, we are sending several statements to the database from one
> source
> (my application). When this application is running nobody else will be
> accessing the database.
> If I do a table lock isn't it still releasing that lock after each
> statement?
> I ask because each update I send to the database will be effecting only
> one
> row at a time so I don't think I would get performance gains because I
> lock
> the table for one record update then lock it again etc etc. The manager
> itself could most likely do that faster anyway.
>sql