Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yacwroy
Starting Member
3 Posts |
 Posted - 2008-02-24 : 18:28:50
|
| HiI am evaluating the possibility of replicating a database over a network to our HQ from the control site (one way). The original database is on SQL server.The database is likely to grow to many terabytes so we would be using transactional replication.The table to be replicated recieves about 500 records per second.The table will probably consist of a record key (8 byte int), site ID (4 byte int), reading (8-byte float), and timestamp (8 byte timestamp). All up, 28 bytes + whatever overhead exists.MINOR DETAIL:The HQ's copy should be preferably no more than 1 hour behind the control site's. This would be a long-term setup that would last for many years. Our link is currently about 2 MB/s, and is fairly reliable.QUESTIONS:I'm guessing that (bytes/record)*(records/second) won't be the whole story. Does anyone have an estimate of the average data efficiency factor for transactional replication?How would I go about calculating how much bandwidth would be needed? Is there a formulae hiding somewhere?Thanks in advance.Simon. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-24 : 19:29:59
|
| If your database is going for 1TB, then its not a good idea to do transaction replication because when you create snapshot for first time it will consume too much time and server might be down. Here is the best solution:If you want automatic failover:Check database mirroring in Enterprise edition as it uses multiple thread in synchronous mode:If you don't want automatic failover:You can do DB mirroring in asynchronous mode.Currently, We have almost 1 TB database which we are doing synchronous database mirroring from chicago to Dallas. it is working fine. We are getting almost 8 MB /sec. |
 |
|
|
yacwroy
Starting Member
3 Posts |
Posted - 2008-02-24 : 23:36:55
|
| OK I just looked up mirroring - this seems very similar to transactional replication as far as I can see."http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx"Synchronous mode would not be desirable, as the network latency is high and bandwidth is to be kept as low as possible, and there is no desire for automatic failover (The data should only flow one way - nothing is generated at head office that needs to be sent back to the control site). The data is for our regional water supply, mostly generated by flow meters and other instrumentation. The primary reason for transferring the data to head office is to allow us to perform queries on large blocks of data (for reporting and other purposes) without taxing the current connection. The head office machine must be able to be queried, and preferably have additional writable tables in the database to store query results (which may need human manipulation if the readings are incorrect). However, this could be done on a secondary database on the same server as long as the primary head-office database is read-only queriable.sodeep: "If your database is going for 1TB, then its not a good idea to do transaction replication because when you create snapshot for first time it will consume too much time and server might be down."I believe we could live with a few hours downtime if it were absolutely necessary for an initial snapshot, however I would have to check on this.A FEW MORE QUESTIONS:From the article: "As a rough guideline, the network bandwidth should be three times the maximum log generation rate."How would I estimate log generation rate based on record size and record generation rate?Would the bandwidth requirements for mirroring be similar to the bandwidth requirements for transactional replication?From the article: "Because the mirror database is in a recovering state, it cannot be accessed directly."This would prevent me performing read-only queries, right?Which is more robust (transactional replication or mirroring)? IE, able to handle down-time and ignore mismatching tables?Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-25 : 10:01:57
|
| I got what you are saying. Here is the facts about DB mirroring:1) Your server won't be down as Transaction replication to create first snapshot which might take 8-10 hours(depends on network bandwidth)2) In mirroring, You are going full and transaction log backup and copying it to Secondary server and of course database in secondary server will be in recovery mode. To access the queries, you can create database snapshot in Secondary Server.3) You can observe the log generation rate and transfer rate in DB mirroring in DB mirroring monitorBut if you are doing bulk insert( inserting millions of rows/day) then DB mirroring is not good idea.But DB mirroing is best solution than Transaction replication. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-25 : 10:07:42
|
| Or check Peer-to- Peer Replication . |
|
|
|
yacwroy
Starting Member
3 Posts |
Posted - 2008-02-26 : 22:35:52
|
| Thanks for the help Sodeep.I don't think snapshots would work as the data all sits in one multi-terabyte table (or possibly a few, not sure yet), and we would want new queries on fresh data a few times an hour. Snapshotting terabytes of data multiple times per hour would not work. From a quick search on google it seems partial snapshots can't be done.If it isn't possible to query a mirrored database without snapshotting it then mirroring wouldn't work for us.If we went with transactional, it would probably be possible to do the initial transfer at the control site (100MB+ network) then quickly drive the server down to head office and install it, which would probably take less than an hour all up. Hopefully no data would be lost, the logs would just back up for a while.If neither mirroring or transactional replication work for us, I guess we could create a script to capture every new record into a new table, export this to file and purge it every 10 mins or so, copy this file to the local server here, and add that to the database here. This should be fairly robust, it would just take some time to set up. |
|
|
|
|
|
|
|
|
Calculating Asynchronous Replication Bandwidth