I have two databases in same instance of SQL Server 2005, configured for Transactional replication. When data is changed, the changes are replicated properly in both databases. For the new created tables, the table is not created automatically in another replicated database. Even it does not allow changing the structure of exiting table. Can you please let me know how I can manage this issue? The changes in database objects are very regular in our system, which needs to be replicated frequently.
Just as you had to add the tables to replication initially, so you have to add new tables to the existing publication. Replication is granular in nature. That is, you can choose to publish any subset of them while leaving replication blissfully unaware of the others. So, when you add a new table, you have to explicitly choose to add that table to any publications that you want it added to. I've found the easiest way to manage that is through calls to the sp_addarticle and sp_addsubscription stored procedures.