sql server replication: SUMMARY: I have a table which is about 11GB in size, This table contains 3 fields. One of the fields contains a ntext() data type. The total number of records is about 400,000. This table replicates fine when it is over a very fast gigabit(Gb) network or even a 10/100 link(100 mbs). But once I moved the Database to the datacenter (remote location to where I am now) it seems to not replicate (key word here Is “seems” to not replicate). The link between the two database is a 5 Mbit connection(one at HQ and the other at our DataCenter).

Next:
I did some quick math to see how long a 10GB (Gigabyte) file would take to transfer over a 5Mb(megabit) WAN Pipe. At the very best with our including any tcp/ip over head or connection issues or anything. This size pipe would (at the best transfer and replication rates) take at the very minimum 5 hours to transfer. So Okay …..I said let’s try it.

Well even after 5 hours it would still seem that the replication drop would hang. It would essentially seem like it locked up.

Did a lot of research.
Found out that with large binary objects or char(max) or any type of record that contains large amounts of data. The replication article has to have a large change on how it replicates (See below)

REFERENCE:
=========================================================================================
http://technet.microsoft.com/en-us/library/ms151246.aspx
===========================================================
How to: Initialize a Subscription Manually (SQL Server Management Studio)

Initialize a subscription to a publication manually by copying the schema (and typically data) to the subscription database. The schema and data should match the publication database. Then specify that the subscription does not require schema and data on the Initialize Subscriptions page of the New Subscription Wizard. For more information about accessing this wizard, see How to: Create a Push Subscription (SQL Server Management Studio) and How to: Create a Pull Subscription (SQL Server Management Studio).
When you synchronize the subscription for the first time, the objects and metadata required by replication are copied to the subscription database.

Note: This statements do not involve the Subscribers. Only the host or distributer(publisher)

Shows all properties associated with the Publication/Articles.
sp_helpmergearticle @publication = N'MyDatabase_merge2005new_12hours', @article = N'ProductSignatures'


Now go ahead and change this ………..stream_blob_columns is not an available option using the SQL Enterprise Mangager GUI.
sp_changemergearticle @publication = N'MyDatabase_merge2005new_12hours', @article = N'ProductSignatures',
@property =N'stream_blob_columns' , @value = 'true', @force_invalidate_snapshot = 0, @force_reinit_subscription =0
------------------------------------------------------------------------------------------------
For Example>
PublicationName = MyDatabase_merge2005new_12hours
Table/Article = ProductSignatures
------------------------------------------------------------------------------------------------

Once this is changed …… I also changed the Replication profile on the Subscribers to be (NOTE: This is available in the replication Monitor GUI). Right Click the subscriber(right pane) and select Agent Profile…Create a new user profile and change the –BcpBatchSize from 100000 to 2000. This way it will replicate records in groups of 2000 and possibly update the gui with a status of the job(THE STATUS WAS NEVER UPDATED FOR ME).


Now all the reconfiguration is complete:
Add the subscriber again and wait …..
YOU WILL SEE A MESSAGE IN THE REPLICATON MONITOR FOR THE JOB….
(right click subscriber and view details)
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Well for me….this made me want to restart the job at least 5 different times after 2 or 3 hours. Well finally I decided to check different ways…

So I looked at the Activity Monitor on both servers…This particular query said is was suspended….Sound like an error. But NOT REALLY…….this is simply what a bulk insert operation looks like when monitoring the process ID via the activity monitor……….So ignore this.

My final way to track this large 10 GB transfer was to do this.
1>Start the replication.
Wait for the snapshot to initiate with out any errors….and have the replication subscriber get to the state described above. Note:if you right-click the subscriber at this time you do have an option to start the job… Only stop or restart the job. Do not do any of this, because you will be restarting from scratch.

2> Run a backup of the database.
Without this Table my database is about 9 GB in size. With this table the database double in size to 20 GB.
Once backup is complete…….the large table should probably have no records accept maybe a couple(because we just started the replication). By backing up the database, you ensure it will shrink to a smaller size.
3> Now right-click the properties of the actual database and look at the size of the database. My size of the database
Was about 9 GB to start out with….I then started looking at the screen every several hours to watch it grow. Also notice, you may want to change the growth settings of the database to be growing at about 10% or more. So that it does not have to keep expanding the database so often.

2 hours pass by: 10GB (Gigabyte)
4 hours pass by: 11GB (Gigabyte)
6 hours pass by: 11GB (Gigabyte)
12 hours pass by: 17GB (Gigabyte) …the business reopend the next morning again…
15 hours pass by: 18GB (Gigabyte)
17 hours pass by: 20GB (Gigabyte)…
=================================================================
So total through put for this job was about 602 MB per hour via a 5Mbit wan link.

IMPORTANT: At any point that you can right-click the subscriber and click “Start replication”….it means the replication failed and you may or may not be able to continue….But at least try to “start replication” again to see if it picks back up where it left off.




Why did I have re-replicate. Because our back-up servers are at HQ and for some reasons all my replication jobs were continually running even though they were on regular 5 minute plus schedules….

Notice: If you can any other error messages during the intitial replication process. This would indicate the orginal snapshot or other related replication stuff did not transfer to the partner / subscriber database.

Other reference materials for search engines(via Doug Lubey of Louisiana):
Error messages: