New answers tagged

1

You can use following PowerShell script to get last failover time Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap


0

Adding MSSQL as a Generic Service Resource is not the Answer. That will just put the Cluster Manager in charge of the SQL Server Service, OK, yes it will fail-over automatically, but you will notice in SQL Server Configuration Manager that your services are now set to "Manual" indicating that the Cluster Manager is now in control of your SQL server Service. ...


0

Given the wait types of LATCH_EX and ACCESS_METHODS_DATASET_PARENT I highly doubt AlwaysOn was the culprit. These two latch types may go hand in hand. You'll want to investigate where the latch_ex waits are actually happening - sys.dm_os_latch_stats may help here where the latch_class is not 'buffer'. Given that removing AlwaysOn did not help with the ...


0

I want to point out something. When a failover occurs, any client using the listener to connect will have its' connection closed. This doesn't matter if MultiSubnetFailover is set or not. What MultiSubnetFailover helps with is the client driver to spin up multiple connection threads (1 for each IP) without the application knowing and connecting to the ...


1

If you were referring to Database Mirroring when you say mirroring (as opposed to SAN mirroring), then don't. It has been deprecated since 2012 now so it is a potential candidate for complete removal in the future releases. Go with Always On Availability Groups. The difference in DBA overhead should be mostly the same. What is different, IMHO, is the ...


2

I would recommend you to use Always on availability group for server reasons. Database mirroring is deprecated as of SQL Server 2012 and will be removed on the future versions, so it is better to not use it on a new developments AOA group will give you a feature which you cannot get it on mirroring. Using AoA group, is like using a combination both ...


-1

Questions to ask yourself start with: How fast do you need it up? Is it costing you money per hour being down? Are you out of SLA? If you don't have SLA's yet for this service can you get them real quick or have at least a good idea if it's OK to be down? Typically you would want to learn why it is down then decide but sometimes time doesn't allow that. ...


2

Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all? A COPY_ONLY full backup is still a full backup. The only difference between the two is the COPY_ONLY will not reset certain ...


2

The short answer is No you cannot do that. First, if you suspend the data movement in one of the availability group database, you no longer able to failover it to your secondary node. Unless you make a forced failover. If you want to suspend the data movement in one of your databases, and also want to make the failover, you have to remove that specific ...


0

Our plan is to remove the Databases from the AGs on the primary instances, but leaving the listener in place so the applications should still connect to the DBs via the VIP/DNS. We cannot suspend movement as the outage could be long and MS recommend movement is suspended for a short period only. I would not remove the databases on the primary ...


0

How large are your transaction log files? I'd be prepared to try going with leaving them disconnected, provided no reindexing, bulk ops etc go on during the outage, and provided you can be 100% confident it will only be 6 hours. In my experience OLTP transaction log growth is normally modest, and provided your t-logs are a reasonable size, you should be ok. ...


1

While AlwaysOn/Availability Groups are a welcome edition to SQL Standard Edition 2016, the GUI is not supported for group creation. Try using the T-SQL code to create the group. Per Microsoft: https://msdn.microsoft.com/en-us/library/mt614935.aspx "To create a basic availability group, use the CREATE AVAILABILITY GROUP transact-SQL command and specify the ...


1

The simplest answer is, no, you won't be able to facilitate this with a 3 node cluster in the manner described. The reason is due to quorum. Assuming the 3 nodes, 2 at Primary and 1 at DR with Windows Server 2012R2. Dynamic quorum is on by default, this will automatically adjust node weights in case of a node failure. Dynamic witness is also on by default ...


1

SQL Management Studio Unfortunately, there are some caveats that make using Application Intent in SQL Management Studio somewhat painful: To connect manually with ReadOnly intent, after bringing up the Connect to Server dialog from the Object Explorer, users must remember to: Click Options >>. Go to the Additional Connection Parameters tab. Enter ...


2

The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ? IMHO, NO - since you are on sql server 2012. Why ? SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is ...


3

Msg 19405, Level 16, State 17, Line 3 Failed to create, join or add replica to availability group 'AGName', because node 'Node3' is a possible owner for both replica 'Node3\ReadOnly' and 'Primary/Primary'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. This happens for two main ...


2

In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule backup jobs for a given availability group. The ultimate goal is to have the regular backups run on the synchronized secondary and, most importantly, not bound to the availability of a particular secondary node. This can be accomplished without issue. Just keep in mind that your ...


2

The short answer is yes, much the same as when you mirror from a lower version to a higher version. Make sure you do not have the 2016 as readable so it's not trying to upgrade the database version. That said I haven't tried this, just going on how log shipping/mirroring work. If it works it is only supported doing this as a means to upgrade, leaving 2014 ...


1

Can I add multiple publishers to a subscriber(which is part of availability group as a primary server)? Yes, this is the multiple publishers model and is supported. In which node can I configure distributer? The distribution database is currently not supported for use in AlwaysOn Availability Groups. The only supported method for transparent ...


6

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


-1

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


1

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


2

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



Top 50 recent answers are included