Now days, as it’s very common and normal trends, The DB size as 100~150…. n GBs are being considered the normal, and even many databases are crossing the terabyte range. In general practice commonly a DBA or sometime Developer starts backups in one process that take a long time and Database is continuing to grow, this back time will increase accordingly, to reduce the backup time, it’s always suggested take backup in Disk instead of tape in off hours. It’s my personal opinion always take backup to San Disk and then archive it to Tape. Disk capacity is matter because it’s directly proportional to costing/expense and to reduce backup size we can use compression. As we know taking a Full backup is very intensive I/O process as every data page in the database must be read and then written to another file. It’s my personal observation, having multiple physical disks, we can achieve higher I/O rates and complete the backup process more quickly with in addition to writing to multiple files, it is also helpful to write to different physical disks to alleviate the I/O bottleneck.

Here are steps, How to take Backup on multiple disks / multiple files using SSMS.

Step 1) Right Click on Database à Task à Back Up

It will shows screen as

Step 2) Click on Add from Destination section and set there multiple physical disk with multiple files by clicking on ADD button again and again.

And finally suppose we have set 2 disk with 2 files it will show like as below

Step 3) Click on OK, Backup will start with 2 physical disks.

The sample script for this process will be as below,

BACKUP DATABASE [VirendraTest] TO DISK N’C:\VirendraTestOnCdrive.bak’,
DISK = N’E:\VirendraTestOnEdrive.bak’ 
WITH NOFORMAT, NOINIT,
NAME
N’VirendraTest-Full Database Backup’SKIP, NOREWIND, NOUNLOADSTATS = 1
GO

 

Please suggest, your finding.

Hi Guys, Now days Payment Card Industry Data Security Standards (PCI DSS) is a preventative standard intended to reduce the risk of payment card fraud and information theft. As per my views after SQL server installation on Production Box, I hope below settings should be set using SP_CONFIGURE as,

  • The ‘Ad Hoc Distributed Queries’ Server Configuration Option should be set to ‘0’
  • The ‘CLR Enabled’ Server Configuration Option should be set to ‘0’
  • The ‘Cross DB Ownership Chaining’ Server Configuration Option should be set to ‘0’
  • The ‘Database Mail XPs’ Server Configuration Option should be set to ‘0’.
  • The ‘Ole Automation Procedures’ Server Configuration Option should be set to ‘0’
  • The ‘Remote Access’ Server Configuration Option should be set to ‘0’
  • The ‘Remote Admin Connections’ Server Configuration Option should be set to ‘0’.
  • The ‘Scan For Startup Procs’ Server Configuration Option should be set to ‘0’.
  • The ‘SQL Mail XPs’ Server Configuration Option should be set to ‘0’
  • The ‘Trustworthy’ Database Property should be set to Off
  • Unnecessary SQL Server Protocols should be disabled.
  • SQL Server should be configured to use non-standard ports
  • The ‘Hide Instance’ option should be set to ‘Yes’ for production SQL Server instances.
  • Non-clustered SQL Server instances within production environments should be

    designated as hidden to prevent advertisement by the SQL Server Browser service.

  • The ‘sa’ login account should be disabled or renamed.

Extended Stored Procedures

The following extended stored procedures should not be used by any application or maintenance script.

  • Execute on ‘xp_availablemedia’ to PUBLIC should be revoked.
  • The ‘xp_cmdshell’ option should be set to disabled
  • Execute on ‘xp_dirtree’ to PUBLIC should be revoked.
  • Execute on ‘xp_enumgroups’ to PUBLIC should be revoked.
  • Execute on ‘xp_fixeddrives’ to PUBLIC should be revoked.
  • Execute on ‘xp_servicecontrol’ to PUBLIC should be revoked.
  • Execute on ‘xp_subdirs’ set to PUBLIC should be revoked.
  • Execute on ‘xp_regaddmultistring’ to PUBLIC should be revoked.
  • Execute on ‘xp_regdeletekey’ to PUBLIC should be revoked.
  • Execute on ‘xp_regdeletevalue’ to PUBLIC should be revoked
  • Execute on ‘xp_regenumvalues’ to PUBLIC should be revoked.
  • Execute on ‘xp_regremovemultistring’ to PUBLIC should be revoked
  • Execute on ‘xp_regwrite’ to PUBLIC should be revoked
  • Execute on ‘xp_regread’ to PUBLIC should be revoked.

Authentication and Authorization

  • The ‘Server Authentication’ Property should be set to Windows Authentication mode
  • CONNECT permissions on the ‘guest user’ should be revoked within all SQL Server databases excluding the master, msdb and tempdb
  • Orphaned Users should be dropped from SQL Server database. A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance and is referred to as orphaned and should be removed.

Password Policies

  • The ‘MUST_CHANGE’ Option should be set to ‘ON’ for all SQL authenticated logins
  • The ‘CHECK_EXPIRATION’ Option should be set to ‘ON’ for all SQL authenticated logins within the ‘Sysadmin’ Role
  • The ‘CHECK_POLICY’ Option should be set to ‘ON’ for all SQL authenticated logins

  • Auditing and Logging
  • The ‘Maximum number of error log files’ setting should be set to greater than or equal to 12.
  • The ‘Default Trace Enabled’ Server Configuration option should be set to 1.
  • ‘Login Auditing’ to both failed and successful logins

 

Thanks for Reading, Keep smiling, keep learning

As we use PERFMON – Performance Monitor to identify real time happening on SQL Server, Here using a DMV “sys.dm_os_performance_counters” may be used to calculate number of transactions on SQL Server as

DECLARE @Counter bigint
SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name ‘Transactions/sec’
AND object_name=‘SQLServer:Databases’
AND instance_name =‘VirendraTest’   — Your DB Name

— Wait for 1 second

WAITFOR DELAY ’00:00:01′

SELECT cntr_value @Counter FROM sys.dm_os_performance_counters
WHERE counter_name =‘Transactions/sec’
AND object_name =‘SQLServer:Databases’
AND instance_name =‘VirendraTest’— — Your DB Name


As a Developer / DBA, it’s a very frequent day to day routine task to delete unwanted objects – commonly Tables from SQL Server Instance. There may be different people have their own perception as per their system/environment to find unwanted objects, but here I am just discussing about empty table which belong to 0 (zero) records/rows and to list out those table here we can use any of below queries.

SELECT OBJECT_NAME(OBJECT_IDAS TableName , SUM(row_countAS [No. of Rows]
FROM sys.dm_db_partition_stats WHERE index_id in(0,1)
GROUP BY 
OBJECT_ID HAVING SUM(row_count)=0
ORDER 
BY TableName

———————————- OR ———————————————–

SELECT Obj.name as TableName, ps.row_count as [No. of Rows] FROM sys.indexes AS sIdx
INNER 
JOIN sys.objects AS Obj ON sIdx.OBJECT_ID = Obj.OBJECT_ID
INNER JOIN 
sys.dm_db_partition_stats AS ps ON sIdx.OBJECT_ID = ps.OBJECT_ID
WHERE sIdx.index_id < 2 and ps.row_count=0 AND sIdx.index_id = ps.index_id
ORDER BY TableName

 

 

It’s a very vast topic and people became very interested to know who has been deleted the database from their working environment , Here I am using an undocumented process to get the information from box very quickly and hope there will be not any incedent to using this command as its only select statement.

SELECT Operation, SUSER_SNAME([Transaction SID]) As UserName,  [Transaction Name][Begin Time][SPID]Description
FROM fn_dblog (NULL, NULL)  WHERE [Transaction Name] = ‘dbdestroy’

will return result as and Username here for further analysis.

I have seen many Developer/DBA uses user defined function or stored procedure to split a comma separated ( or any delimiter to separate values like |,;-.& or any character/special character) column into rows using various coding logic like while/for loop or using cursor etc.

Let see an example, below is a sample data,

And requirement is to split the every SkillSet column values in a single row according EMPID and EMPNAME like

To get above desired result without using any Function, Stored Procedure or any loop, simple flat SQL select statement will be like this,

SELECT EmpID,EmpName,Split.Data.value(‘.’, ‘VARCHAR(100)’AS String
FROM (Select EmpID,EmpName,CAST
(‘<M>’REPLACE(SkillSet,‘,’, ‘</M><M>’)+ ‘</M>’ AS XML) AS String FROM VirendraTestAS Data CROSS APPLY String.nodes (‘/M’AS Split(Data)

 

If you want to do RnD, below is the sample code for same.

–Step 1 ) Create a Sample table named as VirendraTest

CreateTable VirendraTest(EmpID Char(5),EmpName Varchar(30),SkillSet nvarchar(100))
go

–Step 2 ) Insert few sample data in above created table i.e. VirendraTest
Insert into VirendraTest
values(‘E001′,‘Virendra Yaduvanshi’,‘MS-SQL Server,C,C++,C#,.Net’),
(‘E002′,‘Manish Raj’,‘Account,Cash Management,Admin’)
      (‘E003′,‘Sanjay Singh’,‘.Net,VB6,Oracle’),
      (‘E004′,‘Shajia Khan’,‘Coldfusion,Delphi’),
      (‘E005′,‘Vikash Rai’,‘Php,Mongo,Cloud’),
      (‘E006′,‘Sandeep Arora’,‘Network,C,C++’),
      (‘E007′,‘Manpreet Kaur’,‘Java,Android,Mobile Devlopment’)
–Step 3 ) Check Table contents

Select from VirendraTest

–Step 4 ) Split comma seperated column SKILLSET in rows using below query

SELECT EmpID,EmpName,Split.Data.value(‘.’, ‘VARCHAR(100)’) AS String
FROM (Select EmpID,EmpName,CAST
(‘<M>’REPLACE(SkillSet,‘,’, ‘</M><M>’)+ ‘</M>’ AS XML) AS String FROM VirendraTestAS Data CROSS APPLY String.nodes (‘/M’AS Split(Data)

 

 

Its very common when a Developer/DBA wanted to see sample of few records, commonly they use SELECT TOP N records from a table being ordered by a column,  the query look like as

SELECT TOP N [COLUMNNAME] FROM TABLENAME ORDER BY [COLUMNNAME]

As we know above statement is that if the table has multiple records having the same value as that of the selected list then all those records will not be selected. It will select only one record. But if we want to select all the rows, with same value as the one selected we have to include WITH TIES option in the query. So the query would be

SELECT TOP N [COLUMNNAME] WITH TIES FROM TABLENAME ORDER BY [COLUMNNAME]
For demonstration purpose, below are the step by step details example

Step -1 , Create a Sample Table as PRODUCTLIST

Create table ProductList(ID Int Identity(1,1),PName varchar(30),Price Decimal(10,2))

Step -2 , Insert some sample data

insert into ProductList (Pname,Price) values
(‘Bajaj CFL’,    210.00),
(‘TL’,    135.00),
(‘Table Fan’    ,450.00),
(‘Iron’    ,450.00),
(‘Cable’    ,250.00),o
(‘USB Disk’    ,450.00),
(‘Floppy’    ,120.00),
(‘CD-R’    ,280.00),
(‘CD-W’    ,450.00),
(‘USB Cable’    ,180.00)

 

For testing, SELECT TOP 3 * from PRODUCTLIST Order by Price DESC will return only 3 records as


But SELECT TOP 3 WITH TIES from ProductList Order by Price DESC will return more than 3 records as


 If we examine about performance, initial one without TIES is more better compare to WITH TIES option, Let see an example here, suppose we have to find top 1 record having maximum price, let see the execution plan


Here we can see, WITH TIES option is performing very poorly compare to initial Select TOP query code, this happened because of ORDER BY clause, to resolve this here we have to create a proper index here for same to get the optimal performance.