Rakesh Patil

SQL interview questions and answers on SQL Server Failover Clusters – II

November 5, 2018 by

SQL interview questions 1: You had a request to install SQL Server 2008 failover cluster on Windows Server 2012 R2. A Windows failover cluster is already ready. SQL Server 2008 installation failed at setup support files and the installation was failed for the rule “Cluster Service Verification “. How do you resolve this issue?

RCA:

When the log file InstallFailoverClusterGlobalRules is verified, it’s clearly saying that SQL couldn’t verify the cluster service. We verified and confirmed windows cluster service is online, but SQL couldn’t access the cluster service. We tried installing SQL Server from a command line by skipping the rules; It didn’t work and ended with the same error.

Setup/SkipRules=Cluster_VerifyForErrors/Action=InstallFailoverCluster

While searching for the solution we found that the problem is expected, and the root cause is SQL Server setup is trying to check windows cluster service using the deprecated feature in Windows 2012 R2.

Automation Server. Since these are deprecated features these are not enabled by default in Windows 2012 R2

Solution:

Check the windows Cluster Installation using the PowerShell command:

It showcases all windows cluster features installed/enabled status. If you observe “Failover Cluster Automation Server” and “Failover Cluster Command Interface” is Available but not installed.

Install the feature “Failover Cluster Automation Server” using the PowerShell command:

After you see the feature got installed successfully, try installing SQL Server and you should get success this time.

SQL interview questions 2: We need to install and configure SQL Server 2012 Active/Passive cluster on Windows Server 2012 R2. The Windows cluster is ready. Now can you tell me how do you install SQL Server Failover Cluster, at a high level?

Since Windows Server Failover Cluster is ready will start with the next step:

  • Configure MSTDC Role
  • Go to Failover Cluster Manager → Roles → Configure Role
  • Select Distributed Transaction Coordinator (DTC)
  • Client Access Point: Name and IP address required. Make sure that you logged in the account should have an appropriate right on Active Directory (AD) as this creates a computer object on Active Directory with the given name.

    We need a dedicated drive at least with 2 GB free space for MSDTC, assign that drive to this role After this configuration, you should be able to see MSDTC is up and running under Roles in Failover Cluster Manager.

  • Install SQL Server on Node -1
  • Run SQL Server Installer
  • Select New SQL Server Failover Cluster Instance

On Instance Configuration Page:

  • SQL Server Network Name: Careful while giving this name as this is the name that applications will use to connect to the database. Clients can identify this SQL instance using this name.
  • Instance Root Directory: Need not change this. Remember this is the path where SQL binaries available, not your databases.
  • Important Note: Your account which is using for SQL Server Cluster Installation should have proper rights to Active Directory as a new object will be created at the AD with this network name.
  • Cluster Resource Group: Give a suitable name for Cluster Resource Group Cluster Disk Selection: Select the cluster volumes/disks that are going to be used for storing SQL databases.
  • Cluster Network Configuration: Give the ipv4 IP address.
  • Server Configuration: Give service account under which SQL the server will run. As Microsoft recommended we need to use a Domain/AD account. Continue with the next page and complete the installation on Node-1
  • Install SQL Server on Node -2
  • On Node-2 and start SQL Server installer.
  • Select Add node to SQL Server Failover Cluster.
  • Same steps as we followed on Node-1

    We need not give Resource Group Name, IP Address, and Service Accounts Give password for service accounts Continue with the remaining steps as it is and finish the installation.

  • Then connect to Failover Cluster Manager and try to failover SQL Server resource from Active (Node 1) to passive (Node 2) to make sure failover is working as expected.

SQL interview questions 3: SQL Server configured on 2 nodes Active/Passive cluster. Now the requirement is to add SQL Server Reporting Services to the existing SQL Instance then we can get the same failover functionality for our SSRS. Can you explain how to do this?

Since SSRS is not a cluster aware service there is no straightforward method to do this. But yes, we can achieve this as below:

  • Installing SSRS:
    If we try to add SSRS feature to existing SQL clustered instance it fails at-rules page and doesn’t allow you to install SSRS.

    This can be resolved by skipping the rules using below command: Issue the below command at cmd

    Setup.exe/SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck/Action=

    It starts SQL Server installer and bypass the cluster instance check installation rule. We can be able to add SSRS feature for the existing SQL Server instance. Once it is done on Active node repeat the same process on the passive node.

  • Configuring SSRS:
    Now configure SSRS using SSRS configuration manager. The most important part is while giving SQL instance path use the SQL Server failover cluster network/virtual name instead of the node name.
  • If we use node name while configuring after failover report manager failed to locate the SQL Server instance. Once SSRS configured on active node do the same on a passive node.
  • Now your SSRS is installed and configured on a clustered instance. Failover SQL Server from Active node to Passive node and test SSRS functionality on the passive node.

SQL interview questions 4: We have been using SQL Server cluster instances for our applications. Now our client required a name change for SQL Server Network name which is being used by our applications to connect to SQL Server cluster instance. How do you change SQL Server network name?

  • Before changing the Network Name Check the current SQL Server Network/Virtual name.

    Using T-SQL statement: SELECT @@Servername or Failover Cluster Manager: Name under Server Name List out all clients which are using this SQL Server i.e. Applications, third-party tools, reporting tools etc.

    Change the SQL Server Network Name:

  • From Failover Cluster Manager Right click on Server Name Properties on the General tab, you can see the column DNS Name which nothing is but the network/virtual name.
  • Change the DNS name with the required new name and click on Apply. It takes a few seconds to change the name

    Post changing the Network Name:

  • Check the Server name as we did in the first step. Now you should see the new name
  • Recycle the resources and services: On failover cluster manager right-click on Server Name Take offline and Bring Online.
  • Now try to connect to SQL Server instance using the new network/virtual name. Now Failover SQL Server service from Active node to Passive Node and test the connectivity
  • Once you confirm all working fine then inform the clients to update their connection string to sue the newly changed network name to connect to Failover Cluster SQL Server instance.

SQL interview questions 5: We have a SQL Server 2012 cluster setup. On the active instance one of the DBA members were trying to remove SYSADMIN accounts. He was assisted to remove SYSADMIN role from all existing logins and add a new login SQL12Admin with SYSADMIN rights. Accidentally he removed all logins from SYSADMIN role but not able to add SQL12Admin to SYSADMIN role as he doesn’t have rights to do that. Also, SA account was disabled as a part of security policy. Now, can tell me how do you get their SYSADMIN rights back for at least one login SQL12Admin”?

  • Usually, if we have the same situation on a standalone server this problem can be resolved by restarting SQL Server in single user mode and we can add a login to the SYSADMIN role. But in this case, we need to deal with the cluster instance.
  • On active node open Failover Cluster Manager and expand to the clustered SQL Server group within services and applications. Right-click and select Take this service or application offline.
  • Along with SQL Server cluster disk and IP address is also offline. Take the clustered disks and the IP address back online with right-click those resources and choosing to Bring this resource online.
  • Open a command prompt and start SQL Server in single user mode net start mssqlserver/m. SQL Server starts in single user mode. However, it prevents us from connect from SSMS. In CMD connect to SQL Server instance using SQLCMD sqlcmd -S 192.168.1.102
  • On a successful connection add the login “SQL12Admin” to SYSADMIN role: sp_addsrvrolemember ‘SQL12Admin’, ‘SYSADMIN’
  • Stop SQL Server net stop mssqlserver
  • Open Failover Cluster Manager and Right Click on SQL Server group and Bring this service or application online. Now you should be able to log in using “SQL12Admin” with SYSADMIN rights

SQL interview questions 6: I have 2 nodes Active/Passive failover cluster on SQL Server 2012 built on Windows Server 2012 R2. Now on the active node SQL instance, one of the databases are not coming online due to a page corruption. Can you get it online by initiating a manual failover to another node?

No. failover clustering is to give 24×7 support for your Services (Ex SQL Server) not for your data. When you have 2 nodes Active-Passive cluster:

2 Different SQL Server installed on 2 Nodes Both nodes access data/data files from a single shared disk.

That means if a user database “EmpDB.MDF” is stored on disk E: When Node A is active: SQL Server on A access data files from disk E When Failover to Node B: SQL Server on A disconnect the session to datafile “EmpDB.MDF” on E drive and a new session start from SQL The server on Node B to E drive. When there is a page corruption on Datafile it can’t be resolved by initiating a failover.

SQL interview questions 7: I have a 2 Node Active/Passive cluster. On the Active Node there are 3 SQL Agent jobs that are scheduled. Now a failover initiated, and SQL Server Resource group moved to Node 2. My question is “Are these jobs are also replicated to Node 2? Or we need to manually create them again on Node 2?

All Jobs and Maintenance Plans are taken care by the failover procedure. We need not move/create SQL Jobs/Maintenance Plans on another node. The logic is simple in that all these jobs and maintenance plans are stored on system database MSDB and the failover just start using the SQL Service on Node 2 but the data source (All databases physical file location) is the same as it accesses from Node 1.


Rakesh Patil

Rakesh Patil

Data Architect at Phoenix Innovations
I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations.
I have hands-on Experience in Performance tunning, Database Disaster recovery,
AlwaysOn,Replication and AWS.
Rakesh Patil
Clustering, SQL interview questions

About Rakesh Patil

I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations. I have hands-on Experience in Performance tunning, Database Disaster recovery, AlwaysOn,Replication and AWS.

52 Views