Post reply

Replication Articles read-only AND updateable at the same time

  • April 21, 2015 at 11:47 pm

    #300095

    Yes, a title with apparently mutually exclusive requirements. However I will continue.

    We have many users with a mobile application running SQL Mobile and using merge replication to get data back to the SQL 2008 R2 database. This has worked very well for many years.

    We now have a requirement to have this data reported on using Reporting Services. This is where it gets messy.

    Due to a limitation of Report Builder(see this blog) we cannot provide access to users for creating their own reports. The report database is remote from the host and there is no VPN.

    We hit upon the idea of creating an almost identical publication but the articles as read-only. It was only after this was done that we started having trouble with our existing mobile users.

    It seems that a published article is EITHER Bi-directional OR Read-only even if they are in separate publications.

    I then thought of using Transactional Publication but this too is blocked on creation with "automatic identity range support is useful only for publications that allow updating subscribers"(Merge and Transactional publication are mutually exclusive)

    So in the final analysis is there a way for me to have merge replication AND some other form of SQL replication/data transfer that can have the same data transmitted readonly to a separate full SQL server database?

    Your insights, ideas and advice appreciated.

  • April 22, 2015 at 3:20 am

    #1792647

    I'm sorry, I don't understand a couple of things and I hope you can clarify.

    Where will be the data read by the reports? At the subscribers? At the publisher?

    Why do you need a separate publication? Why readonly?

    -- Gianluca Sartori

  • April 22, 2015 at 3:51 am

    #1792655

    Thanks for your interest

    The Report Data will be at a SQL server 2008 R2 Subscriber.

    The reason for the two publications is this -

    We have a number of subscribers running with merge replication on SQL Mobile. These users, via an application, NEED to be able to add new records to the subscription data and then merge that data with the publishing database. Since the application controls the data input this is desirable.

    The Report Data we want to be read only. We want to prevent the SQL server 2008 R2 Subscriber from being able edit or modify the subscribed data in any way and have that modified data merge back with the publishing database.

    To reiterate the Report server is hosted at a different site from the publishing database and there is no VPN between the sites.

    I hope this helps.

  • April 22, 2015 at 4:22 am

    #1792659

    If I understand correctly, the Reporting Services instance will be in a different place from the subscribers and the data has to be read from the subscribers. Are you sure this is a good idea?

    -- Gianluca Sartori

  • April 22, 2015 at 5:03 am

    #1792669

    spaghettidba (4/22/2015)

    If I understand correctly, the Reporting Services instance will be in a different place from the subscribers and the data has to be read from the subscribers. Are you sure this is a good idea?

    Gianluca,

    perhaps this image will clarify things for you.

  • April 22, 2015 at 5:10 am

    #1792673

    Ok, thanks, much clearer now.

    You could use log shipping with standby[/url] to create a redonly copy at the reporting site. Would this work for you?

    -- Gianluca Sartori

  • April 22, 2015 at 7:45 pm

    #1792896

    Gianluca,

    I read up on log shipping having never dealt with it before and I believe the answer is no.

    The replicated data is EAV structured and makes use of row filtering to deliver specific data on a per publication basis.

    So I'm really not shipping an entire database as such but filtered rows per table. This data needs to remain logically segmented as different departments may not share data for privacy reasons.

    I believe log shipping would need to ship the database in it entirety(that's what it's for as I read it) and so the reporting server would get the report data as well as all the other department's data. This isn't permitted.

    Additionally we have a high uptime need for reporting. It seems log-shipped secondary databases are only available between restores. We were hoping for a few minutes of latency.

    This is why I was hoping to just make one small tweak of the publication(read-only), create a readonly version for reporting and be done with it.

    I do appreciate your help but I'm slowly coming to the conclusion what I'm trying to do is not off the shelf functionality and will require some creative methods to solve.

    However if you or anyone else has any bright ideas I'd be very grateful for the advice.

  • April 23, 2015 at 1:38 am

    #1792939

    Yes, log shipping requires a copy of the whole database and cannot be filtered.

    Back to replication, with "readonly" you mean that you set "Synchronization direction" to "Download-only to Subscriber, prohibit Subscriber changes"?

    If so, a possible solution could be to set it to "Download-only to Subscriber, allow Subscriber changes" and revoke write privileges from any user except the replication agent user. Would it work in your scenario?

    -- Gianluca Sartori

  • May 6, 2015 at 12:55 am

    #1795839

    Thanks for the suggestion Gianluca. Since we corresponded last I have discovered a great deal about SQL replication. None of it good for me.

    Simply the replication topologies(Merge/Transactional/Peer-to-Peer) are not compatible with each other unless Automatic Identity range is set to manual and managed manually.

    Additionally the same article in in two merge publications cannot be Bidirectional in one publication and Download-only(subscriber edits or not) in the other.

    However your suggestion about preventing write back got me thinking.

    Do you think I could set up two merge publications (essentially identical) with bi-directional (these wouldn't conflict with each other) and have a subscriber that is "denywrite" such that the subscriber can pull the publication but if changes are made at the subscriber, are "denied" from being written back on the next merge?

    I've been experimenting and I haven't succeeded with this. I'm not sure what user or mechanism can be denied write\inert\update\delete.

    I've tried setting denydatawriter on the PAL subscriber and the merge replication agent. Didn't work.

    I haven't quite worked out what user is used to perform the write of merged data.

    I'll need to dig through the various users and permission configured for publications.

    Thanks for your suggestion.

  • May 6, 2015 at 2:34 am

    #1795852

    If you set it up as bidirectional you have to deny writes at the subscriber, not at the publisher. If nobody (except the merge agent and sysadmin logins) can write to the database at athe subscriber, it's not much different from download-only from a logical point of view. Obviously, the merge agent wil always check for changes at the subscriber to replicate them back at the publisher, but there will never be any if nobody can write to the tables at the subscriber.

    -- Gianluca Sartori

  • May 6, 2015 at 5:01 pm

    #1796082

    Gianluca,

    You have been so helpful.

    As the particular subscriber I want to "denydatawriter" to is a web subscriber in a an infrastructure i don't control this is where the risk lies.

    I was hoping to control that from my end via either the Merge agent account, the publication agent account, the distribution agent account or even the Web URL access account.(the last one I'm pretty sure won't help)

    I would have thought the Merge, publication and distribution agent accounts were the only account involved in the merge replication and that I could maybe get what I was after.

    Thanks for your help again.

Viewing 11 posts - 1 through 10 (of 10 total)

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