Combining transactional and snapshot replication

Hi

I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)

We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)

When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.

If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.

Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?

Or is there a better way of doing this?

I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...

TIACertainly, you can setup two publication - one for those tables with no PK and one for those tables with PK. They will have the same subscriber and destination but the one will be snapshot and one will be transactional. The other option you have is to create a surrogate key for each table without PK. I don't know how feasible it is since your database schema is controlled by the person other than you. You can also consider Log Shipping if the function of the subscriber is read-only.|||Thank you for your quick response. I'll set up two publications as you suggest. For some reason I was hung up on including the PK tables in both publications and I was wondering why I was getting errors... <doh!>

Regarding log shipping, I've had a quick look around for info. It seems that it is not as easy as "standard" replication to set up and keep tabs on. Also, in my environment, although the replicated database will be read only on the target, one of the target SQL boxes will have a database which will capture input from web-connected users. Will this make a difference? Do you think that log shipping will be the better solution for me?

I appreciate your help