Replication of a large database

Hi, all!
I will greatly appreciate if you share your experience of the replication of
large database.
Our production database is over 100 GB. There is transactional replication
of all the tables of the database to another server. The replicated database
on the subscriber is used as failover and for running reports. We run almost
24x7 environment.
There are some database schema changes coming with a new application version
which cause dropping and recreating the publication.
1.Can transactional replication of the large database be settled completely
online with the concurrent snapshot option? Will it cause great amount of
transactions waiting to be delivered? The production database is heavily used.
2.Last time the initial synchronization was running 8 hours. No sync
initialization could be a solution but I found information that it raises
errors creating stored procedures on the subscriber with SP4, and I have SP3
on the publisher and SP4 on the subscriber. Is it better idea to divide
publication of the large database logically in several smaller articles?
3.Because the database is large, I am going to implement partitioning using
partitioned views for historical data archiving. Do you know some tips
concerning transactional replication of partitioned views?
1) Yes it can be done, but I would not recommend it. I would try a backup
restore with a no sync option. Then you have to fix all triggers,
constraints, identity columns, etc.
2) Can you elaborate on this problem? I have not run into it.
3) Indexed Views can be replicated as views or tables. DPV's would typically
reside on your main server. To get them to work correctly you must implement
check constraints, so you would have to make sure that the check constraints
were applied on the subscriber(s).
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
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:DC26B8CB-34EB-4FCC-A258-DCD7D3064EF7@.microsoft.com...
> Hi, all!
> I will greatly appreciate if you share your experience of the replication
of
> large database.
> Our production database is over 100 GB. There is transactional replication
> of all the tables of the database to another server. The replicated
database
> on the subscriber is used as failover and for running reports. We run
almost
> 24x7 environment.
> There are some database schema changes coming with a new application
version
> which cause dropping and recreating the publication.
> 1.Can transactional replication of the large database be settled
completely
> online with the concurrent snapshot option? Will it cause great amount of
> transactions waiting to be delivered? The production database is heavily
used.
> 2.Last time the initial synchronization was running 8 hours. No sync
> initialization could be a solution but I found information that it raises
> errors creating stored procedures on the subscriber with SP4, and I have
SP3
> on the publisher and SP4 on the subscriber. Is it better idea to divide
> publication of the large database logically in several smaller articles?
> 3.Because the database is large, I am going to implement partitioning
using
> partitioned views for historical data archiving. Do you know some tips
> concerning transactional replication of partitioned views?
|||Thanks for reply, Hilary
2) I am afraid that No sync initialization can cause more problems which can
take longer time to fix
(http://support.microsoft.com/default...b;en-us;827175) than just
initial syncronization.
Also I wonderif it is reasonable to create several small publications on the
logical basis instead of creating one publication of all the tables of the
database. And then if necessary recreate small publication if database schema
changes.
"Hilary Cotter" wrote:

> 1) Yes it can be done, but I would not recommend it. I would try a backup
> restore with a no sync option. Then you have to fix all triggers,
> constraints, identity columns, etc.
> 2) Can you elaborate on this problem? I have not run into it.
> 3) Indexed Views can be replicated as views or tables. DPV's would typically
> reside on your main server. To get them to work correctly you must implement
> check constraints, so you would have to make sure that the check constraints
> were applied on the subscriber(s).
> --
> 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
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:DC26B8CB-34EB-4FCC-A258-DCD7D3064EF7@.microsoft.com...
> of
> database
> almost
> version
> completely
> used.
> SP3
> using
>
>

Replication internals...

Hi,
I need to answer a question for management and I have little replication
knowledge. I'd appreciate any input here.
What happens to the dbs on either side i.e. dist and subscriber, when in the
middle of the operation, one of the systems crashes? vs. what happens when
you click the stop sync button? How do you recover both in the event of the
former assuming the stop synch does not separate, but controls, parent from
children records.
Thanks much!
replication stored the last recorded transaction in the meta data tables on
the subscriber and the publisher\distributor.
When the system comes back up the data will be replayed to the Subscriber
provided it has not being cleaned up on the publisher\distributor.
Transactions are applied on the subscriber within a transactional context.
If you are replicating data which is part of a pk-fk relationship these
commands will be replicated as past of a transaction - unless you are not
enforcing the relationship for replication.
So it is very much like the ACID properties you see when you apply the
transaction on the publisher.
If the distribution database is corrupted its all bets off, as it holds the
transactions to be applied on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JimMac" <JimMac@.discussions.microsoft.com> wrote in message
news:5BF14F6D-AB6E-4CB6-AC8E-125F43FFE652@.microsoft.com...
> Hi,
> I need to answer a question for management and I have little replication
> knowledge. I'd appreciate any input here.
> What happens to the dbs on either side i.e. dist and subscriber, when in
the
> middle of the operation, one of the systems crashes? vs. what happens
when
> you click the stop sync button? How do you recover both in the event of
the
> former assuming the stop synch does not separate, but controls, parent
from
> children records.
> Thanks much!