PaulB » Databases are handled by SQL Server instances.
PaulB » SQL Server instances are handled by Operating System
This I know. I am new to multiple instances on one server at a production level. In the past I simply created new DBs on the same instance and, since clients were usually paying, there was one physical server pro instance.
My question is whether 2 DBs on one instance is a better use of resources than 1 DB per instance. Both scenarios would be on one physical server. This is why I'm looking to draw on people's experience here.
On the one hand, separate instances allow one to allocate different levels of system resources (RAM and numbers of cores, for example) as well as making the logical separation of the two DBs easier. The two DBs are owned by different companies and are bound by transactional replication. When it comes to putting the DBs onto their own individual physical servers, separate instances will ease the work to be done. The publisher is a complete subset of the subscriber.
On the other hand, SQL Server manages its resources quite efficiently and fewer system resources would be used up with a 1-instance-2DB setup. There are fewer services, to start off with. I imagine that replication would be faster too, when it's simply inside the one instance.
BTW, I think that I've found the answer to question number 1. Snapshots are not necessary for transactional replication. I just have to get around the seemingly wanton IDENTITY_INSERT error messages. Some of the publisher tables refuse the distributor with this error while others don't. I have to go read up BoL and have a look at the free chapter in Hilary Cotter's book to see if there is anything there to help me overcome this next hurdle.
Many thanks for your reply