We have a situation where we have multiple databases. Some of the data, however, in each database is reference data that is common to each database. Is there a way to put these tables into a separate .mdf file and have all of the databases access this one .mdf file to see the reference data? Most of the reference data will be read-only, but some of it will be written to occasionally by power users of the app.
Yes you can put the reference data into its own database. You could add a synonym to the other databases so that it thinks the reference data is local. Or you could use a view, or cross the database via code.
No issues as long as the source database is on the same SQL instance. If it's on a remote SQL instance and the synonym uses a linked server, then you've possibly got performance issues due to the linked server.