Unanswered: Transactional Replication on an "unusual" Database
I have set up replication with in house stuff however I am now trying to set up replication with a third party db and I am struggling. Rather than keep thrashing about I hoped that you guys might be able to let me know if these issues can be overcome or that I am stuffed.
I am extending a dedicated, read only reporting SQL Server. Although read only the data needs to be more or less up to the minute (a minute or so delay is fine but not much more). As such I am using transactional replication with no updating by the subscriber.
Problem #1 - I thought I had read that when using transactional replication the published articles needed to have primary keys only if the subscriber would be updating the data however I think I must have imagined that since popping back into BoL does not confirm this. Most of the tables in the database I am trying to replicate do not have primary keys although they do have unique indexes defined (they even name them primary key and alternate key). I am considering going to the vendor and asking if they can explicitly define their primary keys as primary keys though I don't know how much joy I will get. To my knowledge this would be a hassle for them but should affect nothing functionally. Do I have any alternative to this? For example I beleive I could create indexed views and replicate these but it really does not seem an attractive path.
Problem #2 - the application is constantly being developed and they send us monthly updates. Because I can't control the content or the regularity of these DDL changes I think I am going to have to spend a lot of time disabling the publications, applying the update and then reapplying replication. Is there a better, quicker way e.g. a script that can do this automatically? I currently use EM as it really looked several fold easier than any other method. In a perfect world I would prefer not to start using OSQL and system sprocs for this but if needs must....