User access while snapshot replicating 2

[billo102]

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

 

[TheBugSlayer]

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)

 

[gmmastros]

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:

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.

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

 

[billo102]

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.

 

[billo102]

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