Hot answers tagged

11 votes
Accepted

Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

Yes it is the same thing. See section 3 which explains this term. First lets get few terms straight. Windows Server Failover Clustering A Windows Server Failover Clustering (WSFC) cluster is a ...
user avatar
  • 12.3k
8 votes
Accepted

Always On Primary Replica On Prem, Secondary Replica On Azure

We only want to have 2 replicas, one on-prem and one on azure. Is this possible? Yes, it's possible. There are extra items you'll want to think about such as having a domain controller (if ad-...
user avatar
5 votes
Accepted

AlwaysOn - Problem with adding secondary replicas

Did you add the browser service to the firewall? UDP 1434. Because you're using instance names so they have to get resolved. Otherwise remove the instance name to use that 1433 port.
user avatar
  • 3,428
4 votes

Stored Procedure on a read-only AlwaysOn Availability Group replica

A possible workaround would be; Just create a database in the secondary instance and then create the stored procedures accessing the data from the databases being replicated. EDIT: But this database ...
user avatar
  • 1,338
3 votes
Accepted

Listener IP not changing after failover

This behavior occurs due to the time to live (TTL) on the DNS records. A client does not do a DNS lookup every time it attempts to connect to a server. It first looks in its DNS cache to see if ...
user avatar
  • 7,783
3 votes
Accepted

File Share Witness with 4 Node Cluster

No, the cluster will not go down until a node cannot form quorum. Assuming all four nodes have a quorum vote, there are five possible votes. Accordingly a node will have to get three votes to ...
user avatar
  • 7,783
3 votes
Accepted

Cluster IP's under Access Point for Administering the Cluster

Please see No such thing as a Heartbeat Network and Everything You Know About Clustering is Wrong. These don't really answer your question, but perhaps will change the question to, "Do we need a ...
user avatar
  • 7,783
3 votes

Does SQL Server route connections to a secondary replica which has commit mode as synchronous in case of an AG with multiple secondary nodes?

It will depend on your routing list. SQL Server 2104 and SQL 2012 read-only routing directed traffic to the first available replica in the routing list, unless it was not accessible, and then it ...
user avatar
  • 12.3k
3 votes

File Share Witness location

For this setup it technically matters but in the end it really won't. The long and the short of it is it should be at your primary site since that has 75% of all resources. Why it doesn't technically ...
user avatar
2 votes
Accepted

A question about multisite Always On design

In traditional Always On Availability Groups, all replication traffic comes from the primary replica. If you have two replicas in a remote data center, they will both be updated from the primary ...
user avatar
  • 41.4k
2 votes
Accepted

SQL Availability Group restore database to standalone server

That's the error you get when you restore the master database from a server with AlwaysOn Availability Groups configured, or try to restore additional user databases onto a server that had the master ...
user avatar
  • 41.4k
2 votes

How to Start DB2 10.5 HADR on the Standby Database

I got the exactly same error message and logs in db2diag.log, what I did to solve this issue is don't do the roll forwarding after the restore, must keep the restored DB in roll forwarding pending ...
user avatar
2 votes

Making Production database as Publisher and Distributor for Snasphot Replication

Since your database is 100GB, you can use backup and restore method with compression (make sure Instant file Initialization is enabled). Another approach will be to use logshipping with delaying the ...
user avatar
  • 61.3k
2 votes

Automatic fail over in Always on availability group with 3 Node in synchronous mode and 1 Node in Asynchronous Mode

My question is if the primary goes down which node among the remaining 2 replica will be failed over. The node that is set to Synchronous with Automatic failover will be the chosen one to failover ...
user avatar
  • 61.3k
2 votes
Accepted

What happens when the same application from different server tries to connect to the always on primary server

From a technical standpoint, there are no problems with multiple applications, running on multiple different machines, connecting to the same SQL Server instance. If you experience the failover ...
user avatar
  • 27.4k
2 votes
Accepted

Stored Procedure on a read-only AlwaysOn Availability Group replica

Stored procedures are contained in databases, so if the database you are creating the stored procedure on is in the availability group, you will create it on the primary--it would be impossible to ...
user avatar
  • 7,783
2 votes
Accepted

Log Shipping or Always on as DR for SQL Failover CLuster

My experience is that an AlwaysOn high availability group, HAG, is easier to maintain than log shipping. Lets set some variables for our scenario. 1.) Your version and edition of SQL Server is SQL ...
user avatar
  • 130
2 votes
Accepted

How to make AG a proper DR solution (single IP and sync logins/jobs)?

If there was a clustering setup then 1 IP address could have been used that will auto connect to the active sql server. In absence of clustering, and in case of the above AG setup, is it possible for ...
user avatar
1 vote

Meaning of LFL and XHDR in Db2

This is too long for a comment so I'll add an answer. XHDR seems to be the header structure of the log file. I found this info here As for LFL I have not found anything which makes me wonder whether ...
user avatar
  • 21.7k
1 vote

How do you configure a 4-node AlwaysOn cluster to have two listeners?

is there a way to configure to have two listeners Sort of, but it's unusual. The more normal practice is to have a single AG Listener, with read-only routing configured. That way everyone connects ...
user avatar
1 vote
Accepted

How To Prevent Live Servers From Detecting a Forced Failover?

Snapshot Assuming the use of VMs, take a snapshot of the DR server after the source site is disconnected. I might recommend shutting down the server first just so the snapshot is in a good, clean ...
user avatar
  • 7,783
1 vote
Accepted

DBCC check on Always On Secondary Replica

Checkdb should be run on both primary and secondary replicas. Normally for big databases and where you have busy system and you cannot afford to run checkdb on both replicas in one go specially on ...
user avatar
  • 18.4k
1 vote

Domain Controller on Multi Subnet Always on Availability Group

Suppose if the Primary site goes down the server on the DR will still be able to used cached credentials. Assuming the DCs and the rest of your infrastructure will fail over to DR by some other ...
user avatar
1 vote
Accepted

Listener Port 1433 for Always on Availability Group

If the listener port is 1433 and the clients also gets connected through port 1433 to the reporting server that is part of the Availability group will there be any port conflict. If 1433 is just ...
user avatar
  • 61.3k
1 vote

Read only replica part of always on availability group for Reporting

My questions is will there be any problem if the read only replica for reporting is included as part of the always on availability group instead of setting Transaction Replication with the Listener. ...
user avatar
1 vote
Accepted

Making Production database as Publisher and Distributor for Snasphot Replication

It depends on your hardware. I've seen a table in the 70GB range take approximately 20 minutes to create a snapshot. Longer on older hardware. If anything else is being done, it can result in blocking....
user avatar
  • 529
1 vote

Cluster Validation on Remote Node

I would like to run a validation test on that node (dr site). Will this affect the other 2 nodes which are in synchronous mode? No not the data flow as such. You can run cluster validation of all the ...
user avatar
  • 18.4k
1 vote

Log Shipping or Always on as DR for SQL Failover CLuster

Log shipping is by far easier to maintain than an Availability Group for the scenario you described. The ability to fail over back and forth quickly using an AG is great for high availability, but ...
user avatar
  • 628
1 vote

Removing Databases in Recovery Pending Status after AG group was removed

Pause the Availability Group node in Failover Cluster Manager, and then restart the SQL Server service on that node. The database will drop out of recovery pending mode and could be deleted.
user avatar
  • 11
1 vote

Problem Configuring Log Shipping for DR Server

The main step is initialization. You can take a full and/or differential backup of the Production, restore at the DR using that backup and then setup log shipping with no problems at all. You must ...
user avatar
  • 155

Only top scored, non community-wiki answers of a minimum length are eligible