I am having this scenario; I have 2 SQL Server 2005 databases, they are basically identical in structure. The first one is used by my web application and the other is by desktop application ... I created the web db for security reasons ,.. i dont want online users to have access to the major backed (desktop db) directly so the web app is writing to the web db ... now, I want to update my major backend as transactions to the web db happen and vice versa to keep the users of both applicaions happy ... Is there a good design patterns for that? I am thinking of using db triggers for this but not sure on the implementation details ...
Your help is much appreciated
Well I would have thought this would have been better managed as a single database with appropriate SQL Server security. Are you more comfy with other applications rather than setting up SQL Server and so decided to manage your security that way?
Anyway - I think you mean you have one db that is written to and also a read only copy and you want to keep the read only copy up to date? Read up on transactional replication in that case. However this would probably be harder to set up (and more difficult to maintain) than a single database with appropriate security.