I want two write a small script to determine which is the currently active (primary) server in the AG.
Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?
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?
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 .
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.
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 .
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?
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?
This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.
View 1 Replies View RelatedWe 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 ?
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?
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.
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 RelatedWhat 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 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?
is there a way to restore all file groups except one? example: Database A has 10 filegroups, but 1 of them is defunct, so i cant delete it and there's no backup for restore it.Can I create a new DB restoring the 9 good FGs from a database A's backup?
View 9 Replies View RelatedWhere can I find dates and times to when an availability group was moved outside of the SQL error log?
View 1 Replies View RelatedWe 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 ?
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.
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 ?
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.
We have a SQL 2014 active passive cluster with 5 instances. When the cluster was installed one of the nodes was a virtual machine. As we started to have problems and this is not a Microsoft supported configuration we decided to replace the virtual node with a phyiscal one. On 3 of these 5 instances we configured static ports (1433 TCP) this was required for applications and firewall implementation.
Now with the physical node joined to the cluster we have issues with these three instances. Whenever one of these instances is moved to the passive node the server authentication is changed from mixed to windows only. I'm no SQL expert at all but to me it looks like the configuration of the instances are not replicated to the passive node? I found some similar problems on the net but these are mostly under sql 2008.
I am trying to setup querying Active directory from sql for the first time.
We are running on windows server 2012 and using sql 11.0.2100.60. Have tried the following
sql is on sever dev
AD is on sever DO
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
GO
[Code] ....
I get the following error when I try and query
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT name
FROM 'LDAP:// xxxx.internal'
WHERE objectCategory='Person' AND
objectClass = 'contact'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
I have more than 3000 Active Directory Users, I have created Role Level Security on one table by using Suser_name(), Now all the active directory users need to connect sql server and access the database role object. How can I achieve that without Using .net?
I am able to add all active directory users to sql server in one go, but I am also trying to achieve same time to map with database role as well? Is it possible ?
we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server. When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server. Or how can i automatically close connections when they get higher than 50 connections.
This settings should be sql server 2008 related.
I am running into a weird issue with a new SQL Reporting Services 2014 server I built. I installed SQL Reporting 2014 on Windows Server 2012 R2 and configured Kerberos, but the site is extremely slow. After some reconfiguration and log captures I have determined the issue has to do with the Kerberos setup, however I am running a similar configuration with SQL Reporting Services 2008 on Windows Server 2008 R2 and do not run into the same errors.
The error I see while using Wireshark is KRB Error: KRB5KDC_ERR_BADOPTION NT Status: STATUS_NO_MATCH. When I drill down the into the error I can see the kerberos string is testprjmnmtreports14.company.com, which is the URL we are using to access the site. I made sure to add that name as an SPN for the service account that is running SQL Reporting Services, however I still receive the error.
Then I tried configuring the site to run without a hostheader, so I accessed the site with the server name, ECTSTSQLRS5, and the site works perfectly fine, no errors are reported either. So it seems I have isolated the issue down to Kerberos but I am not sure how to resolve it. Here is some more information about my environment:
DNS/URL used: testprjmnmtreports14.company.com
Server Name (FQDN): ECTSTSQLRS5.company.int
AD Domain Name: company.int
Server Version: Windows Server 2012 R2
AD Functional Level: 2008 R2
As you can see I am trying to use a .com address but my AD domain is .int which I think is the issue, but I do not have the same problem on my other server that is running Windows Server 2008 R2. What do I need to do to allow my new site on 2012 R2 to work with this DNS Alias?
I have configured replication between Always ON Availability Groups (Listener) (PUBLISHER), remote distributor to XYZ SUBSCRIBER...with above link ...
Now, I want to know how to replicate Data from XYZ SERVER a PUBLISHER to Always ON Availability Groups (Listener) (SUBSCRIBER)? Distributor Database being on XYZEX:
XYZ SQL SERVER as PUBLISHER, and DISTRIBUTOR
to
Always ON Availability Groups (Listener) SUBSCRIBER...
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 ..
as of now , In the event of Disaster happens on Node 1 i will have to manage JOBS on Node 2 (backups, Maintenance, replication, day-day other Jobs)..Is there a way i can call all jobs to run from a remote server which executes on Active NODE(which ever is active)? so that in the event of disaster happens on node1, when node 2 becomes online and i don't have to do anything with SQL Agent or jobs on Node 2?
Hi all,
just want to confirm this:
We have a department and want to grant them access to various databases on a sql server 2000 instance. Can we create an AD group for these users and just add the group as a login to SQL Server? Or do we have to create a db user for each member of the group and map the logins one by one?
Thanks
I am setting up a 3 node cluster as part of an availability group.Initially I tested failover between nodes using SQL Management Studio and everything failed over successfully when I stopped a node, and I was still able to write queries. I started to test with an application which connects using an SQL user and whenever I would switch nodes, I would get login failed. I believe the cause of this issue is because the server logins SID's which are tied to the database are different than the server logins on the other nodes which resulted in login failed.how can I ensure that the server logins SID's are the same between nodes? Is there a way of copying this over or how is this supposed to be done? I read a little about contained databases where I could just set the login on the database itself vs. creating a server login but I would rather not go down that route.
View 4 Replies View RelatedI want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.
I changed the Active Directory Group login to an individual active directory login and the connection worked fine.
I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?
Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.
Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?
We are currently using 2008 environment. We do have an SSIS Package running. The package used to run everyday and take the production server full backups and restore into the another server. Then do some delete commands and do some updates in that database on that server (We have some sensitive data other than Production we have to run that scripts in any environment). After run all those delete statements another team will read the data from that database.
We are planning to migrate to 2014 and set up always on and use the replica as the source. In this case how the package will work?
How to change that SSIS package. With the 2014 always on we are directly reading the data there is no backups to restore then how to run the delete statements?