SQL 2012 :: Find When Database Was Last Removed From AlwaysOn Listener?
Feb 25, 2014How to find when (date/time) a database was last removed from AlwaysOn Listener?
Is there any query (DMV) to find the details easily?
ADVERTISEMENT
SQL 2012 :: Error At Adding Listener For AlwaysOn
Jun 11, 2015I 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....
SQL 2012 :: AlwaysON Multi Subnet Cluster / AG Listener
May 3, 2013When 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 RelatedSQL 2012 :: AlwaysOn Availability Group Listener DNS Resolving To DR Node
Jan 19, 2015We 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.
SQL 2012 :: AlwaysOn Avail Group - Prevent Login Direct To Server Instance Only Through Listener?
Sep 30, 2015Is 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?
SQL 2012 :: Can't Find Online To Setup AlwaysOn From Scratch
Apr 29, 2015I am trying to setup alwayson on 2008r2 server on 2 instances in the same machine fort testing purpose. I installed 'Failover Cluster Manager' now I don't see 'Nodes' to add up.Should I run 'Validate a configuration Wizard'? or something else.. Can't find online to setup 'Alwayson' from the scratch...Will I able to setup 'Always on' on same server but different instances like mirroring? or has to be different servers?
View 5 Replies View RelatedSQL 2012 :: AG - How To Find Cluster Failover Count In AlwaysOn
Sep 30, 2015How can we find the cluster failover count in always on ?
As my AG is configured as synchronous mode , AG went offline and we manually restarted the AG service when we check the properties on AG role they r in default setting ?
SQL 2012 :: Tables Removed From Diagram After Database Restore
Jun 9, 2015In last week my database was crashed and some how i managed to restore it back on SQL2K12 but after restoration all the relationships are removed and sql server is showing below message when i open diagram of the database.Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.how to get back all the relationships of the tables.
View 2 Replies View RelatedAlwaysOn - Use Availability Group Listener Or Just Point To Cluster Name
May 6, 2014I 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.
SQL Server Admin 2014 :: AlwaysOn Listener On Multi Subnet AG
Feb 16, 2015I 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.
Recovery :: AlwaysOn Listener - Force Connection To Secondary For AD Group
Nov 6, 2015I 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 RelatedKerberos Ticket Encryption Type And AlwaysOn Availability Group Listener
Jul 6, 2015I 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?
Recovery :: Cannot Create Listener For High Availability Group Of AlwaysOn On Cluster Environment
May 27, 2015I 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) ...
SQL 2012 :: Log Management Of Primary Database In AlwaysOn?
Mar 1, 2015In always on docs in msdn they mention only about backup of secondary.. explain the backup of primary or how logs are managed in primary database. My doubt is a normal database in full recovery mode the log file will grow if we didnt take proper log backup,how the same is managed in primary in Always On.
View 2 Replies View RelatedSQL 2012 :: AlwaysOn Database Not Synchronizing / Suspect
Mar 2, 2015We have a database in an AlwaysOn Availability Group that has gone into a state of Not Synchronizing / Suspect on the secondary.
The reason why this happened is because the secondary ran out of disk space so the log file wasn't able to be written to. The database was set to synchronous mode.
Is the only way to recover from this to do a re-initialization or is there another way to recover?
SQL 2012 :: AlwaysOn Availability Group Per Database
Jun 26, 2015We 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 RelatedSQL 2012 :: Database Failover History In AlwaysOn Group
Jun 16, 2014If 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 RelatedSQL 2012 :: How To Check Errors When Database Is Configured In AlwaysOn
Dec 15, 2014how to check the errors when a database is configured in Always on in sql 2012?
View 1 Replies View RelatedSQL 2012 :: AlwaysOn Secondary Database In Restricted Mode?
Jun 27, 2015Secondary replica database(setup in async mode) of AlwaysON went in "restricted mode" during weekly reindex operation.
So I have tried below steps
1) Executed following statement on the same secondary replica database where the issue exists
alter database <DBNAME> set multi_user with rollback immediate
but it failed with the error saying "The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed."
2) Primary database is multi_user but still tried following command on primary replia database(thinking it will replicate)
alter database <DBNAME> set multi_user
but no luck. The secondary alwaysON database shows (synchronizing) as the alwaysON is set in async mode but the command doesn't replicate across secondary
so we are left with the only option to re-setup alwaysON but I want to avoid it as database size is huge..
any other options or am I missing anything?
MCP,MCDBA(SQL 2000),MCTS(SQL 2005),MCITP(SQL 2005)
SQL 2012 :: Enable / Disable AG Listener
Jul 3, 2014How can one enabledisable Availability Group listener? I know AG listener can be deleted and added but didnt find anything to disable or enable that.
View 1 Replies View RelatedSQL 2012 :: SSIS Connection Initial-catalog Removed When Adding Expression To Set It Dynamically?
Feb 14, 2014SQL 2012 SSIS package.I have a package connection that has the initialcatalog set in the connection string/properties page. This package connection also has an expression defined to set the initial catalog at runtime according to a passed in parameter. It works fine.
I am trying to create a second package in this same manner, but the connection does not seem to want to keep both the hardcoded initialcatalog and the expression to set it dynamically.
I can hardcode the initcatalog just fine, when when I add the expression to set it dynamically later, it clears out the initialcatalog I added.
What am I missing, why was I able to do this in the other package? I compared as much as I can think of between the two packages, all seems similar.
SQL 2012 :: Cannot Connect With Read Only Intent When Using Listener Name
May 6, 2014I have an issue when connecting with application Intent=readonly when using Listener name. I can connect to the read only secondary with application Intent=readonly if I use the server name. It gives a host does not exist error when using listener. This happens only if I am using the application Intent keyword. I can connect for normal readwrite operation using listener name.
View 3 Replies View RelatedSQL 2012 :: Using Availability Group Listener For UNC Connection?
Sep 10, 2014I 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?
SQL 2012 :: Availability Group Listener Port
Oct 26, 2014So 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 RelatedSQL 2012 :: Removing IP Address From Listener Pool When Demoting One Of Remote Sites
Aug 15, 2014We have a multi-site AG and are demoting one of the remote sites out of the AG. In doing so, we've discovered that no logic exists to remove an IP address from the listener.
It seems that the ALTER AVAILABILITY GROUP MODIFY LISTENER logic lacks functionality other than ADD. [URL] ....
I'm afraid to just remove the IP address from the cluster object as that IP is also stored in the HADR systables.
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
Recovery :: Find Errors In AlwaysOn Synching
Oct 16, 2015We've just started using AlwaysOn High Availability and run into a wierd issue. I have 1 particular database that is not syncing data to the secondary replica. But when i look at all the dashboards, everything is green, they all say synchronised - but if I query the data they are not.
The database has additional sql transactional 1 way replication a different server which i was wondering if was causing any problems, but it's working ok.
I'm just wondering if there's any other more detailed logs i can check to see why the data is not flowing. The availability dashboard and it's event log all says ok.
SQL 2012 :: How To Find Available Database Space
Sep 8, 2015How to find available database space?
View 3 Replies View RelatedSQL Server 2012 :: How To Find Particular Table Is Present In Which Database
Jan 19, 2014I have created a table but i forget in which database. I know the table name but i dont know the database name..
I have to filter my table from all database ... how can i do and find....
SQL 2012 :: Possible To Find All Tables That Has A Join With Given Table In Database
Jan 7, 2015I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.
sys.dm_sql_referencing_entities doesn't work here.
Note: i dont want to identify by FK References, for 2 reasons:
1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined
How To Recover SQL Database From A Removed Laptop Hard Drive?
Jul 23, 2005Dear group:I have removed my hard drive from my laptop (which is now toast) andhave managed to recover nearly all the data from it by installing thedrive into my desktop. I was hoping to reboot the dektop to see if Icould load the operating system on the laptop's hard drive so I coulddo a manual backup of the SQL database on it. This does not work.Does anyone know of a way to recover my SQL database and all its tablesgiven the circumstances above?TIAISZ
View 5 Replies View RelatedSQL 2012 :: Find Buffer Cache Usage By DB Objects Within Particular Database
Jun 22, 2015I am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.
I am running the following query
select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb
[Code] ....
Following are the results:-
DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....
Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.
SQL Server 2012 :: Find Common Link Between Two Tables In Database Without Key Constraints?
Jul 17, 2015CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
SQL 2012 :: AlwaysOn Pending Changes
Aug 18, 2014How to find the pending transactions to be applied on secondary in Always On? Can i get those details from dashboard?
View 2 Replies View Related