Post reply

System database replication

  • April 25, 2022 at 4:30 pm

    #4021915

    Hi,

    I've never needed to worry about this before, but an incoming application writes data to the master database during its installation. So although after install of said application we can make its databases HA in an AG, the replica(s) won't have that vital data in their master databases so the application will fail when the user databases are failed over to any of the replicas.

    It's not as easy as installing the application twice (or n number of times for each replica), nor during upgrades which are going to be quarterly.

    What would the best approach here? FCI are a non-starter because this is a multi-site HADR requirement and shared storage won't be replicated.

    Some additional context... the MS SQL Server user databases are populated via ETL from the production DBs (not MS SQL Server); this particular function is for reporting (SSRS)

    Thanks

    • This topic was modified 2 years, 7 months ago by  lanky_doodle.

  • April 25, 2022 at 5:16 pm

    #4021940

    I've had to do this in the past for an app. Here's what I did:

    • find out the data being written. dup this table structure in a small admin db (Admin) on the same server.
    • If the data is written in master to a user table, add a trigger to update the other table in Admin.
    • If the data is in a system table, use a job to periodically (hourly, 5 minutes, etc.) compare the master table to the Admin table and update accordingly.
    • Replicate Admin.dbo.MyTable from instance A to instance B, or in this case, you could add it to the AG.
    • Reverse the process on the second instance, ensuring the data from Instance2.Admin.dbo.Mytable gets to Instance2.master.dbo.MyTable.

    This worked well for me, separating out the data from master into a user db and then having a process to get it back into master as appropriate (update, DDL, etc.)

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

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