Re: Transactional Replication Large Database
- From: "geekyguy" <geeky@xxxxxxx>
- Date: Sat, 25 Apr 2009 15:58:04 -0400
"xAvailx" <bjzamora@xxxxxxxxxxx> wrote in message news:592e5e40-61b9-4306-a186-6e4f917fb164@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,
Sql Server 2005
I am not a replication expert but have set it up a few times. Now I
have a very large database 150GB, with 3500+ tables that needs to be
replicated. Data will be used for reporting and is an SAP database.
The current setup is for a test environment, but the publication setup
is taken so far about 24 hrs and it still is only about 1/3 way
through (I can tell by running profiler and checking what table it is
setting up articles). The connection between publisher/subscriber is
on 1GB, but i don't think this matters yet as it is still creating the
scripts.
I am looking for tips/articles on how to get this setup quicker, it
looks like the tables are locked up. I've read a little about using no-
sync, but there seems to be a lot script changes that need to take
place and we have over 3500+ tables, I am concerned this will take
longer that if I just let SSMS do its thing.
Looking for advice/articles/best practices when replicating large
databases.
Any help is appreciated.
Hi,
Sql Server 2005
I am not a replication expert but have set it up a few times. Now I
have a very large database 150GB, with 3500+ tables that needs to be
replicated. Data will be used for reporting and is an SAP database.
The current setup is for a test environment, but the publication setup
is taken so far about 24 hrs and it still is only about 1/3 way
through (I can tell by running profiler and checking what table it is
setting up articles). The connection between publisher/subscriber is
on 1GB, but i don't think this matters yet as it is still creating the
scripts.
I am looking for tips/articles on how to get this setup quicker, it
looks like the tables are locked up. I've read a little about using no-
sync, but there seems to be a lot script changes that need to take
place and we have over 3500+ tables, I am concerned this will take
longer that if I just let SSMS do its thing.
Looking for advice/articles/best practices when replicating large
databases.
Any help is appreciated.
You haven't given much info about your hardware config, but having just done this myself...
I have my primary dB doing a full backup every 24 hrs and differentials every hour. I copied the most recent full backup and most recent differentials to the secondary server, then restored the dB on the secondary server in "no recovery" mode (if you're using the GUI, this is the second option for "recovery state"), then restored the differentials one by one with the same settings to get it up to date.
Then configure your tlog shipping as normal...this method takes the backup and restore process out of real-time and lets you do it at your "leisure"<g>
.
- References:
- Transactional Replication Large Database
- From: xAvailx
- Transactional Replication Large Database
- Prev by Date: RE: Transactional Replication Large Database
- Next by Date: one table in two merge publication with different filter
- Previous by thread: RE: Transactional Replication Large Database
- Next by thread: report shows logshipping out of sync, but event viewer shows retore?
- Index(es):
Relevant Pages
|
Loading
