Hot answers tagged

40

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


20

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


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


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.


12

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database? It depends on the sync mode you have used when configuring Availability group - Sync or Async ! On Secondary replica, all the transactions use Snapshot Isolation level ONLY and all locking hints are ignored as well. That's ...


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


10

It is not possible to delete a database that currently resides in an availability group. It is also not possible to restore over top of it while it remains in the availability group. Thus, this database was not in an availability group to begin with. What you are describing is not possible as you will get an error if the database is involved in Always On. ...


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

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


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

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

You should use smart index maintenance scripts as opposed to maintenance task. Should I run the job in primary or in replica? Yes, as they get replicated on secondary replicas. If I run it only on secondary then what benefits do I get? you cannot run on secondary replica. Index maintenance generate a lot of transaction log activity that affects ...


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

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

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

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


7

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


7

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


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


6

What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID. For instance, on the instance where you have the orphaned user and the following returns the user: exec sp_change_users_login 'report'; go Copy the SID from the column UserSID. And if you already have an ...


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

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


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


6

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


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

Putting in a simple language : A login gives you access to server. A user gives you access to database. A login must map a user so that you can access the database. For SQL Authentication, you should create the login with SAME SID and map it to your user database/s on all the nodes with proper permissions (follow the principle of least privileged). This ...


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


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


5

The scenario is called out and supported on the link you've provided. Availability Group with One Remote Secondary Replica If you have deployed an availability group only for disaster recovery, you may need to fail over the availability group to an asynchronous-commit secondary replica. Such configuration is illustrated by the following figure: ...



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