I'm researching SQL Server Transactional Replication. We are developing three internal applications where each application will have its own database. We have a need to allow the same user access the different applications.
We are thinking of having a User database that is separate from the application database so we are not duplicating user data in three different databases. We would like the User database to be the publisher and the three application databases to be the subscriber in the replication process. We think the publisher database will push newly registered users to the subscribers so we are replicating user data (inserts/updates/deletes).
Will the publishing of user from publisher to subscribers happen within a transaction? Does the transaction piece of transactional replication guarantee that the push from publisher to subscriber(s) completes successfully?
The net result of registration will be a token which contains the user's GUID (among other data points). The token, created after registration completes, is returned to the calling application so the user can continue with its journey with the target application.
The target application will unpack the token and use the user's GUID to make sure the user has access to the application. So, the replication process has to push the user's GUID to subscriber databases so the user's GUID is available once the target app unpacks the token and checks the existence of the user's GUID in the corresponding application database.
Are we looking at this correctly? Any guidance on this approach is greatly appreciated. Thanks.
Would it not be easier to point all 3 applications at the same user database and have all 3 applications insert/update the same table(s)?
But, assuming you need them separate, and to answer your question...
Transactional replication is in effect, just a log replay at the subscriber on the articles being published, and its fast. We utilize replication a great deal for ETL/Warehouse/Reporting purposes at my job, and we replicate tens of millions of rows of data per day, in near real-time. There is latency, but its only a second or two.
What you have described definitely sounds doable given the information at hand. Once you insert a new user, it would appear in the subscriber databases almost immediately after the transaction is committed.
From MSDN Transactional Replication:
"The Log Reader Agent runs at the Distributor; it typically runs continuously, but can also run according to a schedule you establish. When executing, the Log Reader Agent first reads the publication transaction log (the same database log used for transaction tracking and recovery during regular SQL Server Database Engine operations) and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data in transactions that have been marked for replication. Next, the agent copies those transactions in batches to the distribution database at the Distributor. The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database."
I think it's important to mention merge (bi-directional) replication here also.
You cannot update the subscriber data in transactional (one-way) replication. Doing so breaks the replication, and the subscription usually must be re-initialized to fix it. Merge replication is bi-directional, where the subscriber is update-able, and those updates are propagated to the publisher and other subscribers.
Merge replication is more complex to configure, and in my experience more difficult to maintain, however it would give you the flexibility to have each of your applications insert/update their own copy of the user database and have those changes flow to the other user databases.