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?
December 1, 2021 at 3:53 pm
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?
December 1, 2021 at 6:04 pm
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?
December 1, 2021 at 6:32 pm
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:
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
December 1, 2021 at 7:44 pm
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.
December 2, 2021 at 9:07 am
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
December 2, 2021 at 9:58 am
Is the snapshot agent account :
Is the merge agent account :
December 2, 2021 at 11:20 am
The Snapshot agent (repl_snapshot) is part of the following: (I checked this through "security > logins")
The merge agent account (repl_merge), I have checked the following: (checked through "security > logins)"
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:
"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):
December 2, 2021 at 11:36 am
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?
December 2, 2021 at 8:31 pm
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:
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?
December 7, 2021 at 12:03 pm
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
December 7, 2021 at 3:04 pm
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