Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

HA - Database Replication issue

Technically speaking i know that HA doesn't support the database, but i am having some issues when failing the database over using Basic Availability Groups.

Below is the layout of our setup.

Pretty strightforward one would think.

Lets say for example the Edinburgh site is the Primary for all parts (HA & Database) and the Glasgow Site is the Standy for all parts (HA & Passive SQL Database)

All Web Users / Network devices are configured to look at the Solarwinds VIP - so far so good.

Solarwinds Application connects to SQL Cluster which in turns read/writes to the SQL Databse instance which then using Basic Availability Groups replicates this data to the Secondary Replica database - so far so good.

Primary site goes down.

All Web Users / Network Devices still connect to the same Solarwinds VIP - so far so good.

Solarwinds Application connects to SQL Cluster which in turn tries to read/write to the Secondary Replica database that has now changed role to become the Primary, however i am unable to login to the Solarwinds Web Console as it says the SQL server does not exist.

From SSMS i can connect to the Primary SQL Database at the Glasgow site and can query the tables and make updates.

The only difference that i can see is that when the SQL Database is Primary at the Edinburgh side the default SolarWindsOrionDatabaseUser is configured to be a user type of 'SQL User with login'

When the sites are failed over and everything is live at the Glasgow site and i look at the same Database User, this time it says that the default SolarWindsOrionDatabaseUser is configured to be a user type of 'SQL User with no login'.

I suspect that this is why i cannot connect to the web console when the database has failed over, however i cannot seem to find an easy way to resolve this.

I may add that i only have this issue if the Database is failed over, i do NOT get this issue when only the application servers are failed over.

To get round the database issue i have to rerun the configuration wizard each time to get the user type to change to be 'SQL User with login' but this requires downtime, which i am trying to aviod by using HA.

Am i doing something simple wrong?

Surely i'm not the only one that's using Basic Availabilty Groups to replicate the Database, do you get the same issue, or what do you do differently.

On the initial configuration wizard, for the SQL Server address, do you use the SQL Cluster IP address or do you use the Basic Availabilty Group Listener IP address?

Any thoughts on how to resolve this issue without any downtime would be aprpeciated.

Thanks

Duncan

Find more posts tagged with

Accepted answers

sum_giais

First thing I'd check is the sql login permissions, user mapping to the database and if the SID is identical (should be with local logins to avoid complications) of the login on each availability group member server. If the login SID is different check out this link for specifying a SID for create login.

CREATE LOGIN (Transact-SQL) - SQL Server | Microsoft Docs

Good luck!

All comments

sum_giais

CREATE LOGIN (Transact-SQL) - SQL Server | Microsoft Docs

Good luck!

pratikmehta003

issue is with the permission of the account at DB level.. you would have to check that once...

and point your Solarwinds to listener name or IP address i would suggest so that application doesnt have to check which node is online...

We have a similar setup but with an addition of Windows cluster in place for SQL Always ON

dunky2k

Many thanls for the tip, never thought about the user SID.

Deleted and recreated the user on the Secondary Replica so that the SID macthed and that sorted the issue out.

aLTeReGo

Note that this should not be necessary when using a Windows account with Windows Authentication. It is only required when using local SQL accounts with SQL authentication.