We're customizing a CMS that has a few hundred tables in it. We are considering moving our custom tables to a separate database on the same server.
Should we expect much of a slow down in SQL Server? There will be many multi-join stored procedures joining data across the CMS database and our custom database.
should not make an impact......however you can also use a schema instead and place all your tables under that schema in the same database. This way all your stuff will be logically grouped together
I would say it depends if the filegroups are the different. If they are the different, you might see the queries speed up (see SQLMenace's comment below).
My current place of employment has 3 databases that all communicate with each other on the same server, and there is no noticeable lag.