Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have SQL 2008 installed on Win 2008 VM on one hardware which is in subnet of (parent) 192.168.0.0.24 and second site has similar hardware with win2008+SQL2008 in 10.1.1.0/24 (child) and third one is in 172.16.1.0/24.

We want to know how do i enable block level SQL 2008 replication from one site to another and i have ipsec vpn of T1 link.

My objective is -1 day child site database in parent site and is there any way the database can be replicated at block level instead of replicating entire database. There are 3 databases with 15GB, 12GB and 1GB. I can initiate by copying manually and am not sure how to configure to asynchronous replication of the database between child to parent site.

Please help and am zero in SQL 2008

share|improve this question

1 Answer 1

From your question, it is not entirely clear what the child sites are for. Are the child sites for disaster recovery only, are they read-only copies of the parent data or do you need to update the child copies and have the changes make it back into the parent?

SQL Server does not directly supply block level replication of any kind. All of SQL Server's replication features are data-level. You would either be doing full pulls all of the time or sending changes, depending on exactly what you need.

SQL Server has other relevant technologies besides the features that it calls "replication". Those technologies are Database Mirroring, Always-on Availability Groups and Log Shipping. Exactly what you might use depends heavily upon exactly what you are trying to do and your familiarity with SQL. If you are just interested in disaster recovery or maybe read-only copies, the simplest thing to do is probably log shipping. Log shipping has been around the longest, it works on any version of sql server and it's actually pretty simple, under-the-covers.

Normally, block-level replication is handled by smart SAN storage of some kind (Vmax and Symetrix jump to mind first, but there are many other options) or you might use something like DataKeeper's Steeleye, which is software only.

Block-level replication is normally one-way-only, with a primary and a secondary system. I've never seen a system with two secondaries, I am not a storage specialist but I am a SQL guy. Also note that in SQL Server setups that use block level-level replication (via SAN or similar technology), the scheme is really that of a active-passive failover cluster and the "child" copies are typically not available for any use.

Regardless of the method of replication, all of the data will have to be pushed from the parent to the child first, before replication can actually begin. After that, the child sites would only receive changes to the data. You say that you could manually copy the data. Usually, the first copy of the data is done using some wort of management tool that is tied to whatever product you are using. You can't just copy stuff from a command line using the regular windows tools.

A T1 line has a bandwidth of 1.544 mbit/sec. If my math is right, that is about 694.8 MBytes/hour. It will take about 42.27 hours to transfer those three databases the first time. That isn't very fast.

If your data gets many changes, it is very possible that the 1.544 mbit/sec will not be fast enough to keep up. If you do something like run a reindex command, which could change a lot of data in a block-replication scenario (but wouldn't change anything in a data-replication scenario), the replication will almost certainly fall behind. If you are running asynchronously, it is likely that there will be some sort of write buffer (a write queue or write cache) that will accept and hold changes on the parent side until they can be sent to the child side, but the size of the buffer may not be large enough to hold all of your data. Once the buffer fills up, you will effectively be running synchronously or your system will be loosing data (thereby destroying your databases).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.