Group Policy – replication snapshot

snapshottransactional-replication

Has anyone seen a domain Group Policy stop a transactional replication snapshot from being configured and executed "A required privilege is not held by the client".

To cut a long story short… I am having trouble getting to much from our server admin guys. All they would do was create me a new server, added it to an OU with a filter to block the Group Policy and I got it to work (using domain user for agent) but now I can't get anything out of them about the Group Policies being delivered.

So my question is, does anyone know the policy setting that could be blocking the making of a snapshot.

Any help greatly appreciated.
David

Best Answer

I cannot say for sure, never having encountered this exact problem before, but I'd recommend looking into the local security policies. In the standard GPO tree, you can find those under:
"Computer Configuration" -> "Policies" -> "Windows Settings" -> "Security Settings" -> "Local Policies". Under that, I'd look particularly closely at the "User Rights Assignment" section.

If your server admins won't let you see what GPOs are being applied, you can get a list yourself if you've got administrative access on the server you're having problems with. Assuming you're running Windows Server 2008 or newer on it, try:

gpresult /H {Your path here, for example C:\gporeport.html}

This will output an HTML report to the file you specified, which details what GPOs are applying, what specific settings are configured and which GPO is controlling which setting. This might not be a direct answer to your question, but a good starting point to troubleshoot from. (Specifically, if anything at all is set in the local security policy, try asking your server admins to filter out the specific GPOs that are applying those settings. Narrow it down, rinse and repeat.)

Related Solutions

Sql-server – Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script

Maybe Brandon's idea here will help: How to add an article to existing replication

Summary of his suggestions to fix:

execute sp_refreshsubscriptions for pull subscriptions
or in push subscriptions use the sp_addsubscription as you mentioned (worked for me)

I wrote some code to help identify the "orphaned articles on subscriber's":

--Run from distributor
SELECT  pub.data_source AS Publisher ,
        a.Publisher_db ,
        p.Publication ,
        sub.data_source AS Subscriber ,
        s.Subscriber_db ,
        a.Article
FROM   distribution.dbo.MSarticles as a
LEFT JOIN distribution.dbo.MSpublications AS p ON a.publisher_id = p.publisher_id AND a.publication_id = p.publication_id
JOIN sys.servers AS pub ON p.publisher_id = pub.server_id
LEFT JOIN distribution.dbo.MSsubscriptions AS s ON a.publisher_id = s.publisher_id AND a.publication_id = s.publication_id AND a.article_id = s.article_id
LEFT JOIN sys.servers AS sub ON s.subscriber_id = sub.server_id
WHERE s.subscriber_db IS NULL --Leave off for great documentation, but this will show the "orphaned" subscribers effected by this issue.

Sql-server – Excessive replication snapshot size causing failure

Note that Character representation of any data will almost always exceed the original size. Depending on type, it can exceed by a large margin. Eg. an int column takes 4 bytes, but the character representation of 1000000 takes ~16 bytes as Unicode, including delimiters. That is a x4 increase right there. Dates, floats, numerics all will usually increase, and by even bigger factors than x4.

The native format alternative is the bcp utility native format (since snapshots are applied using bcp). This format is only slightly longer than the native SQL storage when comparing individual values, but it can result in actually smaller files than SQL Server required storage because the bcp file is dense: there is not page header overhead, nor partially filled pages (fragmentation, fill-factor). See Use Native Format to Import or Export Data.

Additionally you should also compress the snapshots. See Enhance General Replication Performance:

Use a native mode snapshot unless a character mode snapshot is required. Use the default native mode snapshot for all Subscribers except non-SQL Server Subscribers and Subscribers running SQL Server Compact, which require a character mode snapshot.

Use a single snapshot folder for a publication. When specifying the publication properties related to snapshot location, you can choose to generate snapshot files to the default snapshot folder, to an alternate snapshot folder, or to both. Generating snapshot files in both locations requires additional disk space and more processing when the Snapshot Agent runs.

Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files. The Snapshot Agent performs a sequential write of data to the snapshot folder. Placing the snapshot folder on a separate drive from any database or log files reduces contention among the disks and helps the snapshot process complete faster.

When you create the subscription database at the Subscriber, consider specifying a recovery model of simple or bulk-logged. This allows minimal logging of the bulk inserts performed during the application of the snapshot at the Subscriber. After the snapshot has been applied to the subscription database, you can change to a different recovery model if necessary (replicated databases can use any of the recovery models). For more information about selecting a recovery model, see Restore and Recovery Overview (SQL Server).

Consider using the alternate snapshot folder and compressed snapshots on removable media for low-bandwidth networks. Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and make it easier to transfer snapshot files on removable media. Compressed snapshots can, in some cases, improve the performance of transferring snapshot files across the network. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files, and by the Distribution Agent or Merge Agent when applying the snapshot files. This may slow down snapshot generation and increase the time it takes to apply a snapshot in some cases. Additionally, compressed snapshots cannot be resumed if a network failure occurs; therefore they are not suitable for unreliable networks. Consider these tradeoffs carefully when using compressed snapshots across a network. For more information, see Alternate Snapshot Folder Locations and Compressed Snapshots.

Best Answer

Related Solutions

Sql-server – Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script

Sql-server – Excessive replication snapshot size causing failure

Related Question