none
Replication: Pull subscription configuration question

    Question

  • SQL2008 EE, SP4
    Windows Server 2008 EE

    I have setup transactional replication between one Publisher and one Subscriber VM server, both in same DC & each with 8 processors & 16GB RAM. There are 3 databases of 1, 7 & 23 GB size, with what I would consider modest query/transaction workload. There are 3 corresponding Publications with a subset of 'articles' (tables) in each database comprising their respective Publications.

    The distribution database is on Publisher server and initially, the publications were configured as 'push' subscriptions which means majority of replication workload was on the publisher server and system resource utilization (esp CPU) became pretty high at time. 

    I reconfigured the subscriptions to be 'pull' subscriptions, to try and shift some of the system resource workload from the constrained publisher to the subscriber server, which was barely breaking a sweat. This modification seems to have accomplished the goal of redistributing server workload attributed to Replication, more evenly.

    However, the only way I could configure Agent Process account of the subscriptions was to use a Windows domain account, which in my case I had to use my own windows account. Since the clients organization security policy is that the windows user accounts must change passwords every 3 months. Therefore, the subscriptions require me to manually update the agent security password periodically, which is not only high maintenance but subject to breakage due to me forgetting to change pwd. I would prefer that a generic windows domain account be created with a non-expiring password for this purpose, but organization policy won't allow this.

    Is there another way to configure security accounts for pull subscriptions that would be low-maintenance? Or is this pretty much my only option, given the limitations I'm forced to work within?

    Thanks in advance,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, November 17, 2017 9:52 PM
    Friday, November 17, 2017 9:49 PM

Answers

  • The snapshot account and the account  you should be using to connect to the publisher will be local accounts. In your case it will be ServerA\SQL_replication. It is only the distribution agent account in the connect to the subscriber account you would enter the remote server account. Use any old account to create the subscription. Then you need to edit your job and add -SubscriberSecurityMode 1 -SubscriberLogin ServerB\sql_replication -SubscriberPassword IllNeverTell
    Monday, November 20, 2017 7:00 PM
    Moderator

All replies

  • What SQL Service account being used? Domain account? Does SQL service account also need to change password every 3 months?

    Saturday, November 18, 2017 1:08 PM
  • SQL Service startup accounts are virtual accounts, so they are not 'known' outside the box, by other servers.

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    Saturday, November 18, 2017 1:48 PM
  • Hi Phil - Have you thought about using pass through authentication.

    What this means is that on your publisher/distributor you create a windows account. Let's call it test. Then you assign it a password - let's call it test. This is not a domain account, but a local machine account. Then you create the same windows account and password on your subscriber server. This would be the account you would use as the distributorlogin and the subscriberlogin.

    What will happen is that the distribution agent will then try to authenticate using the local machine accounts and if it fails will try the AD. If the login has the same on both sides and the password is the same the AD will not be consulted and everything will work fine.

    This will not address the changing password requirement, but it will get around it if your AD requires password cycling.

    Monday, November 20, 2017 2:17 PM
    Moderator
  • Thanks for the suggestion Hilary.

    I'm having a little trouble understanding and implementing it though. Once I create local accounts on both the publisher & subscriber machines, aren't they still different and therefore unrecognized on the other machine?

    When I create sql_replication account on ServerA then the full account name is actually ServerA\sql_replication and attempting to create the same account on ServerB, it gets created as ServerB\sql_replication.

    If I try entering just sql_replication in Agent security properties, I get error:

    What am I missing? Do you have a more detailed article with guided instructions?

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    Monday, November 20, 2017 6:04 PM
  • The snapshot account and the account  you should be using to connect to the publisher will be local accounts. In your case it will be ServerA\SQL_replication. It is only the distribution agent account in the connect to the subscriber account you would enter the remote server account. Use any old account to create the subscription. Then you need to edit your job and add -SubscriberSecurityMode 1 -SubscriberLogin ServerB\sql_replication -SubscriberPassword IllNeverTell
    Monday, November 20, 2017 7:00 PM
    Moderator