Tag Info

Hot answers tagged

15

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


12

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


9

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.


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

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


8

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


7

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

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


6

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


6

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


5

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


5

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


4

Over the past several months, I have been working with Microsoft's Product Support team, and they have acknowledged two bugs in SQL Server 2012 in relation to this issue. They will be releasing patches for those bugs as part of the next service pack for SQL Server 2012.


4

I'll be the first to admit the documentation is not very clear on this. They state that you should be offloading your backups to the secondaries, but while most statements are made in a general sense, they really mean, specifically, log backups (and copy_only backups, if you have some need for those). You will need to occasionally run full backups on the ...


4

No, this is not possible with Availability Groups. Any incurred delays are usually from things like network, secondary replica log block flush, etc. There's no way that I can think of to turn that knob and create more of a time gap. It sounds like if you want to take this "delayed transactions" approach, Log Shipping may have actually been the best ...


4

Here's my go-to query to pull the events that Aaron mentioned (availability_replica_state_change). This script also takes the liberty of converting the UTC time to your local time: declare @xel_path varchar(1024); declare @utc_adjustment int = datediff(hour, getutcdate(), getdate()); ...


4

Yes, you will need to create the jobs on any other replicas that you would want those specific jobs to run if they were the primary replica. You will need to create your own logic for if/when each SQL Server Agent job will run. For instance, do you want to run a job only if the current instance is the primary replica of a particular Availability Group? ...


3

You don't have to write a cursor tsql script to check for new database created and schedule it to run for e.g. every minute. Instead use EVENTDATA() function in conjunction with server level trigger. IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'ddl_trig_database') DROP TRIGGER ddl_trig_database ON ALL SERVER; GO CREATE TRIGGER ...


3

Advantages of Transactional Replication over Availability Groups for Reporting Workloads As a counter-point to the answer above, here are some reasons why transactional replication may be a better solution than availability groups for reporting workloads. You can replicate a subset of tables in the database. Availability Group Replicas are always the ...


3

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


3

Yes, this is possible with some manual work. You can take database snapshots on AlwaysOn Availability Group replicas, and remove those snapshots as well. You could automate the creation of snapshots every X minutes, and then tearing them back down periodically. It will add storage overhead - it will require additional space that will depend on the change ...


3

If the database has a long scheduled maintenance you don't need high availability or do you? Keep in mind that every change is written to the transaction log and than transmitted to the secondaries. I wouldn't use the full recovery model for such a maintenance. If you don't do such a thing each week/month, I would turn off HADR for these databases, change ...


3

You need to set that in the AG ROLE properties as below : Windows server 2012


3

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


3

Can I related the crash to availability mode settings? No. By the looks of your log messages, you actually lost cluster quorum due to the removal of two nodes' votes, and then your witness (file share). Is this a 3 node cluster with a file share witness perchance? In that case, if you pulled these events from one of the node's event logs, then it may ...


3

I'm aware of two concepts to accomplish this. Prerequisite: Based on Thomas Stringer's answer, I created two functions in the master db of our two servers: CREATE FUNCTION [dbo].[svf_AgReplicaState](@availability_group_name sysname) RETURNS bit AS BEGIN if EXISTS( SELECT ag.name FROM sys.dm_hadr_availability_replica_states AS ars ...


3

Well, you can keep using mirroring. "Deprecated" does not mean "doesn't work any more" - it just means that, at some point in the future, it won't be supported when you move to some version > 2012 (probably at least 3 versions later, maybe more given the backlash this announcement has caused). By then, I am sure they will have a replacement for mirroring ...


3

Thomas Stringer has answered a very similar question at: Transaction Log Maintanance While Using AlwaysOn Availability Group The answer is that the Availability Group replicas are aware of each other and when either the primary or secondary does a backup, both logs are able to free log space. The article you referenced also offers the explanation that Log ...


3

I see you're combining Failover Cluster Instances (FCIs) and Database Availability Groups. That's fine, except that the installer won't allow you to set up an availability group on failover cluster instances that can be owned by the same servers, just like the error message says. In other words, the set of servers that can run HRITS\BAK can't overlap with ...


3

Just for completeness, there is the option of using plain old mirroring. The advantages here include having two copies of the database without the complexity of using Availability Groups, and without needing shared storage for Failover Clustering. Disadvantage, although slight, is mirroring is deprecated. Failover times with mirroring are on the order of ...



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