Sql-server – Is selecting from the same server but different database slower than the same database

The replication topology that you are describing is known as the Central Subscriber Model which is comprised of two or more Publishers replicating to one Subscriber. The dilemma you are having is how to go about initializing the Central Subscriber, which is a common conundrum.

I have talked about this in detail in my post Central Subscriber Model Explained and have found through experience that the best way to initialize a Central Subscriber is with a snapshot, utilizing horizontal partitioning, static row filters, and setting the Action if name is in use article properties to Delete data. If article has a row filter, delete only data that matches the filter.

If you have anymore questions please let me know. I hope this helps.

If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague).

I typically advocate for separate databases, though, for a variety of reasons:

• You seem to be separating on things that are functionally different - there is no real advantage to containing data for logically separate apps in the same database, whether you use schema separation or not - unless they truly do share the same copy of the data.
• Easier to split out a database onto a separate server if it scales faster or outgrows the server it's on. Even with separate schemas it will be tedious to move just one app's data out of that database; if they start in their own database, it's as simple as backup, restore on the other server, and change the connection string for that app. If you have one database and you outgrow the server, you have to move the whole thing. And then again next time.
• Easier to manage recovery separately - you may want use full recovery and frequent log backups for your source control, but don't need such rigor for the database that holds employees' favorite Power Ranger characters. You can also avoid having to restore all applications to the same point in time if you have to revert on e of the databases for some reason.
• It sounds like more work to manage this, but really, you can make it so that managing 2 databases is not really any different from managing 2,000 databases.

In a previous life I managed a system with many databases; they were one per client with (largely) identical tables/views/procedures. I've answered multiple questions here based on my experience and knowledge through that architecture (including some drawbacks to be aware of in the first answer):

• Is that a good idea to use one database for 50.000+ shops?
• Handling growing number of Tenants in Multi-tenant Database Architecture
• Automation of backups of Large Number of Database
• At what point does one database per client become unfeasible?

I have yet to hear anyone argue successfully for storing different data (different apps, different customers, etc.) in the same database. I think anyone who chooses to do this is either confident that their data will never outgrow that server, or is focusing on the near term goal of simplicity & minimal effort (which can be quite short-sighted, and bite you in the long run).