ALWAYS ON: What is the best practice to mange users in AG which supports automatic failover
- Details
- Written by Ranjeet
I was sitting with few DBAs and were discussing for designing some best solution for high availability and disaster recovery for our database on SQL Server level. The first thing I said was to configure always on availability group on WSFC cluster. It is the best mechanism to provide both high availability which supports automatic failover and disaster recovery too which same features. But I got a counter question asking that what about logins and users who connecting to the database. Answer is very straight in SQL Server 2012 and 2014. SQL Server is giving a features of contained database which take care of this. Anyway on the spot I missed to say the same thing. But later on explained the same solution.
Earlier I was thinking contained database features help to just migrate/move database from one instance to other instance. But you know it is being used in always on availability group very well. I will have soon a separate article where will discuss on contained database.
T-SQL: REPLICATE Built-In string Function
- Details
- Written by Ranjeet
REPLICATE built-in string function is very common and useful function. It accept two parameters. Let me just point out its usage. First parameter should be string and second one will be number.
It replicate same string specified number of times passed as second parameter. It very useful to insert dummy large data into a table. You can replicate same value multiple time to reach your target size.
Please see below example queries:
Query 1:
select REPLICATE('SQLSERVER ', 5) as ReplicateFunctionOutput
T-SQL: What is SET NOCOUNT and SET ROWCOUNT
- Details
- Written by Ranjeet
Microsoft SQL Server provides several SET command which manage how the information will be handled/shown in current session. Although there are global setting of these commands. You can overwrite those settings by putting SET command in T-SQL batch.
I personally as DBA use SET command very frequently when does some query/procedure related performance tuning for getting more information on STATISTICS and SHOWPLAN. Its very useful to troubleshoot performance on query level
Being a developer, NOCOUNT and ROWCOUNT is used frequently inside the T-SQL batch, stored procedures and functions.
MEMORY: What is Torn Page Protection and Checksum Protection
- Details
- Written by Ranjeet
Torn page protection was introduced in SQL Server 2000 version whereas checksum was introduced in SQL Server 2005 version. Both are serving different purposes by using different method.
Torn page protection is a page error/corruption detection methodology. It is the way to detect any page corruption happened or not due to any power failure. There are possibility of half written page into the disk when power failure happens. To detect any page corruption in this case SQL Server keep 2 bit signature at the end of every 512 byte sector in page. It is also very less resource consuming process.
Checksum is calculated for the data in each page that is written, and stored in the
TABLE: Get Table and Index Used Space Used
- Details
- Written by Ranjeet
While doing space analysis on object level. We need to query some system tables which can give us sufficient information. I am going to tell you three way to get these details.
Below system tables can fulfill above request:
Sysobjects
Sysindexes
sp_spaceused
Way 1: Below query gives required details
select so.name as TableName, sum(case when si.indid in(0,1) then si.reserved end)*8/1024 as DataPageSize,
sum(case when si.indid not in(0,1,255) then si.reserved end)*8/1024 as IndexPageSize,
sum(case when si.indid =255 then si.reserved end)*8/1024 as BlobDataPageSize
from sysobjects as so
join sysindexes si on so.id=si.id
where so.type= 'U'
group by so.name
order by DataPageSize desc
INDEX: Get Index metadata from SQL Server
- Details
- Written by Ranjeet
Being a good DBA, it is very much important to understand the current running system. To do so you must have rich understanding of system tables, Catalog Views, DMVs, and system functions. I am listing Index related system catalogs and functions which help us to get idea about what happening with indexes inside the SQL Server:
Dynamic Views:
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns
SSMS: SQL Server template default Location
- Details
- Written by Ranjeet
It is a good idea to keep your all the scripts in template folder where Microsoft SQL Server use to keep default templates.
I use to do the same. Today i thought to take a backup of all these scripts which i had created in last few months to solve my problems. You know these scripts are very important. You will need to research again if you need these again some time later on. So, I tried to get the path but couldn't find it.
After some R&D finally i got the path as below:
T-SQL: How to get Date and time part separately from datetime column in sql server 2008
- Details
- Written by ranjeet
Convert built-In conversion function has very rich number of code to convert datatime into different format. We have different option to get date part and time part separately. You can see my older blogs which giving multiple option valid through SQL Server 2005 to 2014. But here I am showing one more option which is only valid in 2008 and later version.
Query to get date part from datetime column:
Query 1:
select convert(date,getdate()) as DatePartFromDatetime
Output:
T-SQL: RIGHT Built-In String Function
- Details
- Written by ranjeet
RIGHT is a string function which use to cut part of string from right side. This function is applied through all the available SQL server version. It requires two parameters. First parameter is string/expression and second parameter is numeric. It returns string only.
Please see below example queries:
T-SQL: LEFT Built-In string Function
- Details
- Written by ranjeet
LEFT is a string function which use to cut part of string from left side. This function is applied through all the available SQL server version. It requires two parameters. First parameter is string/expression and second parameter is numeric. It returns string only.
Please see below example queries:
Query 1:
SELECT LEFT('SQLSERVERBASE', 3) as LeftOutput
Output:
Query 2:
SELECT LEFT(name, 3) as LeftOutput FROM TESTP(NOLOCK)
Output:
