| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-06-23 : 14:38:02
|
| Hi,I read it on BOL (which i cannot find now) that since indexing job holds table locks, users cannot be running queries during the same time. So I have been running indexing job when users are not doing anything but there are some persistent connections to the database (ex: application/batch/web servers) as 'sa' not accessing any tables or data.So I assumed that we can have connections to the database but not querying tables when running index job. - Is this assumption correct? Now since our application will be made available over the net for 24/7 use, I scheduled a 2 hour downtime every week for indexing. Since our user connections are made using 'sa' via application/web servers, how do I terminate those? in other words since i donot know if a user is doing anything or not or if it is just a application server connection, how do i restrict access to the tables assuming my assumption above is true?Thanks,Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-06-23 : 15:07:57
|
quote:
Since our user connections are made using 'sa' via application/web servers, ...
1. Never do this.2. Are you actually experiencing blocking? Do you see spinning spids and the create index or dbcc reindex or whatever you're running as the cause?Jonathan{0} |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-23 : 16:03:11
|
quote:
quote:
Since our user connections are made using 'sa' via application/web servers, ...
1. Never do this.
This cannot be stressed enough.This is bad for countless reasons. Number one being you open you entire database to a potential exploit (sql injection comes to mind). btw, this would be the worst kind of exploit imaginable. Not only would the attacker have unrestricted access to every database in your server, but they would also have free reign as a priveleged user on your server. This could be the kind of situation that loses you your job.Another problem, is that as the DBA you will be unable to prevent access to a database from this web application because you will be unable to lock this particular user out. That makes performing maintenance on a quiet system more difficult. You seem to be having this particular problem right now.My advice, do the following:1. Create a new application user that does not have DBO privs or system admin role. Apply the appropriate security to this user (creating custom roles as necessary)2. Use a strong password. Make sure your application has a facitliy to easily change the user password used for accessing the database, because you may want to change it periodically. And while you are at it, make sure you do not have a blank password for the sa user.3. The DBO user and/or sa account is only for DBA use. in a production system, you should never give these user logins to anyone but a DBA.-ecEdited by - eyechart on 06/23/2003 16:19:49 |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-06-24 : 17:56:37
|
| I think I misstated the 'sa' connections part. PeopleSoft application security built in application server takes care of the access to the database. Actually the application server checks the user profile before the user gets to the database. So even though the application server makes persistent connection to the database as 'sa', users will filter through since application server verifies whether the user is listed in PeopleSoft's security table to see anything in the database. I agree if someone hacks the application server & gets app server id, they will have access to the database as 'sa'. Unfortunately, PSoft recommends to setup certain services in a certain way. It is not mandatory so I know I can change it.But to restate my question about INDEXING:Is it true that I can have a connection to the database but I should not query against any table to do indexing since indexing holds lock on the tables??Jonathan, I donot see any blocking so I don't suspect indexing job is creating problems.Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-24 : 18:29:32
|
| creating indexes will just lock the index exclusively (of course) and a shared lock on the table to stop updates.This means that no one will be able to access the index or update the table. Effectively preventing access to the table.If anyone is querying the table when the create index command is issued then it will be blocked.Anyone trying to get access will then be blocked.If you connections aren't doing anything then it won't affect your index creation - although you misht want to kill them anyway. Might be a good time to bounce the server.Could also look at dbcc indexdefrag which theoretically can run when your system is up - but I suspect it causes so much i/o if it has to do work that it's not worth it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-06-24 : 19:07:10
|
| NR, I understood your point. Since PS application server (an NT service) makes connections to database as 'sa', KILL command wont work as the service is set to auto-restart if a connection dies. So I think I will need to code for bringing down windows NT service itself in the index building script instead of just terminating a connection.Q:I guess i should try this but if there is an index job running & if i login as 'sa' in query analyzer and do a select on a table, will it give an error msg or just hang?Thanks!Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-24 : 19:17:25
|
| If you are table scanning and the index isn't clustered then the read may well work.Otherwise I think it will just hang - you may get a timeout error though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-24 : 19:26:32
|
| Sarat,As NR pointed out DBCC INDEXDEFRAG is your best bet as it will not hold long terms locks (It does this by doing it in small transactions) and enables users to query and update the table. Ensure that you backup prior to running this as the log file can grow.. It does suffer if the index is heavily fragmented... Look it up in BOLDavidM"SQL-3 is an abomination.." |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-06-25 : 19:28:05
|
| I agree. Actually I do defrag every friday noon (when users are logged in) & it is not a big deal since I haven't seen much change in performance. The script also backs up the log after every 100 tables or so and if necessary shrinks the log too. But are you suggesting to do this INSTEAD of REBUILD? Why?I do index rebuilding and defrag once a week.Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
|
Indexing while maintaining connections to the db