0

I have a client app that works with a huge 1 table from a given database and insert some statistical data into another 10 tables. To prohibit usage of the database by other applications I transfer it to single user mode. All works ok, but having only 1 connection is taking a long time to process and insert all the data. If I use multiple threads and multiple connections without 'single user mode' the time is cut about 5 times. The question is, is there another way that would block access to the database by other applications, but allowing at the same usage of multiple connections from my application?

Eugen
  • 2,934
  • 2
  • 26
  • 47
  • Why not just lock the tables in question? – lc. Mar 13 '13 at 19:28
  • "To prohibit usage of the database by other applications I transfer it to single user mode". Why? Is this to stop other applications using the table under any circumstances, or only when you are writing to it? And if only when writing to it, why can't they read data you are not writing (i.e previously commited data) See [http://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes] for how to lock the entire table within a transaction. – Stephen Byrne Mar 13 '13 at 19:44
  • @lc: if I lock tables from my application would it stop other ones to read/write into them? – Eugen Mar 13 '13 at 20:16
  • @StephenByrne: yes, I need everyone else do not be able read/write into database at any cost while I update the statistics. – Eugen Mar 13 '13 at 20:17
  • @Eugen ah ok so it's the entire database you need to lock down not just this one table. I wonder could you just change the password of whatever user is being used to log into this database, execute your code (now you are the only one who knows the pwd) and then change the password back? Brutal, but it would do the job.... – Stephen Byrne Mar 13 '13 at 20:46
  • @StephenByrne users can login as windows users, can't change that easily :) – Eugen Mar 13 '13 at 21:02
  • You could try using an application lock. Have your update app place an application lock using sp_getapplock(). Have the clients check this lock and only allow access if it is clear. – Chris Dunaway Mar 13 '13 at 22:48
  • @Eugen - you mean that the applications that these users use are using integrated security? (toungue twister lol) Can you provide some more background here - are these all internal apps? external 3rd parties? What actual control do/don't you have over these apps, assuming you had infinite time and resources?Could you for example block access to the server itself via firewall rules, during the "downtime"? – Stephen Byrne Mar 14 '13 at 09:59
  • We have an internal main app and another tool of ours that updates statistics. I need to block the main app (can't change source code at this time) while I run my tool. We use both, integrated and SQl security models. – Eugen Mar 14 '13 at 23:50

2 Answers2

1

How about using a Logon Trigger? Create a table with user names that are allowed to Logon. If the user is not in the table, roll them back so they cannot Logon.

Michael Harmon

Michael Harmon
  • 746
  • 5
  • 10
0

You could introduce a layer in between the clients and the database.

The layer could keep track of the clients and allow only one to access the database for the transfer.

The layer would be the only process allowed to access the database directly.

Emond
  • 50,210
  • 11
  • 84
  • 115