SQL 2000 backup and reporting server

[garyradford]

Can anybody tell me if the following will work?

We have 2 new servers, both identical hardware and running windows server 2003 with SQL server 2000.

We need the primary server to be used as the application server and the secondary to serve as a reporting server aswell as a standby server for the primary.

First thoughts were to use log shipping but as this locks the database during restore it would prevent the secondary being the reporting server.

This brings me to replication. I am concerned about the administration on this and ensuring the data is up to date.

Now for your opinion. As the SQL app will only be used during the day, could I script a full back up on the primary , restore to the secondary at night and use replication during the day to keep the reporting server up to date?

If this will not work, any other ideas would be welcomed and appreciated.

 

[Catadmin]

Use log shipping, but don't use the built in version. See MRDenny's FAQ: faq962-5754 for more details. With log shipping, you have to do manually intervention in case of switching standby to primary regardless of whether you use the packaged version or Denny's version, so you might as well use Denny's stuff and have your reporting server too.

The other option (and not necessarily the best as it will slow down your production machine) is to use transactional replication to the other machine. Replication in 2000 is okay, but it's not the best. Constantly breaks over the littlest thing. On the other hand, if the data is good, stays good and nothing happens to the hardware, it can run forever without needing human intervention.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[garyradford]

Thanks Catadmin for the reply.

Denny's FAQ looks like it will help us get to where we want to go but looking through it, the script will log users off the backup server acting as the reporting server.

If this is the case, will the report being generated continue after the restore or will the user need to restart their job?

Thanks again for your input.

 

[Catadmin]

They will probably need to restart their jobs. So you might want to do this log shipping on a schedule and let them know when is the best time for them to run reports.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.