April 4, 2008 at 1:55 pm
I don't have much, if any, expirience w/ SQL Clustering. But, my question is, how are user account hanlded in regards to clusters? Maybe I have a misunderstanding or simple lack of Cluster general knowledge.
Meaning, if there are three nodes w/in a cluster, were does the security reside? Meaning, is there an 'SA' account on all three nodes or do they share security? In that example, if I have a SQL user ID called '123', can I simple change the password of this ID and it will change throughout...?
Again, I apologize for my lack for knowledge on this topic...
-Topher.
April 4, 2008 at 2:21 pm
Your database instance will only reside on one of the servers at a time. The purpose of clustering is high availability. What happens is if the server that the SQL instance is running on experiences a failure, the SQL instance and all of its resources will "Failover" to another server in the cluster. Thus, it still only resides on one server.
April 4, 2008 at 2:36 pm
Understood. So, if you have three nodes w/in your Cluster, where is the data actually being stored? Obviously, it could not be stored on one of the nodes as the data would be lost in chances of a failure.
April 4, 2008 at 10:22 pm
Each instance of SQL Server (this is important, because it also applies to a non-clustered setup with multiple instances) has its own master database. In the master database, the information on what logins may connect to that instance can be found. This is true whether or not the instance is clustered or not. For instance, take the situation like the following:
Single server w/ 2 instances, one named Instance1 and another named Instance2. SQL Server will install in different directories for Instance1 (let's say it's MSSQL.1) and Instance2 (and MSSQL.2). Also, the master database for Instance1 will not be the same database for Instance2. By default, they will be found in the respective Data directory for that installation (and therefore MSSQL.1\Data and MSSQL.2\Data).
Except with a relatively few sets of resources (AD Helper service, Browser service), each SQL Server instance is stand alone and does not share with the other instances. For all intents and purposes, you can treat them as if they were installed on completely separate systems from each other.
K. Brian Kelley
@kbriankelley
April 6, 2008 at 7:26 am
When clustering the data i.e the database files for both the system and users database will need to stored on a shared drives managed by the cluster. so when you failover from node one to node two node, node two will then take control of the shared disks.
The failover of thh shared drives will be dependant on failover of the sql service for the instance. So you couldn't have SQL Server running on node 2 and the shared Disks being active on Node 1
Gethyn Elliswww.gethynellis.com
April 7, 2008 at 2:31 am
Using Micrsosoft Cluster Service to provide high-availability for MSSQL is a good technology, but it is not trivial to set up. Unfortunately, a lot of the complexity is hidden behind a variety of Wizards; I say unfortunately, because it is possible to set up a Cluster and not really have any idea how it works, how it will fail over, etc.
For example, if you don't get your dependencies exactly right, any resource you have clustered will not failover correctly.
Brian's summary is useful to bear in mind. Remember on failover, resources are "moved" from one node to another. What actually happens will vary depending on the resource and the storage solution (eg. a virtualised SAN). But the end result is your MSSQL instance carries on as before, with the same "server name" (which is, itself, a resource), same instance name, and all of the same databases (and thus all of the same logins).
Thoroughly evaluate and test a Cluster before you even consider putting it into production.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply