A closer look at everything related to SQL Server

Archive for the ‘Uncategorized’ Category

GDPR – A new European Law to protect their citizens from Data breaches

I am hearing a lot about GDPR (General Data Protection Regulation) in past few months. I decided to do some digging up to know how it affects us in the United States. Those of you who directly want to go to the source of my information and avoid my 2 cents are welcome to check out these 2 links. This Law is explained in very easy terms for a layperson.



Those of you who are short on time and want to just get the skinny version, then keep on reading.

  1. This law is about protecting European Citizens from data breaches of companies inside and outside Europe
  2. This Law may affect us if and when we store data of EU citizens.
  3. This is for EU consumer safety.
  4. In case of a data breach, the companies even outside EU can become part of the lawsuit.
  5. To avoid the lawsuit, a USA based company have to provide the EU country representative with a notification within 3 days of a data breach.
  6. Companies can be fined if the governing body finds the proof that adequate data protection measures were not taken by the company whose data was breached.

It is good to keep this EU law on our radar even if our company is not storing EU consumer data. I think something like this law will become the norm in next 5 years across the global communities. After all, it is the responsibility of the company who collects and stores consumer data to provide safeguards to protect the data. The basic rationale of this law is simple. Since companies make a profit by using a consumer information, it is fair that they should spend a part of that profit to protect that information. This provides clear accountability on one hand and provides peace of mind to the citizens on the other hand. In my opinion, this is a win-win situation.

I would appreciate your comments on this topic.


What you like to LEARN?

Sneak-peek of Microsoft SQL Server 2016 running on SanDisk flash


Brief Recap of the session:

This technical briefing’s main objective was to demonstrate the latest and greatest features of SQL Server 2016 and how running it on SanDisk hardware multiplies the benefits of new features.

This demo was conducted in Microsoft Technology Center in Chicago and was jointly presented by Microsoft and SanDisk representatives. It was a 300-level advanced preview of the hardware and software without any sales pitch.

Their Fusion IoMemory card goes directly in the PCI slot on the server’s motherboard and they have parity NAND chips on that card for fault tolerance. Price went down considerably for the same product since the April 2015 for two reasons. First their Shanghai, Chaina NAND chip manufacture has become more efficient in producing high quality chips and secondly partnership with Microsoft and other hardware vendors such as Dell and HP.


These are some of the numbers we get with SanDisk flash when tables are stored as normal (i.e; on Sandisk without using columnstore feature of SQL Server).


SQL server 2016 has opened the clogged artery meaning flash drive performance is out of the charts. They were talking in terms of IOPS, throughput and latency. Now SQL server 2016 (compared to sql server 2014) using Sandisk or any flash drive can deliver IOPS in millions, Throughput in GBs and latencies in microseconds. The enhancement is due to the improved ColumnStore feature in SQL Server 2016. IO bottleneck for OLTP or OLAP load almost vanishes.


These improvements directly affects the HA and DR environments. For example Always on Availability Group. Log transfer from one database to another, on the secondary, is much faster because of separating the redo process at the secondary server. AG, in synchronized mode, waits for the confirmation from the secondary server. Before sql server 2016, a record has to be written to the disk on the secondary and applied to the database before confirmation is sent to the Primary node. Now confirmation is sent as soon as the records is written to the disk. Redo process than run in the background at secondary server to apply the changes to the database. SQL 2016 AG now have load balancing feature included.


Another benefit is cost savings in SQL server license. Because most of the time sql load is IO bound and CPU hardly goes above 25% on average. These flash drives will make CPU work for what they are worth. So only 2 CPUs are needed for server load as big as 10 TB Datawarehouse.

My impressions:

Since our databases are not even 1 TB, the whole critical databases in production can be stored on these flash drives. Or we can have our log files and tempdb files on flash drives. There are lot of options. And when these cards are purchased with VM vendors, there is warranty included which replaces these cards when it fails without cost to us.

They also have caching feature, I think this a separate software based product that needed to be purchased separately and maybe useful for application level processes to run faster.

Another point is that we are skipping upgrade to SQL server 2014 so we are naturally going to upgrade to SQL server 2016 in year 2017. I think we can plan in 2016 for testing and purchasing these flash drives.

Additionally, these will also make our HA and DR very reliable in terms of failover.

In my opinion, we can take advantage of flash drives for production load in our VMs when we upgrade SQL Server to 2016.

Everything you need to know about FileStream feature of SQL Server 2012

FileStream directory

The file stream network path technically is this \\SQL Server name\Network Share Name\Database File stream Directory Name\<File Table Name>

  1. SQL Server Name – (starting with \\)
  2. Windows Share Name – It is visible in SQL Server Configuration tool -> SQL Server properties -> FileStream tab.
  3. Database File stream Directory Name – It can be seen in Database Properties -> Options -> FILESTREAM Directory Name
  4. The last part is the file table name.

Some helpful FileStream queries

Exploration Queries:

SELECT DB_NAME(database_id) AS DatabaseName,non_transacted_access_desc, directory_name  FROM sys.database_filestream_options WHERE directory_name IS NOT NULL

SELECT directory_name,is_enabled,filename_collation_name FROM sys.filetables;

SELECT * FROM sys.tables WHERE is_filetable = 1

SELECT object_id, OBJECT_NAME(object_id) AS ‘Object Names’    FROM sys.filetable_system_defined_objects

— To get the root level UNC path of a file table

SELECT FileTableRootPath()

SELECT FileTableRootPath(N’file_table_name’)

Enable Disable Queries:


— Disable Non-Transactional write access.

ALTER DATABASE database_name


— Disable non-transactional access.


— Enable full non-transactional Access


Find locks held by filestream queries:

Open handles to the files stored in a FileTable can prevent the exclusive access that is required for certain administrative tasks. To enable urgent tasks, you may have to kill open file handles associated with one or more FileTables.

SELECT * FROM sys.dm_filestream_non_transacted_handles;

— To identify open files and the associated locks

SELECT opened_file_name    FROM sys.dm_filestream_non_transacted_handles

WHERE fcb_id IN        ( SELECT request_owner_id FROM sys.dm_tran_locks );

— Kill all open handles in all the filetables in the database.

EXEC sp_kill_filestream_non_transacted_handles;

— Kill all open handles in a single filetable.

EXEC sp_kill_filestream_non_transacted_handles @table_name = ‘filetable_name’;

— Kill a single handle.

EXEC sp_kill_filestream_non_transacted_handles @handle_id = integer_handle_id;

Code Re-usability:

— Use the relative path for portable code


DECLARE @root nvarchar(100);

DECLARE @fullpath nvarchar(1000);

SELECT @root = FileTableRootPath();

SELECT @fullpath = @root + file_stream.GetFileNamespacePath() FROM EDMS

WHERE name = N’document_name’;

PRINT @fullpath;

How FileStream works with some of the High Availability Features of SQL Server 2012?

 AlwaysOn Availability Groups and FileTables


When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn availability group:

  • FileTable functionality is partially supported by AlwaysOn Availability Groups. After a failover, FileTable data is accessible on the primary replica, but FileTable data is not accessible on readable secondary replicas.
  • The FILESTREAM and FileTable functions accept or return virtual network names (VNNs) instead of computer names.
  • All access to FILESTREAM or FileTable data through the file system APIs should use VNNs instead of computer names.

Replication and FileTables

Replication and related features (including transactional replication, merge replication, change data capture, and change tracking) are not supported with FileTables.

Setting up Log Shipping

I am doing log shipping since SQL Server 2000. I did it with SQL Server 2005. But for the 4 years that I worked with SQL Server 2008 R2, I did not work with Log shipping. Nothing has changed in SQL server 2012. But to refresh my memory, here I am writing down simple steps for setting up log shipping.

  1. Make sure database is in Full recovery mode. In this case primary server is PriSQL\Prod01 and primary database is PriDB.
  2. Pre-initialize the database at secondary. It means take the full backup of PriDB and a transaction log backup of primary database SecDB and restore on secondary SecSQL\DR01 in Standby mode.
  3. Right click the primary database, go to properties and select “Enable this as a primary database in a log shipping configuration”.
  4. Select how frequently you are going to do log backups in “Backup Settings” button. Also provide the path where log backup goes. Some other settings on this page are set as follows:
    1. Delete files older than: 72 hours
    2. Alert if no backup occurs within: 2 hours
    3. Backup schedule: Every 15 minutes
    4. Backup Path: E:\SQLBackups\PriDB_log
    5. Job name: LSBackup_PriDB
    6. Backup compression: Use default server setting.
  5. Add the secondary server. In this case it is SecSQL\DR01. Secondary database is SecDB.
    1. Tab- Initialize secondary Database:
      • Select database is already initialized.
    2. Tab- Copy files:
      • Destination folder for copied file F:\SQLBackups\LogShip
      • Set 72 hours for Delete copied files.
      • Copy job: LSCopy_PriSQL\prod01_PriDB
      • Copy Schedule: Every 15 minutes.
    3. Tab- Restore Transaction Logs
      • Restore job: LSRestore_PriSQL\prod01_PriDB
      • Restore Schedule: Every 15 minutes
      • Delay restoring: 1 minute
      • Alert if no restore occurs: 2 hours

Value of Organizing

Whether you are starting a new job or at your existing job, it is never too late to organize your SQL server database shop. As somebody famously said, “Organize, don’t agonize”. Having clear idea of what is needed to be done already puts you ahead of your task. As we all know, some DBAs are either doing nothing or fighting fires. We do not want to fall in that category. Best use of slow time is to get organized. Here are some tips to help you achieve that Zen like balance.

Create a list of SQL Server information site:
You cannot keep all there is to know about SQL server in your mind. So what to do about it? Write it down. Yes, either create a spreadsheet and save it in a table or save it on your own blog. Whatever tool you choose, it is important that you feel comfortable using it. Save addresses of MVPs blogs , SQL Server Online forums and other tips and tricks. After a while it will become overwhelming list if you do not configure some type of classification or tagging system.

Note: I will be adding more here later on.

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

A San Francisco cable car holds 60 people. This blog was viewed about 480 times in 2014. If it were a cable car, it would take about 8 trips to carry that many people.

Click here to see the complete report.