August 18, 2021 at 5:13 am
I am having a real hard time trying to create a new instance for a database using SQL Server 2019 Failover cluster.
I am trying to follow these directions. I am stuck on Step 10 which is Instance Configuration. Normally when I create an instance in a regular SQL Server I just give it an instance name and that is it. In the SQL Server Network Name field would I need to create a new computer in the AD? Would the client point to this db in the connection string? I tried doing that. I entered the instance name and ID the same for my new instance (INSTANCE2). Then in the Cluster Resource Group page I get the same same errors for Available storage and Cluster Group, but also SQL Server(INSTANCE1): "The cluster group 'SQL Server(INSTANCE1)' contains resource 'SQL Server(INSTANCE1)' of type 'SQL Server' That are not permitted in a SQL cluster group. Ensure the cluster group does not contain SQL resources from another instance or Generic Service types."
If I click to the next screen (Cluster Disk Selection), I can't select any of my disks.
I get the following errors:
Disk: Quorum: The disk resource 'Quroum' Cannot be used because it is a cluster quorum drive.
SQL-DATA: The disk 'SQL-DATA' is already in use by resource 'SQL Server(INSTANCE1)'. To use a disk in a new SQL Server failover cluster instance, the disk must not have any dependencies that reference it..
SQL-LOGS: The disk 'SQL-LOGS' is already in use by resource 'SQL Server(INSTANCE1)'. To use a disk in a new SQL Server failover cluster instance, the disk must not have any dependencies that reference it.
I am really unsure how to proceed. Any help would be greatly appreciated.
August 18, 2021 at 7:58 am
You need to present all fresh storage. You cannot use the existing storage which has been provisioned and presented to INSTANCE1.
You then need a new computer object in AD for SQLVirtualName2
After you have brand new disks, and pre-staged the necessary AD objects you can then go and install another instance of SQL as an FCI.
August 18, 2021 at 11:57 am
So there is no way at all to share storage for a new instance using fail over clustering? Kinda wish I knew this before hand.
I personally like to create a new instance when a system uses multiple databases, to tidy up. Like SharePoint on premise which creates a bunch of databases with no similar names. Is there a Best Practice for when it is necessary to create new instances?
August 18, 2021 at 12:10 pm
No, no way at all.
The resources used by the role must be independent of other roles resources.
Each FCI **MUST** have its own storage, its own VNN, its own IP.
Best practise is always subjective, create the instances as they are needed, but my best practise is to never have more than 1 instance per cluster/machine, unless someone can give me a very good reason to use stacked instances.
August 25, 2021 at 1:13 pm
To install or upgrade a SQL Server failover cluster instance (FCI), you must run the Setup program on each node of the underlying Windows Server failover cluster. To add a node to an existing SQL Server failover cluster instance, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node to manage the other nodes.
Depending on how the nodes are clustered, the SQL Server failover cluster instance is configured in the following ways:
SEVENMENTOR: BEST TRAINING PROVIDER
Nodes on the same subnet or the same set of subnets - The IP address resource dependency is set to AND for these types of configurations.
Nodes on different subnets - The IP address resource dependency is set to OR and this configuration is called a SQL Server multi-subnet failover cluster instance configuration. For more information, see SQL Server Multi-Subnet Clustering (SQL Server).
The following options are available for SQL Server failover cluster installation:
Option1: Integration Installation with Add Node
SQL Server integrated failover cluster installation consists of the following steps:
Create and configure a single-node SQL Server failover cluster instance. When you configure the node successfully, you have a fully functional failover cluster instance. At this point, it does not have high availability because there is only one node in the failover cluster instance.
On each node to be added to the SQL Server failover cluster instance, run Setup with Add Node functionality to add that node.
Option 2: Advanced/Enterprise Installation
SQL Server Advanced/Enterprise failover cluster installation consists of the following steps:
On each node that is a possible owner of the new SQL Server failover cluster, follow the Prepare Failover Cluster setup steps that are listed in the Prepare section. After you run the Prepare Failover Cluster on one node, Setup creates the Configuration.ini file that lists all the settings that you specified. On the additional nodes to be prepared, instead of following these steps, you can supply the autogenerated Configuration.ini file from the first node as an input to the Setup command line. For more information, see Install SQL Server 2016 Using a Configuration File. This step prepares the nodes ready to be clustered, but there is no operational instance of SQL Server at the end of this step.
After the nodes are prepared for clustering, run Setup on one of the prepared nodes. This step configures and finishes the failover cluster instance. At the end of this step, you will have an operational SQL Server failover cluster instance and all the nodes that were prepared previously for that instance will be the possible owners of the newly-created SQL Server failover cluster instance.
If you are creating a failover cluster instance that spans multiple subnets, Setup will detect the union of all the subnets across all nodes that have the SQL Server prepared failover cluster instance. You will be able to specify multiple IP addresses for the subnets. Each prepared node must be the possible owner of at least one IP address.
January 20, 2025 at 4:03 pm
The creation of a new SQL Server instance within a fail over environment can be quite beneficial in regard to high availability and disaster recovery access. It’s superb how the combination of implementing a failover cluster can guarantee access to a database regardless of whether it is a hardware failure or maintenance. If you are delving deep into this, following the best practices and comprehensive guides that are available online would be a smart move. For sure when you tweak a life insurance landing page service your online presence will be enhanced. It’s about creating an appealing experience where visitors are able to access the information they need, while at the same time ensuring that they metamorphose into clients. Both these activities involve thorough planning and execution but the benefits are quite worth the hassle.
January 20, 2025 at 4:57 pm
Best practise is always subjective, create the instances as they are needed, but my best practise is to never have more than 1 instance per cluster/machine, unless someone can give me a very good reason to use stacked instances.
I worked on a project - quite some time ago that was basically a consolidation project. The goal was to reduce licensing costs and hardware requirements.
We had a third-party product that was used to create and manage the cluster, which allowed us at that time to have shared storage across all nodes in the cluster. With 4 nodes in the cluster - we were able to install multiple instances onto all nodes with failover options across the farm.
These also had to be managed by CPU and memory utilization - with enough resources available to support all instances in a failover scenario (patching servers - for example), so we definitely had to make sure we didn't over-allocate based on memory available - and monitor for CPU usage.
We were able to consolidate more than 40 individually licensed instances into this cluster - reducing overall costs by a significant factor.
I would say the only reason to setup a multi-instance cluster would be to consolidate instances to reduce overall costs and still be able to provide HA/DR for those instances.
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
January 22, 2025 at 5:17 pm
So there is no way at all to share storage for a new instance using fail over clustering? Kinda wish I knew this before hand.
If you didn't, then you did not spend any time learning how clustering works!
Resources are dependent upon other resources in a cluster. In SQL, SQL Server depends upon the IP addresses and the disks (among other things). When there are multiple instances, they need to be able to operate independently. If, as one example, you had an active/active 2 node cluster. Instance1 would be alive on Node1, and Instance2 would be alive on Node2. The disks cannot be shared. You will need at least 6 IP addresses in this same setup. Cluster, node1, Node2, Virtual SQL 1 and Virtual SQL 2.
Node 1 and 2's IP do not move. The cluster and the SQL IP's can be alive on either of the 2 nodes.
Would the client point to this db in the connection string?
Clients will need to connect to the virtual SQL Cluster names, not the server/instance. The database would be part of that.
I personally like to create a new instance when a system uses multiple databases, to tidy up. Like SharePoint on premise which creates a bunch of databases with no similar names. Is there a Best Practice for when it is necessary to create new instances?
There is no single good reason to create a new instance when there are multiple databases. A default or a named instance can handle many databases. You would create separate instances for your environment. As an example, I had an 8 node cluster with 7 instances. They were normally on nodes 1 thru 7 with node8 being the warm spare.
A "best practice" (which is a term that should be banned!) used to be use a named instance and change the port from 1433. That's was security by obfuscation.
If you are on a physical environment with a SAN, you should be able to create logical disks for each instance. One thing to do may be to create tempdb on local disks for better performance. Same with VM's. You can add up to 4 disk controllers for each VM. If you install the OS and SQL on the local drives, the data, log, and backup files should be on cluster drives.
Also, you need to be aware of resource allocation. If each node is configured with 100 GB of RAM, and there are 2 instances, you will need to configure the max memory for each instance as something like 40 GB, leaving 10 GB for the OS. If both instances are on the same node, the resources should not be competing with each other.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply