asked
SQL DB locking
I have an interesting question. The product uses SQL Express. Multiple data layers for DB communication are used. The layers are 1. Ado (legacy code, more of CRUD methods) 2. Ado .net(maintenance activity-backup,upgrade,restore) 3. Entity (Recently added) Layer 1 and 3 connect to different databases. which means Ado layer connects to 'Database A', Entity layer connects to 'Database B'.Layer 2 connects to both of the databases. All are connecting to the same SQL server instance. And according to the design requirement , all these layers/code are in different applications. For ex layer1 is Application 1, Layer 3 and 2 in Application2.(Application means assemblies/exes). Hope i did not confuse! The requirement here is, i want to have some synchronization mechanism between layer 2 and and others. That means if the user has requested for restore/upgrade of 'Database B',entity layer should not be able to read/write on that database. Same with layer1/database1. I thought of 2 ways of implementation 1. Having synchronization at the **Application data layer level** by having mutexes(in my case its a tough task since there are multiple applications connecting to DB , more development efforts) 2. Having some DB level locking mechanism. Having exclusive locks on upgrade and restore. Since these are done at Stored proc/databases level i feel this as more suited for my design. I want opinions on the same and best way to implement synchronization.
If all three different mechanisms use different security, then this is pretty straight-forward. You can promote the security for the .NET code to DBO and then set the database to restricted user. Any active transactions will finish then the database can't be accessed by the other logins. You use the [ALTER DATABASE][1] command to make this happen. Now, if they all use the same security, then the situation is tougher. There you'd need to go for single user. But, you can't guarantee that your user will be the single user in question, and if you mess that up you could have a lot of troubleshooting ahead of you. So, if you do this, you'll need to plan on using the ROLLBACK IMMEDIATE commands which can lead to data loss (depending on how your application deals with disconnections). [1]:
http://msdn.microsoft.com/en-US/library/bb522682.aspx
If all three layers have the exact same security, you can't easily isolate one layer from another. On a guess, all your apps are either DBO or 'sa' aren't they? It's a pretty common approach, but it's inherently problematic, especially when you're suddenly presented with a situation where you want to restrict access. The link I provided shows how to change the permissions on the database. Specifically you want to look at "SINGLE_USER | RESTRICTED_USER | MULTI_USER." If you had different security settings, you could change the security for one login to 'dbo' temporarily and, at the same time, change the database to RESTRICTED_USER. This would keep everyone except 'dbo' out. You do what you need, then you change the database back to MULTI_USER and take away the elevated priveleges.
Thanks for the reply. i Did not get the first part. Can you send some link so that i can read a bit.This can be pretty basic stuff , but i did not understand it :) How does three layers having same security affect the DB?
14 People are following this question.
