Post reply

Are Tables locked (essentially unavailable) during the snapshot?

  • June 9, 2008 at 8:07 am

    #192058

    Are Tables locked (essentially unavailable) during the duration of the snapshot?

    Example: If it takes two hours and 30 minutes for a snapshot of 47 tables to complete once the Publication creation is started, are those 47 tables essentially unavailable to the user community for that two hour and 30 minute duration?

  • June 9, 2008 at 10:37 am

    #826198

    No

  • June 9, 2008 at 1:26 pm

    #826300

    Then why would someone have told me that I will require a downtime to setup replication?

  • June 9, 2008 at 1:31 pm

    #826303

    Configuring replication may take some down-time depending on the type of replication, the configuration done, if it is going to require schema changes, etc. It often does not require it, but planning some is a good idea.

    Running the snapshot agent does not require any down time.

  • June 9, 2008 at 1:36 pm

    #826305

    Do the table bulk copies during a snapshot temporarily lock up the tables? Perhaps that is why a downtime would be preferable.

  • June 9, 2008 at 1:39 pm

    #826308

    I seem to recall that the bcp puts a read lock on the tables. Can't remember if it's a table lock or if it locks pages at a time. The setting up of the replication (at least back in SQL 2000) also used to temporarily lock the tables.

    Possibly the reason for the downtime recommendation is because of the load that the snapshot will put on the server as it copies out all the tables to disk. Depending how much spare IO capacity the server has, you may notice a slowdown or you may not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 10, 2008 at 3:26 pm

    #826994

    wjones21 (6/9/2008)

    Do the table bulk copies during a snapshot temporarily lock up the tables? Perhaps that is why a downtime would be preferable.

    IF the method you use to create the snapshot is "native" then a lock is kept on the table while bcp is running. IF the method you use is ether concurrrent or db snapshot then there is just a breve metadata lock but activity can continue normally without a lot of problems. The advantage of native though is that is faster and less space is used in the disk drives.


    * Noel

  • June 11, 2008 at 7:22 am

    #827227

    Good info! Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply