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)
- Backs up the production database
- 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)
- Breaks the user connections to the reporting database
- Performs a restore on the reporting database using the backup file that was copied to the holding directory by the production job.
- Sets some permissions for various users.
- 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