'In this post we’re going to create some encrypted columns in a table in a test database and look at some of the practicalities and limitations of working with Always Encrypted (AE).
There are actually a fair few limitations, but not because there anything wrong with the technology, rather they naturally fall out of logic of working with encrypted data.
Let’s just bash on with it and you’ll see what I mean.'...
'Legislation and regulation sometimes dictates that certain activities must be tracked within a database. On occasion, it will be required that queries be audited and tracked to reach compliance with the legislation or regulation. To achieve this compliance, this article will demonstrate how to use Extended Events to audit statements being executed within the database.
Over the course of a few recent articles, I have included little hints here and there alluding to this article. In fact, now would be a good time to review one of these articles in preparation for what I will be sharing today. Take a moment to refresh your memory on this article about finding the right event – here.'...
http://jasonbrimhall.info/2016/08/08/audit-all-select-statements/
'Recently we moved a client from their aging on-prem environment into Azure. As part of this there was a lot of system upgrading being done to get everything onto current versions. Part of this included upgrading SSRS 2008 R2 to SSRS 2016. As a part of the migration we needed to ensure that email delivery of reports from SSRS was working as expected. This required setting up SMTP with authentication as the only way to send emails out of Azure is to use some sort of authenticated SMTP service such as SendGrid or Office 365. We opted for SendGrid as it includes some nice reporting as part of the email sending.'...
'I was recently scolded for suggesting that, in some cases, a non-clustered index will perform better for a particular query than the clustered index. This person stated that the clustered index is always best because it is always covering by definition, and that any non-clustered index with some or all of the same key columns was always redundant.
I will happily agree that the clustered index is always covering (and to avoid any ambiguity here, we're going to stick to disk-based tables with traditional B-tree indexes).
I disagree, though, that a clustered index is always faster than a non-clustered index. I also disagree that it is always redundant to create a non-clustered index or unique constraint consisting of the same (or some of the same) columns in the clustering key.
Let's take this example, Warehouse.StockItemTransactions, from WideWorldImporters. The clustered index is implemented through a primary key on just the StockItemTransactionID column (pretty typical when you have some kind of surrogate ID generated by an IDENTITY or a SEQUENCE).
It's a pretty common thing to require a count of the whole table (though in many cases there are better ways). This can be for casual inspection or as part of a pagination procedure. Most people will do it this way:
SELECT COUNT(*)
FROM Warehouse.StockItemTransactions;
With the current schema, this will use a non-clustered index:
'...
https://sqlperformance.com/2017/03/sql-indexes/performance-myths-clustered-vs-non-clustered
'When you're deploying any application, one of the first questions that comes up is "What will this cost?" Most of us have gone through this sort of exercise for sizing a SQL Server installation at some point, but what if you're deploying to the cloud? With Azure IaaS deployments, not much has changed–you're still building a server based on CPU count, some amount of memory, and configuring storage to give you enough IOPS for your workload. However, when you make the jump to PaaS, Azure SQL Database is sized with different service tiers, where performance is measured in DTUs. What the heck is a DTU?
I know what a BTU is. Perhaps DTU stands for Database Thermal Unit? Is it the amount of processing power needed to raise the temperature of the data center by one degree? Instead of guessing, let's check the documentation, and see what Microsoft has to say:
'...
https://sqlperformance.com/2017/03/azure/what-the-heck-is-a-dtu
'Simple post today: How to compare two plans in SSMS.
This task is a task that is often done by a DBA or developer who is wanting to compare changes of an index or perhaps even comparing queries between DEV and PROD.
As an example, we take the following AdventureWorks2012 query:
SELECT DISTINCT City
FROM Person.Address
WHERE ModifiedDate = ‘2007-05-01’
And save the query plan as a .SQLPLAN file.
![image_thumb[1]](https://danieljanikcom.files.wordpress.com/2017/04/image_thumb11.png "image_thumb[1]")
Having noted the KEY LOOKUP operator, I know this query can be improved with by adding City to the ncl_demo index as an included column.
For fun, I also ran the plan through my query plan website: HowsMyPlan.com and it noted the same concern.'...
'As Power BI becomes more prevalent in data analytics and visualization within the enterprise, data security becomes a significant concern. Power BI at its best is deployed to the Power BI service hosted on Microsoft’s Azure platform. Every enterprise should understand the level of security available with their data. Companies who have made the leap to cloud technologies such as AWS, Microsoft Azure, Salesforce, and Microsoft Office 365 should have an understanding of the data compliance and security capabilities of those solutions. However, companies who want to take advantage of Power BI but have just started their cloud journey or are cloud adverse need to know the nuances of Power BI and security.
I have been involved with data and cloud security questions a lot of the past few years. With Power BI’s rise in significance, I have had to answer more specific questions about the service. In order to provide proper guidance and not have a reference for myself, I am putting together a short series of posts on various data security items in Power BI. The topics included enterprise gateway, privacy levels, data classification, and compliance. The focus of these articles are related to using the Power BI service as this is the cloud implementation of Power BI. The desktop has setting which impact deployment of assets, but is not the focus of this series.'...
'The ability to scale out SSRS, so that multiple report server instances can access a single report server database, is an Enterprise Edition-only feature. So when Rodney Landrum was tasked with implementing a scale-out solution, with failover, for SSRS on SQL Server Standard Edition, it required some creative thinking. This article describes his solution, its merits and drawbacks.'...
https://www.simple-talk.com/sql/reporting-services/scaling-ssrs-sql-server-standard-edition/
'This week at DataWorks Summit, we are pleased to announce general availability of Azure HDInsight 3.6 backed by our enterprise grade SLA. HDInsight 3.6 brings updates to various open source components in Apache Hadoop & Spark eco-system to the cloud, allowing customers to deploy them easily and run them reliably on an enterprise grade platform.
What’s new in Azure HDInsight 3.6
Azure HDInsight 3.6 is a major update to the core Apache Hadoop & Spark platform as well as with various open source components. HDInsight 3.6 has the latest Hortonworks Data Platform (HDP) 2.6 platform, a collaborative effort between Microsoft and Hortonworks to bring HDP to market cloud-first. You can read more about this effort here.
HDInsight 3.6 GA also builds upon the public preview of 3.6 which included Apache Spark 2.1. We would like to thank you for trying the preview and providing us feedback, which has helped us improve the product.
Apache Spark 2.1 is now generally available, backed by our existing SLA. We are introducing capabilities to support real-time streaming solutions with Spark integration to Azure Event Hubs and leveraging the structured streaming connector in Kafka for HDInsight. This will allow customers to use Spark to analyze millions of real-time events ingested into these Azure services, thus enabling IoT and other real-time scenarios. HDInsight 3.6 will only have the latest version of Apache Spark such as 2.1 and above. There is no support for older versions such as 2.0.2 or below. Learn more on how to get started with Spark on HDInsight.
Apache Hive 2.1 enables ~2X faster ETL with robust SQL standard ACID merge support and many more improvements. This release also includes an updated preview of Interactive Hive using LLAP (Long Lived and Process) which enables 25x faster queries. With the support of the new version of Hive, customers can expect sub-second performance, thus enabling enterprise data warehouse scenarios without the need for data movement. Learn more on how to get started with Interactive Hive on HDInsight.
This release also includes new Hive views (Hive view 2.0) which provides an easy to use graphical user interface for developers to get started with Hadoop. Developers can use this to easily upload data to HDInsight, define tables, write queries and get insights from data faster using Hive views 2.0. Following screenshot shows new Hive views 2.0 interface.''...
'I received a question from the backup engineers configuring a new backup system. They are working with different technology owners to set up the different schedules. As part of this checklist they asked me whether they should trigger a transaction log backup after the FULL BACKUP of databases in the SIMPLE RECOVERY model?
My response to this question included a brief explanation about how SQL Server transaction logs are managed under the SIMPLE RECOVERY model. When a SQL Server database is configured with SIMPLE RECOVERY - a DBA needs to understand that transaction logs will be immediately truncated. The purpose is to remove the inactive virtual log files.'...
http://www.sqlserver-dba.com/2016/09/is-a-log-backup-allowed-on-a-simple-recovery-database.html