MSSQL Replication

  • Hi,

    I am using the following:

    2 x freeradius servers on Redhat

    2 x MSSQL 2019 Servers running on Windows 2019 Server

    Currently all is working fine with regards to auth requests and responses. No issue there at all. The problem I have is trying to work out which form of replication to configure. Here is what I need:

    A master/master scenario where if one SQL Server goes down, or a radius box then the other sql can handle the updates and when the "down" server comes back up, the changes are replicated. This has to be both ways, hence a master/master scenario.

    Is this "peer-to-peer" replication or standard "Transactional" please?

  • As an add on to this, I am not a Windows Server guy. I got the replication working on Linux without issue but cannot even get snapshot replication working on Windows Server 2019. I think this has something to do with permissions etc rather than the replication configuration.

    Has anyone configured replicaiton okay on Windows 2019 server and if so what were the users/permissions you had to set on the folders for this to work please?

  • I've not used peer to peer replication, I think it would do what you want (active-active servers) but it's an Enterprise Edition feature.

    Merge replication would also do it.  Data changes could happen on either server and if one went down the changes would be queued up until it came back (up to a configurable amount of time).

    If the server had to be rebuilt then you'd have to set the replication up again from scratch or restore backups if you have the replication scripted and suitable backups.

    Transactional replication is one way so if you lost the primary (publisher) you wouldn't easily be able to get the data back to it when it came back.

    If you are just after having one server that is active and a secondary server that is inactive and takes over in the event of a problem (and can be switched back and forth) then mirroring, AlwaysOn Availability Groups or Log Shipping might be of use.

    Regarding the folders, the replication agents runs via executables that are called by SQL jobs.  The agents run under Windows accounts that are specified when replication is set up and those accounts need read/write on the snapshot folders.    Do you get any errors showing up in the SQL jobs?

    • This reply was modified 1 month, 3 weeks ago by  DNA_DBA.
  • Hi SSCrazy Eights,

    Firstly, love the name... excellent. Sounds like a character from a Tarantino movie   🙂

    I think I almost have it configured by following this example:

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-preparing-the-server-for-replication?view=sql-server-ver15

    I have set up the second section and after configuring the publisher and clicking on Agent viewer, one of the replication jobs is just cycling because of this error:

    the publisher login must be a member of the db owner

    However, I am logged in on the publisher as Administrator and that is configured on the distribution and radius DBs and is a member of "db owner".

    In fact, to ensure I covered all bases I just placed every login under the 2 x DBs and made them a member of "db owner" and yet I am still getting this error. Even after restarting the server. Very frustrating and any help would be greatly appreciated.

    Thank you

     

  • Is replication really the way you want to go on this.  Replication is a real pain in the ass when it goes wrong.

    If what your after is more HA why not look at a traditional failover cluster instance or an always in availability group?

    Either way you have 1 standard connection point and if the active machine was to fail it would failover to the other, if an FCI then there is no data resynch that needs to happen but in an AOAG once the downed machine is upped within a reasonable time the data will be synced back.

  • Hi Ant-Green,

    Unfortunately I have to test all differing types of failover/replication with mysql and mssql. I have to prove operational value to customers. Mysql is working fine. No issues with that at all.

    As I mentioned, the problem with proving this on Windows 2019 Server is I am not a Windows guy. Last time I configured a Windows Server was back in 2001...  🙂

    So, I am currently stuck at this position and given that the Administrator is part of the group, as I mentioned previously, then I am not sure why this is not functioning.

    Any help would be extremely appreciated.

    Thanks

    • This reply was modified 1 month, 3 weeks ago by  CliveG.
  • Is the snapshot agent account :

    • a db_owner on distribution database
    • a db_owner on the publisher database
    • a read/write account on the snapshot shared folder

    Is the merge agent account :

    • a db_owner on distribution
    • a db_owner on the publisher database and subscriber database
    • a read account on the snapshot shared folder
    • added to the PAL
  • The Snapshot agent (repl_snapshot) is part of the following: (I checked this through "security > logins")

    • a db_owner on distribution database
    • a db_owner on the publisher database (in this case "radius")
    • a read/write account on the snapshot shared folder (repldata)

    The merge agent account (repl_merge), I have checked the following: (checked through "security > logins)"

    • is a db_owner on distribution
    • is a db_owner on publisher
    • does have a read account on the snapshot shared folder

    I do not even know, or cannot find, where the PAL is. I can see the publisher properties but cannot find the "publication properties"... not even in the monitor section where it appears it is meant to be.

    Subscriber is configured as per the following document:

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-ver15

    "View the status of snapshot generation" is where I am stuck. When clicking on that I get the following error message:

    "The publisher login must be a member of the "db_owner" role at the publsher database when generating a regular snapshot"

    As I have mentioned, repl_snapshot is a member of db_owner for distribution and radius (I have attached a screenshot):

     

     

    Attachments:
    You must be logged in to view attached files.
  • Okay, I think I may have found where the issue is, but am unsure of how to solve the issue.....

    Firstly, I need to find out who is "logged in" for the publisher as I think it may be "<machine name>\Administrator" and if that is the case I do not appear to be able to add that user to the Role Members list for the DB as shown in the attached JPGs.

    How can I get around this or do I have to start the replication from scratch again?

    Attachments:
    You must be logged in to view attached files.
  • Okay. I decided to uninstall and re-install and try again. Now I have a different issue.

    I have set up "repldata" shared folder and created the four required entities as follows:

    • repl_snapshot
    • repl_distributor
    • repl_logreader
    • repl_merge

    I have completed everything as per the tutorial mentioned previously and have ensured the repl location is the shared path and that repl_snapshot has full-control and the others have read access.

    Everything, again, goes fine until I click on "view snapshot agent status" and I get the following (shown in the attached image). I have searched the web for a resolutiona dn all of them say the same "check that repl_snapshot has the correct permissions for shared and security on the repldata folder". I have checked this countless times now and it has full control in both areas (shared and security):

    As mentioned, I have searched everywhere for a resolution to this issue. Any help please?

    Attachments:
    You must be logged in to view attached files.
  • Sorry it's been such a pain to get it set up, it's normally not as bad as that!

    Can only suggest adding the following to the snapshot job step to maybe show why it's failing:

    -OutputVerboseLevel 2

    The other thing is that you might be able to try is to run the agent manually to maybe see why it's failing.  ie: start a command prompt as the replication snapshot account, go to the snapshot agent location and run it with the parameters that are in the job step

  • This was removed by the editor as SPAM

  • Okay, I have it working.

    Note: This is for Windows 19 Server (64 bit):

    Windows path pointing to the wrong OLE Drivers.

    Need to add into the path under:

    system properties > Advanced Settings > Environment Variables > System Variables > PATH

    Add:

    %SystemRoot%\SysWOW64\

    %SystemRoot%\SysWOW64\1033

    And make sure they are referenced before any call to SQL in the PATH. Then restart the SQL Server Service and the SQL Agent Service and re-configure.

Viewing 13 posts - 1 through 12 (of 12 total)

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