We currently have one SQL Server w/ 1 DB that supports the following sites:
* Internal Use (heaviest transactional usage - ~1k users)
* External Use - Clients (low usage - 20k users)
* External Use - Consultants (low usage - over 100k users)
Currently platform is SQL 2k
We are looking to host the Client Server internally and use transactional replication to bring down changes. Requests from the Client Server are going to be fed to the Internal Use server as well as fed into the Client Server - after the processing is complete on the Internal Use server the relevant data will be replicated down to the Client Server and external ID's will be updated appropriately. We were looking to implement the simplest form of replication hence why we are talking about not dealing with two-way replication or merge replication.
Would it be in our best interest to have the new environment remain SQL 2k, or is there enough incentive to look to upgrade to SQL '05? This is a client facing site that will be accessing this DB Server so risk is a big factor when making this decision. Do we carry more risk going with the hybrid environment or by sticking with SQL 2k?
One last question, let's say we have a MEMBER table that has two biography fields, one is a varchar(4000) the other is a text field, let's say there is also a LAST_LOGIN datetime field. The way I understand replication working is the entire row is replicated down (or at least all of the columns that are flagged for replication). If this LAST_LOGIN field changes is the entire row with all of its large contents going to be replicated down, even though just a datetime field changed? Is there an issue with replicating TEXT fields?
I will cross-post this into the replication forum if it exists (never looked for it previously)