December 8, 2007 at 7:07 pm
Hi all,
I've never set up replication in any flavor of SQL Server, so I was wondering if this can be done?
We have two servers, one with production databases and the other with reporting/analysis databases. We would like to move the prod databases to the analysis server nightly. The data is about 300GB. We are running SQL 2005 on Win 2003 Enterprise on both servers. Here are my questions:
1. How long would it take to replicate 300GB over the network?
2. Can we somehow move the databases once and then any changes on the nights after?
3. Can we somehow tell SQL Server to replicate at a certain time? If replication goes past the timeframe, what will happen? Will replication stop or fail?
3. If the answer to the above is yes, then should I be looking at replication, mirroring, or something else?
Sorry, I am totally clueless about this area, and I really appreciate your help.
December 9, 2007 at 2:29 pm
I'm not an expert in replication but I'd say that this is achievable.
if replication is what your after then i'd say Transactional replication is what you need. after the initial restore it will keep your secondary uptodate as transactions occur on your primary server.
Look up transactional replication in Books Online it should tell you all you need to know
Gethyn Elliswww.gethynellis.com
December 9, 2007 at 8:40 pm
You need to read up on replication in books online to understand what it is and if it will meet your needs.
With snapshot replication, you'd move the entire database every time, and 300GB might not move easily or in a timely fashion.
With transactional, you'd need to move a snapshot first (not sure if this is possible even in your environment) and then you'd move transactions. So you wouldn't be moving 300GB a night unless you had 300GB of changes.
If you are trying to get new data across, replication can definitely help, but you need to understand it well to make the decision for your environment.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 10, 2007 at 3:10 am
You can choose transactional replication.
1. How long would it take to replicate 300GB over the network? cant say may be between 4-10 hours but againg depend on your environment, ...
because you need to set up the replication and apply the initial snapshot. snapshot can be the lattest backup and restoring it in the subscriber will take time and this will be the saffest method, followed by the Distribution Agent copies any required replication metadata. Again I would say ...Initializing with a backup is the fastest way to deliver data to the Subscriber and is convenient, because any recent backup can be used if it was taken after the publication was enabled for initialization with a backup. Other methods like attach the mdf and ldf to the subs node. Export / import etc . This depend on your network and network traffic.
http://msdn2.microsoft.com/en-us/library/ms151705.aspx
http://msdn2.microsoft.com/en-us/library/ms165720.aspx
"More Green More Oxygen !! Plant a tree today"
December 10, 2007 at 7:49 pm
I would think Log Shipping might be a better choice. It is easy to configure and maintain. With Transaction Replication, you might need to deal with how to replicate new objects, users, logins, etc.
Ed
MCITP DBA
December 11, 2007 at 3:28 am
Hi
As experts here said depending on your environment and requirements you need to decide on the method to move your data. I would prefer Logshipping or even mirroring to replication .
"Keep Trying"
December 11, 2007 at 7:07 am
Re-reading this, I'm wondering what the purpose of your transfer is? Is it going to be accessed as a reporting database? Or is it backing the an SSAS cube? Is it a DR copy?
That will change the recommendation.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 11, 2007 at 12:38 pm
Replication may be an option if you need only a portion of the tables rather than the whole database for reporting analysis.
Transactional replication require primary keys on tables to be published.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply