0

I need to configure merge replication between 2 databases. These databases have foreign key integrity, which makes the replication not work, so I resorted to:

  1. Dropping all FKs on subscriber database,
  2. Replicating, and
  3. Recreating the FKs.

This however leaves the subscriber database vulnerable to FK violations.

So my questions are:

  1. Does the replication lock the subscriber database, raise a transaction, and render the database unusable in any way during the process?
  2. If not, could I initiate such a lock manually through TSQL?
  3. If none of the above is possible, is there something I'm missing?
Georgi Gamzakov
  • 11
  • 4

1 Answers1

0

don't know about lock initiated by replication, but for the time of your maintenance you can set the whole database to single_user or restricted_user. 

ALTER DATABASE SET RESTRICTED_USER

i recommend the second one as it allows access to the database to all users who are, quote:

members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles

(see here: http://msdn.microsoft.com/en-us/library/aa933082%28SQL.80%29.aspx)

, only regular users are restricted. it will wait until all regular user connections are done

ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

will kill all such connections immediately. This

select DATABASEPROPERTYEX ('ocon_reportdb','UserAccess') DATABASEPROPERTYEX_UserAccess

reads the current status

UPDATE: there are maintainance activities such as statistics performed by the database engine. using WITH ROLLBACK IMMEDIATE will also kill those connections, so be careful

UPDATE2: specs to have acces in restricted_user-mode

Der U
  • 3,192
  • 1
  • 12
  • 13