Tag Info

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

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.

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

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

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

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

Refer to Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server) From the reference : Change Data Capture: Databases enabled for change data capture (CDC) are able to leverage AlwaysOn Availability Groups in order to insure not only that the database remains available in the event of failure, but that ...

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

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

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

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

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

Yes AG support multiple subnets as described here. Also make sure that your data provider supports MultiSubnetFailover .. .NET Framework 4 supports it. To answer your question ... IF you use .NET framework 4 or 3.5 then the provider will support it as described here. Also, a good reference to SQL Server Multi-Subnet Clustering is well documented. With ...

While I haven't run AlwaysOn in production on 2008 R2, I have experimented with a VM lab using 2008 R2 guests on 2012 Hyper-V. The only real issue I encountered was having to install hotfix KB2494036 to enable changing the node weight property of the cluster nodes. My test environment simulated three quorum-voting nodes in the primary facility, two of which ...

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

I encountered a bit of similar frustration w/ the documentation on Books Online saying 'when scheduling your job, add logic to only execute on the primary' but not saying how to do so. The sys.fn_hadr_backup_is_preferred_replica is super-nice for backup jobs, but there doesn't seem to be a similar function for 'is primary' or what have you. Fortunately you ...

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

In order to redirect reporitng app or application with only read only queries you need to specify read only intent in application connection string (web.config or app.config in case client application is .NET) The below is excerpts from SQL Server BOL The application intent connection string property expresses the client application’s request to be ...

I was able to resolve this issue in my lab. I setup an Availability Group between two virtual machines in my lab. I was not able to add the listener as part of the initial setup and also couldn't add it afterwards. Each server had two network adapters on different sub-nets. When I disabled the 2nd network adapter on each server, I was able to add the ...