SQL 2000 Strategy for keeping Reporting Server Updated with Prod Server Data

[awaria]

SQL Server Enterprise 2000 SP3 | Windows 2003 SP2

I have to build up a reporting server to offload the performance impact when users run reports against the production
server. ERP server where finance group wants reporting server to be updated a couple times during the day so I cannot rely strictly on restoring from the production server's backup from the night before.

I appreciate your recommendations for a strategy to meet this requirement.

Replication, scripting, backup & restore, ??

Thanks,

awaria

 

[gmmastros]

Since you are using SQL2000 Enterprise, I would encourage you to take a look at log shipping.

There is a document here that describes how to do it.

http://www.sqlservercentral.com/Forums/Topic403992-24-2.aspx#bm1085420

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

George,

Thanks for the link.

I will review and hopefully get very familiar with log shipping.

Is the thought that log shipping can replace the need for nightly backup and restores, or to be
used in tandem with the backup/restores to keep data updated during working day?

Thanks again for your suggestion.

awaria

 

[gmmastros]

It can probably replace the nightly backup/restore.

Just to be clear... you should ALWAYS backup for disaster recovery reasons. With log shipping, you shouldn't need to restore to the reporting database.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Log Shipping seems simple on the surface, however, you have to watch or it will break. I have not used sql 2000 log shipping for several years but it will keep the two databases in sync.

I find that when I start log shipping, grabbing a new backup is the easiest way so if there is a problem with log shipping recreating it will resync.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[awaria]

Just found out the destination server is SQL Server 2000 Standard, whereas, the source server is
SQL Server 2000 Enterprise, will that cause a problem?

Also source is on SP3 and destination is on SP4.

Thanks for sharing your expertise.

awaria

 

[awaria]

Question, I have been researching and it seems that folks suggest Log Shipping or Replication.

Can someone elaborate on the difference or benefits of using one over the other for this purpose.

Thanks again,

awaria

 

[awaria]

Attempting to add destination server, but, no servers appear to select.

I registered the destination server in the source server's console.

Could this be due to the destination server is 2000 standard and the primary
SQL server is Enterprise 2000?

Need assistance in getting log shipping setup with destination server.

Thanks,

awaria

 

[djj55]

Enterprise to Standard should not be a problem. Can you type in the server?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!