AlwaysOn High Availability group showing all Databases.

Question

AlwaysOn High Availability group showing all Databases.

jayjj

Mr or Mrs. 500

Points: 576
More actions
April 28, 2014 at 8:32 am

#306792

Hi guys,
I came across an issue while migrating from SQL 2005 to SQL 2012 and using AlwaysOn Group. For some strange reason, when ever i connect to the Listener name for each AlwaysOn group, it list all the databases which is on the SQL instance, so i would be able to see databases that is not part of that Availability Group. I am not using default port, so have to put the port after the Name to connect and both Instance and Listener are using different port.
Testing the fail over works fine too, when i perform a manual failover, i can connect to any of the databases in the group from my application with no problem.
Has any one seen this before, considering that the Listener Port is different to the port which the instance is using?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Perry Whittle SSC Guru Points: 233855 More actions · Answer 1

jayjj (4/28/2014)
For some strange reason, when ever i connect to the Listener name for each AlwaysOn group, it list all the databases which is on the SQL instance, so i would be able to see databases that is not part of that Availability Group.

connecting via SSMS?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

jayjj Mr or Mrs. 500 Points: 576 More actions · Answer 2

Perry,

Yes if i connect to the Listener name via SSMS it list all the Databases including ones not in That Group. This is also the same if i try to create an ODBC connection to the Listener.

Perry Whittle SSC Guru Points: 233855 More actions · Answer 3

And that's exactly what I would expect it to do, you're connected to the primary instance and will see all databases available to you on that instance.

What are you expecting to see, have you configured read only routing scripts yet?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

jayjj Mr or Mrs. 500 Points: 576 More actions · Answer 4

Perry Whittle (4/29/2014)
And that's exactly what I would expect it to do, you're connected to the primary instance and will see all databases available to you on that instance.
What are you expecting to see, have you configured read only routing scripts yet?

What i am saying is that i am connecting to the listener name, not the instance name, so i was expecting to see just the Databases inside that AlwaysOn Group that the listener is linked to.

EG. My Instance is called SQLINTS1 with AlwaysOn Group INST-AG1, that contains 3 Databases. If i connect to the AG listener Name, INST-AG1 using SSMS, then i can see all the other Databases which are on the instance SQLINTS1 and not part of INST-AG1.

Are you saying this is normal to see all the other Databases too?

Perry Whittle SSC Guru Points: 233855 More actions · Answer 5

The listener is just a virtual network name and ip which through ssms will connect you straight to the primary replica\instance.

You'll then see what your account grants you access to

Now, whether or not this is an oversight is another question?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 233855 More actions · Answer 6

The listener is just a virtual network name and ip which through ssms will connect you straight to the primary replica\instance.

You'll then see what your account grants you access to

Now, whether or not this is an oversight is another question?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

jayjj Mr or Mrs. 500 Points: 576 More actions · Answer 7

Perry,

Thanks for the reply. I am new to this feature so learning as i go, but just asked the question as i had thought you would only see all that's in in that AG group only.

Thanks

Perry Whittle SSC Guru Points: 233855 More actions · Answer 8

this is the behaviour I have observed across SQL server 2012 and 2014. The listener is a VNN and VIP into the instance itself and not the availability group.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉