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
>
>