siugoalie78 avatar image
siugoalie78 asked

ASPState database replication

I have an environment that basically has two primary databases at different locations that replicate with each other in a Peer to Peer topology. We also use an ASPState database to maintain user logins. What my customer would like to see is for a user to be able to log into one site, then also be logged into the other site. I am wondering is using SQL Replication on the ASPState database will accomplish this. I have searched Google for this scenario, and not found anything useful. I have found a few articles on mirroring the ASPState database, but this won't work for us since both ASPState databases need to be read/write. I am basically looking for any pros and cons using replication, and if anybody is currently doing that, please let me know how it works.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

MVDBA avatar image
MVDBA answered
I wouldn't advise this at all. you would have to use merge replication with some form of conflict management to do this. there would then be a latency between the 2 databases (especially under heavy load) merge replication would also require you to add a GUID column to each table replicated which may affect the use of the session tables. (especially since it is not uncommon for ASPState databases to be rebuilt every now and then) you also have some potential issues with session expiry - whatever deletes or expires the session is going to occur at both sites. you would be better trying to get the second site to be able to talk to the main session database... or come up with a clever way of using cookies/browser session to take a copy of the ASPState row and if you move , then that is posted back to your remote site.... may have some complications with that though - especially in security (people modifying their session on the second server)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siugoalie78 avatar image siugoalie78 commented ·
Thanks for the reply. One thought - if we are only talking about 2 ASPState databases, then if I designate one is always the winner with conflicts, would transactional Peer to Peer work? That would not require a GUID column, correct? I understand there would be delete/delete conflicts when expired sessions are cleaned up, but that wouldn't impact the data, and I could set up a job to clear those automatically.
0 Likes 0 ·
MVDBA avatar image MVDBA commented ·
Not entirely sure on the guid. I seem to remember sql uses it as a row identifier, but I could be mistaken
0 Likes 0 ·
siugoalie78 avatar image siugoalie78 MVDBA commented ·
Thanks - I'll have to double check that, I know it requires a primary key, but don't think it is required to be a GUID, so if that's the case, it can just use the existing PK's.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.