In my project I have two separate DBs on the same server. The DBs are self-sufficient except for three columns in DB "B" that need to be accessed in DB "A".
Are there performance considerations if I were to have a stored proc in A that accessed three columns from B directly?
Currently, we run a nightly job to import the needed data from table B to table A, so that the stored proc isn't going out of the scope of A.
Is that the best method?
Are cross DB stored procs within best practices?
It all depends on the data you are referencing and whether or not indexes are set up to support this direct access. The best thing I can tell you is to create the query, run it, and see if the performance is good enough.
If the performance is not satisfactory then run the query again and have your management tool generate a execution plan so you can identify the bottleneck.
There should be no problem since the databases are on the same server. Usually problems occur when you do this with linked servers and you could run into network latency
Yes, what you're currently doing - i.e. replication - is the "correct" thing to do.
When referencing data in another database, you can't use referential integrity, data constraints and lots of the other good stuff that make an RDBMS a good tool to use.
Accessing the other database direcly ties the databases together - they MUST exist on the same server, for all time. You may run into badwidth issues using linked servers and executing the proc on demand.
Replication gives you far more flexibility.
To clarify the other posters comments.
There is no "direct" negative performance impact when using cross database access via stored procedures. Performance will be determined by the underlying architecture of the individual databases, i.e. indexes available, physical storage locations etc.
This is actually quite a common practice and so long as you follow standard query tuning principals you will be just fine.
We do this all the time. As long as they are on the same server there is no problem. If you have a requirement that the data must be in table a on database a before it can go into table B on database b, you will need to write a trigger to check as foreign key relationships can't be set across databases.