SQL Server Blog

I never really used replication before I joined my current company. We host client databases in our environment and we had a request to replicate a client’s database to them. Our infrastructure group did not want to allow us to replicate directly from production to the client site.

I did some testing and found that I could make a subscriber a publisher as well. After some testing, we installed a new database server on the otherside of the firewall and replicated to this server. From here, I then created another publication and pushed this out to the client.

To my surprise, I was reading about replication and found that it was actually a valid method of replication. I figured I was being a bit of a cowboy with this approach. While I’d rather go direct using VPN, this method is very stable for us

See this link for more details:
http://msdn.microsoft.com/en-us/library/ms152553.aspx

I had a support request recently from one of our clients who wanted was trying to replicate our database to a reporting server. One of our legacy tables contains text data and transactional replication was failing with the error

SQL Server can be configured to allow a maximum size, in bytes, of text or image data which can be replicated. This applies to each insert, update, writetext or updatetext statement issued.

The default is 65536 bytes (64k). In our environment, 262144 bytes (256k) would be sufficient. Here is how to change this setting.

You do not need the advanced options enabled for this change.

sp_configure ‘max text repl size (B)’, 262144
go
reconfigure
go

To view the change, run

sp_configure ‘max text repl size (B)’
go

You should now see the config_value column now display 262144.