Hi Guys, its very surprising morning today, when I opened my mail box in morning, got a subscribed mail from SQL Server Blog’s from technet.com. Its saying Mr. Satya Nadella, CEO of Microsoft, announced SQL Server 2016, an intelligent platform for a mobile first, cloud first world. The next major release of Microsoft’s flagship database and analytics platform provides breakthrough performance for mission critical applications and deeper insights on data across on-premises and cloud. Top capabilities for the release include:

Always Encrypted – a new capability that protects data at rest and in motion
Stretch Database – new technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure.
Row-level Security and Dynamic Data Masking
Built-in Advanced Analytics, PolyBase and Mobile BI
Row-level Security and Dynamic Data Masking

In-database analytics – Enhancements to our industry-leading in-memory technologies for real-time analytics on top of breakthrough transactional performance
………………….. and many more…
Always Encrypted

Data security is top of mind, especially for mission critical applications, and SQL Server has been the enterprise database with the fewest security vulnerabilities six years running.  To help customers with data security and compliance when using SQL Server on-premises or in the cloud, SQL Server 2016 introducing Always Encrypted. Always Encrypted, based on technology from Microsoft Research, protects data at rest and in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.


 

Stretch Database

Today, in the Ignite keynote, we can gain the benefits of hyper-scale cloud in the box with new hybrid scenarios including Stretch Database. As core transactional tables grow in size, we may need to archive historical data to lower cost and to maintain fast performance. This unique technology allows us to dynamically stretch our warm and cold transactional data to Microsoft Azure, so our operational data is always at hand, no matter the size, and we benefit from the low cost of using Microsoft Azure.  We can use Always Encrypted with Stretch Database to extend our data in a more secure manner for greater peace of mind.


Real-time Operational Analytics & In-Memory OLTP

Building on our industry leading and proven in-memory technologies, customers will benefit from the combination of real-time operational analytics with blazing fast transactional performance – a first among enterprise vendors.  For In-Memory OLTP, which customers today are using for up to 30x faster transactions than disk based systems, Now we can be able to apply this technology tuned for transactional performance to a significantly greater number of applications as well as benefit from increased concurrency.  With these enhancements, the in-memory columnstore will deliver 100X faster queries with in-memory OLTP for in-memory performance and real-time operational analytics.


Built-in Advanced Analytics, PolyBase and Mobile BI

For deeper insights into data, SQL Server 2016 expands its scope beyond transaction processing, data warehousing and business intelligence to deliver advanced analytics as an additional workload in SQL Server with proven technology from Revolution Analytics.  It will make advanced analytics more accessible and increase performance for our advanced analytic workloads by bringing R processing closer to the data and building advanced analytic capabilities right into SQL Server.  Additionally, PolyBase into SQL Server, expanding the power to extract value from unstructured and structured data using existing T-SQL skills. With this wave, we can then gain faster insights through rich visualizations on many devices including mobile applications on Windows, iOS and Android.

Additional capabilities in SQL Server 2016 include:

  • Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always Encrypted.
  • Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load balancing.
  • Native JSON support to offer better performance and support for your many types of your data.
  • SQL Server Enterprise Information Management (EIM) tools and Analysis Services get an upgrade in performance, usability and scalability.
  • Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Azure and place your SQL Server AlwaysOn secondaries in Azure.

In addition, there are many more capabilities coming with SQL Server 2016 that deliver mission critical performance, deeper insights on your data and allow you to reap the benefits of hyper-scale cloud.

SQL Server 2016 can capture, transform, and analyze any data, of any size, at any scale, in its native format —using the tools, languages and frameworks in a trusted environment on-premises and in the cloud.

A vulnerability discovered recently could prove to be the final nail in SSL’s (Secure Socket Layer) coffin. The latest vulnerability to hit SSL, called ‘Factoring attack on RSA-EXPORT Keys’ (or FREAK), is claims to breach as many as 36 percent of all websites trusted by browsers. According to reports, several highly ranked websites, including the likes of FBI and NSA, are vulnerable to the flaw. Microsoft has confirmed that all versions of Windows, which have the implementation of SSL/TLS, are vulnerable to FREAK. This implies that if an enterprise is using Windows operating system, an attack on the network can force all software that uses the Windows Secure Channel component to install weak encryption on the Internet. Although widely adopted, SSL has had a chequered history. Discovered as a means to enable secure transmission between two points, SSL was quickly adopted by the industry to ensure secure data transmission. However, SSL was continually bogged down by vulnerabilities. SSL 2.0 was open to length extension attacks, had no protection for the handshake, and was vulnerable to truncation attacks. When 2.0 version gave way to SSL 3.0 that too had several flaws. In October 2014, researchers from Google identified a flaw in the design of SSL 3.0, which exposed it to a padding attack. Called POODLE, (an acronym for Padding Oracle on Downgraded Legacy Encryption), the attack required only 256 SSL requests to disclose one byte of encrypted information. It was also at the receiving end of Heart bleed bug. Given the large number of flaws in SSL, the PCI SSC (Payment Card Industry Security Standards Council) recently came out with a special bulletin that called for imminent amendments to the PA-DSS (Payment Application Data Security Standard) and PCI DSS (Payment Card Industry Data Security Standard). The bulleting warned the payment card industry members that SSL could no longer be relied upon for data protection. To overcome the shortcomings of SSL, a new protocol called Transport Layer Security (TLS) has come to the fore. Based on open standard, it is not only more extensible but also ensures support in the future. Also, TLS is scalable enough to secure connections on client’s side that have only SSL. The bottom line is that enterprise information technology practitioners can no longer bank on SSL 3.0 for ensuring privacy and protection of your data. It is time you ensured that your websites and browsers moved away from SSL to leverage more modern and state-of-the art security protocols like TLS. Meanwhile, to counter FREAK, OpenSSL has published a patch. Other vendors too are in the midst of issuing patches for plugging the vulnerability.

Source : ITNEXT

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.