Tag Info

Hot answers tagged

13

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


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


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


7

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


7

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

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


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


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


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


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


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


3

It is possible, but it isn't an out of the box configuration. What you would need to do is setup a separate DNS named which points to all the secondary servers that you want to route requests to. Then setup the read only routing to point to that secondary DNS name. Then assuming that AD is setup correctly when people are redirected to that DNS name ...


3

The Availability Group Listener name is a Computer Name Object (CNO) in Active Directory, not just a DNS entry: http://technet.microsoft.com/en-us/library/ff367878(v=exchg.150).aspx Its location in AD determines where it gets registered. If you want to create a different one somewhere else, you can create a DNS CNAME (alias) pointing to the real ...


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

You have to do following : Drop the availability group then using regedit, (first backup the registry of old availability group that starts with HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap) and then delete that key and Finally create a new Availability Group with new name. Note: Extra Caution .. as you are dealing with Windows Registry, Back it up ...


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


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


2

I have been able to find the documentation that I was looking for. CDC is supported off of the readable secondary but Change Tracking is not unfortunately. The link is specified below but here are the relevant parts for my needs. Redirecting the Query Load to a Readable Secondary While in many cases a client application will always want to connect to ...


2

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


2

You must either use a Contained Database, or you must recreate the users on the other server(s) with the same password hash and SID. A script to do this is provided by Microsoft: How to transfer logins and passwords between instances of SQL Server Mark's solution was partially right however his recommended solution was for Mirrored databases, as opposed to ...


2

In the absence of any other input, here's what I was able to deduce. Short story: this appears to work fine. As far as I can see, enabling TDE for a database is a logged operation, but the actual data encryption is not logged (otherwise the transaction log would grow considerably, which it doesn't). I assume there's something like the differential change ...


2

If you're talking about accessing the primary replica (and the Availability Group database on the primary replica) at all times, this is where the Availability Group Listener comes into play. See this quote from BOL on the topic: You can provide client connectivity to the primary replica of a given availability group by creating an availability group ...


2

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


2

Clustering works very differently in 2012, and using your knowledge from SQL Server 2008 could be confusing you. Try the following from PowerShell (as admin): Import-Module failoverclusters Get-ClusterGroup If you see something like: Name OwnerNode State ---- --------- ...


2

How did you setup your Availability Group? It sounds like what happened is you blanketed the backups and restores from the primary replica server to the secondary replica server. In other words, you did a: backup database <DatabaseName> to disk .... <so on and so forth> to all databases on the primary and a restore database ...



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