141

Distributed Database Systems 101 Or, Distributed Databases - what the FK does 'web scale' actually mean? Distributed database systems are complex critters and come in a number of different flavours. If I dig in to the depths of my dimly remembered studies on this at university I'll try to explain some of the key engineering problems to building a ...


50

The way I always like to visualize high availability solutions is the following: SQL Server Failover Cluster Instance (FCI) What is highly available? The entire instance. That includes all server-objects (logins, SQL Server Agent jobs, etc.). This also includes databases and their containing entities. It's a great solution for highly available SQL ...


39

The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible. The licensing math on that proves the point at ~$7,500 USD per core for Expensive Edition. Buying the correct hardware can pay for itself in reduced licensing costs. See Processor Selection for SQL Server by Glenn Berry. It's a great resource ...


22

two (or more) servers in a Windows Failover cluster, SQL Server as a clustered instance What Kind of workload? "It depends" - but seriously, this is useful for an online application where you need to have local in data center High Availability. You are protected against a failure of one machine, or of one operating system. The logins, jobs, new databases, ...


21

Relational databases can cluster like NoSQL solutions. Maintaining ACID properties may make this more complex and one must be aware of the tradeoffs made to maintain these properties. Unfortunately, exactly what the trade-offs are depends on the workload and of course the decisions made while designing the database software. For example, a primarily OLTP ...


14

The fundamental answer is that the consistency model is different. I am writing this to expand ConcernedOfTunbridge's answer which really ought to be the reference point for this. The basic point of the ACID consistency model is that it makes a bunch of fundamental guarantees as to the state of the data globally within the system. These guarantees are ...


14

As SQL Server is available for Linux can I configure Clustering on Linux nodes? You can configure Corosync and pacemaker for Linux. Good luck. I mean mixed with Windows - some nodes are on Linux, others on Windows in the same cluster? In the same cluster, no. Someone will argue with me and say, "Well, Sean, you can do read-scale availability groups like ...


13

By itself, no: clustering relies on shared storage. A failover cluster with shared storage looks like this: All 4 of the components - the 2 servers, the switch, and the shared storage - should be located in the same data center. Disaster recovery is about maintaining availability even when you lose a data center. That means you need the same infrastructure ...


11

how does SQL know when to fail over There is a Flexible Failover Policy which you can use to adjust the sensitivity to the criteria that will trigger a failover. The default is FAILURE_CONDITION_LEVEL = 3 which means "critical error, server unresponsive, or server down." You can see more details here and here. how would my application ... know where it ...


10

First, there's no SQL Server 2013, but the good news is that the version number of SQL Server doesn't matter. It's more based on Windows failover cluster networking, which is explained here in a 3-part series by the Microsoft folks: http://blogs.technet.com/b/askcore/archive/2010/02/12/windows-server-2008-failover-clusters-networking-part-1.aspx And then ...


10

You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event ...


10

Hold on hold on hold on While the performance and licensing aspects are interesting, they're not the only aspect of a workload to consider. One thing that can have an impact on processor choice is worker threads. Worker Threads? Yeah buddy! They're the things that your SQL Server will use to run your queries and do all the background stuff that it needs ...


9

Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server. From MSDN: "Database mirroring is a primarily software solution for increasing database availability. Mirroring is ...


9

Long shot perhaps, but worth checking file \Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini or the equivalent on your setup. You may have to manually edit it with the instance name. Otherwise SSIS connections might be looking for an msdb of a default SQL instance that doesn't exist.


9

It is also important to consider what is shared. Failover Clustering uses two or more server nodes sharing one disk array. If the disk array goes down then you lose service, regardless of how many server nodes there are. If the server room where that disk array is located catches fire or floods then you lose service. AlwaysOn Availability Groups and ...


9

You have a quorum issue in this setup. There are only two participants in the cluster: SQL1 SQL2 If SQL1 goes down, there's no way for SQL2 to know that it should be the primary. For all SQL2 knows, the network has gone down and SQL1 is still operating as the primary. Thus, SQL2 goes to the RESOLVING state (it's not sure what to do). This is to prevent ...


7

Knowing that two instances are absolutely not related to each other, i guess that you can use two instances on one server with 64gig for each. At least to my understanding, OS is the one who assigns memory to apps, so it should be fine :)


7

Database mirroring questions from TechEd: failovers and partner timeouts ... when the cluster is failing over and the principal instance is unavailable for 60-90 seconds, the other instances in the mirroring session will time-out after ten seconds and initiate a mirroring failover... The solution to the problem is to change the partner time-out ...


7

I would not say that its a bad idea, but its always prefer not to touch the CPU affinity on sql server unless you know for sure that the problem is due to excessive context switching (and this too might be not the actual problem, but just a symptom). This seems to be working okay, even when failover occurs, but is this a bad idea? Forcing affinity means ...


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

MariaDB Galera Cluster: Solution 1: 1) I've changed safe_to_bootstrap parameter to 1 on one of the node in the file /var/lib/mysql/grastate.dat: safe_to_bootstrap: 1 2) After that I killed all mysql processes: killall -KILL mysql mysqld_safe mysqld mysql-systemd 3) And started a new cluster: galera_new_cluster 4) All other nodes I reconnected to the ...


7

Yes it is the same thing. See section 3 which explains this term. First lets get few terms straight. Windows Server Failover Clustering A Windows Server Failover Clustering (WSFC) cluster is a group of independent servers that work together to increase the availability of applications and services. SQL Server 2016 takes advantage of WSFC services ...


7

I have noticed after every WAN issue where the WAN connection is flapping, and the off site nodes constantly disconnect and reconnect, the memory of the primary server gets consumed by the "HADR Log Block Msg Pool" Yes, this is currently by design. It is expected that the network between the two sites can handle the traffic and is available. Since it seems ...


7

If you want to just use stand alone instances - which is the most common method of implementing availability groups [stand alone to stand alone] - then you only need to do a stand alone install. So, yes, you can do the stand alone if that's what you're looking for. It is possible to use a SQL Server failover cluster instance install to also work with ...


6

You don't get "load balancing" with SQL Server. There are a lot of "High Availability" features including clustering. That is, you can't have read/write on the same data with multiple SQL Server instances. You can off load reporting onto a snapshot, or replicate data between Instances, or "shared scalabl" but none is full OLTP on all data on every node. ...


6

HA HA HA HA What a coincidence. I was asked to evaluate it myself. Here is what has been done so far... My supervisor installed it I created two EC2 instances I have created Master/Slave status between them I create the Cluster on ScaleArc EC2 I submitted the Master EC2 as a ScaleArc Master I submitted the Slave EC2 as a ScaleArc Slave I adjusted the ...


6

My answer won't be as well-written as the previous one, but here goes. Michael Stonebraker of Ingres fame has created a MPP shared-nothing column-store (Vertica) and a MPP shared-nothing New SQL database (VoltDB) which distributes data between different nodes in a cluster and maintains ACID. Vertica has since been bought by HP. I believe other New SQL ...


6

For some reason however, when I pick the SCRIPT INDEX -> CREATE TO -> NEW QUERY WINDOW from SSMS Object Explorer, it offers the basic index creation script where the index is NOT filtered. "For some reason" indeed. So weird and strange. You probably have "Script for server version: SQL Server 2005" set in SSMS Tools > Options > SQL Server Object ...


6

There are a few things that I can think of off the top of my head. You're running a multi-instance failover cluster so in theory I'd expect to see each node to be sized such that at any given point in time it can handle the load of all three instances. Chances are that this is not the case, but maybe it is. Ideally, you'd also have a spare node that can ...


6

I dont see anything wrong in your setup. To be fair, this is the correct way of setting up SSIS in a clustered environment. Read up SSIS and clustering: What you should do instead Also for the login failure, its worth to check the Windows Server 2008 R2's firewall rules to allow an inbound rule for either the msdtssrvr.exe executable or the DCOM port ...


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