Post reply

URGENT! Permission issue for the subscriber and performance issue

  • January 26, 2007 at 12:15 am

    #171186

    Hi expert,

    I am not the SQL Server expert, please be easy.

    We have a dual-home server, home1=Chicago (primary database is sitting here), home2=Washington (planned secondary data in here)

    The daily process is DB1(store raw data around 250GB) => log shipping (in every minutes)=> DW DB in home1

    We are in Washington, so we want to set up the local database in one new server (all servers are running SQL Server 2005 including the new one) to gain performance and easy for quering in Excel. So the new process should be:

    The daily process is DB1(store raw data around 250GB) => log shipping (in every minutes)=> DW DB in home1 => log shippping (in every 3~6 hours) => DW DB2 in home2

    The permissions for the log shipped copy of the database are inherited from the primary database and cannot be changed without recovering the database which breaks log shipping.  This means that home2 accounts could not be grant permissions on the log shipped secondary database as the primary cannot have home2 accounts added to it.

    The alternative way to do replica is using backup and restore job nightly but the performance will be not acceptable.  DO I HAVE OTHER ALERNATIVES?

    PLEASE HELP!

  • January 26, 2007 at 3:07 am

    #685768

    Sounds like you need to look at replication - have you considered transactional replication ?

    Replication is quite a big topic - so I'd suggest you have a good look in BooksOnline regarding this option...

    Is your Washington site a DR site only - or are they running reports/queries there too ?

  • January 26, 2007 at 10:03 am

    #685890

    Thanks.  Will the transactional replication can resolve the permission issue?

    Issue:=>The permissions for the log shipped copy of the database are inherited from the primary database and cannot be changed without recovering the database which breaks log shipping.  This means that home2 accounts could not be grant permissions on the log shipped secondary database as the primary cannot have home2 accounts added to it.

    We want to set the Washington site as a read only database for running reports/queries only.

Viewing 3 posts - 1 through 2 (of 2 total)

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