{"action":"created","issue":{"url":"https://api.github.com/repos/dotnet/orleans/issues/255","labels_url":"https://api.github.com/repos/dotnet/orleans/issues/255/labels{/name}","comments_url":"https://api.github.com/repos/dotnet/orleans/issues/255/comments","events_url":"https://api.github.com/repos/dotnet/orleans/issues/255/events","html_url":"https://github.com/dotnet/orleans/issues/255","id":63503843,"number":255,"title":"Relational data store improvements, opinions here","user":{"login":"veikkoeeva","id":5158694,"avatar_url":"https://avatars.githubusercontent.com/u/5158694?v=3","gravatar_id":"","url":"https://api.github.com/users/veikkoeeva","html_url":"https://github.com/veikkoeeva","followers_url":"https://api.github.com/users/veikkoeeva/followers","following_url":"https://api.github.com/users/veikkoeeva/following{/other_user}","gists_url":"https://api.github.com/users/veikkoeeva/gists{/gist_id}","starred_url":"https://api.github.com/users/veikkoeeva/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/veikkoeeva/subscriptions","organizations_url":"https://api.github.com/users/veikkoeeva/orgs","repos_url":"https://api.github.com/users/veikkoeeva/repos","events_url":"https://api.github.com/users/veikkoeeva/events{/privacy}","received_events_url":"https://api.github.com/users/veikkoeeva/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/dotnet/orleans/labels/enhancement","name":"enhancement","color":"84b6eb"},{"url":"https://api.github.com/repos/dotnet/orleans/labels/help%20wanted","name":"help wanted","color":"159818"},{"url":"https://api.github.com/repos/dotnet/orleans/labels/work-in-progress","name":"work-in-progress","color":"fad8c7"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":18,"created_at":"2015-03-22T07:48:15Z","updated_at":"2015-04-12T16:36:50Z","closed_at":null,"body":"Continuation to discussion at #251 about on-premises installations and a note on using relational databases. This is more like gauging issues and ideas after taking a peek at [CreateTables.sql](https://github.com/dotnet/orleans/blob/master/src/OrleansProviders/SQLServer/CreateTables.sql) and [SqlStatisticsPublisher](https://github.com/dotnet/orleans/blob/master/src/OrleansProviders/SQLServer/SqlStatisticsPublisher.cs).\r\n\r\n* Is there already a lot of SQL Server usage around? This is to see how backwards compatible possible changes would need to be.\r\n* It may be smarter to put Orleans tables to a schema of their own, say, ``[Orleans]`` instead of built-in [[dbo]](https://msdn.microsoft.com/en-us/library/bb669061%28v=vs.110%29.aspx). Reasons are the usual about management, security, separation etc. This varies from a database to database, so there would need to be separate scripts to be run before starting Orleans (in production environment it's good if the program doesn't have DDL rights).\r\n* It may be good to provide views (in case of SQL Server, perhaps schema-bound) to access the actual tables. This would \"name\" the queries, perhaps help the query planner, allow enforcing further constraints via views, allow for re-arranging the tables underneath more freely etc.\r\n* In [SqlStatisticsPublisher on line 148](https://github.com/dotnet/orleans/blob/master/src/OrleansProviders/SQLServer/SqlStatisticsPublisher.cs#L148) I see the statistics are written in a loop. \r\n    * Does someone have numbers as to how many items are there usually in the list and the frequency of writes? Though, even if there were only two, at first blush it'd appear more sensible to write all the values at once instead row-by-row.\r\n    * Is someone aware of triggers in these tables? If so, changing this to write in batches may break someone's triggers. \r\n    * Why are the values ordered by name?\r\n* Would there be harm done in changing the ``ETag`` types to [ROWVERSION](https://msdn.microsoft.com/en-us/library/ms182776.aspx) (or perhaps ``DATETIME2``)? This would go better with [other parts of the .NET stack](http://stackoverflow.com/questions/21099336/is-there-automatic-etag-handling-in-odata-with-webapi-2-and-entity-framework-co) and is probably more what it's supposed when considering technically.\r\n* It appears just by looking at the column names in tables that they could be slimmed down and normalized more, which would in turn mean less I/O and consequently jitter etc. Perhaps even faster aggregate reads.\r\n\r\nI would be pleased if anyone having an opinion or knowledge would dump it here. I'm currently running a test cluster on Azure, but running Orleans on-premises in a basic enterprise setting will likely mean using a relational data store. It's not that bad, it can be rather fast even for queuing -- and even a must if there are auditing policies. :)"},"comment":{"url":"https://api.github.com/repos/dotnet/orleans/issues/comments/92085816","html_url":"https://github.com/dotnet/orleans/issues/255#issuecomment-92085816","issue_url":"https://api.github.com/repos/dotnet/orleans/issues/255","id":92085816,"user":{"login":"veikkoeeva","id":5158694,"avatar_url":"https://avatars.githubusercontent.com/u/5158694?v=3","gravatar_id":"","url":"https://api.github.com/users/veikkoeeva","html_url":"https://github.com/veikkoeeva","followers_url":"https://api.github.com/users/veikkoeeva/followers","following_url":"https://api.github.com/users/veikkoeeva/following{/other_user}","gists_url":"https://api.github.com/users/veikkoeeva/gists{/gist_id}","starred_url":"https://api.github.com/users/veikkoeeva/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/veikkoeeva/subscriptions","organizations_url":"https://api.github.com/users/veikkoeeva/orgs","repos_url":"https://api.github.com/users/veikkoeeva/repos","events_url":"https://api.github.com/users/veikkoeeva/events{/privacy}","received_events_url":"https://api.github.com/users/veikkoeeva/received_events","type":"User","site_admin":false},"created_at":"2015-04-12T16:36:50Z","updated_at":"2015-04-12T16:36:50Z","body":"@sergeybykov I did some work to simplify the SQL testing story and realized I'm a bit already implementing what [Dapper](https://github.com/StackExchange/dapper-dot-net) does. Would it be all right to add a dependency on this with regards to relational story? It's all right if not. I wrote some queries to create database on the fly and then some code to cache SqlParameters etc. Just to make the flow more fluid.\r\n\r\nI had a disk crash on Saturday, so this gets a little longer. I try to submit some more barebones  some day next week. I have a question, though, now that I got a few hours of thinking done..."}}