sql server – Read-Scale Availability Group between a Transactional subscriber and another machine

I am trying to configure Read-Scale replica between a transactional subscriber and a 3rd machine. I could create the group. But, when i attempted to add the DB (which is a transactional – subscriber) to be part of my read scale replica group, I am getting the following message and not able to complete my read scale replica. My replication is already configured and working fine. Configured the AG as well. All i am doing is, trying to add the DB to the AG.

Here is the message:
Special considerations exist when adding a replication Subscriber database to an availability group. For more information, see “Replication and Availability Groups” in SQL Server Books Online.

But the BOL is not saying anything about AG on a subscriber. Any help on this would be highly appreciated.

First Responder Kit and Availability Groups

Is there anything in the First Responder Kit that deals specifically with Availability Group issues? Is there anything new developing?

Thanks

availability groups – What is the fastest way to make a copy of a 5TB SQL Server DB on the same server without having to fully reinitialize the DB to add it to the AG

I have a 3 node Always On AG that hosts a 5TB database. Due to business requirements, at 12:01am on the first day of every month, I need to create a copy of that database on the 3rd node (replica) appended with the previous month name. For example, on April 1st, I create a DB called “mydatabase_march2021” which is a copy of “mydatabase” current as of April 1st at 12:01am.

My current process involves removing the DB from the AG on the 3rd node promptly at 12:01am, renaming it, and then starting the slow process of backing up and restoring the 5TB DB to the 3rd node and adding it to the AG. Is there some other way I can do this without having to fully reinitialize the DB on the 3rd node? I need to have this copy available to business users by 5am.

I have tried removing the DB from the AG, offlining SQL, making a copy of the DB files, but there is no way to attach those files and keep the DB in recovering (which is necessary to reinitialize).

Additional Information:
Windows Server 2016 Standard
SQL Server 2012 SP4 Enterprise (will be upgrading to SQL 2016 or 2019 at some point in the next year)
Node 1 is in datacenter A
Node 2 and Node 3 are in datacenter B

availability groups – The Windows Server Failover Clustering (WSFC) resource control API returned error code 19

Here are the details of that error code (19):

ERROR_WRITE_PROTECT

19 (0x13)

The media is write protected.

You need to consult the cluster log (use Get-ClusterLog to get that) for additional details about what writes failed within the cluster operation being performed. Check that out and update your question with any errors you see.

That being said, combined with this symptom:

…if you reboot second node, database wont up.

You might be experiencing disk problems. Check the Windows system event log and SQL Server error log for messages related to failed writes or corruption.

aws – SQL Always on Availability Groups Direct Seeding

Setting up a new SQL cluster and HA using SQL Availability Groups.

  1. SQL 2019 STD 2 Node Multi Subnet Basic AG
  2. Primary Node AWS
  3. Secondary Node VMWare on premise

I am using the newer Automatic Seeding option to replicate the database from the primary to secondary. When looking at the DMV sys.dm_hadr_physical_seeding_stats the transfer_rate_MB_per_Second starts quickly then drops down to 0 mb/sec and the seed takes hours for a 4GB database.

I have switched on the trace flag 9567 for compression but this doesn’t seemed to have helped. My network and VMware engineers have investigated and cannot see any issues, a standard SMB copy between servers works as expected.

Are there additional settings required in AWS for this process or something we may have missed, any help appreciated.

sql server – SELECT query in a SP in AlwaysOn Availability Griup Read Only Routing

There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn Availability Group?

Will the select statement will go to the secondary server for reading and rest will be done on the primary server or since it is an inline statement in a stored procedure, thats why everything will take place on the primary itself.

I could not find any documentation regarding this, how will the select statment behave?

SPN for Availability group(AG) listener for SQL Server 2016

If you configure the listerner with a domain admin account it should be automatic but usually it is not enough.

It’s better to have full control of what you are doing.
First thing to remember is that an spn is a registration done on the domanin compunter account or domain user account that is running sql server service. While it is the same for both nodes, you have to do only one registration.

Check existing spn for your system:

setspn -L domainsqlserverservicelogin

or

setspn -L domainclusterservercumputeraccount$

Then you can register a listener in this way:

setspn -S MSSQLSvc/listener_name:port domainsqlserverservicelogin
setspn -S MSSQLSvc/listener_name.domain.local:port domainsqlserverservicelogin

It’s better to register both listener name and listener FQDN to be sure to match less precise application configurations.

The same commands with computer accounts:

setspn -S MSSQLSvc/listener_name:port clusterservercumputeraccount$
setspn -S MSSQLSvc/listener_name.domain.local:port clusterservercumputeraccount$

To register spn you have to be domain admin.

A Microsoft tool that can help you if Microsoft Kerberos Configuration Manager for SQL Server:

https://www.microsoft.com/en-us/download/details.aspx?id=39046

Once everything is on place you don’t have to do anyting else unless you change something like serfice users, listener names and/or ports

availability groups – is Sql Server AlwaysOn AG a multi-site active-active replication technology?

Scenario FCI: I understand multi-site active-active replication in SQL Server can be installed with an FCI (Failover Cluster Instance). The replication part occurs at the storage level with a connected SAN. So it’s considered active-active, but only one node has the FCI ownership, but the data is replicated between sites at the storage level.

Scenario AG: But, I’m searching for a widely accepted definition of active-active replication. In Microsoft’s documentation, the secondary replica in an AG (Availability Group) is active if the same can be used for read-only queries. So, an AlwaysOn AG could be used to get replication between the two sites, with the primary active read-write and the secondary active-read-only.

So the difference is:

Scenario Main Site DR Site
FCI read-write read-write
AG read-write only-read

So my question is: I’m seen that FCI with synchronous storage level replication is considered active-active replication. But, I’m seen too that active-active replication is considered only when you can read-write to both sites. So, my questions are:

  1. is there some widely accepted definition of active-active replication?
  2. Scenario FCI and scenario AF are considered active-active replication?
  3. what’s the difference between active-active replication and master-master replication?

info visualisation – Visualizing squashed availability of N-items

I’m trying to squash the availability view of n-items into a single day column in a availability calendar. The use case for this reserving products. We can have n-pieces of any particular product, say bicycles, kayaks, etc. We may have two pieces of an product or three hundred pieces of it.

Because of the possibility of large amounts of reservable items of the same type, we can’t use the normal 2D resource calendar where one axis defines the specific resource to be reserved and the other axis defines time. If there’s 100 rows of items, it quickly becomes a chore to find a timeframe where 20 pieces of them would be available at the same time.

Before entering our reservation view, users pick how many pieces of the specific product they want. Users can reserve 0-N pieces, where N is the total amount of items we have of a specific product. After that, they’re presented with a time/calendar view that has open slots on times where the is enough equipment available. If enough equipment is available, users can reserve the items for one to eight hours.

My problem is, that I don’t know how I could visualize the following or similar cases:

Say we have an product. We have two pieces of this product, labeled item A and item B. Item A is reserved from 12:00 to 13.59 and item B is reserved from 14.00 to 15:59. An user wants to reserve 1 piece of item, from 12:00 to 15:59 (but we don’t know this). There is technically one item free for the whole desired duration, but it’s not the same item for the whole duration.

Can I somehow convey to users that it’s possible to rent to from 12:00 to 15:59, but he/she has to switch the item midway? Can I somehow show that if the reservations begins before 14.00, you can only reserve it to 14:00? Is there a better way of visualize squashing the availability of n-items? Should I even try to visualize it?

I could simple force all reservations to prefer item A, leaving item B free for reservation, but this would cause uneven wear in the items on the long run.

image of time tables

availability groups – what will cause a sql server AG readonly replica to stop serving readonly clients?

I understand that if a failover happens (among any nodes in an AG), then all nodes lose their existing connections.

And I assume that if a primary suspends data movement, that will have no effect on secondaries (just a potential pile of logs).

But suppose the primary node goes down?
Or a database is removed from the AG on the primary?
Will the secondary replicas keep doing their thing in those scenarios?