July 2, 2007 at 5:30 pm
I just started a new job and want to create a duplicate copy of a database on our SQL 2000 standard server. I've have used MySQL and other desktop db's for years but am no db admin so I am learning as I go. The db is question is for our ERP app, I want a mirror copy of the db on the same server for users to query without causing problems for the production db. The db can be 24 hours old, it does not need to be live data. I cannot tell which way is the better method for our situation. Creating a dts package to make a copy seems easy but as the database grows I'm not sure that is the best approach.
The db is about currently is about 4gb and contains 2 years of data, I'm assuming 2GB/yr, and after 5 years we will start purging detail. Every night there is a 5 hour window of idle time, so there is plenty of time for a scheduled job to run.
Just looking for some advice on the best way to go
Thanks
Mike
July 2, 2007 at 5:54 pm
If it can be yesterday's data, replication and dts copies are probably not the most efficient, or easiest to manage, alternatives. Why not just take the backup from the primary database and restore it to the reporting environment once a day?
July 2, 2007 at 7:06 pm
since it can be 24 hrs old u can go for Database snapshot if u can afford for sql 2005..but cant modify db snapshot it can be used only for reporting purposes......in sql 2000 u can perform backup and restore once a day in another server.......
[font="Verdana"]- Deepak[/font]
July 2, 2007 at 11:15 pm
Replication can be a good alternative you can have snapshot replication implemented since you say its just 4GB database. Snapshot replication is almost like your full database backup and restore feature only so this can be implemented but rmember to schedule it only in the night after your production hours.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 3, 2007 at 10:26 am
Thanks for the replies
I didn't think about the backup/restore option, thanks for the idea. I do have funds approved to upgrade to 2005 Standard but that will tie into an upcoming upgrade to our ERP app. Right now I must say Replication sounds like the best long term solution. I figure the db will grow to at least 10 Gb, possibly 20 Gb, and people may someday want access to fresher data. Seems like there would be so less data would need to be moved around. Are there any problems or limitations with a SQL 2000 server replicating to itself?
Mike
July 4, 2007 at 1:32 am
With proper planning there will be no problems with replication. But if you estimate the database will grow for 20GB think twice before implementing snapshot replication. Once you upgrade to 2005 use the idea of database snapshots as they occupy less space in disk and are as efficient as any user databases.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply