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] 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). :