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 |
|
BLee337
Starting Member
2 Posts |
 Posted - 2006-09-20 : 17:35:37
|
| Hello,I am new to the forums, so bear with me on my first question.We currently have a production installation of SQL Server 2000 Enterprise Edition that has a 40 gig database that is hit heavily. I am setting up another installation of SQL 2000 to host a read only copy of the production database to strictly run reports. The data will need to be updated once a day at night. I am looking at the following options.-Snapshot Replication. This will be done overnight, but I am concerned with how a snapshot will handle a 40 gig DB and the time it will take.-Transaction Replication. Will need to do the initial snapshot, but then only update changes via the TLogs at night? Not sure if you can schedule how often the Tlogs are referenced to change the read only db.-DTS Pakcage. Schedule a DTS package to copy the production DB to the Report Server every night. Again, with 40 gig DB, this is going to take awhile.Does anyone have any thoughts on what method would be better? Or any other suggestions? Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-20 : 17:51:47
|
| Transactional replication does not use transaction logs. But it does do it via a job so you could modify the job to your liking. I would just do BACKUP/RESTORE each night.Tara Kizer |
 |
|
|
BLee337
Starting Member
2 Posts |
Posted - 2006-09-25 : 16:34:23
|
| Tara,Thanks for the response. In your opinion, if I wanted to do the backup/restore option like you mention, would below be ideal?-on source server, create backup job-on target server, create copy job to move over backup file-on target server, create job to restore dbI know I am simplifing the process, so please add anything else I may need to know. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-25 : 16:49:17
|
| Yes that looks good.Tara Kizer |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-10-03 : 03:56:27
|
| transactional replication is how i'll do it sizing for growth of the database in the future and not worrying how large the backup files are going to grow--------------------keeping it simple... |
|
|
|
|
|
|
|
|
SQL 2000 replication or dts issue