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
ADVERTISEMENT
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
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
Nov 13, 2014
I am using SQL Server 2012 and my AlwaysOn High Availability features is not enabled? What should I do. Is this requires any extra system requirements to be installed?
View 5 Replies
View Related
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
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
Nov 29, 2014
Secondary server is offline due to a hardware issue and the log files are growing on Primary sever for availability databases. The log drive is running out of space. How can I stop the log growth for the primary databases?
View 4 Replies
View Related
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
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
Sep 5, 2014
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.
View 2 Replies
View Related
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
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
Nov 3, 2015
We are looking at going down the High Availability Always On route. However we have some concerns around the lack of support for MSDTC. In short we are concerned that developers may introduce functionality either on purpose or by mistake that uses the or escalates the Query’s to the MSTDTC. As this could result in database splitting.
Understand that this will be a moot point in SQL 2016 but for 20122014 is it possible to disable the MSDTC to protect against this and run High Availability Always On. ? Does it just need to be disabled on the SQL Server or does it need to be done on the application server ?
View 0 Replies
View Related
Aug 13, 2015
How do I add my second (secondary) node in my AlwaysOn Availability Group, after adding my head node, and the secondary node is a virtual machine. See based on the attached file if it is the correct way?
View 2 Replies
View Related
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
Aug 17, 2015
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies
View Related
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
Sep 22, 2015
I have an active passive cluster on my primary Data center in NY and have a DR Active / passive SQL Cluster in TX. These are two separate clusters in the same domain using the same SQL server credentials.Both clusters host an active / passive SQL instance. Lets call it SQLNY(Primary) and SQLTX (DR). I want to enable always On Availability group within the two SQL Instances SQLNY and SQLTX. The listener will be SQLAG which will be used by the Application to connect to the SQL instance. Is there a practical way to implement this? This will not only give me instant fail over within the NY (Primary) but also give me the flexibility to fail over to TX. I am using SQL 2014 Enterprise Edition on both clusters.
View 6 Replies
View Related
Jul 11, 2014
I have a situation where I have two servers in SQL Server 2012 R2 AlwaysOn Availability Group. One is primary and the other one being secondary. I am only running SharePoint Database on it.I have run out of space on the primary server and about to run out of space at the secondary server. I have tried shrinking database transaction log files, but it returns an error that it cannot be shrunk as the database is in the AlwaysOn Availability Group.
Questions:
1. Several forums suggest that databases need to taken out of AlwaysOn Availability Group in order for the shrinking to work properply?
2. Would it have any impact on the database if it is taken out of availability group and then added back?
View 6 Replies
View Related
Apr 29, 2015
Is AlwaysOn Availability Group support SCCM , SCOM and DPM ??what is the best Solution for this products to be highly available ??
View 3 Replies
View Related
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
Mar 9, 2015
We have H/A setup on SQL2012. Seems to work fine.I thought there was code I could add that would check to see if it was running on the primary node?
View 3 Replies
View Related
Feb 13, 2014
I have four instances and each instance have its own Availability Group with its own listener.
Would like to know if you can have one listener for multiple Availability groups?
View 2 Replies
View Related
Apr 28, 2014
We had 3 Availability Groups set up in SQL 2012 last year but they were poorly named so I am just looking to rename them but there doesn't seem to be any command for it that I can find.Can they not be renamed once created? I guess I could just create new ones and move the DB's into them but just thought I would check!
View 1 Replies
View Related
Sep 15, 2015
We have multiple SQL 2012 SQL servers setup in an alwaysOn availability groups. Where should we schedule the re-index? We have Server1 as the primary and 2 secondaries Server2 and Server3. Are their any tricks to have it run on which ever one is the primary?
View 1 Replies
View Related
Jun 9, 2015
I inherited a SQL 2012 Ent server sitting on a 2008R2 server using AlwaysOn High Availability, two nodes.
Available Mode: Synchronous commit
Failover Mode: Manual
Connection in Primary role: Allow all connections
Readable secondary: No
seesion timeout: 10
Somebody decided to give SQL server priority boost so I need to change this ASAP. So I plan on doing the following.
1. Manually fail over to the secondary, which does not have the priority boost set to true
2. change the setting
3. restart the service
4. Manually fail over
My question is with the service restart. How does SQL handle if the DB changes on the new primary while the secondary is having the service restarted. Where can I see if the DB are sync again or if not where are they in the sync process.
View 2 Replies
View Related
May 19, 2015
Discovered that a geo-spatial AlwaysOn HA database (1 of 4) was not synchronizing as at a point in time earlier in the day. Suspend Data Movement appears to be working perpetually without finishing. The SQL Server services is in a Pending Changes state after an attempt to restart it from SQL Configuration Manager. The Cluster Dashboard says it is in a Not Synchronizing state, with only the one database in question having a yellow triangle, all 3 others show green.
The warning for the cluster is:At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state. There is no abnormal data movement from the primary to the seconday.The warnings for the unhealthy database are:
The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.Either a database administrator or the system has suspended data synchronization on this availability database.So how to get this database back to synchronizing state?
View 2 Replies
View Related
Jul 9, 2014
We have a vendor that is exposing our database via a High Availability replica. They are geographically far away from us though so we would like to extract portions of the database over to our side for our reporting /warehousing purposes. I was curious if it is possible to setup snapshot replication on a high availability group?
View 0 Replies
View Related
Mar 26, 2015
We have two server configured in always on mode .
windows is sql server 2012 64 bit edition and sql server is 2012 64 bit edition.
RAM installed on both server is around 65 gb of which 49 gb is max server memory allocated for sql services on both servers.
database related to reporting services are also in always on group .
We have also configured for reporting services and both are running on their respecting server.
Issue is on primary server reporting service is using almost 7 gb while on secondary it is using 10 gb even when there are 5 reports and its used within offices .
what issue and how to check why ssrs is using high memory..
any query , perfmon counters
reports are randomly used at client side
i have checked memory utilization through task manger..
View 0 Replies
View Related
Apr 23, 2007
Hi,
Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.
If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?
Thanks,
J.
View 3 Replies
View Related
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
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
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
