11
Your existing configuration of HAProxy -> PGBouncer -> PGServer approch is better. And that only works.
Here is the reason: HAProxy redirects connection to different servers. this results in MAC address change in the database connection. So if PGBouncer is above HAProxy, each time the connections in the pool gets invalidated because of MAC address change.
11
It is not possible to delete a database that currently resides in an availability group. It is also not possible to restore over top of it while it remains in the availability group. Thus, this database was not in an availability group to begin with.
What you are describing is not possible as you will get an error if the database is involved in Always On. ...
10
Here's my go-to query to pull the events that Aaron mentioned (availability_replica_state_change). This script also takes the liberty of converting the UTC time to your local time:
declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());
-------------------------------------------------------------------------...
10
SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed).
can I expect consistent results to be returned from both replicas, every time?
Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a different ...
9
Most data warehouses are in Simple recovery model, and most true HA solutions require Full recovery (AGs, Mirroring). The minimum for Log Shipping is Bulk Logged, but that's not really true HA since there's no automatic failover.
If that's the case for yours (because really, a data warehouse in Full recovery is banana-town-crazy), your best bet would be a ...
9
If you have a running query or an open transaction, SQL Server doesn't have a way to move that query or transaction over to another server.
When your SQL Server instance (we'll call Instance A) restarts:
Running queries will be aborted
Open, uncommitted transactions will be rolled back
New queries will get a connection timeout
We can't fix #1 and #2, but ...
9
Go to SSMS
Object Explorer
Expand Always On High Availability
Expand Availability Groups
Right click your AG
Select Show Dashboard
In the dashboard, above the list of AG databases and their status, there is Add/Remove Columns button
Pick Estimated Data Loss (time) and add this column to dashboard
You will be able to see the estimated data loss time for each ...
8
A solution that will work with SQL Express would be ideal, SQL
Standard is fine, but I dont want to have to upgrade to SQL
Enterprise.
If you want something straight out of the box, that's going to be difficult to find. However, 2016 does bring Standard Edition Availability Groups. Standard Edition before 2016 does have Mirroring. Additionally if you're ...
answered Jul 2 '16 at 22:15
Sean Gallardy - Retired User
20.8k2 gold badges33 silver badges64 bronze badges
8
What is the advantage of Always On Failover Clustering vs Always On Availability Groups?
The main advantages given 2012->2017 for an FCI is:
No instance level metadata or objects required to be synchronized
Databases can be in any recovery model
Only one copy of the databases is required (1x total disk space required)
Generally easier to troubleshoot as it ...
8
We only want to have 2 replicas, one on-prem and one on azure. Is this possible?
Yes, it's possible. There are extra items you'll want to think about such as having a domain controller (if ad-attached clustering/cluster at all is used) and what type of pipe you'll need, region and latency, load balancer setup, etc., but it's not a hard requirements to have ...
answered Aug 30 '19 at 1:07
Sean Gallardy - Retired User
20.8k2 gold badges33 silver badges64 bronze badges
7
pgbouncer maintains connections in a pool with a postgres server. TCP connection establishment times are significant in a high-volume environment.
Clients making a large number of DB requests will have to setup a connection with a remote PGBouncer for each request. This is more expensive, than running PgBouncer locally (so the application connects to ...
7
Advantages of Transactional Replication over Availability Groups for Reporting Workloads
As a counter-point to the answer above, here are some reasons why transactional replication may be a better solution than availability groups for reporting workloads.
You can replicate a subset of tables in the database. Availability Group Replicas are always the ...
7
An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys....
7
1.When and how do we install Microsoft Updates?
Using AOAGs could potentially help with this, depending on how much downtime is considered to be "too much". There is still a blip associated with switching the primary to a synchronous secondary as the clients will be disconnected (this may or may not be an issue in your environment) as the move happens. They ...
answered Dec 11 '14 at 13:40
Sean Gallardy - Retired User
20.8k2 gold badges33 silver badges64 bronze badges
7
Use the Failover Cluster Manager application to manually stop the services.
This tells the cluster you are intentionally shutting down the service, so it won't attempt to bring it back up on another node.
The Failover Cluster service will wait until you manually bring the service back online using Failover Cluster Manager, where you can decide which node ...
7
You should be able to see the reason automatic seeding didn't complete by looking in the SQL Server error log from the time period where you created the AG (on both the primary and secondary replicas).
One common cause for this failure is that the Availability Group doesn't have the "CREATE ANY DATABASE" permission on the secondary. To correct that, you ...
6
I'm a DBA supporting both SQL 2000-2012, Oracle 11g, and Oracle 11g RAC.
IMO, Always On Availability Groups in SQL 2012 comes very close to the availability and scalability of RAC at much less cost in both dollars and complexity. You can scale out reads by querying against the mirrors, but you'd want to direct all DML to the primary server (SQL Server ...
6
If you want to be able to sustain a failover of two nodes within the failover cluster, then you'll need to ensure that you have five voters for quorum.
You currently have three nodes, and even if you were to add a disk witness, you'd still be at the same point: whether you have three or four possible quorum votes, you'd still need to have three votes for ...
6
You can change this behavior with the ALTER SERVER CONFIGURATION statement. The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want. See the bottom of the referenced link above for samples/examples. Here is one copied from the source:
ALTER SERVER CONFIGURATION
...
6
If a primary member unexpectedly dies, the secondary one won't elect itself the new primary anyway, since it won't have the majority (which is two in a replica set of two machines).
This is correct. In order to elect (and maintain) a primary, a majority of voting members need to be available.
It is impossible to read the data from the database if the ...
6
For automatic failover, you need a tie-breaker.
Otherwise, what would happen when the two servers had a network split and couldn't see each other? You wouldn't want them both automatically promoting themselves to primary, and both accepting writes. Think about what would happen on a table with an identity field, for example: both servers could quickly end ...
5
SQL Server 2014 does not introduce new online operations per se, but does improve a number of operations to increase availability.
From the what's new section:
Partition Switching and Indexing
The individual partitions of partitioned tables can now be rebuilt. For more information, see ALTER INDEX (Transact-SQL).
and
Managing the Lock Priority of Online ...
5
Sounds like an undertaking. Online index operations are not available in every edition of Microsoft SQL Server, hopefully this can get you started (it's in table form):
SQL Server 2012:
http://msdn.microsoft.com/en-us/library/ms190981.aspx
SQL Server 2008R2: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.105).aspx
SQL Server 2008: http://msdn....
5
Postgres-XL is attempting to solve this as of 2014. They're aiming directly at big data on PostgreSQL, and they have developers from Stado onboard.
5
Based on what you have described Log shipping is going to be the way to go. Log shipping is old school, tried and true.
Replication is pretty fragile, provides massive amounts of room for failure especially when it comes to adding new tables and getting those tables into the replication topology.
As for 2012 upgrade costs they may not be as bad as you ...
5
As Aaron Bertrand mentioned in his comment, AlwaysOn is only designed to support offloading of read-only operations, in addition to its (really neat) redundancy capabilities.
If all you're looking for is a place where your developers can check out the production data without putting load on the production server, then perhaps it's a technology that might be ...
5
As noted, mirroring is not dead. (At least not yet.) The general population doubts that Microsoft would totally abandon HA for Standard Edition.
However, there are other methods. In the SQL Server 2012 documentation when referring to mirroring it says: "If your edition of SQL Server does not support AlwaysOn Availability Groups, use log shipping."
http:...
5
I found my mistake. I realized that spider_link_mon_servers table must contain spider nodes, not backend nodes. And my spider nodes are not running on the same instance as backends. Spider tables run on port 3307.
So I need to configure monitoring servers like this (let's call them "proxy"):
CREATE SERVER server_main_proxy
FOREIGN DATA WRAPPER mysql
...
5
Once created an Availability Group (AG) cannot be renamed, although the databases within the AG can be renamed. You'd have to re-create it.
See here for the options for ALTERing an AG: https://msdn.microsoft.com/en-us/library/ff878601.aspx
When creating the AG using the GUI (wizard), once all the steps are completed you will see a 'Script' button which gives ...
5
If you were referring to Database Mirroring when you say mirroring (as opposed to SAN mirroring), then don't. It has been deprecated since 2012 now so it is a potential candidate for complete removal in the future releases.
Go with Always On Availability Groups. The difference in DBA overhead should be mostly the same. What is different, IMHO, is the ...
Only top voted, non community-wiki answers of a minimum length are eligible
