January 7, 2004 at 8:31 am
Hello all, I am a SQL newbie, and would like to ask for your input on a project we want to take on.
We want to have live replication of our production SQL DB on a server sitting in our office in Prague. The size of the DB is 14gb and is heavily used during the day.
How feasible is this? what do you suggest the best approach would be?
Any input would be appreciated.
Thanks in advance.
January 8, 2004 at 6:18 am
"Live Replication" with SQL Server is performed through the use of Replication and Publications. There are different types, but the type you are looking for would be "Transactional Replication".
The easiest way to put this in place would be to use the SQL Server Enterprise Manager, establish the master database as the "Publisher", and set up a Transactional Replication agent to to the offsite database. EM can walk you through this procedure. It involves establishing the master server as the "Publisher", and the offsite machine as a "Subscriber". Both can be handled through EM from your location so long as both servers are registered with EM. Click the SERVER where the database to be replicated resides, select the "replicate data" icon, then select the "configure replication". After that is complete, Repeat, but select "create or manage a publication" instead of "replicate data". The "replicate data" and "create/manage" steps will be repeated on the offsite server. Once running, a "snapshot" is taken of the master so that a mirror copy can be placed on the offsite database, then transactions will be applied.
This type of replication will require a timestamp column be placed at the end of a row, where the table in that database does not have a primary key. All tables must have a primary key (not just a "unique index") in order for replication to function.
If Bi-directional replication is required, use Merge Replication instead of Transactional. This will keep changes made in both locations up to date.
Note one thing however, whichever method you choose, WAN performance is certainly not the same as LAN performance. You will see latency between transactions applied over a WAN. The reason is simple - 1.5MB (T1/E1 speeds) vs 100MB or higher LAN speed - bandwidth counts.
Lastly, Snapshot Replication would not be suggested as this just makes a copy of the current database. Given its size, replicating all of that everyday is just a waste of bandwidth. You will have to do this once (at the beginning), but transactional or merge replication will only send updates to data, not the full database.
-- Joe
-- Joe
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply