February 23, 2019 at 12:41 pm
I know we can have 4 secondary database using Always on. However, i am little unsure that 4 secondary means 4 databases only or 4 Availability groups(each group can have multiple databases)?
Thanks
February 24, 2019 at 9:22 pm
Depending on your version and edition of SQL Server, you can have up to 8 AlwaysOn Availability Replicas (up to 3 synchronous), and between 1 and N databases in each Availability Group. The number of replicas is determined by version and edition. Prior to SQL Server 2016 , you need Enterprise Edition for AlwaysOn, and in SQL Server 2016 onwards, you can create basic availability groups. Prio to SQL Server 2012, AlwaysOn AGs did not exist.
Basic AGs are limited to one database per group, and one secondary replica per group. Enterprise AGs can feature multiple replicas, depending on the version:
2012 Limits: 1 primary replica, 4 secondary replicas, 2 of the replicas can be synchronous.
2014 Limits: 1 primary replica, 8 secondary replicas, 2 of the replicas can be synchronous.
2016+ Limits: 1 primary replica, 8 secondary replicas, 3 of the replicas can be synchronous.
(SQLTurbo.com)
The number of databases in an Enterprise AG is limited by available resources. Basically, due to thread utilisation in SQL Server, you will eventually hit an upper limit for the number of databases you can have. More info can be found here to help calculate your database limit based on your hardware.
February 27, 2019 at 11:14 am
Thanks. Another question.So i am looking to setup always on high availability for DR and reporting purposes.So, i can take all access of users from Production and point it to reporting server only. How does the security would work when there is fail-over occurs, because secondary would become primary and primary would become secondary(they will not have access on primary after fail over right). How to handle situation like this? Is that setting up Application intent parameter to read only on secondary would handle this problem?
February 27, 2019 at 1:34 pm
Admingod - Wednesday, February 27, 2019 11:14 AMThanks. Another question.So i am looking to setup always on high availability for DR and reporting purposes.So, i can take all access of users from Production and point it to reporting server only. How does the security would work when there is fail-over occurs, because secondary would become primary and primary would become secondary(they will not have access on primary after fail over right). How to handle situation like this? Is that setting up Application intent parameter to read only on secondary would handle this problem?
You do not want to setup a secondary that is both HA\DR and read-only (reporting). Instead - you will want a separate node for HA\DR only (synchronous mode - not read-only) and a third node as asynchronous set to read-only. Reporting processes/users would then access the reporting replica directly (don't need or want a listener for this instance) - and your application will use the listener address to access the current primary replica.
This allows you to setup and configure reporting users separately from production users. On the read-only replica you only add the logins for those users that need access - all other logins would not be added to that instance. The HA\DR instance will need the same logins as the primary. The read-only replica does not need any agent jobs from the primary and can therefore support any automated exports/reports/build processes as needed - the HA\DR node(s) will need the exact same jobs as the primary and they need to be setup so they check if this node is the primary node...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2019 at 9:58 pm
Admingod - Wednesday, February 27, 2019 11:14 AMThanks. Another question.So i am looking to setup always on high availability for DR and reporting purposes.So, i can take all access of users from Production and point it to reporting server only. How does the security would work when there is fail-over occurs, because secondary would become primary and primary would become secondary(they will not have access on primary after fail over right). How to handle situation like this? Is that setting up Application intent parameter to read only on secondary would handle this problem?
You can use tools like the DBATools PowerShell module to manage the synchronisation of logins, jobs, credentials etc between your replicas (http://dbatools.io).
In planning your architecture, you need to consider all aspects of the end state. What is your DR process? What level of availability do you require? Should reporting be offloaded to a separate replica in the event of a failover as well? What are your licensing spend limitations?
Answering questions like these will help identify the options suitable to provide your end-state. For example, if you have Software Assurance, you get a free secondary licence BUT you cannot run any workload on this replica, including backups, reporting etc. If you don't want to pay for a second instance, then you can't offload reporting.
To your question, if you want reporting users automatically redirected post-failover to use the new secondary (original primary), then you need to ensure both replicas are readable, and configure your routing rules to route read connections to the opposite replica. Routing rules only apply for the primary server in question, so if ServerA has a routing rule to route to ServerB first then ServerA second for read only connections and ServerB has the opposite routing rule (A first, B second), then after failing over from ServerA to ServerB, ServerA will now be the replica used for read only connections automatically.
March 5, 2019 at 2:20 pm
also remember windows users will be different than sql logins you can get the dreaded mismatch with the sids
March 5, 2019 at 2:45 pm
tcronin 95651 - Tuesday, March 5, 2019 2:20 PMalso remember windows users will be different than sql logins you can get the dreaded mismatch with the sids
SQL logins are susceptible to mis-matched SIDS, not windows logins. This can be eliminated by creating the logins on the secondary server by specifying the SID.
CREATE LOGIN FOO WITH PASSWORD = 'XXXXXXX', SID = 4E4BCE40-B7B4-4F74-BAB4-2183DEC07BD5
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 7, 2019 at 7:12 pm
I was looking to setup always on availability group in SQL Server 2014. Found that distributed transactions are not supported in prior to SQL 2016. Distributed transactions are used by the vendor product. Not able to understand the connection between application using sql server distribution transactions. However, my understanding is the distributed transactions are also not supported in database mirroring when you setup for automatic fail over right? So the way i look at is that the distributed transactions does play a role when you configure always on availaibility group or database mirroring for automatic fail over only right? If i configure always on availability group for manual failover with Asynch mode, so distributed transactions should not be a concern right? Please advise?
Thanks
March 8, 2019 at 8:39 am
Admingod - Thursday, March 7, 2019 7:12 PMI was looking to setup always on availability group in SQL Server 2014. Found that distributed transactions are not supported in prior to SQL 2016. Distributed transactions are used by the vendor product. Not able to understand the connection between application using sql server distribution transactions. However, my understanding is the distributed transactions are also not supported in database mirroring when you setup for automatic fail over right? So the way i look at is that the distributed transactions does play a role when you configure always on availaibility group or database mirroring for automatic fail over only right? If i configure always on availability group for manual failover with Asynch mode, so distributed transactions should not be a concern right? Please advise?Thanks
Each server will need to be configured for Distributed transactions. Unlike a cluster, where the DTC is configured as a cluster resource, this will need to be configured on each server.
In one of my AG environments, we have 3 servers. The primary, and 2 secondaries. One secondary is a synchronous read-only copy used for ad-hoc queries and some reporting. The second is at our co-lo, set up as asynchronous, and would only be made primary in the event of a disaster.
We have read-only routing configured between the primary and the synchronous secondary. The only time this fails over / back is during patching, unless there are some other issues that cause a failover.
If a user needs to run an ad-hoc query, they connect to the secondary server by name, not through the listener. All apps or reports that are read only use the listener with ApplicationIntent=ReadOnly" set in the connection string.
Logins will need to be synced between the nodes. When a new login is created on the primary, it does not get transferred to the secondary. However, any permissions granted in the databases are synced to the secondaries.
We have this automated, so if a new login is created it gets created on all three servers.
I strongly suggest you do some learning on this subject and invest in yourself.
A starting point may be the great series of articles on this site:
http://www.sqlservercentral.com/stairway/112556/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 8, 2019 at 9:47 am
Thanks John for the info! However, i have been doing lot of learning on my side as well, just wanted some clarification on DTC. There were no much information posted about the real need of Distributed transactions.
Do you still think that DTC is needed to be configured for manual failover as well? I am under impression that Distrubuted transactions are only needed for Automatic failover in Always on Availability Group.
I have read that Distributed transactions are also not supported for Mirroring(with Witness Server) and Always on Avaialability group prior to 2016 for Automatic failover. Please advise?
March 8, 2019 at 10:00 am
Admingod - Friday, March 8, 2019 9:47 AMThanks John for the info! However, i have been doing lot of learning on my side as well, just wanted some clarification on DTC. There were no much information posted about the real need of Distributed transactions.
Do you still think that DTC is needed to be configured for manual failover as well? I am under impression that Distrubuted transactions are only needed for Automatic failover in Always on Availability Group.
I have read that Distributed transactions are also not supported for Mirroring(with Witness Server) and Always on Avaialability group prior to 2016 for Automatic failover. Please advise?
Like I said, we have simply configured DTC on each server. We have had no issues when we have had to fail over, with both mirroring and AG groups
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 8, 2019 at 10:47 am
So DTC is part of windows operating system right? Distributed transaction has nothing to do with SQL Server right?
What version of SQL Server did you configure Always on Availability group?
March 8, 2019 at 10:56 am
Admingod - Friday, March 8, 2019 10:47 AMSo DTC is part of windows operating system right? Distributed transaction has nothing to do with SQL Server right?
What version of SQL Server did you configure Always on Availability group?
DTC is part of Windows.
We have mirroring on 2008R2 and 2012, which are going away.
We have AG groups on 2016 and 2017
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 8, 2019 at 11:01 am
Thanks. Do you know when Microsoft is planning to stop supporting database mirroring?
March 8, 2019 at 11:06 am
Admingod - Friday, March 8, 2019 11:01 AMThanks. Do you know when Microsoft is planning to stop supporting database mirroring?
No, but a google search for "database mirroring deprecated" returns quite a few hits.
Here is one more thing. Licensing. The primary needs to be licensed, as well as the read-only secondary. If the secondary is not read-only, it will only need software assurance.
So, if you have one read-only secondary, your license costs double.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply