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


ADVERTISEMENT

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

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

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

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 :: 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 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

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

Recovery :: AlwaysOn Listener - Force Connection To Secondary For AD Group

Nov 6, 2015

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 Related

SQL 2012 :: AlwaysOn Avail Group - Prevent Login Direct To Server Instance Only Through Listener?

Sep 30, 2015

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?

View 0 Replies View Related

Recovery :: Migrating Existing Availability Group To New Datacenter Availability Group

Nov 20, 2015

need to migrate a cluster with an AG dtabases to new data center cluster with AG.

I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.

View 17 Replies View Related

SQL 2012 :: Backup Of Availability Group?

Aug 6, 2015

Proposed configuration:

Using a centralized backup server from which to run SQL backups. (this server is not part of this AVG)

AVG with backup preference set to primary.

SQL backup (daily full and hourly logs run from centralized backup server) with target set to AG Listener name.

Any failover should still target Primary Replica.

View 2 Replies View Related

SQL 2012 :: Reestablish One Database In Availability Group

Jul 29, 2014

I've got an availability group with multiple databases, replicating to multiple secondary servers. On one of the secondary servers, some of the databases are not synchronising, and when we try re-establish the sync we get an LSN error. I can't see any obvious way to re-establish only one database on one secondary without affecting all databases on that secondary or affecting that database on all secondary nodes.

The options I seem to have are to either remove the database and then re-add it, in which case this affects all secondary replicas, or to remove the secondary replica and add it, in which case all the DBs are added.

View 3 Replies View Related

SQL 2012 :: Server Clustered Availability Group

Jul 30, 2014

I have 2, 2 node clusters

PROD1(cluster 1) Clustered SQL instance1
PROD2(cluster 1)
DR 1 (cluster 2) Clustered SQL insatace 2
DR 2 (cluster 2)

I have set an availability group up from the PROD instance to the DR instance.How does the AG behave if a SQL instance fails at PROD? Does it try to fail over to Node 2 on Prod before going over to DR? or bring the Replica at DR online straight away? Can we only use Manual Failover of the AG in this scenario to make use of the High Availability of the Windows cluster?

View 3 Replies View Related

SQL 2012 :: Migrating VLDB To Availability Group

Feb 4, 2015

I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases weren't substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.

My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)

The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more. My plan to migrate application is to -

First Outage Window

1) Restore Database from 2008 to 2012 Primary Replica
2) Change application ARECORD(or cname not sure which) to Primary replica
3) Run database on single node until next outage window

Week Later
1) Add database to availability group
2) Change ARECORD/CNAME to listener

What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. How to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.

View 4 Replies View Related

SQL 2012 :: Backup Status In Availability Group

Mar 16, 2015

some of the databases are not backed up in availability groups , they are showing the status as synchronized , how can we check the reason of this ?

View 2 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Per Database

Jun 26, 2015

We 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 Related

SQL 2012 :: Backup / Restore Availability Group Following Failover

Jul 21, 2014

I have 2 servers in a SQL Server Fail-Over Cluster. IOW I use always-on availability groups. I run backups - full, diff and log - regularly via SQL Agent on one server only depending on which is primary. If there is a fail-over, then backups will continue on the other server. If I have to restore a database in an availability group I probably would need some combination of full, diff, and log backups from each server. Would that actually work? I test the backups weekly however I just realized that I never tested that scenario.

View 1 Replies View Related

SQL 2012 :: Schema Changes On High Availability Group Databases

Jul 23, 2014

So we have our HA group servers and databases, now we want to deploy schema changes to the HA group databases.

1.) Can we deploy the changes to Server2.mydatabase whilst still having Server1.mydatabase available to users?

2.) If yes, what is involved in doing so

3.) If no, What is the best suggestions to apply schema changes to HA databases.

View 1 Replies View Related

SQL 2012 :: Availability Group Is Not Ready For Automatic Failover

Dec 19, 2014

how to resolve and trouble shoot the availability group is not ready for automatic failover in sql2012

View 4 Replies View Related

SQL 2012 :: High Availability Group Transaction Log Shrink

Feb 3, 2015

I have a Customer running a database in a High Availability Group and I am not familiar with the set up... They have a transaction log that quadrupled in size during a data import and update which was generated by an external application. They have limited server space and would like to shrink the log again now as this import / update only happens once a year. The way this has always been dealt with in the past was by shrinking the DB and logs after the update.

Now however, when attempting to do a log or db shrink, an error message is generated which says that the log cannot be shrunk as the DB is in use as part of an Availability Group....

The more I search and try to read up on this subject, it looks like the DB has to be removed from the Availability Group before the log can be shrunk and then the Availability Group has to be re-created or restored in some way. Is there a simple solution to this conundrum?

View 9 Replies View Related

SQL 2012 :: Adding Datafile To Database In Availability Group

May 15, 2015

one of my database is configured in availability group , I need to add another datafile to that database , how can I do this?

View 6 Replies View Related

SQL 2012 :: Automated Script Run On Availability Group Failover?

May 29, 2015

Let's say I have a two node AG, Server A and Server B. Server A is normally the primary and Server B is the replica. Whenever the primary fails over from A to B or from B to A, I'd like to automatically run a script that will restart the SQL Agent service on the new replica.

View 3 Replies View Related

SQL 2012 :: Availability Group Not Synchronizing After Network Outage

Aug 18, 2015

I've been working with sql server for some time, but couldn't find any way to fix this problem without rebooting the production and secondary sql servers, which is not very good for uptime .

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

Then the handshaking keeps failing, and the secondary databases all show not synchronizing.

The primary DBs all show synchronized.

I tried following several different troubleshooting guides for always on, but nothing I tried fixed the issue.

The only way to get them in sync is to reboot both primary and secondary. Just rebooting the secondary didn't fix it.

The secondary server error log showed AlwaysOn Availability Groups connection with primary DB established, then DB mirroring login succeeded, then error 35250, the connection to the primary replica is not active, then connection timeout.

I increased query connection timeouts to 60, but that didn't work.

View 3 Replies View Related

Recovery :: Availability Group Loses Connection To Secondary Server

Jul 30, 2015

I have a SQL 2014 SP1 set of servers with two asynchronous copies of an availability group.   One of the asynchronous sites is down and SQL can no longer replicate the changes. I need to understand how long SQL Server can continue this way before the secondary replica will no longer be able to catch up. I assume this is really tied to the transaction log on the primary replica but would like it clarified.

View 4 Replies View Related

SQL Server 2012 :: Automatically Adding New Databases To Availability Group

Oct 28, 2013

automatically replicates new databases to Availability Group partners - if you do a little prep work on your environment first.To make it work:

1) Create linked servers on all group members pointing to all other servers in the group, with names matching the hostnames they represent.
2) Ensure suitable credentials (or 'current context' impersonation) for linked servers. Also: Enable RPC and RPC OUT
3) Run the DDL code below.
4) Schedule hadr_replicate_queue on [master] to run as often as you want initial syncs to occur. Every 5-10 minutes is plenty for most purposes.
5) Connect to an availability group listener and call CREATE DATABASE :)

I use a slightly more extended version of this code at home to do things like permissions synchronization across replicas - I essentially allow applications to install direct to an availability group replica and then have all the relevant objects replicate to other nodes. I don't really like going through manually and doing things, even though there's an AddIn from SQLSkills for management studio - it still requires manual intervention.

The main use I have for this at home is that I'm using the Azure pack, and want to automatically ensure that my newly created 'SQL Server Cloud' databases are highly available, plus it means when I install a non-alwayson aware product it doesn't require any extra work afterwards to allow failover to another machine.

* AlwaysOn Self-Population Script
* By: Steve Gray / steve@mostlyharmful.net
* Usage: Free, but buy me a beer if you're ever in Brisbane.
**/
USE [master]
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')

[code]....

View 4 Replies View Related

SQL 2012 :: Availability Group Failover Stops Working After First Failure

Apr 21, 2014

I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different failover scenarios to see that everything is working properly. Everything works fine until I try testing the failure of the SQL Server service. When I stop the SQL Service on the primary server, it fails over to the secondary server as expected. I then start the service on the (now) secondary server and it comes back online as the secondary server. I then try to test that the service will fail back over when I stop the service on the new primary server.

However, when I stop the service, the secondary server now shows "resolving" and never comes back online. When I bring the service back up on the primary server, the secondary now shows as secondary instead of resolving. So to see if it's something about failing over from one server to another, I do a manual failover making the original primary server the primary again and everything is as it was originally.

I then stop the service on the primary server, but the secondary server now says resolving and the AG will not become available again until I start the service on the primary server.

It seems that when I first configured the quorum it worked fine the first failover scenario, then stopped working. I then added the file sharing witness, and failover worked the first time again, but not after that. For some reason after the initial failover it won't automatically failover again after that.

Config:

Servers: Windows Server 2012 Standard
SQL : SQL Server 2012 Enterprise SP1

View 5 Replies View Related

SQL 2012 :: AlwaysOn High Availability Group Showing All Databases

Apr 28, 2014

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?

View 8 Replies View Related

SQL 2012 :: Linked Servers Connecting To High Availability Group

Dec 16, 2014

I have a HA Listener which is visible and can be connected to, it has a read only secondary on a different subnet so when connecting to it we use the applicationintent = readonly and multisubnetfailover = true.

Trying to connect it as a linked server is giving me problems. I tried putting the extra info into the provider string but keep getting the failure to initialise error. I am trying to link SQL2012 to a 2012 HA group but will also need to connect from a sql2008 server as well

View 1 Replies View Related

SQL 2012 :: Compress Data Transfer During Synchronization Between Availability Group Nodes?

Apr 13, 2015

I got this situation where my network admin observerd that there is a high network utilization between 2 nodes in our AG (the primary node & the DR site, 2 separate locations of course); then he advised to compress the data transfer between those 2 nodes as the previous DBA already did that before!

Ok, I have no clue about this, so decided to google it, got nothing. My backup is already compressed through some third party app (just in case if that matters to the subject).

View 3 Replies View Related

SQL 2012 :: How To Check For Successful Full Backups On Availability Group Databases

Apr 20, 2015

I am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?

Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?

Do you just check that the SQL Agent job that runs the backup was successful?

Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?

Or is there a better method?

View 1 Replies View Related







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