Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

AlwaysOn Availability Group creation - Is it possible to delay the adding of a secondary replica?

A
Avante last edited by

Currently I have a windows failover cluster with a single node only, because the second node will only become available on an production go live date. Prior to this, I was wondering if it would be possible to create the AlwaysOn Availability Group with just the Primary replica. This is in order that I could bring a listener online, in order address various configurations, in order to reduce the tasks required on the go live date. Thank you.
Reply Quote 0
1 Reply Last reply
Mystic last edited by

Unfortunately not. You have to specify at least one secondary.
Reply Quote 0
1 Reply Last reply

2
Posts

10
Views

Suggested Topics

C

ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available
SQL, Database Testing • sql server 2012 availability groups • • Cleofas

2

0
Votes

2
Posts

0
Views

T

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each. Here's the T-SQL involved: ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'COMPUTER01' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'COMPUTER01' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')); ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'COMPUTER02' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'COMPUTER02' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433')); ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'COMPUTER01' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01'))); ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'COMPUTER02' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02'))); GO Sounds like you may be missing the configuration and/or routing information for the primary.
M

Availability Group with log backups to NUL
SQL, Database Testing • sql server 2016 availability groups • • meriah

2

0
Votes

2
Posts

0
Views

A

No, you aren't missing anything except the opportunity to do a point in time recovery of the database. AG's protect you from MOST hardware failures but they don't protect from user errors that drop a table or truncate data. Even if the business didn't have a SLA for RPO I would have log backups running as a DBA just to cover things, but you can backup to DISK='NUL' and the log will clear just like you backed up to a regular disk file.
How to let particular logins to work on the secondary replica only?
SQL, Database Testing • sql server availability groups • • Laycee

2

0
Votes

2
Posts

0
Views

N

You can configure this by setting the "Connections in primary role" setting when configuring read-only routing. You have 2 options: Allow all connections (default) Allow read/write connections - which really means, "Connections where the Application Intent connection property is set to ReadOnly are not allowed. This can help prevent customers from connecting a read-intent work load to the primary replica by mistake." This setting isn't configured at the login level though - this relies on what you said in the first part of your question, that your logins are using ApplicationIntent=ReadOnly in their connection string. If they don't ask for that specifically - meaning, if they don't declare that they're only going to write - then SQL Server has no fast way of knowing that they have no writeable permissions in any database, on any object. After all, they could even execute a stored procedure signed with a cert, and that cert could allow them to do writes. So if you really want to stop them from logging in even if they don't use ApplicationIntent=ReadOnly, then I'd do it by simply disabling their logins on the primary. Then, run an Agent job every minute on every replica, checking to see if this particular replica is a primary. If a failover has occurred, and this replica is now a primary, disable the reporting-only logins. If the replica is a secondary, and the reporting-only logins are disabled, then enable them. This will of course run into problems if you really do want to disable someone's login - it'll instantly be enabled again in a minute.
A

How to 'pause' SQL Server AlwaysOn Availability Group
SQL, Database Testing • sql server availability groups • • authera

2

0
Votes

2
Posts

1
Views

The simpliest way seems to simply shut down both nodes. That way, no one will be able to connect to them. If ever you need to roll back to them, just turn one of them (or both) on and they will then be accessible. If you do not want to stop the SQL instances, then you could delete the listener from the AG. As all you users/apps should be using the listener to connect, that will prevent from any connection, leaving the servers and AG online. In this case, if you need to rollback, then you just need to re-create the listener in the AG (Do not delete it from the active directory)
F

How to list all forced query plans and cancel one
SQL, Database Testing • sql server sql server 2016 ssms exec languageution plan query store • • fbio

2

0
Votes

2
Posts

0
Views

A

You should be able to get information about 'forced' plans by querying this catalog view (sys.query_store_plan) filtering on is_forced_plan = 1. At the bottom of the page are links to other useful catalog views for Query Store. This stored procedure (sp_query_store_unforce_plan) allows you to 'unforce' a plan.
S

What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?
SQL, Database Testing • sql server sql server 2016 foreign key primary key identity • • shizuka

2

0
Votes

2
Posts

0
Views

J

As you're going to have new id values, it's not going to to be easy to do a straight copy. I might suggest copying the data to a set of staging tables with no foreign keys defined. Then, in the staging tables, increase the existing ID values for all rows in a given table by a fixed amount that will guarantee they are higher than the max value in the destination tables (or you could change them to negative values if you haven't already used those) - you'll need to apply the same update to the foreign key fields in any child table at the same time. Then you can use SET IDENTITY_INSERT {TableName} ON; to allow you to insert the updated records to the destination table. You should turn that off again once you are done. SQL Server will automatically take account of the new rows you have inserted to re-seed the Identity values for new records moving forward so once you've inserted your records all is good.
D

Unable to Restart the SQL server Agent from SSCM
SQL, Database Testing • sql server sql server 2016 sql server agent • • derk

2

0
Votes

2
Posts

0
Views

Y

Since we identified it was a permissions issue via the comments, I would suggest you adjust the account running the SQL Agent service to something different if this is a server. Microsoft does a great job to outline the permissions needed for the SQL Agent service account. That article also highlights a few pitfalls regarding some minimal permission grants you want to do so users outside of the sysadmin role can properly utilize Proxy accounts, etc. If it's your local PC, it likely doesn't matter that you adjust this account nearly as much since that service isn't configured for automatic startup by default. Glad you got it sorted.
J

Is there any option to enable Window NT logs in SQL Server Logs
SQL, Database Testing • sql server windows sql server 2016 sql server 2019 • • juvenalb

2

0
Votes

2
Posts

0
Views

R

I think this has the answer you are looking for. See this link to different same question Looks like a user needs membership in the Event Log Readers group or the local Administrators group.
J

Conditional Connection in SSIS based on Availability Group status
SQL, Database Testing • sql server availability groups ssis • • jules

2

0
Votes

2
Posts

0
Views

Let's assume that you have an availability group with only one secondary replica (you could easily expand this sample for more replicas) 1. Connections You will need 3 connections: AG listener (to run the query that will return which server is holding the primary an secondary) Primary (to be parameterized with the primary server) Secondary (to be parameterized with the secondary server) 2. Variables Create 2 variables with data type String ServernamePrimary ServernameSecondary 3. Query To map a query result set to a variable using Execute SQL Task, your query needs to return only one row. You will map each column to the variable that you wish. Knowing that, I've changed you query to the following: WITH CTE_AGStatus as ( select cs.replica_server_name, rs.role from sys.dm_hadr_availability_replica_states rs join sys.dm_hadr_availability_replica_cluster_states cs on rs.replica_id = cs.replica_id join sys.dm_hadr_name_id_map n on rs.group_id = n.ag_id where n.ag_name = 'DVAG001' ) select ServernamePrimary = MAX(case when role = 1 then replica_server_name end) , ServernameSecondary = MAX(case when role = 2 then replica_server_name end) from CTE_AGStatus (Feel free to change it as you wish, just remember to return only one row) 4. Mapping the result set to variables On the execute SQL Task Editor, paste the query above, set the OLEDB connection 'AG listener' and change the result set to 'Single row' Then go to the 'Result Set' tab and map the columns to the variables 5. Change the connection strings Click on the 'Primary' connection manager and go to the 'Properties' tab, then click on 'Expressions', select the property 'ServerName' and add your variable on the Expression. Repeat the process to the 'Secondary' connection manager 6. Check the process To validate, you could add other steps and use breakpoints to check how your variables are running. The next steps are up to you. Now you have one connection pointing to each server of your AG.
What happens in this strange SQL Server Mirroring situation?
SQL, Database Testing • sql server availability groups mirroring • • carriann

2

0
Votes

2
Posts

0
Views

J

Firstly I should say great question usr. Sean, Robert and Max have both correctly stated that due to the re-negotiation between the Primary and Secondary, that the correct LSN is always resolved so that there is not possibility of any discrepancy in that scenario. In response to the discussion with Shanky, in Synchronous mode, the Commit first has to occur on the Mirror (as you have correctly assumed usr) and this does indeed mean that the log has to be written to (those respectively log buffers flushed to disk on commit). Remember that this only means the log file has the transaction hardened to it, but at this stage, the transaction would still be "in the redo queue". This allows for a very fast commit to the Mirror, meaning that the commit on the Primary can also occur very quickly afterwards - this thereby reduces the chances further that there would be a discrepancy in the first place. Other thing to consider is that if you are using delayed durability, this alters the mechanics further, and could actually result in lost transactions to your Mirrored database as a whole (due to the change in behaviour of the log buffer flushes - which no longer flush on commit), but resolution would between the two would still be able to occur to find the correct lsn point.
B

Availability Group Read Only Routing Not Working for SQL Logins
SQL, Database Testing • availability groups sql server 2017 sql authentic languageati read only routing • • baileigh

2

0
Votes

2
Posts

0
Views

The issue was not in SQL or Windows Authentication methods, but in default_database setting Found that for my Windows Authentication Login, default_database = [MyDatabase] (which participates in availability group), while my SQL Authentication Login has default_database = [master] Because of above setting, while connecting in SSMS using Windows Authentication, Read Only Routing worked right after ApplicationIntent=ReadOnly was specified. For the SQL Authentication Login, not only ApplicationIntent=ReadOnly needs to be specified, but also it should contain Initial Catalog=[MyDatabase] Adding Initial Catalog=[MyDatabase] fixed the issue, SQL Authentication Login could connect and was directed to Read-Only secondary
O

What permissions are needed to see view definitions via a query?
SQL, Database Testing • sql server sql server 2016 ssms view • • Oba22

2

0
Votes

2
Posts

0
Views

P

Simply granting VIEW DEFINITION and SELECT permissions on INFORMATION_SCHEMA and sys schema wont give you rights to see the definition of the view. Permissions that you added will provide you information about all objects in sys schema and information_schema. I suppose you want to see the view definition which are created in some other schema, in which case you would have to provide the user withVIEW DEFINITION to that particular schema. Such as : GRANT VIEW DEFINITION ON SCHEMA::SchemaWhereViewsAreCreated TO [YourUser]
K

One Availability group, two primary nodes
SQL, Database Testing • sql server availability groups • • Kadyn

2

0
Votes

2
Posts

0
Views

J

As Aaron Bertrand mentioned: You need one Windows Server Failover Cluster (WSFC) with both server member of the WSFC. You need AG1 (Availability Group) and Listener1. DB1 will go in this Availability Group. You need AG2 (Availability Group) and Listener2. DB2 will go in this Availability Group. Now you have the option of having both database primary in one server or each database primary on separate server. You should also upgrade from RTM. There will be no service packs for SQL 2017, and so CUs aren't really optional. You don't have to install every one, but you need to have a regular schedule of applying them, and for a new solution you should always start with the latest CU. See Announcing the Modern Servicing Model for SQL Server.
How to show the Availability group listener instead of the nodes?
SQL, Database Testing • sql server availability groups connect configuration sql server 2017 • • morde

2

0
Votes

2
Posts

0
Views

J

Is there a way I could make sqlProd_ts to show up on the picture above, instead of the nodes? No, much as Dan Guzman has said, the listener is not an instance of SQL Server and as such will not respond to browser interrogations. In fact, availability groups do not use the browser service. If you want people to use the listener, tell them the listener name.
G

How to bring all replicas on a SQL availability group to an earlier state / point in time?
SQL, Database Testing • sql server restore availability groups ssms • • guilherme

2

0
Votes

2
Posts

0
Views

Yes, that is the correct approach. From my current understanding, restoring a back up on the primary alone would cause a data conflict (or corruption issues) on the secondary replicas Unless you remove the database from the Availability Group, you will not be able to restore the same database on the primary node. On a side note: If it is just dropping one table (depending on the size of the table compared to the database size) I might consider restoring the backup with a different name and recreate that table in the primary. There are many ways to do that which is not the scope of this question.

AlwaysOn Availability Group creation - Is it possible to delay the adding of a secondary replica?

Suggested Topics

ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available SQL, Database Testing • sql server 2012 availability groups • • Cleofas

Availability Group with log backups to NUL SQL, Database Testing • sql server 2016 availability groups • • meriah

How to let particular logins to work on the secondary replica only? SQL, Database Testing • sql server availability groups • • Laycee

How to 'pause' SQL Server AlwaysOn Availability Group SQL, Database Testing • sql server availability groups • • authera

How to list all forced query plans and cancel one SQL, Database Testing • sql server sql server 2016 ssms exec languageution plan query store • • fbio

What's the easiest way to move tables with identity key columns while keeping the referential integrity between them? SQL, Database Testing • sql server sql server 2016 foreign key primary key identity • • shizuka

Unable to Restart the SQL server Agent from SSCM SQL, Database Testing • sql server sql server 2016 sql server agent • • derk

Is there any option to enable Window NT logs in SQL Server Logs SQL, Database Testing • sql server windows sql server 2016 sql server 2019 • • juvenalb

Conditional Connection in SSIS based on Availability Group status SQL, Database Testing • sql server availability groups ssis • • jules

What happens in this strange SQL Server Mirroring situation? SQL, Database Testing • sql server availability groups mirroring • • carriann

Availability Group Read Only Routing Not Working for SQL Logins SQL, Database Testing • availability groups sql server 2017 sql authentic languageati read only routing • • baileigh

What permissions are needed to see view definitions via a query? SQL, Database Testing • sql server sql server 2016 ssms view • • Oba22

One Availability group, two primary nodes SQL, Database Testing • sql server availability groups • • Kadyn

How to show the Availability group listener instead of the nodes? SQL, Database Testing • sql server availability groups connect configuration sql server 2017 • • morde

How to bring all replicas on a SQL availability group to an earlier state / point in time? SQL, Database Testing • sql server restore availability groups ssms • • guilherme

ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available
SQL, Database Testing • sql server 2012 availability groups • • Cleofas

Availability Group with log backups to NUL
SQL, Database Testing • sql server 2016 availability groups • • meriah

How to let particular logins to work on the secondary replica only?
SQL, Database Testing • sql server availability groups • • Laycee

How to 'pause' SQL Server AlwaysOn Availability Group
SQL, Database Testing • sql server availability groups • • authera

How to list all forced query plans and cancel one
SQL, Database Testing • sql server sql server 2016 ssms exec languageution plan query store • • fbio

What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?
SQL, Database Testing • sql server sql server 2016 foreign key primary key identity • • shizuka

Unable to Restart the SQL server Agent from SSCM
SQL, Database Testing • sql server sql server 2016 sql server agent • • derk

Is there any option to enable Window NT logs in SQL Server Logs
SQL, Database Testing • sql server windows sql server 2016 sql server 2019 • • juvenalb

Conditional Connection in SSIS based on Availability Group status
SQL, Database Testing • sql server availability groups ssis • • jules

What happens in this strange SQL Server Mirroring situation?
SQL, Database Testing • sql server availability groups mirroring • • carriann

Availability Group Read Only Routing Not Working for SQL Logins
SQL, Database Testing • availability groups sql server 2017 sql authentic languageati read only routing • • baileigh

What permissions are needed to see view definitions via a query?
SQL, Database Testing • sql server sql server 2016 ssms view • • Oba22

One Availability group, two primary nodes
SQL, Database Testing • sql server availability groups • • Kadyn

How to show the Availability group listener instead of the nodes?
SQL, Database Testing • sql server availability groups connect configuration sql server 2017 • • morde

How to bring all replicas on a SQL availability group to an earlier state / point in time?
SQL, Database Testing • sql server restore availability groups ssms • • guilherme