4

As a general rule, this migration should be simple. AGs use the database mirroring framework and endpoints for transmitting log blocks, so the transition is quite straightforward. One thing to note is you currently run your mirrors async but you've stated you want to run your AG sync to provide HA. This shouldn't be a problem, but you need to check your ...


4

Is the above approach the best way to do this? Essentially this is the only way to approach this - indexes can only be created by one command. You could execute T-SQL for the CREATE INDEX manually, but the same command is going to be executed on the server regardless (barring any different options you choose, such as online). As with any production ...


3

You can set up a SQL Agent alert for event 1480 that filters on the string "RESOLVING" to "PRIMARY" The reason for the filter is that 1480 events cover all state transitions. If a secondary loses connection for a bit, it will go into resolving, then back to secondary, so if you don't use a filter you'll get alerts on more than just failover. USE [msdb] ...


2

If you have synchronous replicas you typically have one with automatic failover. The most common reason not to is when the primary is hosted on a Failover Cluster Instance, and it's a rule that only the FCI can failover automatically in that configuration. But you could also have a scenario where you would prefer an outage over a failover, for instance if ...


2

There is no maximum for the supported latency for synchronous replicas. You will sometimes opt for a higher-latency deployment in return for a RPO=0 for a broader range of failures. The most common mission-critical configuration is to have a pair of synchronous replicas on separate racks in the same datacenter, and a third, asynchronous replica in a ...


2

Instance-level certificates are stored in the master database You will need to set your context to master database, and then check certificates: use [master] select * from sys.certificates Or simply use three-part name in a single command: select * from [master].sys.certificates


1

There are no pros as such that motivates you to use this mode of failover. its completely dependent on the requirement of the business and requirement of availability or activation of secondary server. so may be you want to initiate a failover yourself just to satisfy your believe that failover works best this way or you can make yourself confident that ...


1

When you configure your listener to use a non-default port (any port other than 1433), then you need to specify the port number in the server name when connecting in order to connect successfully. This is detailed in the documentation here.


1

I just figured it out. (You have to have the [Domain\sqlengine] added to the Logins for SSMS.) In Management Studio Click [Logins] Perform the following (2) actions Action #1 Double Click [Domain\sqlengine] Click on [Server Roles] (I have the following checked, I actually had them all checked for testing, and now these are the ones that are ...


1

I know this is a bit of an old thread, but we actually have exactly the same requirements - Allow users to connect with read only intent and prevent them from falling back to the primary. The reason is simple - Our databases are complex with lots of little queries constantly running against them from our production systems and our users are typically ...


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