March 31, 2016 at 8:13 am
Hi
Just looking for any observations of best practice.. We're planning to put the State database on a server in the same tier as the web servers - the reason being to cut the traffic through the main firewall between the web farm and the sql cluster which currently holds the state database.
I'm not planning to use in memory.
It appears I should make sure the connection string ( or the connections ) from the web servers to the database server is encrypted .. being a DBA I have to admit this isn't an area I'm very strong in.
Normal paranoia about data security etc. etc. so any observations or advice would be appreciated.
It's SQL 2014 STD
thanks
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 5, 2016 at 8:32 am
No-one have their state database in the web tier or on a separate server then?
Am I unique in following this solution?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 5, 2016 at 12:11 pm
Where I see this, Colin, and with a few people, is that there are companies using Redis to manage some things like state (or lookup data) very quickly.
If you have sensitive information, then you have a security issue, but I'm not sure it's that bad. I'd really limit what goes here.
You are sharing memory with IIS here, so is it faster with SQL Express or some local instance?
Definitely protect credentials well.
April 12, 2016 at 4:35 am
Hi Steve - thanks
just to clarify .. our current setup for all our applications which store state is to use a state database on the same cluster as the client databases - for one of our major systems it was seen that a high %age of the traffic through the firewall between the web and app/db tier was down to the state database traffic. The decision ( not mine ) was to place the state database in the same tier as the web servers to reduce the traffic volume through the firewall. To this end we have a full sql cluster just for the state database - I would have gone with in memory but costs and some miss-trust of volumes of data ( this is a busy system ) mean we don't have that option now.
The whole reason is to reduce traffic .. as a normal paranoid DBA I don't trust the devs when they tell me there's nothing sensitive stored in state and neither do I believe you can't do anything with it if you access or extract it.
Yes I do agree that if the web servers get hacked then we have problems anyway but that still doesn't stop me asking if there's anything I should be aware of !
We're a Microsoft house so using an alternative would mean weeks of regression testing - in theory we just have to re-point our connection string to use the new database server.
We have encrypted the connection strings - ideally I'd prefer windows core for the cluster but sadly the datacentre does not support ( = not allowed ) windows core.
thanks again.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 12, 2016 at 9:08 am
Good luck.
I don't think there's necessarily anything wrong, but if it's a busy system, SQL might struggle. IF you're Enterprise, you can use In-Memory.
I'd start looking at Redis. I see nothing but good reviews from people using it, and it has all the ADO.NET connections available. Even if it's weeks, or months, to test, it might be a good upgrade at some point.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply