New answers tagged

3

Picking up on the sqlserver.read_only_route_complete Extended Event mentioned by Kin and Remus, it's a nice Debug event, but it doesn't carry a great deal of information with it - just route_port (eg 1433) and route_server_name (eg sqlserver-0.contoso.com) by default. This would also only help determine when a read-only intent connection was successful. ...


0

Unfortunately I don't have the environment to test the following, and there are undoubtedly several points at which it may fail, but I'll throw it out there for what it's worth. A CLR stored procedure has access to the current connection through the new SqlConnection("context connection=true") construct (taken from here). The SqlConnection type exposes a ...


0

How sick do you want to be? The TDS stream isn't that hard to proxy, we did it for our SaaS app. The bit you're looking for (literally a bit) is in the login7 message. You could have your users connect via a proxy and log/enforce the bit there. Hell, you could even turn it on for them. :)


3

No, it does not appear that there is any DMV-exposed connection property (either in sys.dm_exec_connections or sys.dm_exec_sessions) or even CONNECTIONPROPERTY that relates to the ApplicationIntent ConnectionString keyword. However, it might be worth requesting, via Microsoft Connect, that this property be added to the sys.dm_exec_connections DMV as it ...


-1

Does your application use a service account or maybe multiple service accounts? If so, use Extended Event to monitor your login traffic but exclude your service accounts on your primary always-on server. You should now be able to see who is logging into the primary always-on server and not using the readonly secondary connection string. I am getting ready ...


0

You can run SELECT @@SERVERNAME which will give you the current servername you are connected to. Would this suit your requirements?


10

It is not possible to delete a database that currently resides in an availability group. It is also not possible to restore over top of it while it remains in the availability group. Thus, this database was not in an availability group to begin with. What you are describing is not possible as you will get an error if the database is involved in Always On. ...


1

The shared network location in that wizard is only used in the wizard and only used as a temporary location to backup the databases to and restore from that location to any of the replicas that could host that database. Apart from that, it has no use and is not used internally anywhere in availability groups. You can easily change the value to whatever new ...


12

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database? It depends on the sync mode you have used when configuring Availability group - Sync or Async ! On Secondary replica, all the transactions use Snapshot Isolation level ONLY and all locking hints are ignored as well. That's ...


0

I have working SQL to determine which machine is the PRIMARY or SECONDARY server and to use within the Stored Procedure scheduled to run on both machines SQL: select ars.role_desc,ag.name,ars.is_local from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id NOTE: I was expecting the ...


0

I think the question has a wrong assumption. So is there a way to keep a single (primary) replica server active in the AG for the databases without removing them but keeping the log file from growing or being stuck in 'AVAILABILITY_REPLICA'? ... I'd rather not remove the AG as the listener is actively used and would require reconfiguration of ...


5

This is completely expected. I've talked about this before but I'll recap it for you. There could be multiples reason you are seeing this behavior. The two that most people are confused or inaccurate of how it works are how availability groups synchronize data and how queries on readable secondary replicas work. Please note the below is ONLY pertinent for ...


4

Since you have configured AlwaysON as Asynchronous, it will be in synchronizing state. Should it say Synchronized at some point? If you want to do a planned failover, you should alter your availablity group to synchronous and then do a failover. When you change the synchronization method to synchronous and when the secondary has catch-up with primary, ...


0

After I reset all the settings and re-created the environment, the problem went away. So the problem was perhaps very specific to local situation and not general to a linked server to AAG. I appreciate all those who commented.


-1

Another way is to insert a step in each job, which should run first, with the following code: IF (SELECT ars.role_desc FROM sys.dm_hadr_availability_replica_states ars INNER JOIN sys.availability_groups ag ON ars.group_id = ag.group_id AND ars.is_local = 1) <> 'PRIMARY' BEGIN --We're on the secondary node, throw an error THROW ...


3

Should you setup a witness? Yes, always with 2012R2+. Why? WS2012R2 added dynamic witness on top of the already included dynamic quorum, both of which are enabled by default. Dynamic witness and quorum work together to give you cluster the highest availability possible given the infrastructure it has. How dynamic witness works is as follows: If there is ...


1

I need to setup full-backup and diff backup. Can I do it on Server C? You can do a copy only full, but unless C is the primary you cannot do a differential database backup. Running below query on server C but it throwing 1 which means I can't take backup on that server. A return value of 1 means it is "preferred" but doesn't necessarily mean you can ...


1

1.Can a separate AG be a 'recipient' (restore target) of Log Shipping? In other words, can I have the primary server in a separate Availability Group be applying log backups (from log shipping) and when ready to cut over to this secondary AG, issue a RESTORE BACKUP WITH RECOVERY? No, databases involved in an availability cannot be restored or recovered ...


1

As I understand your question, the answer is Yes. 1 SQL instance can act as a secondary for multiple Availability Groups. Listeners will point to the appropriate AG. You may have some security concerns to address if the user base of the different AGs is a different set of users...make sure they can't access each other's databases in the case that they are ...


0

I'll answer my own question. No, there seem to be no drawback sharing same cluster between FCI instances and instances with AlwaysOn Availability groups. Thanks for you editions, help and support.


1

Is there anything that would need to be done on the secondary replica prior to shutdown. I would suggest you to suspend data movement prior to the move (and resume it once the secondary server is up). This will have a side-effect of primary database accumulating the unsent transaction log records in the send queue. This means that for your maintenance time,...


0

Just want to answer the question I asked : To configure AOAGs with Synchronous mode + Automatic Failover -- This is possible only with Stand-Alone SQL Instances in SYNCHRONIZED state but not with SQL Server Failover Cluster Instances (Asynchronous) as the principle/point of Automatic Failover to AOAGs with FCI turns out to be a issue/conflict and not ...


1

Unless you can point to the direct requirement you are referring to, I do not see any requirement in Skype for Business (SFB?) that states it has to be on a dedicated instance. I think what you may be referring to is the database needs to be in a dedicated Availability Group. If that is the case you can create another AG on the same instance and cluster to ...


-2

Yes, I believe you can have a separate AG on the same servers but you would need to use different named instances.



Top 50 recent answers are included