On this page, I read:
In the OPEN SYMMETRIC KEY topic in BOL (under Remarks section) we documented that the opened key is bound to the session, not to the execution context (including a module frame) and that it will remain opened until the key is explicitly closed (using CLOSE SYMMETRIC KEY) or the session is terminated. This is indeed the designed behavior.
I am planning for a load-balanced application and would like to know if in a scale-out scenario where there are multiple web servers and database servers, does the above still remain true?
Will the above work with ScaleOut State Server?
SQL Server is a very stubborn (reliable) product. If something is session bound what gives you the impression that using StateServer (a non MS product that looks very interesting but it is not a MS product) that it will give you access to change accessibility of SQL Server session objects. You would have to write a lot of code, via CLR/Powershell/SMO, to hook the APIs from your SQL Server session to StateServer and back. StateServer looks new and you would be a very brave (foolhardy) person to attempt this as State Servers beta developer. When you attempt something like StateServer you have to go to great lengths to maintain ACID (if they do) transactions. SQL Server does. I would not have the courage to suggest such a solution (unless I successfully tested it) to a client. By the way MS has a tool in 2014 called Hekaton that has great performance and puts tables/indexes/code in memory and maintains transactions. However, unlike StateServer, it is not a distributed tool