Is it possible to ONLY allow a login to the availability group listener, but block logins to server instance/nodes?
So: MySQLServerA and MySQLServerB are in an avail group "MyAvailGroup".
I want users to login to MyAvailGroup's listener, but I do NOT want them to login to the actual hosts/nodes directly.
Is that possible?
We are rolling out the use of Availability Group listeners to our SQL Server 2012 Environment which has a 2 node multi-subnet cluster. The Primary is R/W and the Secondary is a non-readable node that would be manually failed over to in a DR scenario
I have set up the AGL and asked the sysadmins to create a DNS record in both subnets with fixed IP's.
The issue I have having is that when I ask the app developers to connect to the databases using the AGL it is totally random whether the AGL resolves to the Primary or DR node - as a result that are having problems getting their apps to connect.
I was thinking of asking the sys admins to remove the DNS record in the DR subnet and then add it back in should we need to fail over - but I was thinking there must be a better way.
I have MasterDataServices installed on a server and the database is in an AlwaysOn Availability Group.The MDM front end currently is set up incorrectly and is referencing NodeA (primary node) for its database.I want to fix this but im unsure if i should:
-point the MDM front end to the cluster name, so it wont matter what server the databsae is on?
-create an Availability Group Listner, which would also decoupple the database referenced from a particular server.
I have a two node HA Always on group using a Listener. I would like to force a certain AD group to always be forced to the secondry node as they would only ever need to run select statements. If there an easy way to do this without using logon triggers?
View 6 Replies View RelatedI ran into a Kerberos authentication issue because of a missing AOAG SPN. Some of the tickets that granted me access to the nodes of the AOAG cluster were using the encryption type that I would expect. However, the MSSQLSvc SPNs were not using what I would expect!
klist
#XX> Client Somebody@somedomain.com
Server: RPCSS/MySQLServer@somedomain.com
KerbTicket Encryption Type: AES-256-CTS-HMAC-SHA1-96
#XX> Client Somebody@somedomain.com
Server: MSSQLSvc/MySQLServer@somedomain.com
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
#XX> Client Somebody@somedomain.com
Server: MSSQLSvc/MyAOAGListener@somedomain.com
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
I can't seem to figure out what the next step should be, and the infrastructure admins are stumped as well. How to proceed?
I have getting issues when i am creating listener for always On . Error shown as below
Can not bring the Windows server fail over cluster (WSFC) resources online. (Error Code 5942). The WSFC service may not be running or may not be accessible in its currents states, or the WSFC resources may not be in a state that could accept the request.
For information about this error code see "system error code" in windows development documentation
The attempt to create network name and IP address for the listener is failed. The WSFC service may not be running or may not be accessible in its currents states or the value provide for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate network name and IP address with network administrator. (Microsoft SQL Server error 41066) ...
I am trying to setup an AG on a cluster, I was able to define where I want my replica, but I need to setup the ip for the listener of that one
When I am adding the Ip at the listener I am getting the following message:
The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. For information about this error code, see "System Error Codes" in the Windows Development documentation.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)
Browsing around I found this guide: [URL] ......
And I make sure that the cluster has permissions to "Read all properties", "Create Computer Objects", even to "Create all child" permissions
I also added those permissions to the account that starts sql at that cluster and I am logged as that and I am still getting the error....
When I fail an availability group between subnets, I am finding that the DNS entry in DNS is staying. So what happens is the Availablity Group listener has 2 records in DNS, one for each IP. This causes the App to timeout at times, since DNS will return either of the two IP's.
View 9 Replies View RelatedHow to find when (date/time) a database was last removed from AlwaysOn Listener?
Is there any query (DMV) to find the details easily?
Considering trying to move 2008 acitve/passive cluster with log ship to day old read only 2008 server to 2012 active/passive to 2012 AG Read only server. Only problem is that read only instance may have to be a 2nd instance on a server. The new box is a beast 64 core 256 gig of RAM hp so this is no dog. So I have these choices
migrate 2008 active/passive cluster to 2012 active passive (this will be it's own ordeal)take new monster box and build two instances, one that will run the AG read only database, the other will house reporitng services and analysis services and a few dw databases. We are not heavy into deep dive analysis services yet kind of in it's infancy. Not sure if this other instance will be sql 2008R2 , may be able to do 2012. MY also have a few small sharepoint databases but they barely use it.
I have set up a couple of servers in a SQL 2012 AlwaysOn Availability Group (non FCI). I have also configured a Listener which enables SQL clients to connect to the server currently servicing the database, as expected.
I would also like non SQL clients to be able to connect to the server currently hosting the database so that they can run scripts sitting in a share. I don't have a shared disk so just have a directory share on each server with the same scripts in each directory.
I am able to ping and RDP to the listener IP address/name and end up on the correct server but am unable to connect to the share ListenerNameShare. Is that actually supported? If it is, any thoughts on what I need to do to get it going. If it isn't what other options do I have?
So I have Availability groups configured as well as the Availability Group Listener, what If I want to change the port that the listener is listening on, do I need to reboot the server or is it dynamic across the board ?
View 5 Replies View RelatedI am trying to build out an AlwaysOn AG with 2 nodes each in a different subnet (in AWS if that matters), windows 2012r2 / SQL 2014 RTM
I created a AG Listener with 2 ip address, 1 for each subnet (checked that neither ip address are used). But whenever i failover the AG to the secondary, and try and connect via the listener it fails,
I am trying to connect via SSMS from the primary instance. and just time out, If i roll over to the primary i can connect no issues, I've tried playing with the connection settings, upping the time out to 30 secs, adding the MultiSubnetFailover=true. etc but not getting any joy.
Currently we have an AlwaysOn AG set up with a listener running on port 1433 (underlying instances are on a non default port). Great, no problems there.If we set up a second AG on the same instance with its own specific listener, can this new listener also be configured to use port 1433?
View 2 Replies View RelatedWe are planning to upgrade our production servers from mirroring to alwayson. Our current mirror setup gives the advantage that it can failover a single database.To have a similar setup in alwayson we are probably going to create an availability group per database. Any other disadvantage in this except for the extra initial configuration work?
View 9 Replies View RelatedIf there is a history kept somewhere of failover events of a database in an AO group? I have 2 replicas with automatic failover and I'm looking for a history of failovers.
View 0 Replies View Related I've started working at an organization that has a sql 2005 cluster with a named instances on it, lets call it Instance1. What they done is to create a dns alias for servername that is the same as the instance name, so when you connect to the sql server you connect "Instance1Instance1".
We want to move to SQL 2014 cluster with AlwaysOn Availability Groups doing the HA/DR, the question is if I create a listener name called "Listener1" is there a way to using DNS or anything else to point "Instance1Instance1" to "Listener1"
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.
Considering that the Listener Port is different to the port which the instance is using?
Setting up a test AlwaysOn Availability Group for one database.
However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.
Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.
I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.
Say I have 2014 AlwaysOn setup and working:
SQLNode1 ->Instance01
SQLNode2 ->Instance02
When I setup my listener: ListenerA...Do I need to use the instance name in it?
ListenerAInstance01 or ListenerAInstance02 depending on which SQLNode is the "active" availability group?
Am I better off to use the same instance name for both nodes, since my goal is to have all databases on both instances in the same availability group and sync'd? When SQLNode1 migrates over to SQLNode2 I will need to update the instance name in my connection string on the listener from ListenerAInstance01 to Instance02? When I connect with SSMS do I just use: ListenerAInstance01 (or 02)?
Need script for below.
1)Add the user ''ADabc' to local admin group in server.
2)Create login 'ADabc' and Grant sysadmin access for ADabc
I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22
I also define an listener . IP .. 23
1- In First step I connected To Listener (23) And in a while I inserted A record to a table .
While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)
2- in second, I Stop the primary .
- I expected this while whitout disconnect, continue.
3- The while code stopped whit this message :
Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
4- I execute again the script, And it worked in new primary.
My questions :
1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?
2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :
Msg 9002, Level 17, State 2, Line 27
The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.
Is this (Fill All space) a reason to switch primary? Or not ?
Environment: SQL Server 2014 on Windows Server 2012 R2.
We have our availability group configured and working. However, when we try to connect to the AG listener from a remote server, we have to use the fully qualified domain name (FQDN) to connect. We'd like to be able to connect just using the host name. Interestingly, ping actually resolves the IP correctly for either.
So, today we have the following:
Dallas: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
Wash: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
and I'm mirroring (synchronous, no witness) a database from Dallas to Wash.Crappy set up. I know.Now customer wants to have the database mirror to another server in Dallas.What are the high level steps to transform my two clusters to use Availability Groups and Always On?Do I need to basically start over and build a new environment?or can I transform my two disconnected cluster?
How you are handling the replication of the many instance-level objects/items (logins, linked servers, server roles, database mail, operators, on and on) to the replicas in an AlwaysOn topology.
I'm especially curious about DBAs managing larger SQL Server environments. In my current environment, we have approximately 80 production SQL instances containing about 650 databases that require high availability and disaster recovery.
We use mirroring today and have a solid, home-grown solution for replicating the instance-level items from production to disaster recovery. AlwaysOn changes things a bit since we'll have multiple replicas and of course the database could be active on any one of those at any time. So my concern is about instance-level items being created in one instance but never deployed to the other instances participating in the AG group.
I created account with permissions in order my program (I use C#.net) can connect to SQL Server 2008 by this account But I don't want that user can use this account to login to DB by SQL Management Studio for security purpose So how to prevent user login to SQL Management Studio ?
Can I setup "db_denydatareader" to MASTER db to prevent the access ?
I have stored procedure .In SP i am using cursur to load data from Parent to several child table.
I have attached the script with this message.
And my problem is how to use direct select and insert or load to speedup the process instead of cursor.
USE [IconicMarketing]
GO
/****** Object: StoredProcedure [dbo].[SP_DMS_INVENTORY] Script Date: 3/6/2015 3:34:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
What I asked for: Three Windows Server 2012 R2 machines with independent storage running a SQL Server 2014 AlwaysOn Availability Group. DB1 would be the primary, DB2 would be a synchronous replica, and DB3 would be a remote asynchronous replica.
What I was given: a two-node Windows Server 2012 R2 WSFC to run SQL Server 2014 Enterprise with shared storage and a third (remote) Windows Server 2012 R2 machine with independent storage, also with SQL Server 2014 Enterprise, to host an AlwaysOn Availability Groups asynchronous replica.
DB1 and DB2 (as Cluster1) share an E: drive. The remote DB3 has its own E: drive. Initially, DB3’s E: drive was claimed as a cluster resource and I couldn’t even see it. I’ve had several ugly days trying to make this work and have temporarily given up, installing DB3 as a standalone SQL Server that is no longer part of the WSFC and pointing everything towards that (it was originally a third node in the WSFC).
Is it possible to create an AlwaysOn Availability Group with nested clusters (i.e. create the AOAG with Cluster1 and DB3 and somehow ignore the individual nodes that comprise Cluster1)?
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
View 9 Replies View RelatedI have a 3 node cluster on which I have installed SSAS as it's own insntance. I have created this as a named instance and can connect to it by serverinstance if I'm on the server itself. However from my desktop I get the error saying instance was not found on server name.
I have defined an alternate port and setup firewall rules and can connect via server:port but not serverinstance. Prior to making this change SSAS was running on default port of 2383 and I could connect just by servername.
I have read many articles for previous versions saying that clustered SSAS will always use 2383 and that you must connect just using servername. However and this is were it gets strange. I have a 2 node UAT cluster with SSAS setup exactly the same way I've described above and I can connect from my desktop as serverinstance.
Should I be able to connect as serverinstances for a named clustered instance in 2012 ?
In our(my company) current design we want to switch from failover clustering to Always On as high availibility solution.
I am currently testing the availiblity Group Listener function and have two questions regarding this.
First of all, is it possible to connect to a a listener by just using its "name" instead of "name,port", it is for our users very inconvenient to start using ports. If this is possible, where can I find information on how to configure this?
Second, is it possible to use the Availiblity Group Listener as loadbalancer or in combination with a loadbalancer to split the users over two or more nodes?*note we don't use azure.
OK, I know about this: [URL] ....
But the script has "ALL SERVER".
What I want is a trigger that is specific to my DB called "JunkStuff". I only want to block a servername from connect to my super dooper DB "JunkStuff".
I am setting up a new pair of SQL 2014 enterprise servers in HA using Availability Groups. One of the servers is located here in our local datacenter (10.0.1.x) and the other SQL server is in our remote datacenter(172.16.1.x). I was able to setup the Windows Failover Custer without much issue. I setup the AG but when I try to setup the listener. I get the following error. I have setup an IP for both networks on the listener. I have confirmed that there is not any DNS records created for AG listener name. But I still get this error.
View 6 Replies View Related