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

In the SQL Server it’s very important part to take the backup of the database on regular time interval. The purpose of the backup strategy is to protect data if in case failure or corruption occurs. With the help of the backup database, DBA’s can recover their damaged database to a healthy state. There should be proper availability of the resources or particular methods of recovery that’s why the best backup and restore strategy is required. The best strategy minimizes data loss and maximizes the data availability.

Note: It is advisable to place backup database and original database on separate devices otherwise if the device containing the database fails then backup database will become unavailable too and user won’t be able to recover database from backup. Separation of the backup and database also enhances the I/O performance for writing backups and use of the database.

The backup and restore strategy contains both; the backup and restore portion. The backup part defines the frequency and type of the backup, the nature of the hardware and speed, how backup are to be tested and where and how there installation is to be considered for security purposes. Meanwhile, the restore part consists of strategy for how and who is responsible for the restore task, so that the minimization of the database loss and data availability could be maintained.

For a better or proper backup and restore strategy, it is necessary to require planning, implementation and testing. Before restore and backup strategy, the user must follow and should consider given below factors:

  • The production goal of the databases for the data availability and protection of data from loss.
  • The nature of the database like; size, pattern, content, etc.
  • Resource availability like; hardware, personnel, space, security of the media, etc.

Selection of the Recovery Models on Backup and Restore:

The backup and restore task occurs within the recovery model context. If we talk about recovery model then it’s a database property that controls how the transaction logs management is done. It defines the type of backup and resource is available for the database.

The choice for recovery model depends upon the business requirements. To avoid transaction log and for simple backup and restore; use simple recovery model and for reducing the work loss; use full recovery model. For more details about recovery models visit here

Designing the Backup Strategy:

After the user has selected the recovery model, the planning and implementation is required for backup strategy. For the optimal backup strategy, there is a variety of factors on which the backup strategy depends.

  • For a working day how many times does an application need to access the database? If there is off-peak period then it is recommended to take the full database backup for the time period.
  • How frequently changes are made to the database like update, alter, etc?

If there are frequent changes occurring in the database then consider the following:

  • In Simple Recovery Model: Schedule the differential backup between the full database backup. A differential backup includes the changes made since the last full backup of the database.
  • In Full Recovery Model: Schedule the frequent log backups. User can minimize the restore time by reducing the number of the log backups by scheduling differential backup between the full backup.

Concentrate on the changes; whether it is on the small part of the database or the large part. For the large part changes in database like in file and filegroups take partial and file backups.

  • How much space in disk is required for the full database backup?

Estimate Size Of The Backup Database: Before proceeding to the backup and restore plan it is necessary to estimate the disk space required for storing the full database backup. Backup is smaller than the actual database because backup contains the actual data not the unused space. User can estimate the size of the database by using sp_spaceused system procedure.

Schedule Backup: After selection of the backup type, it’s recommended to schedule the regular database backups as a part of the maintenance plan for the database.

Test The Backed Up Database:

User should not proceed to the restore strategy until the testing of the backed up database is done. It is very important to test the backup database by restoring a copy of the database on the test system. User should test every type of backup that they intend to use in future. For more information, visit here

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)

 

 

Checksum is one optional mechanism in SQL Server 2014 through which users can easily identify errors while backing up their database. Checksum can be created by backup operation and validated by a restore operation. User can control operation whether it checks for errors and whether it stops or continues on identifying an error. RESTORE and RESTORE VERIFYONLY statements can be used to check errors if a backup contains backup checksum.

Here, we will discuss a few checksums used during the Backup and Restore process.

Backup Checksum:

There are three types of checksums that SQL Server supports such: checksum on pages, a backup checksum, and checksum in log blocks. On generating a backup checksum, BACKUP statement verifies the database consistency with any checksum or torn-page indication in database.

The backup checksum is computed by the BACKUP statement on backup stream, if both page-checksum or torn-page information is on a page. BACKUP statement also verifies the page ID, checksum and torn-page status while backing up the page.

NOTE: Backup operation doesn’t add up any checksums on pages, when creating backup checksum. Pages are unmodified by the backup.

Because of overhead verification and generation of backup checksums, it poses a live performance impact. Both workload and backup throughput may be severely affected. Therefore, backup checksum is optional. While generating backup checksum during backup, monitor CPU overhead and workload impact too.

The source page is neither modified by BACKUP on disk nor the page content.

When backup checksum are enabled, then following steps are performed by backup operation.

  1. Backup operation first verifies the page-level information (torn-page detection and page checksum) before writing a page to the backup media. If any of them doesn’t exist then backup can’t verify page. Content for unverified pages are added up to the overall backup checksum. The backup failure occurs, when page error during verification is encountered by backup operation.
  2. BACKUP generates a separate backup checksum for the backup stream. Optionally, RESTORE operations can use the checksum to validate whether the backup is corrupt or not. The backup checksum is stored in the backup media, not in the database page.
  3. In the column of .backupset the backup set is flagged as backup checksum has_backup_checksums

If backup checksums are available on the backup media during restore operation, both RESTORE and RESTORE VERIFYONLY statements verify the backup and page checksums. Restore operation even proceeds to the verification, if there is no backup checksum. The reason behind is that, the restore cannot verify page checksums without a backup checksum.

Identify Page Checksum Errors during Backup and Restore Operation

After identifying the page checksum errors, a BACKUP and RESTORE operation fails and further operation is continued by RESTORE VERIFYONLY operation. User can control a given operation whether it fails to encounter the error or continues to the best one.

After encountering errors, backup operation continues and performs the following tasks:

  1. Flags the backup set on backup media (containing errors) and tracks the pages in the suspect_pages table of MSDB database.
  2. Logs for the errors in SQL Server error log
  3. It marks the backup set in the is_damaged column of backupset.
  4. Shows a successful message of backup completion, but contains page errors.

For more details on enabling or disabling Checksum click here