September 8, 2010 at 4:38 pm
Hi all,
i am trying to fugure out what will be the best option to create something like a snapshot database for a reporting purposes.
The problem we currently have is that the original database is 250GB in size and this db updates nightly with 100,000,000 records.
i tried to use replication but it did not worked.
is there another options available to create a snapshot for this database on another server so this snapshot will be used for reports?
September 8, 2010 at 5:40 pm
Not sure what you mean when you say replication didn't work. That is definitely one of the options for creating a reporting server.
Other options are:
o Database Mirroring (Enterprise Edition Only), since you cannot create a Database Snapshot on Standard Edition.
o Backup/Restore - simple one, backup the database and restore on other system on a schedule.
o Log Shipping - a bit more complex because you have to put the database in STANDBY for use, then take it out of standby - apply the logs since you put it in standby, and then put it back in standby for regular use.
It really depends on what the requirements are - how old can the data be?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 8, 2010 at 5:47 pm
Thanks for reply,
By saying replication is not working i mean that distribution db has more than 100 million commands nightly and it takes forever to distribute the commands to the subscriber and the distribution agent hangs and we have all kind of blocks between distribution cleanup job and log reader job.
The data has to be recent.
September 8, 2010 at 7:01 pm
What is recent? Current data, one day - one week?
If you need current data, you have one choice - really. That would be replication - and you already know about the issues with that method.
Any other method is going to have some lag - where the reporting database is behind the live database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply