May 1, 2009 at 1:23 pm
Im an Oracle DBA getting into SQL Server. I was wondering if SQL Server has anything like Oracle's restricted session to keep users off the database while I do some maintenance tasks.
Basically we have transaction based replication running on the server. The business users want me to add a couple tables in to the replication setup so they can be used for reports. Problem is when I add the tables, the snapshot re-initialization will take about 30 minutes and will cause locking for the users, and if the user has the lock it will cause delays for the snapshot creation.
My plan... bounce the database to kick all the users off, bring the database up in restricted session or whatever if possible. Add the tables to the publication and reinitialize the snapshot. Then bounce the database again and open it up to the users.
Is this a decent plan? Will it work?
May 1, 2009 at 1:45 pm
Check out the "Alter Database" command in Books Online. You can set a database to single-user, restricted, or multiple user.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2009 at 2:25 pm
Good information.
Now if I put the database in restricted_user mode and attempt to add articles to the publication, when the snapshot job kicks of, it is executed on the distributor which is a different server, will it allow the snapshot to be reinitialized or not? The distributor job is owned by a user called distributor_admin which has sysadmin fixed server role on the other server.
May 1, 2009 at 2:49 pm
Since I haven't tried that, I really can't say one way or the other.
If I were you, I'd set up a test environment and try it out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2009 at 3:31 pm
It would be the snapshot agent connecting to the publisher and taking out the locks rather than the distributor agent. That will run under the auspices of the sql server agent account, so what rights does that have on the publisher?
Anyway I don't think you should go to these lengths, normal practice would be to schedule the snapshot to run at a quite time for the app and avoid locking problems that way.
Having lock contentions doesn't sound as bad to me as preventing the users from using the app at all.
---------------------------------------------------------------------
May 1, 2009 at 4:17 pm
One would think so, but the last time I tried to do this without kickin them off it took nearly an hour and a half, compared to 15 minutes. And the powers that be want it done at a specific time as it is a 24x7 app with whiny users who are extremely vocal.
May 1, 2009 at 4:36 pm
Louis Kapp (5/1/2009)
One would think so, but the last time I tried to do this without kickin them off it took nearly an hour and a half, compared to 15 minutes. And the powers that be want it done at a specific time as it is a 24x7 app with whiny users who are extremely vocal.
😀
---------------------------------------------------------------------
May 2, 2009 at 4:02 pm
If the Distributor account has SYSADMIN permissions, will be work.
A database set to RESTRICTED MODE allow access to all connections with DBOWNER (or higher).
Sysadmin is a permission higher than dbowner.
So, go ahead.
---------------------
Alex Rosa
http://www.keep-learning.com/blog
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply