none
"Stand Alone" Replicated Database RRS feed

  • Question

  • I understand basic replication methods but I need help with the viability of setting up a particular situation.

    The Non-Technical Situation

    We have an vendor supplied accounting application in use in my department that has an SQL server db backend. The app's hosting server and database server are maintained by the organization's IT department. My department is legally an independent entity from the company itself but we have to rely on the main company's IT department for application and database hosting. My organization's IT department does not have the resources or official responsibility to provide anything above and beyond maintaining the servers. I have been hired by my department as an in house data analyst to provide BI that goes above and beyond the simplistic reporting built into the application. The department head's plan was to have a replicated copy of the production db that I could access directly for creating custom queries, views, tables, etc. 

    The Technical Situation

    The IT department is understandably hesitant to let someone else outside of their department from accessing a production database which is where the plan of replication comes in. I know that we could setup a scheduled one way merge replication to a second DB that I could then access for the functionality that I need. This way, I could have my hand in the data while making the IT department happy knowing that any changes that I make on the replicated database will never make it back to the production db. The questions I have are about merge replication and schema. When you originally setup the replication, the database schema and data will be replicated to the subscriber. What happens if I wanted to add custom views, lookup tables, stored procedures, etc. to the replicated database? I will not alter the existing structure of tables, sps, etc. in any way. I will just want to add additional objects that serve my purposes. Will this break the replication because I've added to the subscriber's schema or will the data flow from the publisher to the subscriber as usual since all original schema objects will exist in the subscriber's schema unaltered?

    Like I've said, I don't know enough about this type of situation to speak intelligently about it. And the department head needs to know if her plan is viable before she pushes for the replicated db.

    TIA.

    Wednesday, March 27, 2019 4:41 PM

Answers

  • If the database is large you want to use transactional. If the database is small with a small number of changes snapshot is ok. Snapshot replication does not require every table to have a PK as well.

    What will happen is views on the subscriber (destination database) can break the snapshot, so you should drop them each time in a pre-snapshot script and recreate them in the post snapshot script.

    • Marked as answer by bmorebri Wednesday, March 27, 2019 6:00 PM
    Wednesday, March 27, 2019 5:49 PM
    Moderator

All replies

  • DON'T USE MERGE! it adds triggers and a guid column to every table you are replicating.

    Use transactional replication. 

    If you want to add views, tables, or additional objects you should create a new publication as it might lock tables which belong to the original publication. 

    How up to date do you need to be? Log shipping might work, however when you apply the logs your destination database will need to go offline. I know people who do this daily. Ship the backup and the logs to a point in time and then make the database come only in a read/write mode.

    Wednesday, March 27, 2019 5:28 PM
    Moderator
  • What about snapshot replication? The data does not have to constantly updated. Maybe 2 or 3 times daily.

    The really important question is whether adding new objects to the subscriber's schema like views or tables will break the replication. I do not want those new objects ending up on the production db but I want all of the data and schema updates from the publisher ending up on the subscriber which why I want the replication to flow one way. 

    Wednesday, March 27, 2019 5:35 PM
  • If the database is large you want to use transactional. If the database is small with a small number of changes snapshot is ok. Snapshot replication does not require every table to have a PK as well.

    What will happen is views on the subscriber (destination database) can break the snapshot, so you should drop them each time in a pre-snapshot script and recreate them in the post snapshot script.

    • Marked as answer by bmorebri Wednesday, March 27, 2019 6:00 PM
    Wednesday, March 27, 2019 5:49 PM
    Moderator
  • Thanks!
    Wednesday, March 27, 2019 6:00 PM