Hot answers tagged

38

The way I always like to visualize high availability solutions is the following: SQL Server Failover Cluster Instance (FCI) What is highly available? The entire instance. That includes all server-objects (logins, SQL Server Agent jobs, etc.). This also includes databases and their containing entities. It's a great solution for highly available SQL ...


18

two (or more) servers in a Windows Failover cluster, SQL Server as a clustered instance What Kind of workload? "It depends" - but seriously, this is useful for an online application where you need to have local in data center High Availability. You are protected against a failure of one machine, or of one operating system. The logins, jobs, new ...


17

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group: 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 where ag.name = ...


13

My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually. Something like this script from SQLSoldier, originally posted as Transferring Logins to a Database Mirror, should do the trick.


10

Elijah. There's two separate questions here: 1. Is DTC supported with AlwaysOn Availability Groups? As Microsoft says in big letters, no. I totally understand that you want to try it anyway, but keep in mind that you're now putting something into production that Microsoft simply will not support, AND you're using two separate niche features together (AGs ...


9

If you want to ensure you minimize the amount of time you are without HA during your migration, here's a trick I recently used: Set up the AG with a "dummy" database so that your AG is ready in advance. Log ship the database(s) to both your primary & secondary AG replicas--at this point, the log shipped database is not part of the AG, you're just ...


8

Lots and lots of caveats here. I've tested this in a very limited way in a scenario where the data/log paths are congruent across all replicas, haven't added error handling, etc. You can call this stored procedure from the DDL trigger if you decide to go that way, as Kin suggested, or from a job, or what have you. See additional comments inline. CREATE ...


8

Swiped from my answer on StackOverflow, only to prevent others from spending effort on the same type of answer. Personally I think this will work a lot better than transactional replication, though I haven't done any formal comparisons of the two in a true migration scenario. I know that with the amount of troubles folks have with transactional replication, ...


8

No, there's no way to do zero-downtime failover with AlwaysOn (or in SQL Server in general, as far as I'm aware). To do that, the SQL Server you're connected to would have to do state transfer to another node mid-query, and since many failovers are unexpected, that's not possible. However, you can enable "read-only secondaries" in AlwaysOn, and then your ...


7

No. During a failover, clients will notice. Your connection to the SQL Server will be broken, and you'll have to retry your transaction.


7

It is very clear that the synchronous secondary replica is not able to keep-up with the load primary is generating (even though both machines are of same configuration). And the side effect of this is the log on primary will keep on growing (even we take log backups it can't truncate the log) In synchronous mirroring/alwayson the secondary must ...


7

When you run the log backups from a secondary server, when the log backup is complete the secondary replica will tell the primary replica that the log backup has been completed and what the last LSN to be backed up was. The primary replica will then mark the VLFs which were backed up as inactive. That change (which is logged) will then be transferred to ...


7

There's two parts to this question: First, can you use the Failover Partner connection string tip with AlwaysOn Availability Groups? No. AlwaysOn AG's "Listener" technology is the replacement. Have your connection strings point to the listener name and they'll always get the primary replica. (For the next part of this answer, I'm assuming you're using ...


7

Nismo, It's not the SIZE of the database that matters (no jokes intended), it's the rate of change coupled with the infrastructure available. For example, a relatively static database might perform poorly on 1Gb connection on an overloaded switch with 5400 RPM sata drives. If the rate of change (aka look at your log flush bytes) is less than around 200 ...


6

AGs are based on Windows Clustering. The WSFC procedures for quorum loss apply. WSFC Disaster Recovery through Forced Quorum Force a WSFC Cluster to Start Without a Quorum Once the WSFC is running, you can then force AG, if needed. Perform a Forced Manual Failover of an Availability Group: After forcing quorum on the WSFC cluster (forced quorum), ...


6

AlwaysOn secondaries map all reads to versioned reads, which are (almost) lock-free Even with versioned reads there are some (few) locks in place, eg. schema-stability locks on obejcts. There is never any sort of propagation of locks from secondaries to primaries. Furthermore, there cannot be any propagation of locks from secondaries to primaries simply ...


6

It is also important to consider what is shared. Failover Clustering uses two or more server nodes sharing one disk array. If the disk array goes down then you lose service, regardless of how many server nodes there are. If the server room where that disk array is located catches fire or floods then you lose service. AlwaysOn Availability Groups and ...


6

You can backup the transaction log from either of the replicas. Doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable (provided no other stoppers are in place like active transactions, etc.). To do a test, in a non-production environment, setup an availability group ...


6

Listen to your adviser. By restoring a backup, you are essentially replacing the database schema and data. You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY. Once your backup is in place, put the DB back into HA and ...


5

When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials? No, SQL Server service accounts can be different. We have a 3 node cluster running AlwaysON and on all the servers SQL Server is running with a different account. e.g. If you have one server in NY and other in LD, then Active Directory Accounts ...


5

No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery. Quote from the Data Loading Performance Guide: Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. Re ...


5

As long as you don't care that: in the async model, the REDO queue may fall way behind, so the backups on your secondary may not represent the point in time that you think they would if they were taken on the primary. you will not be able to use DIFFERENTIAL backups in this model. in the event of a restore, you will have to manually determine which log ...


4

First, let's clarify terms. AlwaysOn is a Microsoft marketing term which includes both SQL Server Availability Groups and SQL Server Failover Cluster Instances. You should stop using AlwaysOn and be specific for all future communications. If you are referring to FCIs: the configuration should be completely transparent to your application. They should ...


4

in relation to your comment, I suspect a Ops related issue here instead of a SQL Server engine issue. These SAN devices usually work on the block layer and some manage transaction log/data file sync better than others, as well as other areas. You can show the ops team that no, SQL Server doesn't randomly corrupt data like this. You can restore backups ...


4

QUICK ANSWERS TL/DR Sort of, you will double the resources but every instance will only be active on one node at the same time. Any SQL Server instance will have exclusive access to it's storage on the node where it's currently active - If all the storage for multiple instances comes from the same SAN one instance can affect the others No you cant, sorry ...


4

Should we care about the redo rate when there is nothing in the redo queue? If there is nothing in the redo queue and the send queue is empty then I wouldn't worry about it as there is literally nothing to be done. This will happen on AGs that have light usage or usage based on specific hours and is then idle the rest of the time. If, however, there IS ...


4

You can't log ship from the principal mirror to a database that's live in an AG, but to another DB you could then make an AG later. Enabling the AG feature and having other AGs has nothing to do with what you're trying to do unless I am misinterpreting.


4

1.When and how do we install Microsoft Updates? Using AOAGs could potentially help with this, depending on how much downtime is considered to be "too much". There is still a blip associated with switching the primary to a synchronous secondary as the clients will be disconnected (this may or may not be an issue in your environment) as the move happens. ...


4

Firstly I should say great question usr. Sean, Robert and Max have both correctly stated that due to the re-negotiation between the Primary and Secondary, that the correct LSN is always resolved so that there is not possibility of any discrepancy in that scenario. In response to the discussion with Shanky, in Synchronous mode, the Commit first has to occur ...


4

Very interesting question .. Based on my understanding, I will try to answer this. Other experts can correct me wherever I am wrong. AlwaysON is different from Database Mirroring especially when sending the log blocks to the secondary replica/s. The difference is how the log block gets send to the mirrored instance (for mirroring) or replica (for ...



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