Friday, March 9, 2012

Need Opinions on creating a reporting database more efficiently

Situation:

SQL Server 2000.

At my new employer they have a production database on one server and a copy of it that is set to read only on another server which is used for reporting.

#1

They have an SQL Server Agent job on the production server that: (2 times a day)

  1. Backs up the production database
  2. Copies the backup file to a directory on the reporting server. (Its pretty big and can take time if there are problems with the LAN)

#2

They have an SQL Server Agent job on the Reporting server that: (scheduled to run 2 hours or so after the job on server 1 has run…they figured that it would be a safe bet that the backup and copy process of the first job would be done by then)

  1. Breaks the user connections to the reporting database
  2. Performs a restore on the reporting database using the backup file that was copied to the holding directory by the production job.
  3. Sets some permissions for various users.
  4. Sets the reporting database to READ ONLY.

What I would like to do is find a more efficient way to create this reporting database, I have started doing research into DTS methods but would like some opinions from more experienced users.

Thank You,

Wade

What are you requirements?

Right now there is a big delay, is that problem? Do you need more realtime access? Have you looked at replication? Is it a problem that you have to kick users out? etc etc

No comments:

Post a Comment