We have two situations:
We have a database server that contains tables of job titles. Those job titles need to be accessed by a database on another server. We have established a linked server connection and everything works well. Problems: There can be no foreign key referential integrity because the tables are physically stored on another server. Also, whenever the first server is taken down for maintenance, it breaks the applications on the second server because they are dependent on it for the linked server connection and data.
On another database server, we have a database that is used to hold common data elements. For example, there is a table of US states and territories, a table of zip codes, and various code tables used throughout our applications. Problem: Just like above, there is no referential integrity abilities. In addition, maintaining the security and ensuring that the users who have access to the application database have the necessary access to this "common" database is tedious and time consuming.
My question is: Since this data is read-only for the consuming applications, could we use replication to solve this problem? Could we replicate the one table of job titles from the source to the destination server / database, and could we do the same for the tables in the "common" database (replicate them to any application database that needs them)? I think this would eliminate the above problems, but would it be a wise course of action or would it cause more issues than it solves?