SQL Server Admin 2014 :: Disconnected From Listener In Availability Group (When Primary Failed)

Jun 27, 2015

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 ?

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Exit From Not Synchronizing Primary Database In Availability Group

Jan 27, 2015

I Create an availibility Group with a primary and a secondary. (For test)

Then run Planed Failover.

Switched to secondary. That's ok.

After that I update some tables on secondary (That now is primary)

I Run Again Planed Failover on server 2.

Switched. OK

But primary database Get (Not synchronizing) Status.

And in primary I don't have that updates.

How to sync these databases and exit from Not synchronizing.

View 5 Replies View Related

SQL Server Admin 2014 :: Availability Groups - Adding One More Listener

Sep 1, 2015

We have a server A and a server B setup with SQL2014 High Availability - working fine.

I need to add an additional listener to our new DR site.

What is the best way to do this without impacting production - can I just add the additional replica from nodea as a secondary replica?

View 1 Replies View Related

SQL Server Admin 2014 :: Read Only DNS Listener Failover Automation From Primary To Secondary

Mar 23, 2015

I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.

Env details
xa (current primary)
y2a (secondary)

client access point (resource group): aglreadtest

View 2 Replies View Related

SQL Server Admin 2014 :: Separate One Availability Group To Several AVs?

Jul 3, 2015

We have 4 servers : Server 1 , Server 2 and HA , DR servers.

I designed 2 Plans to get HA support for my databases .

Which of them are better , And is there any problem in my design ?

View 4 Replies View Related

SQL Server Admin 2014 :: Notification On Availability Group Failover

Nov 10, 2014

Is there any way to trigger an event (ie. call a procedure/job/etc.) when/if an AG fails over?

Not looking to use agent alerts.

View 0 Replies View Related

SQL Server Admin 2014 :: Manual Failover Availability Group

Dec 24, 2014

Recently after turning on trace I restarted the sql services on a box which is configured for automatic failover availability groups. The ag has not failed over to other node. The other node was in resolving state. When the restarted server is back, the AG went back to that server. I checked the sys.availability groups field for failover property failure condition level, it's set to 1 which means service restarts should initiate the failover.

View 3 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Availability Group Configuration

Jun 17, 2015

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)?

View 6 Replies View Related

SQL Server Admin 2014 :: Where To Find Availability Group Fail Over Occurrences

Nov 14, 2014

Where can I find dates and times to when an availability group was moved outside of the SQL error log?

View 1 Replies View Related

SQL Server Admin 2014 :: Availability Group Handshaking Fails After Network Outage

Aug 14, 2015

I'm running a primary and secondary on sql server 2012 enterprise edition on windows server 2012, and it runs fine except when a network outage occurs.

Then the handshaking keeps failing, the databases on the replica show as not synchronizing and the only way to fix this is to reboot both primary and secondary.

We keep getting 3520's, etc. on the DR error log

How to eliminate all these prod reboots?

I increased query connection timeouts to 60, but saw no change.

View 0 Replies View Related

SQL Server Admin 2014 :: Re-balancing Blob And Lob Data In Multiple Files On Primary File Group

May 7, 2014

A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.

I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.

I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.

One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.

View 2 Replies View Related

Recovery :: Unable To Connect To Availability Group Listener From Remote Server Without Using FQDN

Jul 21, 2015

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.

View 3 Replies View Related

SQL Server Admin 2014 :: DNS Pointing To A Listener

Jun 25, 2014

In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?

View 1 Replies View Related

SQL Server Admin 2014 :: Rebuild Indexes On Listener

Jul 27, 2015

Do I need to rebuild my indexes on my High Availability listeners?

When I do a full index rebuild on my primary DB's. Does rebuilding also send the rebuild to the listener(s)?

View 1 Replies View Related

SQL Server Admin 2014 :: Monitor AG Listener Remotely?

Jul 28, 2015

I need to check connectivity to AG listener remotely and log that to a table/file. What is the best way to do that? Powershell?

View 4 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Listener On Multi Subnet AG

Feb 16, 2015

I 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.

View 2 Replies View Related

SQL Server Admin 2014 :: Does Each Node Need To Have Same Instance Name For Listener To Work?

Apr 15, 2015

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)?

View 3 Replies View Related

SQL Server Admin 2014 :: Application Connecting To AG Listener Static IP Address - Not Name

Jul 22, 2015

We have a bought in application written in Visual Basic 6 using ADO 2.7 to connect to SQL Server. The application has two connections strings and uses internal logic to determine which of the two connection strings connects to a read-write database. It does something as simple as trying an update that makes no changes to a very small table somewhere.

Previously this application connected to a Mirrored Database hosted on two Windows Server Failover Clusters and SQL Server 2005.For the future the App will be connecting this application to a SQL Server 2014 Availability Group.The Availability Group is made up of four servers, in a cluster, two at one site on Subnet A and the other two at a second site on Subnet B. The Availability Group has been given a Listener with a DNS name of AGLISTEST on port 1433 with two Static IP addresses, one for each subnet.

Our Contractor has configured the Application’s connection strings to connect to the two Static IP addresses assigned to the AG. He is of the opinion that although ADO has zero understanding of an Availability Group, at least one of the Static IP Address will translate to the Availability Group primary server because it is a Cluster Resource and so act like a Cluster IP Address on Windows Server Failover Cluster; i.e. sort of mimicking the previous configuration. This does work and he has demonstrated it, however I can’t find any article that proves to my satisfaction this is intended behavior not something that accidentally works. If it is accidental behavior then that would be something that might stop working at an unexpected point in the future.

All the research I have found relates to connecting to the AG Listener name, and all the multi-subnet issues and Active Directory issues that go with connecting using what is a legacy client.So; the question is, is this translation / redirection of static IP Addresses assigned to an Availability Group listener documented and safe?

View 3 Replies View Related

SQL 2012 :: Using Availability Group Listener For UNC Connection?

Sep 10, 2014

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?

View 0 Replies View Related

SQL 2012 :: Availability Group Listener Port

Oct 26, 2014

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 Related

AlwaysOn - Use Availability Group Listener Or Just Point To Cluster Name

May 6, 2014

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.

View 5 Replies View Related

Recovery :: Availability Group Listener Connection Without Port

May 27, 2015

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.

View 4 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Listener DNS Resolving To DR Node

Jan 19, 2015

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.

View 3 Replies View Related

SQL Server Admin 2014 :: CDC And AlwaysOn Availability Groups

Apr 2, 2015

The MSDN doc makes it sound like after a failover of the primary, the CDC data won't "keep working" on the secondary unless you "To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP <group_name> REMOVE REPLICA. Then add a new secondary replica to the availability group."

We have a few CDC tracked tables that we use and the general idea of AlwaysOn I thought was to minimize all the overhead and let things "just work" so your apps just connect and the listener re-routes everything where it needs to go.

It looks like to get this working properly an automated job /trigger would have to wait for a failover event and then kick off tasks to remove and re-add the replica and perhaps start up the CDC job on the secondary?

View 1 Replies View Related

SQL Server Admin 2014 :: High Availability - Job Error

Aug 18, 2015

I am trying to create a job that runs against my High Availability listener server.

It is a fairly simple SQL statement in the job - execute tsql.

When I try and run the job I get the error:

Executed as user: NT SERVICESQLAgent$SQL2014A. The target database ('BB_Prod') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. [SQLSTATE 42000] (Error 978). The step failed.

I thought there was a way to run a select statement as a job against the listener? The tsql step is only a select.

Is there a way to pass in the application intent = readonly as part of my SQL statement?

View 2 Replies View Related

Kerberos Ticket Encryption Type And AlwaysOn Availability Group Listener

Jul 6, 2015

I 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?

View 5 Replies View Related

Recovery :: Fail To Create Availability Group Listener With Multiple Subnets

Oct 28, 2015

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

SQL Server Admin 2014 :: How Does Security Works Between Availability Groups

May 21, 2014

how does security works between availability groups.

ex if i create an object and grant permissions to a user will that be replicated to secondary replica .

View 4 Replies View Related

SQL Server Admin 2014 :: Load Balancing With Availability Groups

Aug 28, 2014

We are having a conversation at work and the subject of load balancing with SQL came up. Right now we are running SQL Server 2014 on four (4) machines. I am using a AlwaysOn with Availability Groups (AG). Now I know that we can scale out the reading in AG by allowing the secondary serves to receive reads.

Is there a way to be able to do this with writes? Can I have in essences 2 masters that some how reconcile with each other? We are expecting a huge amount of writes in the near future and we need a way for SQL to handle the amount of traffic we are expecting with out any issues.

I explored the possibility of Peer - to - Peer replication; however, it seems that it would be more work if we are constantly making updates to the database scheme.

View 2 Replies View Related

SQL Server Admin 2014 :: Backup Jobs On Availability Groups?

Feb 11, 2015

we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.

we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .

View 3 Replies View Related

SQL Server Admin 2014 :: High Availability Index Rebuilds

Jul 9, 2015

I have a situation where I need to rebuild indexes on a large DB (500G).

When I do a test run of the rebuilds in my test environment it uses 100G of space - which is fine with me.

When I do a rebuild in my High Availability environment - same DB, same script - it eats up over 600G of space and fills the volume.

What can I do without removing my DB from H/A to rebuild the indexes?

View 8 Replies View Related

SQL Server Admin 2014 :: Replication On AlwaysOn Availability Groups

Sep 15, 2015

I am planning to have AlwaysON Availability Groups setup between Server 1 and Server 2

Server 1 -->Publisher-->2014 SQL Enterprise edition-->Windows Std 2012 --> Always on Primary Replica

Server 2 -->Publisher(when DR happens)-->2014 SQL Enterprise edition-->Windows Std 2012 --> Secondary Primary

Server 4 as Subscriber

Server X as Remote Distributor ..

If i create Publications on Server1 (primary replica) to subscriber 4 servcer, will the publication be created automatically in Secondary Replica Server2 ? or do i have to create manullay using GUI/T Sql on Both Servers?

View 1 Replies View Related

Recovery :: Cannot Create Listener For High Availability Group Of AlwaysOn On Cluster Environment

May 27, 2015

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) ...

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved