I am setting up a replication on SQL Server 2008 R2. I have three servers: P1, D1, S1 that each act as the Publisher, Distributor, and Subscriber. On P1, I am replicating 7 databases. Each database has a whole mess of SPs, views, indexed views, tables, and functions that I need to replicate. Initially, my thought was to create one publication that contains all these objects. However, during initial synchronization, it keeps stalling when it tries to replicate an object that contains another object that has not been replicated yet. For example, it could try to replicate a stored procedure that contains a function. But the function has not been replicated yet.
So I ended up creating a publication for each type of object. This way, while synchronizing, if it tries to synchronize an SP with the function, it will keep stalling until the function in the other publication has been replicated. So far it works. It's not exactly how I envisioned it since it will keep erroring out until the function is replicated. But I don't know any other way. Since there are dozens of objects in each type, it will be tough for me to go through each one to determine what uses what.
Is there any other solution? Is there a way to tell SQL Server don't worry about the objects within an object and bypass that error?
Thanks in advance for any suggestions.