TABLE: Query to get no of rows and space used by table in sql server
- Details
- Written by Ranjeet
I have already written two articles to get number of rows and space used by table. Here I am putting them together which will give you both.
select so.name as TableName, si.rows as NoOfRows,
(SUM(a.total_pages)*8)/1000 AS TotalSpace_MB,
(SUM(a.used_pages)*8)/1000 AS UsedSpace_MB,
((SUM(a.total_pages)-SUM(a.used_pages))*8)/1000 AS UnusedSpace_MB
from sys.tables as so
join sys.sysindexes si on so.object_id=si.id and si.indid in(0,1)
join sys.dm_db_partition_stats dp on so.object_id = dp.object_id
join sys.allocation_units a ON dp.partition_id = a.container_id
where so.type= 'U'
group by so.name, si.rows
order by NoOfRows desc
WIN CLUSTER: Unable to save property changes for File Share Witness in sql server
- Details
- Written by Ranjeet
I do have 3 nodes geo-cluster. I was trying to setup quorum. Although since I have 3 node cluster and its odd number of votes, we should be good as of quorum concerned. But since one node is in other data center to I decided to remove that node from voting and configure file share as witness on primary data center. To understand quorum design you can refer my other blog. While configuring quorum, I got below error.
Error:
An error was encountered while modifying the quorum settings.Your cluster quorum settings have not been changed. There was an error configuring the file share witness '\\xxxxxx\sharefolder\'. Unable to save property changes for 'File Share Witness'. Access is denied
It’s clearly showing I don’t have permission to create file on that file share. It’s little tricky. I checked everything and couldn’t see any reason of getting this error.
Solution: This error doesn’t asking for having your account permission to create and save configuration file. But it means the window cluster virtual object doesn’t have permission. So, I simply added cluster object in security and gave full permission on the share and problem resolved.
SQL: query to check and create backup folder structure in sql server
- Details
- Written by Ranjeet
Creating backup is very common task in all DBAs life. One of the aspect while creating backup is to check either backup folder does exist or not. Or better a DBA can put a step before taking backup is to whether folder exist or not. If not then create.
My article does same thing. It check the folder structure for all non-system database and creates if doesn’t exist.
-->> Declare Variables
SQL: How to create directory in file system using SQL query in SQL server
- Details
- Written by Ranjeet
Whenever we need to create any directory to keep backup or other files. We simply go to the drive and create it. But when we need to create in bunch and also in same directory structure for multiple server. We obviously look for some script, an easy way to do. Below is the Sample example.
exec xp_cmdshell 'mkdir I:\BACKUP\DBNAME\'
SQL SERVER: How to enable xp_cmdshell in SQL server
- Details
- Written by Ranjeet
It’s very common to use enable/disable xp_cmdshell command in SQL Server which happen sometimes very often. I know it’s very common and you can get it very easy from anywhere. But still I wanted to keep it in my book so that it can be easily referenced in future.
Below is the script to enable xp_cmdshell command in query window.
-- Enable to allow changes
exec sp_configure'show advanced options', 1;
GO
-- Update currently configred values
reconfigure;
GO
-- Enable xp_cmdshell feature
exec sp_configure'xp_cmdshell', 1;
GO
-- Update currently configred values
reconfigure;
GO
OUTPUT
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
WIN CLUSTER: Quorum configuration Pictorial representation of possible failure and failover in Node and fileshare majority scenario
- Details
- Written by Ranjeet
We went through 3 node multisite cluster architecture and also about possible quorum configuration and its pros and cons. Now it’s time to see the possible node failure and then its failover options scenarios. In previous article, we listed 10 possible failure scenario in both node majority and Node and File share majority as below. Let walk through all the failure and failover scenario when using Node Majority and file share quorum model.
Note: Quorum model is Node plus file share majority. In this scenario Node C has been excluded from voting and file share has been created on primary data center side.
- Healthy Cluster
- Node B is down: When node B is down. Node A which is currently running the services will keep running without any failover. As Node A still has 2 votes which is more than n/2+1.
WIN CLUSTER: Quorum configuration Pictorial representation of possible failure and failover in Node majority scenario
- Details
- Written by Ranjeet
We went through 3 node multisite cluster architecture and also about possible quorum configuration and its pros and cons. Now it’s time to see the possible node failure and then its failover options scenarios. In previous article, we listed 10 possible failure scenario in both node majority and Node and File share majority as below. Let walk through all the failure and failover scenario when using Node Majority quorum model.
- Healthy Cluster
- Node B is down: When node B is down. Node A which is currently running the services will keep running without any failover. As Node A still has 2 votes which is more than n/2+1.
WIN CLUSTER: Quorum configuration solution for 3 nodes multi-site HA and DR in SQL server
- Details
- Written by Ranjeet
After successful design of 3 node multisite cluster. Now it’s time to look/design on quorum configuration model. Let go through each possible quorum configuration.
Possible quorum configuration setting for 3 node multisite cluster
Let me give an overview of cluster and its nodes. We have two data centers. One primary data center and other is secondary data center. Primary data center has two nodes and secondary data center has one node. So total node is 3 which is odd in number. This is good and we can go with node majority. We can still use file share to give better solution. Let see both the solution in details with possible failure diagram.
- Node Majority
- Node + file share Majority
WIN CLUSTER: Quorum configuration model type in window cluster WSFC
- Details
- Written by Ranjeet
We knew what is quorum and how does it work. Now we will explore about type of different quorum model.
What are different mode/types of quorum?
After understanding what the quorum is. Now it’s time to understand types of quorum. Here the question is why different types. Let me put one scenario here. Suppose you have two node cluster. And due to any reason the communication between both the nodes get lost. Although both the nodes are active but can’t talk to each other. Both the nodes is having now 1 votes. And to take ownership nodes should have (2/2)+1 vote. Which actually doesn’t have. These two nodes can be in two different data center too. This scenario is called brain split/partitioned cluster. To avoid this kind of scenario when there are even number of node in cluster. Microsoft has introduced to include to use either disk or file share which will participate in voting and can avoid these kind of scenario. So, on the basis of above scenario we can now understand below types:
- Node Majority
- Node + Disk Majority
- Node + File Share Majority
- Disk Majority or No Majority
WIN CLUSTER: What is quorum in context of window cluster in SQL server
- Details
- Written by Ranjeet
After successful design of 3 node multisite cluster. Now it’s time to look/design on quorum configuration model.
When we talk about quorum, most of the professional are very much of not aware or miss conceptually understood it as you can’t see it on window cluster manager. In this article I will try to answer the very common question of the database professional which usually been asked or try to get answer by exploring it. The questions like, what is quorum, how to configure quorum, where quorum reside, what if there is no quorum, what if quorum node/drive/share/server is down, is quorum physically reside on any server, is quorum is a configuration file, if quorum is a configuration file where does it reside, who configure quorum, is this DBA or system engineer, what different kind of quorum is available, in which cluster scenario which quorum model should use, how quorum play role in failover, is the quorum who actually decide where the service is run, or quorum is responsible to track cluster health. So, there are number of question come in mind when quorum come in mind. Let me answer first what the quorum is? What does mean by presence of quorum and absence of quorum?
What does mean by quorum?
More Articles...
- RDBMS: List of keys in RDBMS in SQL server
- ALWAYS ON: automatic failover risk vs benefit in disaster recovery in SQL server
- ALWAYS ON: 3 nodes multi-site HA and DR architectural solution in SQL server
- TABLE: how to get space from deleted rows in sql server
- SQL SERVER: SQL Server post-installation Configuration settings best practice checklist