Automate refresh of a SQL Server database that is part of an Availability Group

 

 we cannot restore a database that is part of availability group. We need to first remove the SQL Server database from the Availability Group in order to restore the database. In this tip we look at how we can automate this process to ensure that AlwaysOn is re-established after the restore without manual intervention.

First we walk through the steps that need to take place and then talk about how this can be automated using jobs.

Remove SQL Server Database From AlwaysOn Availability Group on Primary

The first step is to remove the database from the Availability Group. We can achieve this using the below command.

Note: you would need to replace the <Availability Group> and <Database Name> with your values.

-- runs on primary server

USE master 
GO
 
ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>]; 
GO 

Restore SQL Server Database on Primary

The next step would be to restore the backup to refresh the database on your primary server. You can also add additional steps for any specific requirements to run after the restore such as granting permissions, etc.

This is the backup that is coming from the other server that will be used to refresh the database on the primary server.

-- runs on primary server

USE master 
GO
 
RESTORE DATABASE [<Database Name>] 
FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE 
GO

Ensure Restored SQL Server Database is in Full Recovery on Primary

We need to ensure the database recovery mode is set to FULL in order to meet AlwaysOn requirements.

-- runs on primary server

USE master 
GO
 
ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT 
GO 

Backup SQL Server Database and Log on Primary

Next you need to take a full backup and a transaction log backup of this restored database.  To make this easier for the restore you should create the backups on a network share which is accessible from both the PRIMARY and SECONDARY servers. This will avoid having to copy the backup from the PRIMARY to the SECONDARY and will also save time by eliminating the copy step.

-- runs on primary server

BACKUP DATABASE [<database Name>] 
TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION 
GO
 
BACKUP LOG [<database Name>] 
TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION  
GO 

Add SQL Server Database to Availability Group on Primary

Next we need to add the database back to the Availability Group.

-- runs on primary server

USE master 
GO
 
ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>];  
GO

Now you are done with all the steps on the PRIMARY server.

Restore SQL Server Database on Secondary

Next we need to restore the full backup and log backup on the secondary server. 

-- runs on secondary server

USE master 
GO
 
RESTORE DATABASE [<database Name>] 
FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY 
GO
 
RESTORE LOG [<database Name>] 
FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY  
GO 

Check Status of SQL Server Database and then Add to Availability Group on Secondary

The next step is important, this enables data synchronization after the database has been restored and is ready to join the Availability Group.

-- runs on secondary server

-- Wait for the replica to start communicating 
begin try 
   declare @conn bit 
   declare @count int 
   declare @replica_id uniqueidentifier 
   declare @group_id uniqueidentifier 
   set @conn = 0 
   set @count = 30 -- wait for 5 minutes 
  
   if (serverproperty('IsHadrEnabled') = 1) 
      and (isnull((select member_state 
                   from master.sys.dm_hadr_cluster_members 
                   where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0) 
      and (isnull((select state 
                   from master.sys.database_mirroring_endpoints), 1) = 0) 
   begin 
      select @group_id = ags.group_id 
      from master.sys.availability_groups as ags 
      where name = N'<Availability Group>' 

      select @replica_id = replicas.replica_id 
      from master.sys.availability_replicas as replicas 
      where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id 

      while @conn <> 1 and @count > 0 
      begin 
         set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1) 
         if @conn = 1 
         begin 
            -- exit loop when the replica is connected, or if the query cannot find the replica status 
            break 
         end 
         waitfor delay '00:00:10' 
         set @count = @count - 1 
      end 
   end 
end try 

begin catch 
   -- If the wait loop fails, do not stop execution of the alter database statement 
end catch 

ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>]; 
GO 

SQL Server Agent Jobs

To automate this process we can create SQL Server Agent jobs on both servers:

1. Primary Server - Create one job that does the following:
   1. Removes database from Availability Group
   2. Restores the database
   3. Sets recovery model of database to FULL
   4. Adds database to Availability Group
   5. Creates database backup and log backup
   6. optional - runs job on secondary server using sp_start_job (there are other things you would need to setup to do this)
2. Secondary Server - Create one job that does the following:
   1. Restores database backup and log backup
   2. Checks database status and adds database to Availability Group

If you don't have the primary server start the job on the secondary server you would need to figure out when to schedule this job to run after the job on the primary server completes.

How to Find which user deleted the database in SQL Server

 SQL Server: How to Find which user deleted the database in SQL Server

In this article we will take a look at the steps which you can follow to quickly identify the user who deleted the user database in SQL Server.

There are two different methods by which one can easily find who deleted the database in SQL Server. The first method is using builtin Schema Changes History Report. The second method is to load the SQL Server Default Trace into a table to see who deleted the database.

Steps to find who deleted the User database in SQL Server Using SQL Server Schema Changes History Report

1. Open SQL Server Management Studio and Connect to the SQL Server Instance.

2. Right click SQL Server Instance and Select Reports -> Standard Reports -> Schema Changes History as shown in the below snippet.

SQL Server Schema Changes History Report to Find which user deleted the database in SQL Server

3. This will open up Scheme Changes History report which will have the details about who deleted the SQL Server Database along with the timestamp when the database was deleted. Refer the below snippet for more information.

Steps to Identify who deleted the user database using Using Default Trace Files

The SQL Server Default Trace file gives very useful information to a DBA to understand what is happening on the SQL Server Instance. 

Execute the below query to find the default path of trace file in SQL Server.

SELECT
	 path AS [Default Trace File]
	,max_size AS [Max File Size of Trace File]
	,max_files AS [Max No of Trace Files]
	,start_time AS [Start Time]
	,last_event_time AS [Last Event Time]
FROM sys.traces WHERE is_default = 1
GO

How to Load SQL Server Trace File in SQL Server Table

Execute the below script to load the default trace file content in a temporary table to read the relevant information with respect to who deleted the user database on the instance of SQL Server. If you don’t find the relevant information in the latest trace file then it is recommended to load the data from all the available trace files on the server to explore the information.

USE tempdb
GO

IF OBJECT_ID('dbo.TraceTable', 'U') IS NOT NULL
	DROP TABLE dbo.TraceTable;

SELECT * INTO TraceTable
FROM ::fn_trace_gettable
('G:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log\log_12.trc', default)
GO

SELECT
	 DatabaseID
	,DatabaseName
	,LoginName
	,HostName
	,ApplicationName
	,StartTime
	,CASE
		WHEN EventClass = 46 THEN 'Database Created'
		WHEN EventClass = 47 THEN 'Database Dropped'
	ELSE 'NONE'
	END AS EventType
FROM tempdb.dbo.TraceTable
	WHERE DatabaseName = 'MyTechMantra'
		AND (EventClass = 46 /* Event Class 46 refers to Object:Created */
			OR EventClass = 47) /* Event Class 47 refers to Object:Deleted */
GO

From the above snippet you could see that the event class 46 represents the database creation time along with the user who created it and event class 47 represents the database deletion time along with the user who deleted the database.

Conclusion

In this article we have seen how easily one can find out who deleted the user database in SQL Server with the help of in built SQL Server Schema Changes History Report.

How to fix login failed with error cannot open user default db...

 Fix Cannot open user default database. Login failed. Login failed for user SQL Server Error..

In this article we will take a look at the steps which you need to follow when you receive “Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)“ SQL Server Error message.

Error Message

Cannot open user default database. Login failed. 
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064).

Cannot open user default database Login failed Login failed for user

You may end up receiving this error when a Windows or SQL Server Login is not able to connect to the default database which is assigned to the user.

If you double click the user (Expand Security -> Logins) you would end up seeing no database being assigned to Default Database as shown in the snippet below.

SQL Server Login Properties Default Database Name is Missing

How to Fix “Cannot open user default database. Login failed. Login failed for user Error Message”

In the Connection Properties specify the database name as TempDB for Connect to Database and click the Connect button to connect to the SQL Server Instance.

Once you are connected to the SQL Server Instance execute the below TSQL to assign the login a new default database.

Use master
GO

ALTER LOGIN [MonitorDB] WITH DEFAULT_DATABASE = TempDB
GO

Conclusion

As a Best Practice it is always recommended to assign the default database to a user as TempDB. This database is recreated every time SQL Server is restarted and this way you can avoid getting these errors.

How to Identify CPU Bottlenecks...

 How to Identify CPU Bottlenecks in SQL Server Using Performance Counters..

There are instance when you are facing CPU bottlenecks on your SQL Server Instance. In this article we will take look at How to Identify CPU Bottlenecks in SQL Server Using Performance Counters.The most common reason for a CPU bottleneck will be insufficient hardware. However, as a database administrator one can make certain configuration changes and tune the SQL Server queries to improve the performance. To begin with Performance Monitor is a great tool which can be used to quickly identify CPU bottlenecks on your SQL Server Instance. Monitoring the below counters can help you in Performance Tuning of SQL Server.

Most important CPU counters to be looked at are: –

Processor: % Processor Time

This counter gives you information with respect to how much time the processor has actually spent working on productive threads and how often it was busy serving such requests. If you find the value of this counter hovering above 80% per CPU then this gives a clear indication that the server is clearly facing CPU bottlenecks.

System: Processor Queue Length

This counter gives you information with respect to the number of threads in the processor queue. This counter basically displays the ready threads; it doesn’t count the threads which are running. Even if you have multiple processors on the system this counter will display a cumulative value. Hence if you have 5 processors on a system and the value for the system turns out to be 60 then it mean on an average the Processor Queue Length for a single processor is 12. Ideally the average processor queue length should be less than 10.

System: % Total Processor Time

This counter gives you information with respect to the total performance of the entire system i.e. it is an average of all the processors together on the system.

SQL Server: SQL Statistics: Batch Requests/sec

This counter gives you information with respect to the number for TSQL command batches received per second.

SQL Server: SQL Statistics: SQL Compilations/sec

This counter gives you information with respect to number of SQL Compilations happening per second.

SQL Server: SQL Statistics: SQL Re-Compilations/sec

This counter gives you information with respect to number of TSQL statement recompiles per second. This counter has the count of number of times statement recompile is triggered by the database engine. This counter value should be very low. Ideally SQL Statistics: SQL Re-Compilations/sec value should not be more than one percent of SQL Statistics: Batch Requests/sec.

For example if your system has 1000 SQL Statistics: Batch Requests/sec then the value of SQL Statistics: SQL Re-Compilations/sec shouldn’t exceed 10 (> 1%) after the SQL Server is stable and its up and running for more than 24 hours.

SQL Server: Cursor Manager By Type: Cursor Requests/Sec

This counter gives you information with respect to number of SQL cursor requests received by server. If you end up seeing 100 of cursor requests per sec then it’s a worrying factor. This means that the code needs to be rewritten to avoid as much cursor usage as possible to improve the overall performance.

Clustering Interview Questions and FAQ's....

1) What is Windows Cluster?

Clustering can be best described as a technology that automatically allows one physical server to take over the tasks and responsibilities of another physical server that has failed. The obvious goal behind this, given that all computer hardware and software will eventually fail, is to ensure that users running mission-critical applications will have very less downtime when such a failure occurs.

2) What is a Cluster Node?

A cluster node is a server within the cluster, and it has Windows Server and the Cluster service installed.

3) What is Cluster Service?

The cluster service manages all the activity that is specific to the cluster. One instance of the cluster service runs on each node in the cluster. The cluster service does the following

·         Manages Cluster Objects and Configurations

·         Manages the local restart policy

·         Coordinates with other instances of the cluster service in the cluster

·         Handles event notification

·         Facilitates communication among other software components

·         Performs failover operations

4) What is called a Resource in Windows cluster?

A resource is a physical or logical entity, which has below properties:

·         Can be brought online and taken offline

·         Can be managed in the failover cluster

·         Can be owned by only one node at a time

To manage resources, Cluster service communicates with a resource DLL through Resource Monitor.

5) What are the different states of a Resource in Windows cluster?

All resources can have following states

·         Offline

·         Offline_Pending

·         Online

·         Online_Pending

·         Failed

6) What is a Cluster Group?

Conceptually, a cluster group is a collection of logically grouped cluster resources. It may contain cluster-aware application services, such as SQL Server Group, File Server.

7) What is Public Network?

A public network (also called as External network) provides client systems with access to cluster application services and IP address resources are created on networks that provide clients access to cluster services.

8) What is Private Network?

A private network (sometimes called as interconnect or heartbeat connect) is a network that is setup between the nodes of the cluster and it carries only internal cluster communications.

9) What is Heartbeat in Windows cluster?

Heartbeats are messages that Cluster Service regularly sends between the instances of Cluster Service that are on each node to manage the cluster.

10) What Failover and Failback terms mean in Windows Cluster?

Failover: Failover is the process of moving a group of resources from one node to another in the case of a failure. For example, in a cluster where Microsoft SQL Server is running on node A and node A fails, SQL Server automatically fails over to node B of the cluster.

Failback: Failback is the process of returning a resource or group of resources to the node on which it was running before it failed over. For example, when node A comes back online, SQL Server can fail back from node B to node A.

11) What is Quorum Drive?

This is a logical drive assigned on the shared disk array specifically for Windows Clustering. Clustering services write constantly on this drive about the state of the cluster. Corruption or failure of this drive can fail the entire cluster setup. It also acts as a voter in the fail over process in case of odd number of nodes.

12) Different types of Quorum Models supported in windows Server 2008?

·         Node Majority – Used when Odd number of nodes are in cluster.

·         Node and Disk Majority – Even number of nodes (but not a multi-site cluster)

·         Node and File Share Majority – Even number of nodes, multi-site cluster

·         No Majority: Disk Only – This is the traditional MSCS quorum model, where a shared quorum disk must be online and nodes must be able to communicate with that disk

13) What is Node Majority model?

This type of quorum is optimal for clusters having an odd number of nodes. In this configuration, only the nodes have votes. The shared storage does not have a vote. A majority of votes are needed to operate the cluster.

14) What is Node and Disk Majority model?

Nodes and a shared disk get votes. This configuration allows a loss of half the nodes, providing the disk witness is available, or over half the nodes are available without the disk witness being available. This is recommended for even number of nodes in the cluster.

15) What is Node and File Share Majority model?

This type of quorum is optimal for clusters having an even number of nodes when a shared witness disk is not an option. Other characteristics include the following:

·         each node and the file share “witness” gets a vote

·         it does not require a shared disk to reach a quorum

·         the file share has no special requirements

·         the file share should be located at a third site, making this type of quorum the best solution for geographically dispersed clusters

16) What is No Majority: Disk only mode?

The disk witness must be available to have quorum, but the number of available nodes doesn’t matter. If you have a four-node cluster and only one node is available, but the disk witness is available, you have quorum. If the disk witness isn’t available, then even if all four nodes are available you can’t have quorum.

17) What I Split Brain situation in Cluster?

Cluster nodes communicate with each other over the network (port 3343). When nodes are unable to communicate with each other, they all assume the resources of the other (unreachable) nodes have to be brought online. Because the same resource will be brought online on multiple nodes at the same time, data corruption may occur. These results in a situation called “Split Brain.”

18) How Spilt Brain situation is resolved?

To prevent Split Brains we need to bring the cluster resource online on a single node (rather than multiple nodes).  Each of the online node cast vote for majority and the resources come online on that group which has more votes or has majority. In case of Even number of nodes Quorum also acts as a voter to eliminate split brain situation.

19) What are the Hardware requirements for Windows Server Cluster?

Windows Cluster

·         Two windows servers (nodes)

·         At least one shared disk array that supports, either SCSI or fibre channel.

·         Each server must have a SCSI or fiber channel adapter to talk to the shared disk array. The shared disk array cannot use the SCSI controller used by the local hard disk or CD-ROM.

·         Each server must have two PCI network cards (one for the private connection and one for the public connection)

·         1 IP Address for Windows virtual cluster name

20) What are the Hardware requirements for SQL Server Cluster?

·         1 IP Address for MSDTC service

·         1 IP Address for SQL Server Active\Passive Instance or 2 IP address for SQL Server Active\Active Instance

·         1 IP Address for SQL Server Analysis services (if needed)

21) How many IP Addresses we require for setting up Active\Passive SQL Server cluster?

·         2 Windows nodes – Public

·         2 Private IP Addresses – Private

·         1 Windows Virtual Cluster Name

·         1 MSDTC

·         1 SQL Server Virtual Network Name

22) How many IP Addresses we require for setting up Active\Active SQL Server cluster with Analysis services?

·         2 Windows nodes – Public

·         2 Private IP Addresses – Private

·         1 Windows Virtual Cluster Name

·         1 MSDTC

·         1 SQL Server Virtual Network Name

·         1 SQL Server Analysis Services

23) How do you open a Cluster Administrator?

Start Menu > Run >  Cluadmin.msc

24) What is SQL Server Network Name (Virtual Name)?

This is the SQL Server Instance name that all client applications will use to connect to the SQL Server.

25) Different types of SQL Server Cluster?

·         Active\Passive

·         Active\Active

26) What is the difference between Active\Passive and Active\Active cluster?

An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.

An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.

27) Difference between SQLSERVER 2005 and SQLSERVER 2008 Cluster Installation?

In sql2005 we have the option of installing SQL in remaining nodes from the primary node, But in sql2008 we need to go separately (Login to the both nodes) for installing SQL cluster

28) Can we change the Quorum settings after installing the windows cluster?

Yes, we can change the Quorum setting after the Windows Cluster installation.

29) Is it mandatory to configure MSDTC in Windows 2008 cluster before installing SQL Server cluster?

No it’s not mandatory to configure MSDTC service to install SQL Server in Windows 2008 cluster. Installation will give you a warning but will not stop the installation.

30) What are the Benefits of SQL Server Cluster?

·         Reduces downtime to a bare minimum.

·         Permits an automatic response to a failed server or software. No human intervention is required.

·         It allows you to perform upgrades without forcing users off the system for extended periods of time.

·         It allows you to reduce downtime due to routine server, network, or database maintenance.

·         Clustering doesn’t require any servers to be renamed. So when failover occurs, it is relatively transparent to end-users.

·         Failing back is quick, and can be done whenever the primary is fixed and put back on-line.

31) What are the Drawbacks of SQL Server Cluster?

·         More expensive than other failover alternatives, such as log shipping or stand-by servers.

·         Requires more set up time than other alternatives.

·         Requires more on-going maintenance than other alternatives.

·         Requires more experienced DBAs and network administrators.

1. What new functionality does failover clustering provide in Windows Server 2008?

New validation feature. With this feature, you can check that your system, storage, and network configuration is suitable for a cluster.

Support for GUID partition table (GPT) disks in cluster storage. GPT disks can have partitions larger than two terabytes and have built-in redundancy in the way partition information is stored, unlike master boot record (MBR) disks.

2. What happens to a running Cluster if the quorum disk fails in Windows Server 2008 Cluster?

Cluster continues to work but failover will not happen in case of any other failure in the active node.

3. What happens to a running Cluster if the quorum disk fails in Windows Server 2003 Cluster?

In Windows Server 2003, the Quorum disk resource is required for the Cluster to function. In your example, if the Quorum disk suddenly became unavailable to the cluster then both nodes would immediately fail and not be able to restart the cluster service.

4. What are Virtual Servers?

Groups that contain an IP address resource and a network name resource (along with other resources) are published to clients on the network under a unique server name. Because these groups appear as individual servers to clients, they are called virtual servers. Users access applications or services on a virtual server the same way they access applications or services on a physical server. They do not need to know that they are connecting to a cluster and have no knowledge of which node they are connected to.

5. How do you bring the SQL Server down?

In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.

6. How will you add a disk to the SQL Group cluster?

After adding the shared disk in the storage, we can add disk to the respective SQL Server Group.

7. What is the maximum number of nodes in an MNS cluster in Windows Server 2008, Enterprise x64 Edition?

Maximum 16.

8. What does a failover cluster do in Windows Server 2008?

A failover cluster is a group of independent computers that work together to increase the availability of applications and services. The clustered servers (called nodes) are connected by physical cables and by software. If one of the cluster nodes fails, another node begins to provide service (a process known as failover). Users experience a minimum of disruptions in service.

9. What are Services and Application folder represent?

Services and applications are managed as single units for configuration and recovery purposes. If a resource depends on another resource, both resources must be a member of the same service or application. For example, in a file share resource, the service or application containing the file share must also contain the disk resource and network resources (such as the IP address and NetBIOS name) to which clients connect to access the share. All resources within a service or application must be online on the same node in the cluster.

10. What kinds of permissions are required in the active directory to setup the SQL Server cluster objects?

Service account needs create object permissions in the Active Directory.

11. Why do we keep SQL Services in manual mode on each of the instance?

SQL Services should always be in manual mode in case of cluster because these are managed by the Cluster service and it’s taken online on its respective owner node based on the failover.

12. What is Distributed lock management?

Distributed lock management (DLM): Distributed lock management (DLM) enables two servers to access the same physical disk at the same time without corrupting the data. If a device is updating a particular file or piece of data, the device gets locked so that another controller can’t seize ownership and overwrite the data. NT does not currently support DLM, so disks are dedicated to one node or the other.

13. What is “Look Alive”?

LooksAlive: Verifies that the SQL Server service runs on the online node every 5 seconds by default.

14. What is “IS Alive”?

IsAlive: Verifies that SQL Server accepts connections by executing sp_server_diagnostics. This health detection logic determines if a node is down and the passive node then takes over the production workload.

15. What are SQL Server Cluster aware services?

·         SQL Server Service

·         SQL Server Agent Service

·         SQL Server Analysis Service

16. What are SQL Server Cluster unaware services?

·         SQL Server Reporting Service

·         SQL Server Integration Service

17. What are Validation tests in Windows Cluster?

Validation test is a mechanism of verifying that all the components which are participating in the Windows cluster are fine and failover is happening between the nodes.

18. What are the basics tests done by the validation tests in Windows Cluster?

·         Cluster Configuration tests: Validate important cluster configuration settings.

·         Inventory tests: Provide an inventory of the hardware, software, and settings (such as network settings) on the servers, and information about the storage.

·         Network tests: Validate that networks are set up correctly for clustering.

·         Storage tests: Validate that the storage on which the failover cluster depends is behaving correctly and supports the required functions of the cluster.

·         System Configuration tests: Validate that the system software and configuration settings are compatible across servers.

19. Where the results of validation tests are stored?

These reports are automatically stored for you in C:\Windows\Cluster\Reports as MHTML files.

20. Is SQL Server a Load balancing solution or not?

No, it’s not a Load balancing solution.

21. Will there be any downtime in Active\Active cluster in case of any failover?

Yes, definitely there will be downtime when SQL Server failover from one node to another.

22 Can we use other SQL Server cluster Nodes for reporting purpose as we can do in Logshipping and Database mirroring?

No it’s not possible in SQL Server Cluster feature.

23. Can we place out Non Critical SQL Server User Databases on a Clustered Instance on Disks that are not clustered to Save Money?

No, it’s not possible. SQL Server 2012 and all previous versions of SQL Server require databases be created on clustered resources. Internal drives or drives which are not part of the cluster group cannot hold user databases.

24. Can we configure Tempdb database on a local drive?

With the introduction of SQL Server 2012 Microsoft officially supports local disk TempDB in SQL Server cluster configurations.

25. Can we configure Windows cluster between two servers which are having different hardware and software configurations?

No it is not possible.

26. What is SMB share?

SMB stands for Server Message Block file server which can be used as a storage option starting SQL Server 2012 to store system databases (Master, Model, MSDB, and TempDB), and Database Engine user databases .

27. How can we check the current node/host name where SQL Server is running?

Select serverproperty(‘ComputerNamePhysicalNetBIOS’)

28. How to view the Cluster Nodes using command line?

C:\Windows\System32>cluster node

--or

C:\Windows\System32>cluster node /status

29. How to view the status for all cluster resource groups.

C:\Windows\System32>cluster group

--or

C:\Windows\System32>cluster group /status

30. How to get a listing of all available cluster resources?

C:\Windows\System32>cluster resource

--or

C:\Windows\System32>cluster resource /status

31. How to failover a service from one node to another?

C:\Windows\System32>cluster group "groupname" /move:nodeName