INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

User access while snapshot replicating

User access while snapshot replicating

(OP)
Hi

SQL Server 2008 R2

We have snapshot replication setup, just on demand replication. There's an agent job that generates a snap at a certain time, then it subsequently calls the distribution job which replicates our data over to a target database.

My question is, during this "apply" part of replication on the target database, is there a way to restrict user access?
The concern is that users may have the ability to see data they otherwise would not during this replication process (long story). It looks like tables (articles) are updated one by one, and I can easily select against all tables while replication is happening.

I'm looking to see if there's anything built into replication which can lock the database exclusively for the replication job?

thanks

RE: User access while snapshot replicating

Can you switch the database to SINGLE_USER
(ALTER DATABASE DBname SET SINGLE_USER) before replication then set back to MULTI_USER after?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: User access while snapshot replicating

That's good advice. Set single user.

You need to be a little careful though because setting the database in to single user mode with the query presented by TheBugSlayer may not take affect immediately. For example, if another user has a long running query at the moment, the database will not be put in to single user mode until the other query(s) are complete.

You can change the code shown above so that it takes affect immediately. Like this:

CODE

ALTER DATABASE DBname 
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE; 

When your process is done, don't forget to set the database back to multi-user.

CODE

ALTER DATABASE DBname 
SET MULTI_USER 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: User access while snapshot replicating

(OP)
yes I was thinking the same thing, just thought maybe there'd be something built into replication itself that could handle something like this. I only have a small database to contend with here, but what if this was to happen on a database with thousands of tables, where applying the snapshot could take several minutes or hours. If tables are done one at a time then there would be a period where a user is exposed to some data being up to date where other tables are empty.

Single user mode with "rollback immediate" should end users transactions which is what I'd need. I wasn't sure whether putting the database in single-user mode would then affect the replication itself. But I suppose I won't know for sure till I try.

I'll give it a go.

Many thanks for your suggestions ! I'll let you know how I get on.

RE: User access while snapshot replicating

(OP)
I've given this a try and, as suspected, when the database is in single-user mode, snapshot replication doesn't work. So an alternative to this is restricted_user mode, which only allows sysadmin or dbowners to access the database, therefore replication works. I'm thinking of using a combination of each, so single_user with rollback immediate and then switch to restricted_user mode. Back to multi-user afterwards.

I'm still surprised there doesn't seem to be a mechanism within snapshot replication which prevents users seeing data until it's finished.

Anyway, thanks guys

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close