Hot answers tagged

9

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 group of independent servers that work together to increase the availability of applications and services. SQL Server 2016 takes advantage of WSFC services ...


8

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-attached clustering/cluster at all is used) and what type of pipe you'll need, region and latency, load balancer setup, etc., but it's not a hard requirements to have ...


5

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.


4

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 wouldn't be replicated.


3

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 there is a cached entry. You can see what is in the cache by running ipconfig /displaydns. Once you run this you'll see the "Time To Live" property on each record....


3

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 maintain or gain quorum. When the file share witness goes offline, the node that currently owns quorum will maintain ownership because it can still get four votes. ...


3

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 heartbeat network?" With Windows 2008 and later, a heartbeat network is not required, and it's really only going to benefit if your "public" network gets so ...


3

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 would direct the connection to the next replica in the routing list. When you have multiple secondary replicas available for read, it is not possible to spread the ...


2

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 replica, period. You can't do primary-to-secondary-to-secondary. SQL Server 2016 introduced Distributed Availability Groups, which might satisfy your design goal. ...


2

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 status, then you can start HADR on it.


2

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 restoring of log files on the standby server and having the database in standby read mode ( WITH STANDBY). The way you do is - take your regular log backups ...


2

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 when the primary goes down. You can only have one node set as the automatic failover target, see the docs for 2014 (it was the same for 2012): To configure ...


2

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 scenario that you described, it's possible that the reporting workload will interfere with your other workloads from a performance standpoint. In other words, all ...


2

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 create it on the secondary since the replica there is read-only. Once the stored procedure is created on the primary, it is then part of the database and ...


2

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 Server 2016 Enterprise Edition. We are not on the latest SQL Server patch level. We need to apply a newly released service pack. 2.) The SQL Server database ...


1

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 this is some kind of abbreviation similar to LOTCH (LOCK/LATCH)?


1

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 to the same hostname, but read-only workloads will disconnect and reconnect directly to an available readable replica. would SQL on all four nodes need to ...


1

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 state. After the DR testing is done, shut down the DR server and then revert it to the snapshot. This is easy and cheap. The only downsides are that the source ...


1

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 primary replica, people run checkdb on all the secondary replica and failover the primary to secondary making secondary new primary. Now you have new secondary on ...


1

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 means, your DR SQL Server may not be usable until that happens. So the AG will give you a very good Recovery Point Objective (RPO), but the Recovery Time ...


1

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 dedicated to sql server and no other service is using it, there wont be any conflicts. This is documented well in BOL : If you use the default port of 1433 for ...


1

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. Nothing you're not already dealing with such as an extra copy of the database, more networking IO, disk IO, etc., so that's kind of a moot point assuming the ...


1

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. If you write the snapshot files to the same drives as your database files then it will create I/O contention and can impact performance. And saturate your ...


1

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 resources but storage with no downtime. Although few of MS blogs suggest you can run all cluster validation test but storage on WSFC without downtime, still I ...


1

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 often disaster recovery RTO (recovery time objective) is much longer because hey, it's a disaster. While the high availability goal might be to have it all back ...


1

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.


1

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 keep in mind that Production and DR must have the same LSN at the initialization stage. For the log chain to be broken you would have to do this: - restore the DR ...


1

You START Log Shipping by restoring a FULL, possibly a DIFF and then the transaction log chain. AFTER Log Shipping is setup, it will perform the LOG backups, copy those to the DR server and then restore those on the DR server. All that it uses are the LOG backups. Log Shipping also includes a full backup, but those do not get sent to the DR server. You can ...


1

I don't understand your issue. You can definitely log ship while performing Full and Diff backups. Are you trying to have some manually performed version of log shipping? Performing a Full or Diff backup will cause no issues, log shipping just uses log backups so the chain will not be broken. Have you actually tried this out, from your question it appears ...


1

You cannot alter the LOGGED attribute of a LOB column. However, you can alter the table by adding a new LOGGED LOB column, update the new column from the existing column, then drop the original column. It may require less wasted disk space but is unlikely to be faster than using ALTOBJ.


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