Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
 Posted - 2008-07-21 : 19:30:05
|
| Hi all, We have our large-ish (~390Gb) SQL2000 database maintained by a certain global IT provider. We are implementing an SQL 2005 data warehouse soon which will be replicated from the production database. They have asked for a 5 day outage to implement this and it sounds excessive to me. How long should it take if one knew what one was doing?Cheers,Tim |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-21 : 19:34:40
|
| No (or very little) downtime should be required. The IT provider should run a full backup, copy/restore it to the subscriber. While that is being completed, copy the transaction log backups to the subscriber. Once the full backup has been resotred, restore all of the tlogs on the subscriber. Once publisher's data matches subscriber's, setup replication and specify that the data is already in sync. A tool such as SQL Litespeed should be used to compress the backup files. You could alternatively use winzip or similar after the full backup has been completed, but that adds extra time to the process. SQL Litespeed compresses as the backup is being taken.I would not recommend running a replication snapshot on a database that size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2008-07-21 : 21:19:08
|
| Thanks again for your assistance Tara.Cheers,Tim |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-21 : 21:23:07
|
| Actually I don't recommend to use Transactional replication for 390 GB database. Well you can Replicate only needed tables so that your performance won't be affected. Remember your bandwidth should really good to handle this:Check this out to setup Transactional Replication from Backup Restore:http://msdn.microsoft.com/en-us/library/ms147834.aspx |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-21 : 22:19:45
|
| The issue is not db size but transaction rate and amount of data be replicated. |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-11-17 : 17:11:15
|
| Tara:How does one do this part "specify that the data is already in sync"?...Once publisher's data matches subscriber's, setup replication and specify that the data is already in sync |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-17 : 17:31:18
|
| You have to choose sync type as 'initialize from backup' while setting up subscriber and that will be last transaction log backup and It will be in sync with publisher. |
|
|
|
|
|
|
How long to implement replication