Orion does not connect to SQL AlwaysOn cluster after failover

davidb35467

When my AlwaysOn cluster fails over Orion does not connect.

The database configuration in Orion is pointing to the AlwaysOn Availability group name

Any ideas?

This is a new deployment running SQL 2017, Server 2016 and SAM 6.7.1

Find more posts tagged with

sql

my orion deployment

always-on

sql always on

Accepted answers

All comments

dsimpkins

Did you "Copy" the account from the server the DB was first installed on to the secondary nodes?

Reason i put copy in quotes is because it's not a straightforward recreate the account with the same password.

Seashore

(Is it AlwaysOn Cluster or AlwaysOn Availability Groups? Not the same thing. Guessing Availability groups, AG)

First, on the new primary node, check the event log for failed login attempts. If failed login attempts from orion you must setup the login-account in SQL on the new primary node. If you are using SQL accounts google for "sp_help_revlogin". If domain account, just set it up.

Second, you may run "sp_who2" in SQL to see who has connected to SQL, to see if orion connects.

Third, Orion config wizard sometimes mess up the connection string when using AG. Even though you specify the AG-listener it uses the server-name of the node that was primary at that time. You should check that. See the eventlogs on the orion server for what servername it tries to connect to.

Good luck!

mesverrum

Yeah, I have had a few clients who "remade" the SQL account on the secondary members but they didn't re-use the same SID so it wouldn't work, had to learn how to do it myself so I could teach it to their DBA's.

dsimpkins

I use this SQL query which creates all the create logins commands for me, all i have to do then is cut and paste the command i want into a query on the other server.

SELECT 'create login [' + sp.name + '] ' + CASE

WHEN sp.type IN (

'U'

,'G'

)

THEN 'from windows '

ELSE ''

END + 'with ' + CASE

WHEN sp.type = 'S'

THEN 'password = ' + master.sys.fn_varbintohexstr(sl.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(sl.sid) + ', check_expiration = ' + CASE

WHEN sl.is_expiration_checked > 0

THEN 'ON, '

ELSE 'OFF, '

END + 'check_policy = ' + CASE

WHEN sl.is_policy_checked > 0

THEN 'ON, '

ELSE 'OFF, '

END + CASE

WHEN sl.credential_id > 0

THEN 'credential = ' + c.name + ', '

ELSE ''

END

ELSE ''

END + 'default_database = ' + sp.default_database_name + CASE

WHEN len(sp.default_language_name) > 0

THEN ', default_language = ' + sp.default_language_name

ELSE ''

END

FROM sys.server_principals sp

LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id

LEFT JOIN sys.credentials c ON sl.credential_id = c.credential_id

WHERE sp.type IN (

'S'

,'U'

,'G'

)

AND sp.name <> 'sa'

AND sp.name NOT LIKE 'NT Authority%'

AND sp.name NOT LIKE 'NT Service%'

Prineel

Hi there, having a similar issue in a customers environment with AlwaysOn.

-In the Config Wizard, we specify the listener, it defaults to Active Node at the time.

-System goes down when failover occurs.

-On the Secondary node we get permission errors running Config Wizard. Assuming DBA just did a "Copy" of the account?

Would appreciate any assistance with this.

Thanks

dsimpkins

Run this against your Solarwinds DB on all your servers

select

dp.name [User_Name]

,dp.type_desc [User_Type]

,isnull(sp.name,'Orphaned!') [Login_Name]

,sp.type_desc [Login_Type]

from

sys.database_principals dp

left join sys.server_principals sp on (dp.sid = sp.sid)

where

dp.type in ('S','U','G')

and dp.principal_id >4

order by sp.name

If any come back as Orphaned then you have an account that exists in the DB but does not exist on the server or the SID's don't match. If you do have any the script above will help you create the login correctly.

dfairles

Are the database instance names the same on both servers?

sturdyerde

Dumb question, but have you confirmed that Orion is pointing to the listener and not a specific node in your AOAG?

Prineel

Had a look at Database Manager and found the following

Each time we failed over Netflow was still able to connect, Orion DB is the issue.

When running the config wizard and point to listener alone, i can't get passed selecting the OrionDB, it get stuck. Is this a permissions issue?

Netflow DB selects fine.

sturdyerde

It looks like Always On was configured for your NTA database but not [ completely? | properly? ] configured for your primary Orion database.

Prineel

Have some feedback from DBA. Anything else to lookout for?

Solarwinds primary instance Name :- MCPD1VSQP021\INST2

Solarwinds Secondary instance Name :- MCPD1VSQP020\INST1

Listeners Name :- AG03SQLLis

Primary

Secondary

Prineel

Just an update this was sorted by manually updating the SWNetPerfMon.DB file with the following info.

;MultiSubnetFailover=Yes;initial catalog=SolarWindsOrion;data source=AG03SQLLis, 1433;

Thanks again for all the assistance

lawyer26

Hi,

Just confirming that if we add "MultiSubnetFailover=Yes" to the connection string in the SWNetperfmon.db files (on all of the polling engines and the web server, we should just be able to point at the listener and when our database primary fails over we won't have to restart services?

We already have the initial catalog and the data source defined in our existing SWNetperfmon.db files.

Thanks!