“I didn’t buy ten cylinders of CO₂! Why is PCard telling me I did?”

There were a pile of tickets in Adam’s queue, slightly larger than the proverbial molehill, but nowhere near mountain status either. Several employees saw incorrect charges on their purchasing cards, including one of the VPs. The first suspicion was fraud, but these charges looked legitimate: plane tickets to cities where the company had plants, raw materials purchases from vendors the plants usually did business with, etc. Since a VP was impacted, this confusion needed to be resolved ASAP, which meant Adam was pulling long hours to fix it.

“PCard” was the name for the purchasing card application. The purpose of the tool was to allow managers and executives validate the purchases made on the purchasing cards, and determine if they really belonged there or should have had a purhase order. It was one of Adam’s nuisance products, simply because it mostly worked fine- but minor issues were viewed as “my hair’s on fire” class emergencies. The only silver lining was that the application was considered “mature”, which meant that in three months, support would move to a much cheaper offshore team.

The bulk of the application’s logic was an SSIS package. BankCo, the bank which provided the purchasing cards, handled all of the billing and payments, but didn’t provide any sort of reporting on purchases. They did provide a steady feed of flat-files. It true enterprise IT fashion, these files ping-ponged across a series of SFTP servers and network shares until they dropped in a folder that the SSIS package monitored.

Adam started with the bank. Over the phone, they assured him that the charges were correct, and that the problem must be in how Adam’s tool processed the data. Adam wasn’t convinced, but he agreed to examine the import job in detail.

The job imported two major files. The first was a record of every purchasing card issued by BankCo:

4916086563859697 Bobsen     Joebob          032015…
4916410586003002 Bobsen     Sallybob        052015…

The first column was the card number, and the remaining columns contained all of the important metadata- holder, expiration date, date issued, etc. The SSIS job just blindly grabbed this data and jammed it into a table called PCARDS . The schema of the table was nearly identical to the flat file, save for an additional column called PC4, which contained the 4-digit suffix of the card number.

The second file was the actual list of transactions:

9697 Office Max             473.75  04272014…
9697 HmtpnInnSuites         578.95  03192014…
3002 CndaGas Co.            1047.20 05162014…

Again, the PTRANS table was nearly identical to this file format. For relational integrity, the 4-digit suffix linked this table to the underlying purchasing card. The fact that transactions keyed off of the last four digits of the card number immediately caught Adam’s eye. He checked the purchasing card table and confirmed that several cards did have the same 4-digit suffix.

Adam called the bank. “You’ve issued our company multiple cards with the same suffix, but when you send us transactions, the only identifier you send us is the last four digits. So we can’t actually tell which card that transaction is for.”

“That’s impossible. There should never be any repeats in the suffix.”

“And yet,” Adam said, “there are. Can you cahnge the file to send us the whole card number, instead?”

“No! Too many companies depend on the format staying the way it is. We’re not going to make a custom format for you.”

“Couldn’t you add a column at the end? Something? Someone else must have this problem!”

The conversation chased itself in circles until it fell over, panting. Adam hung up the phone with zero progress made. The bank was going to do nothing. Stuck, Adam went to his boss, Steve.

“What I was thinking,” Adam said, “was that we could put all of the unclear transactions in a bucket. When users log in, they’ll be prompted to claim any transactions that might be theirs. There could be an escalation path if transactions remain unclaimed-”

“No!”, Steve said. “First, the business units already feel that the application requires too much data entry. Second, they don’t have a budget to add features, only to fix bugs. Third, no one on the business side is going to actually monitor and maintain that. Fix the bug. Don’t invent new functionality.”

Stymied by the bank and his boss, Adam realized that he was going to have to solve this problem on his own. There was no perfect solution, but Adam was feeling… lucky.

Only a handful of people had duplicate suffixes on their p-cards. Most of the time, the cards were used for one off purchases or to resupply raw materials outside of a regular purchase-order. This meant than some users were heavy users of the cards, but most weren’t. It also meant that many of the common transactions would show repeat business with the same vendor.

Armed with that information, Adam was ready to “fix the bug”. He changed the primary key of the PCARDS table to an auto-generated numeric ID. Then, on PTRANS, he removed the foreign key on the suffix, and replaced it with a foreign key to the new ID field. Finally, he did a bit of creative programming in the SSIS package. If there were multiple purchasing cards that could link to the same transaction, the loading job differentiated them this way:

• If one and only one card had previous transactions with this vendor, assign the transaction to that card.
• In all other cases, assign the transaction to the card which had the most transactions for the month.

Adam slapped a comment on the SSIS package to explain the WTFery he had perpetrated. In the long run, his simple heuristic would almost certainly fail to categorize transactinos correctly, but Adam wasn’t concerned with the “long run”. He just needed it to work for three more months, until the support for the app moved offshore.

[Advertisement] BuildMaster 4.1 has arrived! Check out the new Script Repository feature and see how you can deploy builds from TFS (and other CI) to your own servers, the cloud, and more.

Adding a filtered index can have surprising side-effects on existing queries, even where it seems the new filtered index is completely unrelated. This post looks at an example affecting DELETE statements that results in poor performance and an increased risk of deadlock.

Test Environment

The following table will be used throughout this post:

CREATE TABLE dbo.Data 
(
    RowID       integer IDENTITY NOT NULL, 
    SomeValue   integer NOT NULL,      
    StartDate   date NOT NULL,
    CurrentFlag bit NOT NULL,
    Padding     char(50) NOT NULL DEFAULT REPLICATE('ABCDE', 10),
    CONSTRAINT PK_Data_RowID
        PRIMARY KEY CLUSTERED (RowID)
);

This next statement creates 499,999 rows of sample data:

INSERT dbo.Data WITH (TABLOCKX)
    (SomeValue, StartDate, CurrentFlag)
SELECT
    CONVERT(integer, RAND(n) * 1e6) % 1000,
    DATEADD(DAY, (N.n - 1) % 31, '20140101'),
    CONVERT(bit, 0)
FROM dbo.Numbers AS N
WHERE 
    N.n >= 1 
    AND N.n < 500000;

That uses a Numbers table as a source of consecutive integers from 1 to 499,999. In case you do not have one of those in your test environment, the following code can be used to efficiently create one containing integers from 1 to 1,000,000:

WITH
    N1 AS (SELECT N1.n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 (n)),
    N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
    N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N2 AS R),
    N AS (SELECT ROW_NUMBER() OVER (ORDER BY n) AS n FROM N4)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM N
OPTION (MAXDOP 1);
 
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1);

The basis of the later tests will be to delete rows from the test table for a particular StartDate. To make the process of identifying rows to delete more efficient, add this nonclustered index:

CREATE NONCLUSTERED INDEX 
    IX_Data_StartDate
ON dbo.Data 
    (StartDate);

The Sample Data

Once those steps are completed, the sample will look like this:

SELECT TOP (100)
    D.RowID,
    D.SomeValue,
    D.StartDate,
    D.CurrentFlag,
    D.Padding
FROM dbo.Data AS D
ORDER BY
    D.RowID;

The SomeValue column data may be slightly different due to the pseudo-random generation, but this difference is not important. Overall, the sample data contains 16,129 rows for each of the 31 StartDate dates in January 2014:

SELECT 
    D.StartDate, 
    NumRows = COUNT_BIG(*)
FROM dbo.Data AS D
GROUP BY
    D.StartDate
ORDER BY 
    D.StartDate;

The last step we need to perform to make the data somewhat realistic, is to set the CurrentFlag column to true for the highest RowID for each StartDate. The following script accomplishes this task:

WITH LastRowPerDay AS
(
    SELECT D.CurrentFlag
    FROM dbo.Data AS D
    WHERE D.RowID =
    (
        SELECT MAX(D2.RowID)
        FROM dbo.Data AS D2
        WHERE D2.StartDate = D.StartDate
    )
)
UPDATE LastRowPerDay
SET CurrentFlag = 1;

The execution plan for this update features a Segment-Top combination to efficiently locate the highest RowID per day:

Notice how the execution plan bears little resemblance to the written form of the query. This is a great example of how the optimizer works from the logical SQL specification, rather than implementing the SQL directly. In case you are wondering, the Eager Table Spool in that plan is required for Halloween Protection.

Deleting A Day of Data

Ok, so with the preliminaries completed, the task at hand is to delete rows for a particular StartDate. This is the sort of query you might routinely run on the earliest date in a table, where the data has reached the end of its useful life.

Taking 1 January 2014 as our example, the test delete query is simple:

DELETE dbo.Data
WHERE StartDate = '20140101';

The execution plan is likewise pretty simple, though worth looking at in a bit of detail:

Plan Analysis

The Index Seek on the far right uses the nonclustered index to find rows for the specified StartDate value. It returns just the RowID values it finds, as the operator tooltip confirms:

If you are wondering how the StartDate index manages to return the RowID, remember that RowID is the unique clustered index for the table, so it is automatically included in the StartDate nonclustered index.

The next operator in the plan is the Clustered Index Delete. This uses the RowID value found by the Index Seek to locate rows to remove.

The final operator in the plan is an Index Delete. This removes rows from the nonclustered index IX_Data_StartDate that are related to the RowID removed by the Clustered Index Delete. To locate these rows in the nonclustered index, the query processor needs the StartDate (the key for the nonclustered index).

Remember the original Index Seek did not return the Start Date, just the RowID. So how does the query processor get the StartDate for the index delete? In this particular case, the optimizer might have noticed that the StartDate value is a constant and optimized it away, but this is not what happened. The answer is that the Clustered Index Delete operator reads the StartDate value for the current row and adds it to the stream. Compare the Output List of the Clustered Index Delete shown below, with that of the Index Seek just above:

It might seem surprising to see a Delete operator reading data, but this is the way it works. The query processor knows it will have to locate the row in the clustered index in order to delete it, so it might as well defer reading columns needed to maintain nonclustered indexes until that time, if it can.

Adding a Filtered Index

Now imagine someone has a crucial query against this table that is performing badly. The helpful DBA performs an analysis and adds the following filtered index:

CREATE NONCLUSTERED INDEX
    FIX_Data_SomeValue_CurrentFlag
ON dbo.Data (SomeValue)
INCLUDE (CurrentFlag)
WHERE CurrentFlag = 1;

The new filtered index has the desired effect on the problematic query, and everyone is happy. Notice that the new index does not reference the StartDate column at all, so we do not expect it to affect our day-delete query at all.

Deleting a day with the filtered index in place

We can test that expectation by deleting data for a second time:

DELETE dbo.Data
WHERE StartDate = '20140102';

Suddenly, the execution plan has changed to a parallel Clustered Index Scan:

Notice there is no separate Index Delete operator for the new filtered index. The optimizer has chosen to maintain this index inside the Clustered Index Delete operator. This is highlighted in SQL Sentry Plan Explorer as shown above ("+1 non-clustered indexes") with full details in the tooltip:

If the table is large (think data warehouse) this change to a parallel scan might be very significant. What happened to the nice Index Seek on StartDate, and why did a completely unrelated filtered index change things so dramatically?

Finding the Problem

The first clue comes from looking at the properties of the Clustered Index Scan:

As well as finding RowID values for the Clustered Index Delete operator to delete, this operator is now reading CurrentFlag values. The need for this column is unclear, but it does at least begin to explain the decision to scan: the CurrentFlag column is not part of our StartDate nonclustered index.

We can confirm this by rewriting the delete query to force the use of the StartDate nonclustered index:

DELETE D
FROM dbo.Data AS D 
    WITH (INDEX(IX_Data_StartDate))
WHERE StartDate = '20140103';

The execution plan is closer to its original form, but it now features a Key Lookup:

The Key Lookup properties confirm this operator is retrieving CurrentFlag values:

You might also have noticed the warning triangles in the last two plans. These are missing index warnings:

This is further confirmation that SQL Server would like to see the CurrentFlag column included in the nonclustered index. The reason for the change to a parallel Clustered Index Scan is now clear: the query processor decides that scanning the table will be cheaper than performing the Key Lookups.

Yes, but why?

This is all very weird. In the original execution plan, SQL Server was able to read extra column data needed to maintain nonclustered indexes at the Clustered Index Delete operator. The CurrentFlag column value is needed to maintain the filtered index, so why does SQL Server not just handle it in the same way?

The short answer is that it can, but only If the filtered index is maintained in a separate Index Delete operator. We can force this for the current query using undocumented trace flag 8790. Without this flag, the optimizer chooses whether to maintain each index in a separate operator or as part of the base table operation.

-- Forced wide update plan
DELETE dbo.Data
WHERE StartDate = '20140105'
OPTION (QUERYTRACEON 8790);

The execution plan is back to seeking the StartDate nonclustered index:

The Index Seek returns just RowID values (no CurrentFlag):

And the Clustered Index Delete reads the columns needed to maintain the nonclustered indexes, including CurrentFlag:

This data is eagerly written to a table spool, which is the replayed for each index that needs maintaining. Notice also the explicit Filter operator before the Index Delete operator for the filtered index.

Another pattern to watch out for

This problem does not always result in a table scan instead of an index seek. To see an example of this, add another index to the test table:

CREATE NONCLUSTERED INDEX
    IX_Data_SomeValue_CurrentFlag
ON dbo.Data (SomeValue, CurrentFlag);

Note this index is not filtered, and does not involve the StartDate column. Now try a day-delete query again:

DELETE dbo.Data
WHERE StartDate = '20140104';

The optimizer now comes up with this monster:

This query plan has a high surprise factor, but the root cause is the same. The CurrentFlag column is still needed, but now the optimizer chooses an index intersection strategy to get it instead of a table scan. Using the trace flag forces a per-index maintenance plan and sanity is once again restored (the only difference is an extra spool replay to maintain the new index):

Only filtered indexes cause this

This issue only occurs if the optimizer chooses to maintain a filtered index in a Clustered Index Delete operator. Non-filtered indexes are not affected, as the following example shows. The first step is to drop the filtered index:

DROP INDEX FIX_Data_SomeValue_CurrentFlag
ON dbo.Data;

Now we need to write the query in a way that convinces the optimizer to maintain all the indexes in the Clustered Index Delete. My choice for this is to use a variable and a hint to lower the optimizer's row count expectations:

-- All qualifying rows will be deleted
DECLARE @Rows bigint = 9223372036854775807;
 
-- Optimize the plan for deleting 100 rows
DELETE TOP (@Rows)
FROM dbo.Data
OUTPUT
    Deleted.RowID,
    Deleted.SomeValue,
    Deleted.StartDate,
    Deleted.CurrentFlag
WHERE StartDate = '20140106'
OPTION (OPTIMIZE FOR (@Rows = 100));

The execution plan is:

Both nonclustered indexes are maintained by the Clustered Index Delete:

The Index Seek returns only the RowID:

The columns needed for the index maintenance are retrieved internally by the delete operator; these details are not exposed in show plan output (so the output list of the delete operator would be empty). I added an OUTPUT clause to the query to show the Clustered Index Delete once again returning data it did not receive on its input:

Final Thoughts

This is a tricky limitation to work around. On the one hand, we generally do not want to use undocumented trace flags in production systems.

The natural 'fix' is to add the columns needed for filtered index maintenance to all nonclustered indexes that might be used to locate rows to delete. This is not a very appealing proposition, from a number of points of view. Another alternative is to just not use filtered indexes at all, but that is hardly ideal either.

My feeling is that the query optimizer ought to consider a per-index maintenance alternative for filtered indexes automatically, but its reasoning appears to be incomplete in this area right now (and based on simple heuristics rather than properly costing per-index/per-row alternatives).

To put some numbers around that statement, the parallel clustered index scan plan chosen by the optimizer came in at 5.5 units in my tests. The same query with the trace flag estimates a cost of 1.4 units. With the third index in place, the parallel index-intersection plan chosen by the optimizer had an estimated cost of 4.9, whereas the trace flag plan came in at 2.7 units (all tests on SQL Server 2014 RTM CU1 build 12.0.2342 under the 120 cardinality estimation model, and with trace flag 4199 enabled).

I regard this as behaviour that should be improved. You can vote to agree or disagree with me on this Connect item.

The post An Unexpected Side-Effect of Adding a Filtered Index appeared first on SQLPerformance.com.

This blog post will highlight PolyBase’s truly unique approach focusing on:

• Query capabilities across various heterogeneous data sources – on-premises and cloud (Microsoft Azure) bringing Microsoft data services together forming one complete data platform solution
• Total freedom for users with no lockdown, agnostic to the actual Hadoop distribution and/or underlying operating system
• Faster insights from all your data in a simple and performing fashion allowing users to leverage their existing tools and SQL scripts

1.    Bringing the relational world together with Hadoop & Cloud (Azure)

In the very recent past, various SQL over Hadoop/HDFS solutions have been developed, such as Impala, HAWQ, Stinger, SQL-H, Hadapt to name just a few. While there are clear technical differences between the various solutions, at a high level, they are similar in offering a SQL-like front end over data stored in HDFS.

So, is PolyBase yet another similar solution competing with these approaches? The answer is yes and no. On first glance, PolyBase is a T-SQL front end that allows customers to query data stored in HDFS. However, with the recently announced Analytics Platform System (APS), we have updated PolyBase with new syntax to highlight our extensible approach. With PolyBase, we bring various Microsoft data management services together and allow appliance users to leverage a variety of Azure services. This enables a new class of hybrid scenarios and reflects the evolution of PolyBase to a true multi-data source query engine. It allows users to query their big data – regardless of whether it is stored in an on-premises Hadoop/HDFS cluster, Azure storage, Parallel Data Warehouse, and other relational DBMS systems (offered in a future PolyBase release).

Complete Data Platform with PolyBase as key integrative component

2.    Freedom of Choice

Openness

One important key differentiator of PolyBase compared to all of the existing competitive approaches is ‘openness’. We do not force users to decide on a single solution, like some Hadoop providers are pursuing. With PolyBase, you have the freedom to use an HDInsight region as a part of your APS appliance, to query an external Hadoop cluster connected to APS, or to leverage Azure services from your APS appliance (such as HDInsight on Azure).

To achieve this openness, PolyBase offers these three building blocks.

Building blocks for PolyBase

The syntax for using PolyBase is simple and follows familiar T-SQL language constructs.

T-SQL for creating external data sources (Azure, external Hadoop cluster, HDI region)

• CREATE EXTERNAL DATA SOURCE Azure_DS WITH (TYPE=Hadoop, LOCATION= 'wasbs://<youraccount>.blob.core.windows.net');
• CREATE EXTERNAL DATA SOURCE External_HDP_DS WITH (TYPE=Hadoop, LOCATION='hdfs://…’, JOB_TRACKER_LOCATION='…');
• CREATE EXTERNAL DATA SOURCE HDI_DS WITH (TYPE=Hadoop, LOCATION = 'hdfs://…’, JOB_TRACKER_LOCATION = '…’);

T-SQL for creating external file formats (delimited text files and Hive RCFiles)

• CREATE EXTERNAL FILE FORMAT DelimText1 WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR =','));
• CREATE EXTERNAL FILE FORMAT DelimText2 (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR ='|', USE_TYPE_DEFAULT = TRUE));
• CREATE EXTERNAL FILE FORMAT HiveRC WITH (FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe')

T-SQL for creating external tables (for Azure, external Hadoop cluster, HDI region)

• CREATE EXTERNAL TABLE [dbo].[Old_SensorData_Azure] (…)
WITH (LOCATION='//Sensor_Data/May2009/sensordata.tbl', DATA_SOURCE   Azure_DS, FILE_FORMAT = DelimText2, REJECT_TYPE = VALUE, REJECT_VALUE = 100)
• CREATE EXTERNAL TABLE [dbo].[SensorData_ExternalHDP] (…)
WITH (LOCATION='//Sensor_Data/May2014/sensordata.tbl', DATA_SOURCE = External_HDP_DS, FILE_FORMAT = DelimText1, REJECT_TYPE = VALUE, REJECT_VALUE = 0)
• CREATE EXTERNAL TABLE [dbo].[SensorData_HDI] (…)
WITH (LOCATION='//Sensor_Data/Year2013', DATA_SOURCE = HDI_DS, FILE_FORMAT = HiveRC)

A user can now create statistics for each of the external tables shown above to improve the query performance. We extended SQL Server’s mature stats framework to work against external tables in the same way it works against regular tables. Statistics are crucial for the PolyBase query engine in order to generate optimal execution plans and to decide when pushing computation into the external data source is beneficial.

Performance

While other SQL over Hadoop solutions (e.g. Impala, Stinger, and HAWQ) have improved, it remains true that they still cannot match the query performance of a mature relational MPP system. With PolyBase, the user can import data in a very simple fashion into PDW (through a CTAS statement, see below), use the fast SQL Server column store technology along with the MPP architecture, or let the PDW/PolyBase query optimizer decide which parts of the query get executed in Hadoop and which parts in PDW. This optimized querying, called split-based query processing, allows parts of the query to be executed as Hadoop MR jobs that are generated on-the-fly completely transparent for the end user. Thereby, the PolyBase query optimizer takes into account parameters such as the spin-up time for MR jobs and the generated statistics to determine the optimal query plan.

In general, if it comes to performance the answer usually is ‘it depends on the actual use case/query’. With PolyBase, the user has total freedom and can leverage capabilities of PDW and/or Hadoop based on their actual needs and application requirements.

PolyBase in APS bridging the gap between the relational world, Hadoop (external or internal) and Azure

The T-SQL statement below will run across all data sources combining structured appliance data with un/semi-structured data in external Hadoop, internal HDInsight region, and Azure (e.g. historical data) –

T-SQL SELECT querying external Hadoop, HDInsight & PDW regions, and Azure

SELECT machine_name, machine.location 
 FROM Machine_Information_PDW, Old_SensorData_Azure, SensorData_HDI, SensorData_ExternalHDP
WHERE Machine_Information_PDW.MachineKey = Old_SensorData_Azure.MachineKey
and Machine_Information_PDW.MachineKey = SensorData_HDI.MachineKey
and Machine_Information_PDW.MachineKey = SensorData_ExternalHDP.MachineKey
and SensorData_HDI.Temperature> 80 and Old_SensorData_Azure.Temperature > 80
and SensorData_ExternalHDP.Temperature > 80

This query example shows how simplicity and performance are combined at the same time. It shows three external tables referring to three different locations plus one regular (distributed) PDW table. While executing the query, the PolyBase/PDW query engine will decide, based on the statistics, whether or not to push computation to the external data source (i.e. Hadoop).

Rewriting & Migrating existing applications

Finally, you may have heard that Hadoop is ‘cheaper’ than more mature MPP DBMS systems. However, what you might not have heard about is the cost associated with rewriting existing applications and ensuring continued tool support. This goes beyond simple demos showing that tool ‘xyz’ works on top of Hadoop/HDFS.

PolyBase does not require you to download and install different drivers. The beauty of our approach is that external tables appear like regular tables in your tool of choice. The information about the external data sources and file formats is abstracted away. Many Hadoop-only solutions are not fully SQL-ANSI compliant and do not support various SQL constructs. With PolyBase, however, you don’t need to rewrite your apps because it uses T-SQL and preserves its semantics. This is specifically relevant when users are coming from a ‘non-Java/non-Hadoop world’. You can explore and visualize your data sets either by using the Microsoft BI solutions (initiated on-premises or through corresponding Azure services) or by using the visualization tool of your choice. PolyBase keeps the user experience the same. 

3.    Simplified ETL & Fast Insights

It’s already a painful reality that many enterprises store and maintain data in different systems that are optimized for different workloads and applications, respectively. Admins are spending much time moving, organizing, and keeping data in sync. This reality imposes another key challenge which we are address with PolyBase – in addition to querying data in external data sources, a user can achieve a simpler and more performant ETL (extraction, transformation, loading). Different than existing connector technologies, such as SQOOP, a PolyBase user can use T-SQL statements to either import data from external data sources (CTAS) or export data to external data sources (CETAS).

T-SQL CETAS statement to age out Hadoop & PDW data to Azure

CREATE EXTERNAL TABLE Old_Data_2008_Azure
WITH (LOCATION='//Sensor_Data/2008/sensordata.tbl', DATA_SOURCE=Azure_DS, FILE_FORMAT=DelimText2)
AS SELECT T1.* FROM Machine_Information_PDW T1 JOIN SensorData_ExternalHDP T2
ON (T1.MachineKey = T2.MachineKey) WHERE T2.YearMeasured = 2008 

Combines data from external Hadoop and PDW sources and stores the results in Azure

Under-the-covers, the PolyBase query engine is not only leveraging the parallelism of an MPP system, it also pushes computation to the external data source to reduce the data volume that needs to be moved. The entire procedure remains totally transparent for the user while ensuring a very fast import & export of data that greatly outperforms any connector technology offered today. With the CTAS statement, a user can import data into the relational PDW region where it stores the data as column store. This way, users can immediately leverage the column store technology in APS without any further action.

T-SQL CTAS statement for importing Hadoop data into PDW

CREATE TABLE Hot_Machines_2011 WITH (Distribution = hash(MachineKey),
CLUSTERED COLUMNSTORE INDEX)
AS SELECT * FROM SensorData_HDI where SensorData_HDI.YearMeasured = 2011 and SensorData_HDI.Temperature > 150

Combines PolyBase with column store – Imports data from Hadoop into PDW CCI tables

In summary, PolyBase is more than just another T-SQL front end over Hadoop. It has evolved into a key integrative component that allows users to query, in a simple fashion, data stored in heterogeneous data stores. There is no need to maintain separate import/export utilities. PolyBase ensures great performance by leveraging the computation power available in external data sources. Finally, the user has freedom in almost every dimension whether it’s about tuning the system and getting the best performance, choosing their tools of choice to derive valuable insights, and to leverage data assets stored both on-premises and within the Azure data platform.    

Watch how APS seamlessly integrates data of all sizes and types here

Learn more about APS here

Editor’s note: The following post was written by SQL Server MVP Nicolas Souquet

Introduction

With the verly large amounts of data companies store today, databases are very likely to contain critical and confidential data, making database backups encryption an essential database engine feature. It is easily foreseeable that such feature will soon be required to pass accreditation audits.

Raising the security bar through releases, SQL Server 2014 now enables to encrypt database backups natively. It has been a long awaited feature, as the options to cover this need were rather cumbersome :

-          Transparent Data Encryption, released with SQL Server 2008, but it implies some CPU overhead, and the backup file compression ratio is then very low;

-          Disk encryption, such as BitLocker, but ciphering large disk volumes is a long operation;

-          Third-party software.

In this paper, we will expose how backup encryption works, before walking through the steps needed to encrypt databases backups with T-SQL and with Maintenance Plans. We will finally detail the actions required to restore a ciphered database backup. Backing up to a file and to Azure will be addressed.

How does backup encryption work ?

There is no encryption without keys, and backup encryption is no exception : we first need to create a Database Master Key (DMK) of the master system database, and a certificate. The DMK is a symmetric key, and is unique to each database in each SQL Server instance : we cannot restore an encrypted backup file on a distinct or re-installed SQL Server instance without the master system database DMK (or an encrypted database without its DMK).

The Database Master Key is encrypted by the AES 256 algorithm, using the Service Master Key, which is also a symmetric key. It is encrypted based on the SQL Server service account credentials and the Windows Data Protection API machine key. The Service Master Key is unique per SQL Server instance, and created during SQL Server installation. It is stored in the master system database and in the user database, so as to enable its automatic decryption (cf. sys.symmetric_keys system view).

Finally, a Certificate contains a public key that is digitally signed, and may contain a private key. This private key is protected by the DMK. While SQL Server can generate IETF X.509v3-compliant certificates, it also allows to use certificates generated by third-parties (cf. sys.certificates system view).

We can summarize the encryption hierarchy levels with the following diagram :

The backup process works on a data page basis : it copies data pages from the data files into the backup file. Since SQL Server 2008, we can compress database backups. This feature is supported by an algorithm similar to the ones behind file compression software : it factorizes patterns of data found in the data pages. Whether the backup data pages are compressed or not, SQL Server 2014 is able to encrypt these pages with the AES 128, AES 192, AES 256 or Triple DES algorithms.

Encrypting a database backup in T-SQL

In this example, we will use the Contoso demo database. The code needed to take an encrypted database backup is following the steps described in the previous section. First of all, we must backup the service master key :

USE master

GO

-- Saving the service master key

-- Ideally, the resulting file should be stored on a distinct,

-- secure machine with restricted access

BACKUP SERVICE MASTER KEY

TO FILE = 'E:\SQLServerBackup\SQL2014_service_master_key.key'

ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption';

GO

We then have to create the master key, which again is straightforward. The password used to encrypt the master key can be different than the one used to encrypt the service master key backup file.

-- Creating a new database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption';

GO

-- Saving the database master key

-- Ideally, the resulting file should be stored on a distinct,

-- secure machine with restricted access

BACKUP MASTER KEY

TO FILE = 'E:\SQLServerBackup\SQL2014_Contoso_master_key.key'

ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption';

GO

We can confirm the creation of the master key by examining the sys.symmetric_keys system view : it now shows one, named ##MS_DatabaseMasterKey##. Next, we need to create the certificate, which is achieved with a single instruction :

USE master

GO

-- Creating the certificate

CREATE CERTIFICATE Contoso_BackupEncryptionWithSQLServer2014

WITH SUBJECT = 'SQL Server 2014 Backup Encryption demo with Contoso';

GO

Similarly, interrogating the sys.certificates system view in the master database context will now return a supplementary row, and the column pvt_key_encryption_type_desc indicates ENCRYPTED_BY_MASTER_KEY.

As for every key, we want to save the certificate : this requires to specify a private key file. It clearly implies that one does not work without the other. Here again, a password is required to cipher the certificate file, and optionally a different one can be defined for the decryption. This password is the public key of the certificate.

-- Saving the certificate

-- Ideally, the resulting file should be stored on a distinct,

-- secure machine with restricted access

BACKUP CERTIFICATE Contoso_BackupEncryptionWithSQLServer2014

TO FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate.cer'

WITH PRIVATE KEY

        (

                FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate_private_key.key'

                , ENCRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

        );

GO

At this point, the groundwork is laid for taking encrypted backups. We have 4 files that we need to keep in a secure and distinct storage area :

Backing up a database and ciphering the resulting file only requires us to choose an encryption algorithm and to specify the certificate we want to use :

-- Backing up the ContosoRetailDW demo database, with encryption

BACKUP DATABASE ContosoRetailDW

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

WITH INIT, CHECKSUM, COMPRESSION, STATS = 1

        , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

Restoring a database from an encrypted backup file

Before we walk thorugh the restoration process, we must keep in mind that SQL Server 2014 introduces native backup encryption. Thus, restoring a database from a natively encrypted backup file on a version of SQL Server that is anterior to SQL Server 2014 is not supported.

Restoring the database on the same SQL Server 2014 instance

Restoring a database from an encrypted backup file on the same SQL Server 2014 instance as the one on which its backup has been taken is operated as usual : all the keys and the certificate are already registered in the master database. Consequently, they are opened automatically when needed for decryption.

RESTORE DATABASE ContosoRetailDW_RestoredFromEncryptedBackupFile

FROM DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

WITH MOVE 'ContosoRetailDW2.0' TO 'E:\Contoso\ContosoRetailDW_EncrypteBackup_data.mdf'

, MOVE 'ContosoRetailDW2.0_log' TO 'E:\Contoso\ContosoRetailDW_EncrypteBackup_log.ldf'

, STATS = 1

Restoring a database from an encrypted backup file on another SQL Server 2014 instance

This operation requires to :

• Restore the Database Master Key from its backup file
• Create the Certificate from its backup file, which involves the private key file.

The restoration process is as follows :

USE master

GO

-- Restoring the master key on the target SQL Server instance

-- from its backup file

RESTORE MASTER KEY

FROM FILE = 'E:\SQLServerBackup\SQL2014_Contoso_master_key.key'

-- the password that was used to encrypt the master key in the source SQL Server instance

DECRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

-- the password with which we want to encrpyt it on the target SQL Server instance

-- the service key is different on the source and the target SQL Server instances

ENCRYPTION BY PASSWORD = '@n0therStrongP@$$w0r2!';

-- Since the master key is not registered in the master database

-- We need to open it in order to decrypt it

-- It stays opened for the session duration

OPEN MASTER KEY

DECRYPTION BY PASSWORD = '@n0therStrongP@$$w0r2!'

-- Restoring the certificate by the private key

-- the password is the one we used to encrypt it on the source SQL Server instance

CREATE CERTIFICATE Contoso_BackupEncryptionWithSQLServer2014

FROM FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate.cer'

WITH PRIVATE KEY

        (

                FILE = 'E:\SQLServerBackup\SQL2014_Contoso_certificate_private_key.key'

                , DECRYPTION BY PASSWORD = 'a$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

        )

-- Getting the list of files in the backup file

-- The instruction is identical for a non-encrypted database

RESTORE FILELISTONLY

FROM DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

-- Finally restoring the database

-- The instruction is identical for a non-encrypted database

RESTORE DATABASE ContosoRetailDW

FROM DISK = 'E:\SQLServerBackup\ContosoRetailDW\ContosoRetailDW_FULL_ENCRYPTED.bak'

WITH MOVE 'ContosoRetailDW2.0' TO 'D:\SQL Server\\ContosoRetailDW_EncryptBackup_data.mdf'

, MOVE 'ContosoRetailDW2.0_log' TO 'D:\SQL Server\ContosoRetailDW_EncryptBackup_log.ldf'

, STATS = 1

-- Closing the master key

CLOSE MASTER KEY

In the case of a restoration automation, we may not want to restore and open the database master key each time.

This is achievable by running the following command, after having restored and opened the master key in the same session :

ALTER MASTER KEY REGENERATE

WITH ENCRYPTION BY PASSWORD = 'aN0th€r$tr0n9#!P@$$w0r2_f0rDBb@ckupEncryption'

Taking encrypted backups with Maintenance Plans

The dialog window to customize maintenance plan backup tasks had been revamped in SQL Server 2014, and reveals the option to take encrypted backups. It lets you pick the encryption algorithm among the four available.

However, there is not a way yet to manually create a certificate or an asymmetric key from this interface, or from SQL Server Management Studio Object Explorer : it has to be created following the steps described earlier.

Backing up and restoring in Azure

SQL Server 2014 enables to backup databases to Microsoft’s cloud platform : Azure (More exactly since SQL Server 2012 SP1 CU4). This nice feature is now enabling companies :

-          to store their backups outside their data center, covering the case in which it would face a complete outage;

-          to save their data in a geographically remote location at a low cost compared to investing in a secondary data center.

Prerequisites

In order to backup a database in Azure, we need to have :

-          An Azure subscription

-          Access to Azure by the Azure Portal (or via PowerShell)

-          An Azure Storage Account created

-          A storage container created. In this example, it is named “backup”.

Instructions to save a database to Azure

As the documentation reveals, instead of specifying the TAPE or DISK clause of the BACKUP instruction, we use URL, which makes the implementation of this feature remarkably simple.

Prior to this, we need to create a Credential : it is a security element that contains a user name and password for a user to access resources external to SQL Server. In our case, the Credential will contain the name of the storage account, and the primary key to this storage account.

In the print-screen below, we find the name of the storage account under the NAME column :

Clicking the MANAGE ACCESS KEYS button brings a popup window, in which we find the storage primary access key. So to create the credential with which we will gain access to Azure storage, we write :

USE master;

CREATE  CREDENTIAL SQLServer2014EncryptedBackupInTheCloud

WITH    IDENTITY = 'account_name'

        , SECRET = 'AzureStoragePrimaryAccessKey_ItIsNormallyALongString'

Before issuing the BACKUP instruction, we need to get the URL that we will specify. We can get it by successively clicking on the storage account name, and on the CONTAINERS tab :

This is the occasion to confirm the name of the storage container : “backup”, which is also visible in the URL.

Finally, the instruction is :

BACKUP DATABASE ContosoRetailDW

TO URL = 'http://account_name.blob.core.windows.net/backup/ContosoRetailDW.bak'

WITH INIT, COMPRESSION, CHECKSUM, STATS = 1

        , CREDENTIAL = 'SQLServer2014EncryptedBackupInTheCloud'

        , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

Once the backup is complete, we can see the file in the container :

Restoring a database from Azure

Similar to the restoration instruction sequence we have seen for file backups, it is required to have :

-          the database master key and the certificate restored;

-          the credential existing; if necessary, we can recreate it based on the data gathered from the Azure portal website.

The command batch is then :

OPEN MASTER KEY

DECRYPTION BY PASSWORD = '@n0therStrongP@$$w0r2!'

RESTORE DATABASE ContosoRetailDW

FROM URL = 'http://account_name.blob.core.windows.net/backup/ContosoRetailDW.bak'

WITH CREDENTIAL = 'SQLServer2014EncryptedBackupInTheCloud'

Performance Considerations

SQL Server 2014 offers four algorithms to encrypt backup files, and ciphering is an intensive CPU activity. It is also possible that the various algorithms have a different impact on the size of the resulting backup files.

To measure the impact of the various algorithm on CPU usage, we have taken a Performance Monitor trace with the Processor / % Processor Time counter only, and taken a backup of the same database with each of the four algorithms, plus a non-encrypted backup. All backups were compressed. The batch of commands is :

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_NO_ENCRYPTION.bak'

WITH INIT, COMPRESSION, CHECKSUM

WAITFOR DELAY '00:00:10'

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_AES_128.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

WAITFOR DELAY '00:00:10'

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_AES_192.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

WAITFOR DELAY '00:00:10'

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_AES_256.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

WAITFOR DELAY '00:00:10'

BACKUP DATABASE [ContosoRetailDW]

TO DISK = 'E:\SQLServerBackup\ContosoRetailDW_TRIPLE_DES_3KEY.bak'

WITH INIT, COMPRESSION, CHECKSUM , ENCRYPTION

(

ALGORITHM = AES_256

, SERVER CERTIFICATE = Contoso_BackupEncryptionWithSQLServer2014

)

We wait 10 seconds between each backup, so as for the CPU usage to show bumps on the graph between each backup execution. Below is the commented Perfmon trace graph. It shows that while the AES algorithms have not much impact on the CPU usage compared to each other and to a backup taken with no encryption, the TRIPLE DES algorithm uses 25-30% more CPU than the AES ones.

After having integrated the Perfmon trace file into a table, and associated the CPU usage to the encryption algorithm used based on the time, we computed the median of the CPU usage, which produces the result below :

Finally, we can look at the backup duration and the backup file size. A few columns have been added to the system tables in the msdb system database, to expose backup encryption :

-          dbo.backupset

• key_algorithm
• encryptor_thumbprint
• encryptor_type

-          dbo.backupmediaset

• is_encrypted

Below is a query that retrieves the backup history, specifying the columns from the dbo.backupset table listed above :

;WITH

        CTE AS

        (

                SELECT  database_name

                        , last_backup_start_date_time

                        , physical_device_name

                        , backup_size / (1024 * 1024) AS backup_size_MB

                        , compressed_backup_size / (1024 * 1024)  AS compressed_backup_size_MB

                        , CAST(backup_size / compressed_backup_size AS decimal(5,2)) AS compression_ratio

                        , key_algorithm

                        , encryptor_thumbprint

                        , encryptor_type

                FROM    (

                                SELECT          S.database_name

                                                , MAX(S.backup_start_date) AS last_backup_start_date_time

                                                , MF.physical_device_name

                                                , S.backup_size

                                                , S.compressed_backup_size

                                                , S.key_algorithm

                                                , S.encryptor_thumbprint

                                                , S.encryptor_type

                                FROM            msdb.dbo.backupset AS S

                                INNER JOIN      msdb.dbo.backupmediafamily AS MF

                                                        ON S.media_set_id = MF.media_set_id

                                GROUP BY                S.database_name

                                                , S.type

                                                , MF.physical_device_name

                                                , S.backup_size

                                                , S.compressed_backup_size

                                                , S.key_algorithm

                                                , S.encryptor_thumbprint

                                                , S.encryptor_type

                        ) AS BH

        )

SELECT          D.name AS database_name

                , C.last_backup_start_date_time

                , C.physical_device_name

                , CAST(C.backup_size_MB AS decimal(15,2)) AS backup_size_MB

                , CAST(C.compressed_backup_size_MB AS decimal(15,2)) AS compressed_backup_size_MB

                , compression_ratio

                , C.key_algorithm

                , C.encryptor_thumbprint

                , C.encryptor_type

FROM            sys.databases AS D

INNER JOIN      CTE AS C

                        ON D.name = C.database_name

ORDER BY                C.last_backup_start_date_time

We obtain the following result, which clearly demonstrates that the encryption has no impact on the compression of the backup file size :

About the author

Nicolas Souquet is a Bangkok-based SQL Server database architect, and also a writer and speaker. He has centered his career on SQL Server databases modeling and performance tuning. With 10 years of industry experience, he has been working on all versions of SQL Server, ranging from 2000 to 2014, and has been continuously involved in several OLTP data-intensive environments. He has been rewarded SQL Server MVP in 2009, 2012 and 2013 for publishing articles on his blog (http://blog.developpez.com/elsuket/) and animating SQL Server conferences in Bangkok. He is now gaining interest in Business Intelligence implementations with SQL Server. Finally, he has participated in writing a book on SQL Server 2014 in French with two other SQL Server MVPs, Frédéric Brouard and David Barbarin, which will be released in July 2014.

 About MVP Monday

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.

Back in April 2011 Steve Jones and I launched The Mentoring Experiment. We had some ideas and some goals, but truly it was an experiment to see what we might do to drive more mentoring and mentoring relationships. We had some success at the experiments we tried, but in the end we found that it is just hard to do – mentoring relationships are absolutely about chemistry and that’s just hard to find, and our very unsophisticated matching didn’t come close to that. What we did find was that for many (even most) people a sounding board/coach/very temporary mentor can help them figure out a challenging issue quickly (a few hours of talking). Career direction, career changes, salary, we saw a lot of common themes and I think our mentors did pretty well at helping the people we matched them with.

It was a hard project because we didn’t have a good way to show results or velocity and that matters, especially when you’re the experimenter and trying to measure success and stay engaged. There’s something powerful about seeing SQLSaturday #318 – it feels like success/velocity. It’s not the same to talk about mentoring matchup #318. It’s even harder because Steve and I are both fairly intense about privacy matters and mentoring falls into that zone – we couldn’t (and wouldn’t) just list everyone who wanted a mentor or who was paired up with a mentor. We tried to figure out a way to inject mentoring into the culture more. I’ve said for a while that the one thing I’d go back and change about SQLSaturday would be to have more focus on networking, and I’d add mentoring to that, but it’s really hard to go back and change a formula, especially when it works. Mentoring is several steps harder than networking, and networking isn’t easy. We just couldn’t find a way that didn’t feel forced and didn’t have the potential to create more problems than it fixed. Think about trying to drive mentoring in a chapter meeting with 10-20 people. It’s just hard.

It’s also suffered from lack of attention over the past six months. Steve and I both have other things going on and as we prioritized, this kept getting pushed down. We wanted to continue, but clearly we were struggling, and we decided it was time to wrap this up rather than let it linger, and maybe we should have done it sooner in hindsight. Not all experiments lead to big victories. This one didn’t. At least for us, but maybe it did change the conversation some and we raised the profile of the topic. We’re grateful to our friends that answered our call for mentors and to those who applied to be mentees, taking a chance on us and an idea, and hopefully they all learned something from the experience.

It isn’t the end of our interest in mentoring. We’ll still talk about it, we’re both still interesting in helping people where we can as we’ve always done – quietly, behind the scenes, in a pay-it-forward kind of way. We’d be thrilled to see someone take up the cause, in whatever shape that might take. If you can find a way to build on what we’ve done, or use it for inspiration, or do it entirely differently and make it work better – do it! There’s a lot to be said for trying to do some good in the world.

Unless you have been making a concerted effort to ignore it, you may have heard that Microsoft would really like for you to move much of your SQL Server database infrastructure into a Microsoft data center, whether you go to an Azure SQL Database (which I recently discussed here), or whether you host it on a Windows Azure Virtual Machine. Microsoft calls these persistent virtual machines “compute instances”, and they have two main tiers to choose from, which include the Basic Compute Tier and the Standard Compute Tier. They describe these two tiers as:

Basic Compute Tier: This new tier of compute instances is similar in configuration to the Standard tier with lower prices. These instances do not include load balancer and auto-scaling. They are well-suited for single instance production applications, development workloads, test servers and batch processing applications that might not require these features. The basic compute tier is currently available only for the General Purpose Instances. These instances range from Basic A0 to Basic A4.

Standard Compute Tier: This tier of compute instances provides an optimal set of compute, memory and IO resources for running a wide array of applications. These instances include both auto-scaling and load balancing capabilities at no additional cost. The standard compute tier is available across General Purpose, Memory Intensive and Compute Intensive instances. These instances range from Standard A0 to Standard A7.

There are several important advantages to hosting your SQL Server infrastructure on a Windows Azure Virtual Machine. First, you have no capital costs for storage or hardware, along with no ongoing maintenance of the storage or hardware. Second, you have no OS or SQL Server license costs (when you use a SQL image from the standard Azure VM gallery). Third, you can create a new Azure VM that already has SQL Server installed in a few minutes (even though it may take a little longer to completely configure the OS and the SQL Server instance to your exact requirements). Going forward, it will still be up to you to install Windows and SQL Server updates, but you won’t have to worry about things like firmware, BIOS, or driver updates.

If you want to use Windows Azure Virtual Machines to host all or part of your SQL Server infrastructure, you should be aware of the current pricing details that are available for the specific Azure data center that you want to host your virtual machines, since pricing can vary across different Microsoft data centers. Microsoft currently has 13 different Windows Azure virtual machine sizes, as detailed in their Virtual Machine and Cloud Service Sizes for Azure page. Microsoft reduced the hourly pricing for the memory intensive instances (Standard A5, Standard A6, and Standard A7) by 18% in most of their data centers on May 1, 2014, and the pricing shown in Table 1 reflects those new, lower prices.

The Single-Core Score and the Multi-Core Score in the two right-hand columns of Table 1 are the average scores that I observed using the 32-bit version of the Geekbench 3.05 processor and memory benchmark on a sample Windows Server 2012 R2 Datacenter VM in the East U.S. Data Center. These scores may or may not be representative of what you will see.

Table 1: Selected Virtual Machine Specifications for Windows Azure in the East U.S. Data Center

Currently, Microsoft has nine data centers that can host new persistent virtual machines, which include East U.S., West U.S., Brazil South (Preview), North Europe, West Europe, East Asia, Southeast Asia, Japan West, and Japan East. According to Microsoft, “A1 is the smallest size recommended for production workloads”, and you should select “a virtual machine with 4 or 8 CPU cores when using SQL Server Enterprise Edition”. One useful, if somewhat dated reference for running SQL Server on a Windows Azure Virtual Machine is the Performance Guidance for SQL Server in Windows Azure Virtual Machines that was published in June, 2013.

Windows Azure Virtual Machine Characteristics

When you look at the CPU properties on the Performance tab in Windows Server 2012 R2 Task Manager (in Figure 1 and Figure 2), you will notice that it reports that it is using a relatively old, 45nm AMD Opteron 4171 HE processor, running at a speed of 2.1GHz. This particular six-core processor was introduced in June of 2010, as part of the two-socket Lisbon family. The HE suffix means that it is a “low-powered” energy efficient model which is not a good choice for SQL Server usage, since it gives up a significant amount of performance for a relatively small amount of reduced energy usage. After doing some digging, I have been told that this processor is a special OEM processor for Microsoft data centers.

Figure 1: Basic A0 Virtual Machine in East U.S. Data Center

Figure 2: Standard A7 Virtual Machine in East U.S. Data Center

The other big issue with this processor besides its age and relatively poor single-threaded performance is the fact that it only has six physical cores. This is a problem with the Basic A4, Standard A4, and Standard A7 VM sizes, which have two NUMA nodes and eight total physical cores. This would mean that a VM of that size would cross a NUMA node on the underlying physical host, which is not a good idea for memory performance. I have a hard time believing that Microsoft would do this on purpose. I also have a hard time believing that every single Azure VM in every single data center that I have tried so far is using the exact same elderly AMD processor. It is fairly common knowledge that Microsoft has at least three different generations of hardware (Gen 1, Gen 2, and Gen 3) that they have used so far in their Azure data centers. After some more inquiries, I have discovered that this AMD Opteron 4171 HE processor is an Azure Gen 2 processor.

You can browse the Geekbench 3 online database of uploaded benchmark results, looking for systems using the AMD Opteron 4171 HE processor here. You may notice that every single result for this processor seems to be for a Microsoft Virtual Machine, which is also quite curious. Windows Server 2012 R2 Task Manager is reporting the L1 cache as “N/A” and not even listing the L2 and L3 cache sizes on these Azure VMs. Another curious piece of evidence is the fact that the Standard Instances have about 50% higher Geekbench 3 scores than the equivalent Basic Instances when they have the exact same total processor core counts and memory sizes, for both the Single-Core score and Multi-Core score. This much of a variance does not make any sense if the underlying host machine is actually using the same processor.

Summary

All of this evidence initially led me to the conclusion that Microsoft was probably obscuring the actual processor in the host machine. I thought they might be doing this to try to prevent people from purposely provisioning multiple VMs until they happen to get a VM is running on newer, faster, host hardware. It turns out that Microsoft is not quite that clever. I have been assured that Microsoft does not alter the identity of the CPU in an Azure VM. There are newer Azure Gen 3 processors that you may get in an Azure VM, as you provision new VMs in the future. Another possible reason for my results was that they are likely using some sort of governance to limit VM performance to a reliable, uniform level, regardless of the underlying host hardware, so that they can host more VMs on less hardware over time. This would be a smart course of action for an IaaS hoster.

The relatively low Geekbench 3.05 scores (see Figure 3) for even the largest Azure VMs means that you are giving up a significant amount of processor and memory performance compared to an equivalent physical two-socket server with the same number of processor cores and memory.

Figure 3: 32-bit Geekbench 3.05 Results for Standard A4 Virtual Machine in East U.S. Data Center

Many SQL Server workloads will run perfectly well with this level of processor and memory performance, albeit a little slower than you may be used to. If you factor in the SQL Server 2014 Enterprise Edition license savings from an eight-core machine, plus the capex for a modest, two-socket server and its associated storage, you could afford to run a Standard A7 virtual machine 24×7 for about five to six years. Given that sort of ROI, I can see many organizations making the economic decision to move at least a portion of their SQL Server infrastructure to Azure Virtual Machines. As long as your workload can run on a 56GB or smaller VM, and as long as having less CPU and memory performance than a typical recent vintage laptop is also acceptable, this is a rational course of action.  Microsoft recently announced the availability of larger, much faster A8 and A9 VM Compute Intensive Instances, that use Intel Xeon E5-2670 processors. This will be a huge improvement in performance over the Azure Gen 2 processors.

I’ll be taking a look at I/O performance in Azure Virtual Machines in an upcoming article.

The post Comparing Windows Azure Virtual Machine Performance appeared first on SQLPerformance.com.

Ray was not just a recent addition to the development team. She was also a recent grad just getting started in her career. As a result, she was eager to glean as much knowledge as she could from any source she could find. She poured over all of the documentation provided to her by her team lead. She read the wiki (because that's the storehouse of communal knowledge for every team), reviewed the code and examined the diagrams of the data models used within the application.

All was going swimmingly...until she came across the following.

She hunkered down, trying to figure out what going on. The names of the tables really didn't help out much. I mean, sure. There is a Header table. With fifteen variations on the fSID field. And there are a whole bunch of SetSizes tables. Each of which is...er...nicely numbered. Because the distinction between tblSetSizes_13 and tblSetSizes_6 is readily apparent. Right???

And to top it off, notice how the names of the SetSizes tables have been aligned with the fSID fields. So that fSID1 lines up with the tblSetSizes_2. All in the name of avoiding confusion by the developer.

After spending enough time to ensure that the purpose of the diagram wasn't something blinding obvious (or opaquely obvious), she walked over to her team lead's desk.

"Ahhhh. I see you found it.", he said.

"Found it??"

"Yes. My masterpiece. I spent more than a few hours on that diagram getting everything defined and lined up properly"

Ray had a confused look on her face.

"I even had to set the size of the tables so they would all fit on the screen."

"Set...the size??"

"Yes. That's why I named them like I did."

"I don't think I understand. What is this structure for?"

"It's not for anything. It's art."

Ray paused. "Art?"

"Yes. I like the way it looked. The symmetry. The layout and contrasting colors. I like it, so, I kept the diagram around to preserve it".

"Okay. Thanks. I guess", said Ray.

"No problem. Come back any time you need to have something explained."

Ray retreated back to her desk. Afraid of what she might find, Ray didn't really have the enthusiasm to dig into more of the project code. Or database diagrams. But she did start to think about how long she would have to stay in this job before it wouldn't look bad on her resume if she left. Or maybe she should just start looking now. By using a little artistic license, she could just pretend this job never actually happened.

[Advertisement] BuildMaster 4.1 has arrived! Check out the new Script Repository feature and see how you can deploy builds from TFS (and other CI) to your own servers, the cloud, and more.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Presenter: Jason Strate

In the past, developing a database was a simple affair where you built and deployed the database for a server down the hall in your datacenter, or in some cases to a server under someone's desk. While those good ole days were a much more simple time, they were quite limiting in how a database could be deployed and there were much fewer options for ensuring stability and performance. Today, SQL Developers have many more options for deployment including the services provided through Windows Azure. In this session, we'll dive into what you need to know to start developing databases with Windows Azure and discuss the value that cloud delivery can provide to your database development.

Click here to view the full webinar!

Follow-Up Questions:

Q: Can you also create that BACPAC from within Visual Studio?

Yes, provided I am understanding the questions correctly. You can use SQL Server Data Tools, within VS, to create BACPACs

Q: If you want to learn SQL Azure, how does the pricing work? I was a little confused with how charges are incurred, I assume that it's not by each query you execute, etc..

For SQL Database, pricing is based on the size of the database and the network throughput that is incurred.  The compute per query isn't calculated, just the throughput of data after.  You can find more information here - http://azure.microsoft.com/en-us/pricing/details/sql-database/

As far as getting started goes, if you have an MSDN account, you can actually get lower that then published rates.

Q: How do you troubleshoot problems on the azure db, like querying the DMVs and Extended events, where do you save the file or what other option is there?

Tracing similar to on-premise databases is not available yet.  There are DMVs though that can provide a lot of what is needed.

Q: I have SS 2012 on my machine, when i Create a project in Data Tool I am just seeing SQL Server 2005 & SQL Server 2008 under Project Version

You have to download SQL Server Data Tools seperately from the Visual Studio and SQL Server installs.  The tools can be found here - http://msdn.microsoft.com/en-us/data/tools.aspx

Q: Wouldn't you have to migrate from Cloud 2012 to Cloud 2014 to XXX?

No, changes are introduced across the SQL Database SaaS platform on a regular basis.  The idea is that as you own and use the service, it is continuously improving.

My third Pluralsight course, Working with Temporal Data in SQL Server, is published. I am really proud on the second part of the course, where I discuss optimization of temporal queries. This was a nearly impossible task for decades. First solutions appeared only lately. I present all together six solutions (and one more that is not a solution), and I invented four of them. http://pluralsight.com/training/Courses/TableOfContents/working-with-temporal-data-sql-server

Read more of this story at Slashdot.

I’ve just returned home after teaching two weeks of SQLskills Immersion Events. Total post-event exhaustion for two days. Trying to produce some nice demos on the new SQL Azure Database tiers and services for my SQL Azure Database A-Z preconference ssession at SQLTuneIn. And then this happens…

About a year or so ago, after attending a TechEd talk on XEvents in Azure SQL Database (ASD, the PaaS offering), I looked for and found metadata and blogged about it. The feature never seemed to come to fruition and after a while, I stopped looking.

This morning, I received email from Egbert Schagen, a person I’d met in my conference travels. He let me know that XEvents now appeared to work in ASD. They appeared in the SSMS object explorer. Egbert said he’d seen no announcement for it, and by web search he came across my old blog entry. He thought I might like to know. And he was right.

And so they DO work. I tried this in an Standard S1 Edition database I had and Egbert said they also worked in Web/Business edition. As with almost everything ASD, these event sessions exist at a database level, rather than in master.

There are 12 pre-configured event sessions. It doesn’t look like you can create your own. The sessions are:
azure_xe_activity_tracking*
azure_xe_errors_warnings
azure_xe_object_ddl
azure_xe_post_query_detail
azure_xe_pre_query_detail
azure_xe_query
azure_xe_query_batch*
azure_xe_query_detail*
azure_xe_query_execution*
azure_xe_query_waits*
azure_xe_query_waits_detail*
azure_xe_waits

The event sessions with an asterisk can be turned on and you can “Watch Live Data” in SSMS. The rest of them look like they require a credential because they write to Azure blob storage. More on that as I try and figure through this with the metadata. It appears you can only run one event session at a time. The second concurrent one always produces a “system is currently busy” message on attempting to start it.

I’m not sure what the “official” status is, but for now it looks like you *can* trace in Azure SQL Database. Great! Thanks, ASD developers. Thanks, Egbert.

@bobbeauch

The post Extended Events In Azure SQL Database. For real. appeared first on Bob Beauchemin.

UPDATE: Last month (Sept 2015), Extended Events (not just the metadata that I wrote about) are officially available in Azure SQL Database!! For more information, here’s the main documentation article.

I’ve just returned home after teaching two weeks of SQLskills Immersion Events. Total post-event exhaustion for two days. Trying to produce some nice demos on the new SQL Azure Database tiers and services for my SQL Azure Database A-Z preconference ssession at SQLTuneIn. And then this happens…

About a year or so ago, after attending a TechEd talk on XEvents in Azure SQL Database (ASD, the PaaS offering), I looked for and found metadata and blogged about it. The feature never seemed to come to fruition and after a while, I stopped looking.

This morning, I received email from Egbert Schagen, a person I’d met in my conference travels. He let me know that XEvents now appeared to work in ASD. They appeared in the SSMS object explorer. Egbert said he’d seen no announcement for it, and by web search he came across my old blog entry. He thought I might like to know. And he was right.

And so they DO work. I tried this in an Standard S1 Edition database I had and Egbert said they also worked in Web/Business edition. As with almost everything ASD, these event sessions exist at a database level, rather than in master.

There are 12 pre-configured event sessions. It doesn’t look like you can create your own. The sessions are:
azure_xe_activity_tracking*
azure_xe_errors_warnings
azure_xe_object_ddl
azure_xe_post_query_detail
azure_xe_pre_query_detail
azure_xe_query
azure_xe_query_batch*
azure_xe_query_detail*
azure_xe_query_execution*
azure_xe_query_waits*
azure_xe_query_waits_detail*
azure_xe_waits

The event sessions with an asterisk can be turned on and you can “Watch Live Data” in SSMS. The rest of them look like they require a credential because they write to Azure blob storage. More on that as I try and figure through this with the metadata. It appears you can only run one event session at a time. The second concurrent one always produces a “system is currently busy” message on attempting to start it.

I’m not sure what the “official” status is, but for now it looks like you *can* trace in Azure SQL Database. Great! Thanks, ASD developers. Thanks, Egbert.

@bobbeauch

The post Extended Events In Azure SQL Database. For real. appeared first on Bob Beauchemin.

I have written a SQL Server Change Data Capture GUI interface to facilitate for CDC operations. The project is hosted here https://sqlcdcapp.codeplex.com/. Feel free to download, review and suggest changes. You can even download the source code and implement things too.

This blog describes how to download, install and use the application.

Installation

The application doesn't require installation. Download and extract the zip file. Execute SQLCDCApp.exe to get started.

Enable CDC on a database

To enable CDC on a database, first connect to SQL server and list all databases as shown below.

Click on button “Get Databases” to list available databases as shown below.

Select a database to enable CDC and click “Enable CDC” button. Similarly, to disable CDC on a database, select it from the grid and click “Disable CDC”.  Multiple databases can be selected to enable/disable CDC.

Enable CDC on a table

Click “Get Tables” button shown in above image to list tables for selected databases as shown below.

Select table/tables from the grid and click “Capture Table” to enable CDC or “Disable CDC” to disable CDC on selected tables with default values. Specify custom options if required.

View Change Data

To view change data, select a CDC enabled table from the grid and click “Get Changed Data” button. A new window will open as shown below.

Select the captured instance to view change data, then select the type of data to view by checking on of the radio buttons. Click “GO” button display data as shown below.

Export Change Data

The data can be exported to  

-          A comma delimited (.txt/.csv) file.

-          A table in a database

Export data to a file

Select “Export to file” option and click “Export” button. A dialog box will appear as shown below

Select the file name to save data to and then click “Save”. The data will be written to selected file.

Export data to database

Select “Export to database” and connect to the server to export data to. Select the database and table to export data to. Select “Create New” in the table combo box to create a new table as shown below.

 A new window will open. Provide the new table name and click “GO” button as shown below.

Once done, click “Export” button. This will create the new table in the selected database and will populate it with the change data.

Cheers!!!

Ahmad 

@_ahmadosama

If you like this post do like us on facebook.

All our events: http://www.sqlservergeeks.com/default-category/events

Our spam-free, 24x7 moderated FaceBook group: www.facebook.com/groups/theSQLgeeks

Our FaceBook page: www.facebook.com/SQLServerGeeks

Our awesome gallery: http://www.facebook.com/SQLServerGeeks/photos_albums

And a must watch, our video - http://www.youtube.com/watch?v=EAFhDwXxlzM

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Computers do... funny things to people. With alarming frequency, capable, adult professionals of all stripes just can't seem to overcome the most basic obstacles involved in the everyday practice of computing. When your grandmother struggles to understand that sending email doesn't require postage stamps, it's kind of endearing; when it's your co-workers, who grew up with computers just like you did and use them every day to do every aspect of their jobs, it's... less so. That so many people still fail so frequently leads one to wonder: can everyone really be this feckless, or are we, the creators of the software they struggle to master, just incapable of creating something usable?

Sometimes, the answer is emphatically the former.

Tazza worked for a company that subtitled live television, and, as such, had staff working on a round-the-clock basis. While the twenty four-hour support desk dealt with on-air problems and operational issues, Tazza was the main point of escalation for issues that were harder to classify. Bright and early one morning at 4 AM, Tazza received a call from the support desk.

"Hi Taz," Jordan yawned down the line, "how's your morning?"

"Have coffee, will travel," Tazza said. "Guess your shift's almost over?"

"Thank God. So, look, Mary's trying to prep subs for the 5 AM slot, but she says Titleist isn't working since it was upgraded to the new version yesterday."

"Hmm, that's weird. I'm pretty sure it wasn't upgraded yesterday. I'll take a look, thanks. Get some sleep."

Tazza fired up LogMeIn and tried to connect to Mary's laptop. It seemed to be offline, which was pretty serious if she was trying to finish subs for the next time slot. Taz dialed her number.

"Hi, Mary. I can't connect to your laptop, which might explain the problems you're having with Titleist. Can you access the internet?"

"Hmm..." Mary murmured, her mouse clicking in the background. "How do I check that?"

"Just go to google.com, or any other site that you don't already have open."

"Okay, that's working! Thanks!"

"Wait, wait... I haven't done anything yet. And I still can't see you online. Let me send you an IM."

After a moment, Tazza heard the tell-tale ding! from Mary's side. "Ok, I got it!"

"Okay. Under the Lync chat window there's a couple of buttons. Can you click on the monitor button and share your desktop with me so I can see what's up?"

"I don't have those buttons," Mary said. "Someone changed the Lync software on my laptop and removed all the other stuff, too."

"Uh..." Tazza stared at the screen blankly for a minute, trying to work out what could have actually happened. The subtitlers' laptops were pretty mission-critical; no one would just update or remove a bunch of software from them at random. It was time for a sanity check.

"Ok, Mary, thanks for bearing with me. Next, please click the Start button, type C-M-D and press Enter. In the black window that appears, type "HOSTNAME" and press Enter. What does it say?"

"Let's see... Oh! LAP-JENNYSMITH. What's that mean?"

Tazza took a deep breath. "It means you're using Jenny's laptop, not yours. Jenny isn't a subtitler, so she doesn't have the same software on her computer. Where's your laptop?"

"No, see, this is my laptop, just someone changed everything on it."

"Please humour me, Mary. Do you take your laptop home or do you leave it in the office?"

"I leave it here, but—"

"Okay, so where in the office did you leave it yesterday?"

"Over on the other desk..."

"Would you just take a quick look over there, please?"

"Oh! There it is!"

Taz sighed, already contemplating his second coffee break. "Would you like me to stay on the line to see if it's okay?"

"No, thanks! I'll be fine. I better go get the five o'clock titles done!"

"Have a nice day, Mary."

[Advertisement] BuildMaster 4.1 has arrived! Check out the new Script Repository feature and see how you can deploy builds from TFS (and other CI) to your own servers, the cloud, and more.

Read more of this story at Slashdot.

Editor’s note: The following post was written by SharePoint MVP Steve Smith

This article deals with the subject of ‘Aligning skills with real world business benefits’ and why continued investment in technology skillsets is even more important today than it ever was. We will be looking at the importance of practical training alongside real world skills and aligning it all with qualifications and how a company as well an individual or team would benefit in both the short and long term after being through this process.

After many years in the education space especially around Microsoft products there is no doubt that the products themselves have evolved into much more complex platforms. The knowledge and skills that we developed in the nineties and early 2000 certainly provided a solid foundation for the core skills needed in today’s world. It is also more important than ever to have our end users trained and supported on these products, historically IT would deploy the product such as email and our users would get little if no formal training on how to use the products themselves. As the product range has evolved so has the benefit to the users and the business, but only if they all know how to get the most from it.

But what if you are fairly new to this brave new world of Microsoft technologies what skills am I talking about and why are they still so important?

To read the full post, click here

[Updated]

I rebuilt my Hyper-V virtual machines / virtual network by deleting the old ones and building new machines on a portable external hard drive. So after a while of not starting up the host laptop, I could not start any of the machines. First, I noticed that the state of the machines on the Hyper-V Manager now says “Off-critical.” I am getting this connection error: Failed to lock virtual machine’s configuration. Unspecified error (0x80004005).   I checked the Hyper-V Setting and I found out that the folder path of the Hard Disks and Virtual Machines files has been changed to its default setting. I’m not sure why but that’s exactly what happened:  (This has nothing to do with this error. See correction at the bottom of this post.) Needless to say, I just needed to change that to the appropriate path, which is my external hard drive:  The real issue in this case is the Hyper-V not recognizing the external hard drive because of the fact that the Hyper-V Virtual Machine Management Service  was started before the external hard drive was attached via the USB.

I could have simply restarted the service or the laptop after I attached the external hard drive, and that would have fixed the issue.  After the change, I just performed a quick a restart and that fixed the connection issue:

CORRECTION: The path setting described above has nothing to do with this connection error. The Hyper-V Setting path, illustrated above, has nothing to do with the Hyper-V configuration not finding the correct path. This path setting tells Hyper-V where you want the succeeding virtual machines stored. For more information on this “Failed to lock virtual machine’s configuration” error, please read: Why is my virtual machine “Off-Critical”?