quinta-feira, 30 de junho de 2016

[From Technet] Celebrating with Hadoop and Red Hat communities

This post was authored by Joseph Sirosh, Corporate Vice President of the Data Group at Microsoft

This week I had the opportunity to represent Microsoft in keynotes at both the Hadoop and Red Hat Summits in San Jose and San Francisco, and during that time speak to many customers and members of the open source and big data communities. The energy in these communities is incredible and I’m proud to see Microsoft engage as an active participant and contributor. Below I want to share a quick summary of recent news related to both these events and communities.

Hadoop Summit

This year we celebrated 10 years of Hadoop and along with the rest of the community we’re working hard to make big data easier for customers, no matter the type of data, what they need to do or what platform they’re on. At Hadoop Summit, Hortonworks also announced Microsoft Azure HDInsight as its Premier Connected Data Platforms solution to deliver Apache Hadoop in a cloud environment. This announcement culminates a long-standing partnership with Hortonworks that started in 2011 when Hortonworks was three-months-old. It’s been rewarding to see how far Hadoop has come, and it’s now deployed in thousands of organizations. As an example, Jet.com is using Hadoop with HDInsight to help redefine the e-commerce category by providing consumers with completely transparent pricing that dynamically changes based on the actual costs of the transaction – warehouse location, payment method, and number of items shipped.

Looking back at our journey with Hadoop, it’s also been gratifying to see our contributions accelerate its adoption. Members of Microsoft have been contributing to the development of Apache YARN since its inception. We’ve also been leading or contributing to projects like bringing Hadoop onto Azure and Windows, speeding up the query processing of Hive, making cloud-based stores accessible via WebHDFS, and making Spark execution available through a REST endpoint. Recently, we also announced our commitment to Apache Spark at Spark Summit 2016 including:

  • Spark for Azure HDInsight General Availability, is a fully managed Spark service from Hortonworks that is enterprise ready and easy to use
  • R Server for HDInsight in the cloud powered by Spark, in preview today and will be generally available later this summer makes Spark integration easy no matter if you are working on premises or in the cloud.
  • R Server for Hadoop on-premises now powered by Spark, as the leading solution in the world to run R at scale, R Server for Hadoop now supports both Microsoft R and native Spark execution frameworks made available this week. Combining R Server with Spark gives users the ability to run R functions over thousands of Spark nodes letting you train your models on data 1000x larger and 100x faster than was possible with open source R and nearly 2x faster than Spark’s own MLLib.
  • Free R Client for Data Scientists, a new free tool for data scientists to build high performance analytics using R.
  • Power BI support for Spark Streaming, General Availability, Spark support in Power BI now allows you to publish real-time events from Spark Streaming.

With our investments in R combined with Spark & Hadoop, statisticians and data scientists can rapidly train a variety of predictive models on large-scale data, limited only by the size of their Spark clusters. With Spark, R Server’s compiled code algorithms and transparent parallelization of regression, clustering, decision trees and other statistical algorithms speeds analysis 100x faster on terabytes of data.


Red Hat Summit

At Microsoft, we’re serious about building an intelligent cloud through a comprehensive approach that includes the open source ecosystem. Today, our cloud offerings range from support for Linux in Azure Virtual Machines – and nearly 1 in 3 VMs are running Linux today – to a Hadoop solution in HDInsight, or deep integration of Docker Swarm and Apache Mesos in Azure Container Service that represent our commitment to the ecosystem and highlight the value of our partnerships. In November, Microsoft and Red Hat announced a partnership to add value to the open source investments in the enterprise. At Red Hat Summit, we announced a number of important partnership milestones, including:

  • The general availability of .NET Core 1.0 and ASP.NET Core 1.0, a platform for creating modern applications for Windows, Linux and Mac OS X
  • In partnership with Red Hat and 21Vianet, this week we are extending support for Red Hat Enterprise Linux to Azure China operated by 21Vianet
  • Red Hat is making CloudForms 4.1 generally available, with deep support for Azure including state analysis, metrics, chargeback and retirement, making Azure the best supported cloud in CloudForms
  • Availability of a new OpenShift solution template on GitHub that makes it simple to deploy OpenShift in Azure

In March, we announced our plans to bring SQL Server to Linux, starting with a private preview. In the research note Microsoft Diversifies With Linux Support for SQL Server, Gartner wrote “SQL Server on Linux represents a bold statement that the company understands there is more to the overall IT world than just Windows and this flexibility is necessary to compete in the DBMS market.” Today, at the Red Hat Summit, I will show SQL Server running on Red Hat Enterprise Linux. Our goal is to make SQL Server the platform of choice to support any data, any application, on-premises or in the cloud, and providing you with platform choice. Bringing SQL Server to Red Hat Enterprise Linux will provide enterprise Linux customers with SQL Server’s mission-critical performance, industry-leading TCO, the least vulnerable database,[1] and hybrid cloud innovations like Stretch Database to access data on-premises or in the cloud.  We’ll first release the core relational database capabilities on Linux targeting mid-calendar year 2017.

MongoDB on Microsoft Azure

Additionally, this week, MongoDB announced MongoDB Atlas, a new elastic on-demand cloud service that will provide comprehensive infrastructure and management for its popular database. MongoDB Atlas will become available for Azure customers via a strategic partnership between the two companies. This partnership with MongoDB further reinforces Microsoft’s commitment to providing customers with open source solutions and the most comprehensive cloud platform on the market.

– Joseph

[1] National Institute of Standards and Technology, National Vulnerability Database statistics as of 2/1/2016.

from SQL Server Blog http://ift.tt/299agRf

[From Technet] SQL PowerShell: July 2016 update

This post was authored by Ayo Olubeko, Program Manager, Data Developer Group.

The July update for SSMS includes the first substantial improvement in SQL PowerShell in many years. We owe a lot of thanks for this effort to the great collaboration with our community. We have several new CMDLETs to share with you, but firstly, there is a very important change we had to make to be able to ship monthly updates to the SQL PowerShell component.

Historically, SQL Server’s PowerShell components are included in both the SSMS (tools) installer as well as with the SQL Server engine install. In order to be able to ship SQL PowerShell update, we had to change the identity of the SQL PowerShell module as well as the wrapper known as SQLPS.exe. This change has an impact to scripts doing Import-Module.

This month we introduce CMDLETs for the following areas:

  • Always Encrypted
  • SQL Agent
  • SQL Error Logs

Additionally, we have made some nice improvements to Invoke-SqlCmd and the SQL provider.

New SQL PowerShell module

As alluded to above, in order to ship monthly updates, we have created a new SQL PowerShell module as well as have introduced a new wrapper EXE that SSMS uses to instantiate the SQL PowerShell environment. The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Agent). This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required. Additionally, if you happen to have a script that is using a 3rd-party or community version of a module named SqlServer, you should add use of the Prefix parameter to avoid name collisions.

The motivation for these changes is that the tooling components are being moved to be “application local” and not share any components with the SQL Server engine. This is an important step to enable monthly tooling updates while not negatively impacting the components setup and updated by the SQL Server setup program.

SSMS has been updated to integrate with SQLTOOLSPS.exe rather than SQLPS.exe. Hence, if you launch PowerShell from within SSMS, it will launch PowerShell and configure the session with the new SQL PowerShell module. It is advised to avoid using these EXE wrappers; they exist for legacy reasons within SSMS and are likely to be removed in a future monthly update.

The new version of SQL Server PowerShell included with SSMS does not update the version of PowerShell used by SQL Server. This means that scripts executed by SQL Agent will not be able to use the new CMDLETs. Updates to SQLPS (the version used by SQL Agent) will be done through the traditional SQL Server update mechanisms; more specifically, major changes will be done as part of the next major version of SQL Server as it becomes available.


In the July SSMS update, you will find several new CMDLETs. Once again, we owe thanks to our SQL PowerShell community leaders for helping us prioritize these investments. The CMDLETs all provide help within PowerShell for detailed information. As with the majority of the features in SSMS, the SQL PowerShell CMDLETs work against all supported versions of SQL Server. In some cases, such as Always Encrypted, the CMDLETs obviously only work on versions of SQL Server that support that specific feature set.

CMDLET Description
Add-SqlAzureAuthenticationContext Performs authentication to Azure and acquires an authentication token.
Add-SqlColumnEncryptionKeyValue Adds a new encrypted value for an existing column encryption key object in the database.
Complete-SqlColumnMasterKeyRotation Completes the rotation of a column master key.
Get-SqlColumnEncryptionKey Returns all column encryption key objects defined in the database, or returns one column encryption key object with the specified name.
Get-SqlColumnMasterKey Returns the column master key objects defined in the database, or returns one column master key object with the specified name.
Invoke-SqlColumnMasterKeyRotation Initiates the rotation of a column master key.
New-SqlAzureKeyVaultColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in Azure Key Vault.
New-SqlCngColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store supporting the Cryptography Next Generation (CNG) API.
New-SqlColumnEncryptionKey Crates a new column encryption key object in the database.
New-SqlColumnEncryptionKeyEncryptedValue Produces an encrypted value of a column encryption key.
New-SqlColumnEncryptionSettings Creates a new SqlColumnEncryptionSettings object that encapsulates information about a single column’s encryption, including CEK and encryption type.
New-SqlColumnMasterKey Creates a new column master key object in the database.
New-SqlCspColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store with a Cryptography Service Provider (CSP) supporting Cryptography API (CAPI).
Remove-SqlColumnEncryptionKey Removes the column encryption key object from the database.
Remove-SqlColumnEncryptionKeyValue Removes an encrypted value from an existing column encryption key object in the database.
Remove-SqlColumnMasterKey Removes the column master key object from the database.
Set-SqlColumnEncryption Encrypts, decrypts or re-encrypts specified columns in the database.
Get-SqlAgent Returns a SQL Agent (JobServer) object that is present in the target instance of the SQL Server.
Get-SqlAgentJob Returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent.
Get-SqlAgentJobHistory Returns the JobHistory present in the target instance of SQL Agent.
Get-SqlAgentJobSchedule Returns a JobSchedule object for each schedule that is present in the target instance of SQL Agent Job.
Get-SqlAgentJobStep Returns a SQL JobStep object for each step that is present in the target instance of SQL Agent Job.
Get-SqlAgentSchedule Returns a SQL JobSchedule object for each schedule that is present in the target instance of SQL Agent.
Get-SqlErrorLog Retrieves the SQL Server Logs.
Set-SqlErrorLog Sets or resets the maximum number of error log files before they are recycled.

Invoke-SqlCmd improvements

Invoke-SqlCmd now supports an OutputAs parameter (or its alias -As). This parameter allows you to specify DataRows, DataTables or DataSet as the object type to return. These types map to the .Net types you find in System.Data. DataRows is the default, and corresponds to the old behavior.

Additionally, we added the ConnectionString parameter which allows the script author complete control over the connection context. This unlocks new capabilities such as connecting to SQL Azure using Azure Active Directory authentication.

SQL PowerShell provider enhancements

The SQL PowerShell provider now properly supports the WhatIf and Confirm parameters. This allows you to see the potential impact of a script operation and have the ability to confirm an operation before it is executed.

from SQL Server Blog http://ift.tt/295svcU

quarta-feira, 29 de junho de 2016

[From Technet] SQL Server 2016: Broader access, better reporting, faster analytics

We want to make it easier for businesses to use their data. Otherwise, what’s the point? In SQL Server 2016 you’ll find a virtual tool chest full of features, all with one primary goal: unlocking your data and helping you create new ways of analyzing, visualizing and sharing it.

Building data-rich biz apps

The advent of machine learning and natural language processing made it easier to analyze unstructured data. The challenge was effectively integrating it with structured data, leading to more meaningful discoveries.

Enter SQL Server PolyBase, a feature in SQL Server 2014 that was specific to the Microsoft Analytics Platform System, through which you could access data in a Hadoop Distributed File System. With SQL Server 2016 we cut the strings, making it possible to query data in Hadoop, as well as Azure Blob Storage. Now you can combine the results of your findings with relational data stored in SQL Server.

But that’s just the beginning. PolyBase becomes a data-rich foundation upon which to build powerful business applications. It dynamically creates columnar tables for your structured data, parallelizes the extraction of data from Hadoop and Azure—even pushing data to Hadoop clusters for additional processing.

Meanwhile, on the front end users can continue using their apps, consuming data and discovering new insights, all without needing to understand the finer points of data base management.

You can install PolyBase in one of two ways: using the SQL Server Installation Wizard or from the command-prompt window. See below for an example of the installation script.

Installing PolyBase

Subsequently, you’ll also need to configure to PolyBase to connect either to Hadoop or Azure Blog Storage, using one of the following values:

  • 0 Disable Hadoop connectivity
  • 1 Hortonworks HDP 1.3 on Windows Server and blob storage
  • 2 Hortonworks HDP 1.3 on Linux
  • 3 Cloudera CDH 4.3 on Linux
  • 4 Hortonworks HDP 2.0 on Windows Server and blob storage
  • 5 Hortonworks HDP 2.0 on Linux
  • 6 Cloudera 5.1 on Linux
  • 7 Hortonworks 2.1 and 2.2 on Linux, Hortonworks 2.2 on Windows Server and blob storage

To achieve optimum app performance, even when dealing with larger datasets, consider creating a PolyBase scale-out group. PolyBase scale-out groups include one or more compute nodes, each of which includes a database engine and database movement service, as well as a head node that includes the SQL Server database engine, the PolyBase engine service and the PolyBase movement service. The following diagram gives you a better sense of how PolyBase scale-out groups can keep the data flowing.

Polybase scale out groups

Advanced analytics, at scale

For the first time, SQL Server 2016 gives you the ability to work directly with R, introducing a range of new capabilities around advanced analytics, data exploration and modeling.

SQL Server R Services provides a platform for developing intelligent applications that uncover new insights. You can use the rich and powerful R language and the many open source packages to create models and generate predictions using your SQL Server data. Because SQL Server R Services integrates the R language with SQL Server, you can keep analytics close to the data and eliminate the costs and security risks associated with data movement.

With SQL Server R Services you can tap into the robust capabilities of R and combine them with a comprehensive set of SQL Server tools and technologies that offer superior performance, security, reliability and manageability. You can deploy R solutions using convenient, familiar tools, and your production applications can call the R runtime and retrieve predictions and visuals using Transact-SQL. With Enterprise Edition, you also get the Scale R libraries to overcome R’s inherent performance and scale limitations.

SQL Server R Services

To store a procedure you must first serialize it as a hexadecimal string, which is sent to the server and stored in a varbinary(max) column. Check out the sample script:

Storing an R procedure

Making data mobile

These days, there is so much that can be done with a smart phone and an Internet connection. As such, SQL Server 2016 Reporting Services has made it easier to build concise and consumable mobile reports. And with the Power BI mobile apps you can view, interact with and share data through the Power BI dashboard and SQL Server Reporting Services web portal. Check out this Power BI blog post for the unique capabilities of Power BI for iOS, Power BI app for Android phones and Power BI mobile app for Windows 10.

With the Mobile Report Publisher you can create reports based on shared data sources. An extensive array of charts, gauges, grids and other visuals help users gain greater clarity when consuming data via their mobile device. And with navigator elements, users can filter data based on time, date, location or other pre-determined values.

SQL Server Mobile Report Publisher

When designing a report, the Mobile Report Publisher optimizes the report for mobile devices and automatically populates it based on simulated data, allowing you to review with your business user, find the right layout before you connect the report to the data. Once ready, you can import data either from a local file, or from a report server. The first time you select the report server, the “Connect to a Server” prompt appears, at which point you’ll need to enter the following information:

  • In the Server Address Box: <servername>/reports, where servername is the name of the server hosting Reporting Services.
    NOTE: If the report server is not configured to use Secure Socket Layer, clear the Use Secure Connection box.
    NOTE: If the data set uses a database login, or if you want to use a different Windows account, clear the Use Current Windows Account box and supply the appropriate credentials.


SQL Server 2016 offers a range of new features that transform information into insight and help your employees to make more decisive and strategic decisions, wherever they are. To learn more about the features in SQL Server 2016, download the SQL Server 2016 e-book, or visit the  SQL Server 2016 product page.

from SQL Server Blog http://ift.tt/29di08x

segunda-feira, 27 de junho de 2016

[From Technet] Microsoft SQL Server Tooling team hosts Ask Me Anything session

This post was authored by Vin Yu, Program Manager, Data Platform.

The Microsoft SQL Server Tooling team will host a special Ask Me Anything session on /r/SQLServer, Thursday, June 30th, 2016 from 10:00 am to 3:00 pm PDT.

What’s an AMA session?

We’ll have folks from across the Microsoft SQL Server Tooling engineering team available to answer any questions you have. You can ask us anything about SQL Server tooling or even our team!

Why are you doing an AMA?

We like reaching out and learning from our customers and the community. We want to know how you use SQL Server tools and how your experience has been. Your questions provide insights into how we can make developing and managing SQL Server better. If this AMA session turns out to be useful, we may start doing this on a regular schedule.

Who will be there?

You, of course! We’ll also have PMs and Developers from the SQL Server Tooling engineering team participating throughout the day. Have any questions about the following topics? Bring them to the AMA.

  • Database Tools for Microsoft SQL Server, Azure SQL Database, Azure Virtual Machines with SQL Server
  • SQL Server Management Studio (SSMS)
  • SQL Server Data Tools / Visual Studio (SSDT)
  • SQL Server Reporting Services (SSRS)
  • SQL Server Integration Services (SSIS)
  • SQL Server Analysis Services (SSAS)
  • SQL Server Migration Assistant (SSMA)
  • PowerShell with SQLPS/Command Line Tools
  • Azure Portal for Azure SQL DB, Azure Elastic Database Pools or Azure Virtual Machines with SQL Server

Why should I ask questions here instead of StackOverflow, MSDN or Twitter? Can I really ask anything?

An AMA is a great place to ask us anything. StackOverflow and MSDN have restrictions on which questions can be asked while Twitter only allows 140 characters. With an AMA, you’ll get answers directly from the team and have a conversation with the people who build these products and services.

Here are some question ideas:

  • What’s new in SQL Server Reporting Services?
  • How do I provide feedback for SSMS and SSDT?
  • What tools would I use to migrate my database to SQL Server?
  • What’s a cool trick you don’t think most customers know about?

Go ahead, ask us anything about our public products or the team. Please note, we cannot comment on unreleased features and future plans.

Join us! We’re looking forward to having a conversation with you!

from SQL Server Blog http://ift.tt/28W2SX1

quinta-feira, 23 de junho de 2016

[From Technet] Powering mission-critical performance with SQL Server 2016

Data has become the lifeblood of the enterprise. It’s the foundation for keen insight and effective decisions that lead to business growth. One of our primary design goals for SQL Server 2016 was to provide the performance, security, availability and business intelligence that are critical to helping companies manage their data and identify new opportunities.

Over the next three weeks, we’ll give you a few snapshots of some key features in SQL Server 2016 and how you can use them to drive sustained mission critical performance, get deeper insights from your data and benefit from hyperscale cloud capabilities.

Run queries up to 100 times faster

With SQL Server 2016 we enhanced the In-Memory columnstore capabilities, which accelerate highly concurrent workloads by transferring data to memory-optimized tables. With these enhancements in place, you can run queries up to 100 times faster than previously possible.

In addition, we made memory-optimized tables more scalable—able to store up to 2 TB of data each, and support bigger workloads.

To get started, evaluate your workloads using a new feature in SQL Server Management Studio (SSMS), the ability to generate migration checklists

To do this, right-click a database in Object Explorer, point to Tasks, and then select Generate In-Memory OLTP Migration Checklists. This step launches a wizard that displays a welcome page. On the second page of the wizard, specify a location in which to save the checklist and whether to generate a checklist for all tables and stored procedures in the database or for a specific list that you define. After you make this selection, the next page of the wizard includes a Script PowerShell Commands button and a Finish button. If you select the Script PowerShell Commands button, a text file opens to display the following command:

Save-SqlMigrationReport -Server ‘<Server Instance Name>’ -Database ‘AdventureWorks’ -FolderPath ‘C:\Users\<User>\Documents\<Path>’

When you click the Finish button, the wizard begins to generate a separate checklist for each table and stored procedure specified in the wizard. The status of each checklist is displayed in the table so that you can easily see whether any failed. After the wizard completes the checklists, you can find them as HTML files in the Stored Procedures, Tables, or User Defined Functions folders in the output path that you configured in the wizard.

Real-time, operational analytics

SQL Server 2016 also includes options for real-time analysis of datasets that are more dynamic in nature. With added support for columnstore indexes in memory-optimized transactional tables, you can avoid issues around latency and benefit from real-time analytics capabilities that live up to the name.

Batch execution mode has also been improved so results can be processed up to 1,000 rows at a time, greatly reducing execution time and the utilization of CPU resources. Use SSMS to get started, or add a clustered columnstore index to a disk-based table using a T-SQL statement such as the sample below.

T-SQL script sample

Better security built in

SQL Server 2016 comes with a number of new security features built-in, helping lock down your data at all levels and states:

  • Always Encrypted protects data at rest and in motion by requiring the use of an Always Encrypted driver when client applications to communicate with the database and transfer data in an encrypted state. Without the encryption key, which is kept at the client side rather than in SQL Server, the data is useless.
  • Row-Level Security enables you to protect the data in a table row-by-row, so a particular user can only see the rows to which they are granted access.
  • Dynamic data masking obfuscates a portion of the data to anyone unauthorized to view it. Use one of four functions to protect the data returned by a query:
    • Default: Fully masks string data, numeric and binary values, date and time
    • Email: Partially masks email addresses and the length of an email
    • Partial: Partially masks values using a custom definition
    • Random: Fully masks numeric values with a random value, specified by you

Configuring a table for Always Encrypted can be done using SSMS or with T-SQL, which involves a two-step process:

  1. Create the column master key definition.
  2. Create the column encryption key.

A bit like the outer defenses of a castle, the column master key protects the various column encryption keys that actually encrypt the data within a table. You can create a master key with SSMS, though T-SQL offers a more repeatable process, which starts with a CREATE COLUMN MASTER KEY statement, such as the example shown below.

Example 2.1

Database engine upgrades

Under the hood of SQL Server 2016 are a number of improvements to the data engine that are designed to help companies optimize day-to-day performance, while providing a scalable, efficient solution to manage data growth.

For example, rather than only creating one data file to support TempDB, where the essential, secondary work takes place to maintain peak performance of the data engine, the SQL Server 2016 setup wizard adapts to your server environment. It automatically assigns the number of data files (with a maximum of eight), based on how many processors it detects on your server, thus minimizing the likelihood of any lag in performance.

TempDB dialog box

Driving down costs with hybrid

The cost and logistical challenges of storing and managing data has many companies looking to the cloud as a viable option. With SQL Server 2016 we introduced Stretch Database, a hybrid cloud feature that combines the power of Azure SQL Database with the feasibility and familiarity of an on premises version of SQL Server. You get all of the enterprise-grade security and data management features, along with a virtually inexhaustible amount of storage on the back end for cost-effective historic data availability, and it works with both Always Encrypted and Row Level Security.

Stretch DB diagram

When you enable Stretch Database, a new Stretch Database is created in Azure, as well as an external data source in your instance of SQL Server and a remote endpoint for the database. You can rest assured that user queries cannot be issued against the remote database, and the Stretch Database is protected by several security measures, including encryption and certificate validation.

You can also monitor Stretch Database from a dashboard in SSMS, making it easier to manage the entirety of your data, ensuring that is secure and accessible.


The ability to harness and analyze data has become essential to the success of today’s enterprise. SQL Server 2016 gives you all of the tools you need to manage your company’s data and offers a secure, scalable platform to help power the next generation of business apps. Download the SQL Server 2016 e-book to learn more, or visit the SQL Server 2016 product page.

from SQL Server Blog http://ift.tt/28ZCCwB

quinta-feira, 9 de junho de 2016

[From Technet] Check out the SQL Server sessions we’ve planned for Microsoft Ignite

What do you want to learn at Microsoft Ignite 2016?

  • Do you want to learn how to accelerate SQL Server 2016 to the max?
  • Get practical techniques for database management?
  • Strengthen your defenses against hackers?
  • Discuss real-world questions with product experts, in person?
  • Challenge your strategic thinking?

Perfect. All of that—and more—is waiting for you in Atlanta. The full session catalog is now live, so all you have to do is start exploring which sessions you want to attend.

Here’s a sample of SQL Server topics you’ll find…

Accelerate SQL Server 2016 to the max: lessons learned from customer engagements

The SQLCAT team worked with several customers as part of the Microsoft SQL Server 2016 Preview Program. Hear about how those customers were able to target specific bottlenecks and overcome them to far exceed their performance and scalability targets with SQL Server 2016. Deriving from actual customer engagements, we detail the most common scenarios, “tools of the trade,” and most importantly the “secret sauce” used in those situations.

Make it real with Always Encrypted, Stretch Database and Temporal Tables

So you’ve heard you can keep as much data as you want indefinitely with minimal effort and cost. You’ve also discovered you can secure you data with strong cryptography at all times. You even learned about how to time travel with your data. So how exactly do you design, deploy and manage a database application that can do all that without an army of experts? Find out from the crew that built Always Encrypted, Stretch Database and Temporal Table so you can be that expert. We also share recommended practices, known limitations and workarounds. This session is not about flashy demos; it focuses on practical implementation details you can use when you get back.

Enable operational analytics in SQL Server 2016 and Azure SQL Database

See how Microsoft SQL Server 2016 enables you to run analytic queries on in-memory and disk-based OLTP tables with minimal impact on business critical OLTP workloads, requiring no application changes. This session covers various configurations and best practices for achieving significant performance gains with Operational Analytics.

And that’s just a start…

With more than 440 sessions to choose from, which sessions will you attend?

> Explore SQL Server sessions <

Remember: September 26-30 is approaching fast. We’re excited to see everyone soon—and if you haven’t yet registered, don’t delay! Register for Microsoft Ignite.

from SQL Server Blog http://ift.tt/1XImCVK

[From Technet] WideWorldImporters: The new SQL Server sample database

This post was authored by Jos de Bruijn, Senior Program Manager, SQL Server.

A release the magnitude of SQL Server 2016 deserves a new sample. AdventureWorks, which has been around since the SQL Server 2005 days, has had a good run, but it is time for an upgrade.

Wide World Importers is the new sample for SQL Server. It illustrates how the rich SQL Server feature set can be used in a realistic database. It does not attempt to use every single SQL Server feature, as that would not be realistic. It also showcases the key SQL Server 2016 capabilities and performance enhancements.

Latest release of the sample: wide-world-importers-release

Documentation for the sample: wide-world-importers-documentation

The sample is structured as follows:

  • WideWorldImporters is the main database for transaction processing (OLTP – OnLine Transaction Processing) and operational analytics (HTAP – Hybrid Transactional/Analytics Processing). Here are some examples of the use of SQL Server capabilities with this database:
  • WideWorldImportersDW is the main database for data warehousing and analytics (OLAP – OnLine Analytics Processing). The data in this database is derived from the transactional database WideWorldImporters, but it uses a schema that is specifically optimized for analytics. Here are some examples of the use of SQL Server capabilities with this database:
  • A SQL Server Integration Services (SSIS) package, Daily ETL.ispac, is used to move data from the OLTP database WideWorldImporters to the OLAP database WideWorldImportersDW. The package is designed to use bulk T-SQL statements wherever possible to enhance performance. For details about the ETL workflow, see the WideWorldImportersDW ETL workflow.

The sample also includes a number of scripts that can be used to explore some of the features used in the sample database. In addition, the sample includes two workload drivers, which are small applications that simulate a workload running against the database.

The databases contain sample sales and purchases data starting January 1st, 2013, until May 31st, 2016. The sample includes procedures to generate more sample data, so you can always bring the sample up to the current date. For details, see WideWorldImporters data generation.

The data size is limited to keep the download size reasonable. If you desire a sample database with a larger data size, use the source scripts to create a new sample database, and tweak the parameters in step 6 to increase the data size.

Get started

  1. Download and install a free trial of SQL Server 2016 or configure a test environment using an Azure SQL VM.
    • The Developer Edition is free if you sign up for the free Visual Studio Dev Essentials program.
  2. Download the sample and review the sample documentation.

Send any feedback on the sample to: sqlserversamples@microsoft.com.

from SQL Server Blog http://ift.tt/1UDXlF0

[From Technet] Get a head start on SQL Server 2016 with this how-to guide

For the past several months we’ve been talking about the advances in SQL Server 2016. Now, at last, the wait is over. SQL Server 2016 is now generally available and brings with it a feature set designed for the modern-day enterprise, in which data can be located in a variety of locations, both on-premises and in the cloud.

To get a taste of the most advanced version of SQL Server yet, download a free trial version of SQL Server 2016. And to help you get the most out of this release, download the SQL Server 2016 e-book, for which we enlisted the help of some of the industry’s leading experts and SQL Server community MVPs:

  • Stacia Varga is a consultant and trainer with more than 30 years’ experience helping companies generate business data that leads to improve business processes and better decisions. Stacia has also been a frequent presenter at Microsoft events, including PASS and DevConnections.
  • Denny Cherry is a seasoned designer and administrator of SQL Server solutions. In addition to having a deep and wide knowledge of SQL Server products and services, Denny has extensive experience with hardware, based on nearly 20 years’ experience as a consultant and DBA.
  • Joseph D’Antoni is a specialist in the design and architecture of database, infrastructure, storage, hybrid cloud, and disaster recovery solutions. During his 15 years’ of IT experience, Joseph has worked on a variety of platforms, including SQL Server, Oracle, Hadoop and others.

Stacia, Denny and Joseph joined forces to write “Introducing Microsoft SQL Server 2016,” a practical guide to developing mission-critical apps and hyperscale BI solutions, which lead to deeper business insights. You’ll find in-depth discussions on virtually every facet of SQL Server 2016:

Chapter one: Faster queries

Get up to speed on how to perform real-time transactions and ultra-fast analytics using new features like in-memory transaction processing and in-memory updateable column stores.

Chapter two: Better security

Explore three new principal security features in SQL Server 2016—Always Encrypted, Row-Level Security and Dynamic Data Masking, and learn how to use each one of these features to its fullest potential.

Chapter three: Higher availability

Learn how to deploy more robust, highly available data management solutions using the new features in AlwaysOn Availability Group and AlwaysOn Failover Cluster.

Chapter four: Improved database engine

Find out how to optimize query performance, how to take advantage of hybrid cloud architectures, and how to stay in control amidst growing volumes of data.

Chapter five: Broader data access

Discover how SQL Server 2016 can help you manage, maintain, integrate and import data of all types, leading to richer business insights.

Chapter six: More analytics

Learn how to more quickly build secure, advanced analytics solutions, how to create predictive models with R functions, and how to generate better business outcomes by incorporating these models into application and reporting tools.

Chapter seven: Better reporting

Explore the new data visualization and layout options in Reporting Services, and how Mobile BI lets employees easily access business intelligence, whether in the office or on the road.

Chapter eight: Improved Azure SQL Database

Learn more about Microsoft Azure SQL Database and how features such as elastic database pools and rapid development cycles make it a natural complement to your on-premises DB solution.

Chapter nine: Expanding your options with Azure SQL Data Warehouse

Find out how to exponentially increase your data management and analytics capabilities, without the usual overhead.


In addition to the insight you’ll receive from Stacia, Denny and Joseph, the e-book also includes sample code and links to MSDN videos, articles and other resources where you can get additional guidance on tasks such as creating, dropping and altering scalar user-defined functions, or how to set security parameters for a Stretch Database.

To get started, download a free trial of SQL Server 2016 and the SQL Server 2016 e-book, or visit the SQL Server 2016 product page to learn more.

E-book download

from SQL Server Blog http://ift.tt/1TXL3bg

segunda-feira, 6 de junho de 2016

[From Technet] Microsoft announces major commitment to Apache Spark

This post was authored by Tiffany Wissner, Senior Director of Data Platform Marketing.

This week in San Francisco, thousands of people are at Spark Summit, to explore and understand how they leverage Apache Spark to get the most out of big data. Building on our previous investments, today we are announcing an extensive commitment for Spark to power Microsoft’s big data and analytics offerings including Cortana Intelligence Suite, Power BI, and Microsoft R Server:

  • Spark for Azure HDInsight General Availability, previously announced as public preview, Spark for Azure HDInsight generally available today, and introducing a fully managed Spark service from Hortonworks that has been hardened for the enterprise and made simpler for you to use. You can also rely on the industry’s highest availability service level agreement for Spark at 99.9%. You can get value out of Spark immediately with out-of-the-box integration with Jupyter, the most popular open source notebook for data scientists.
  • R Server for HDInsight in the cloud powered by Spark, previously announced as public preview, R Server for HDInsight will be generally available in the summer making the Spark integration available both on-premises and in the cloud. This makes it easy to move code and projects to the cloud with a few clicks and within a few minutes without buying hardware or hiring specialized operations teams typically associated with big data infrastructure.
  • R Server for Hadoop on-premises now powered by Spark, as the leading solution in the world to run R at scale, R Server for Hadoop will support both Microsoft R and native Spark execution frameworks available in June. Combining R Server with Spark gives users the ability to run R functions over thousands of Spark nodes letting you train your models on data 1000x larger and 100x faster than was possible with open source R and nearly 2x faster than Spark’s own MLLib.
  • Power BI support for Spark Streaming, previously announced with Power BI General Availability, Spark support in Power BI is now expanded with new support for Spark Streaming scenarios. This allows you to publish real-time events from Spark Streaming directly into one of the fastest growing visualization tools in the market today.

It’s an exciting time for Spark users and for R users alike. We have much more to tell you and we’ll look forward to seeing you this week at Spark Summit in San Francisco so come by our booth or join Joseph Sirosh, corporate vice president, Microsoft for his keynote on Wednesday, June 8 at 9:20 AM PT.

It promises to be an exciting week! And if you’re unable to join us in person you can get lots more information on today’s announcement on our apache-spark and r-server sites. Also come and visit us back at this blog where we’ll be sharing additional insights on our news announcements.

from SQL Server Blog http://ift.tt/1t1HERT

quarta-feira, 1 de junho de 2016

[From Technet] SQL Server 2016 is generally available today

This post was authored by Joseph Sirosh, Corporate Vice President, Data Group, Microsoft.

Today we announced the general availability of SQL Server 2016, the world’s fastest and most price-performant database for HTAP (Hybrid Transactional and Analytical Processing) with updateable, in-memory columnstores and advanced analytics through deep integration with R Services. Software applications can now deploy sophisticated analytics and machine learning models in the database resulting in 100x or more speedup in time to insight, compared to deployments of such models outside of the database.

A new platform for intelligent applications

The integration of advanced analytics into a transactional database is revolutionary. Today a majority of advanced analytic applications use a primitive approach of moving data from databases into the application tier to derive intelligence. This approach incurs high latency because of data movement, doesn’t scale as data volumes grow and burdens the application tier with the task of managing and maintaining analytical models. And deep analytics on real-time transactions are next to impossible without a lot of heavy lifting.

SQL Server 2016 simplifies analytics in the way databases simplified enterprise data management, by moving analytics close to where the data is managed instead of the other way around. It introduces a new paradigm where all joins, aggregations and machine learning are performed securely within the database itself without moving the data out, thereby enabling analytics on real-time transactions with great speed and parallelism. As a result, analytical applications can now be far simpler and need only query the database for analytic results. Updating machine learning models, deploying new models, and monitoring their performance can now be done in the database without recompiling and redeploying applications. Furthermore, the database can serve as a central server for the enterprise’s analytical models and multiple intelligent applications can leverage the same models. It is a profound simplification in how mission critical intelligent applications can be built and managed in the enterprise.

A good example of how our customers are benefiting from the new model comes from PROS Holdings, Inc., a revenue and profit realization company that helps B2B and B2C customers achieve their business goals through data science. PROS Holdings uses SQL Server 2016’s superior performance and built-in R Service to deliver advanced analytics more than 100x faster than before, resulting in higher profits for their customers. Royce Kallesen, senior director of science and research at PROS says, “Microsoft R’s parallelization and enhanced memory management on the server integrated with SQL Server provides dramatically faster results on a common platform with built-in security.”

Eliminating the need to move data out of the database for analytics dramatically reduces the latency for insights. For example, Microsoft Dynamics AX, a cloud-scale online ERP offering, gains real-time insights by using a non-clustered columnstore index on their transactional tables to reduce aggregation latency from hours to seconds.

SQL Server 2016 comes with several features and tools to support cross-platform analytics. Polybase allows you to run queries on external data in Hadoop or Azure blob storage. It can push computation to Hadoop where appropriate, so that your analytical application can join and integrate data from big data stores with the data in the relational store. Microsoft R Services, which is integrated with SQL Server also runs on multiple Hadoop distributions and is also integrated with Azure HDInsight + Spark, enabling both choice and standardization in developing analytics code. And finally, R Tools for Visual Studio allows the ease of use of the modern Visual Studio IDE for developing analytical code in R.

Benchmark leadership through efficient use of modern hardware

SQL Server 2016 has ground-breaking performance optimizations and efficiencies, leading to new levels of performance and scale. Modern servers can support a large number of cores with sophisticated vector instructions, can hold terabytes of memory, and provide very high I/O bandwidth with local flash storage. Optimizing for the concurrency and parallelism inherent within such servers can provide dramatic speedups at scale, and often outperform large distributed databases.

For example, Microsoft recently collaborated with Intel to demonstrate stunning performance on a massive 100TB data warehouse using just a single server with four Intel Xeon E7 processors and SQL Server 2016. The system was able to load a complex schema derived from TPC-H at 1.6TB/hour, and it took just 5.3 seconds to run a complex query (the minimum cost supplier query) on the entire 100TB database. The system also demonstrated incredible concurrent query performance, where running all queries concurrently took less time than running them back-to-back, as illustrated in the chart below. This is a feat of scale, performance and efficiency that no other database has achieved to date.


Industry standard database benchmarks further support SQL Server’s performance leadership. It is the only database that is simultaneously the leader in the industry standard transaction processing benchmark TPC-E, and the data warehouse benchmark TPC-H (non-clustered TPC-H 30TB, 10TB, 3TB, 1TB scale factors). For customers, this means incredible value compared to other databases, and the opportunity to consolidate both OLTP and Data Warehouse workloads into a simpler single system, with significant savings in total cost of ownership.

Large performance improvements from prior versions

Our focus for SQL Server 2016 was to deliver great value for our customers with dramatic improvements while keeping the price the same. Simply by upgrading to SQL 2016, many customer workloads experienced large performance gains. For example, the 3TB TPC-H benchmark on the same hardware demonstrated a forty-eight percent performance gain1.

Our customers and partners have realized great benefits by upgrading. Some examples:

RealtyTrac, a provider of housing data and analytics, used an Always-On availability group spanning on-premises and Microsoft Azure to provide high availability and disaster recovery for their data warehousing. By offloading queries to the secondary replicas, RealtyTrac achieved huge performance improvements: “We saw up to ten times speed improvement on some of the extraction processes. SQL Server 2016 in a hybrid-cloud environment with Azure really cuts through all the logistics of managing large data sets,” said Richard Sawicky, chief data officer, RealtyTrac.

Saxo Bank is one of the leading players in global online trading. SQL Server 2016 will enable the bank to use enhanced columnstore on a wider array of tables for even faster analysis. “Using clustered columnstore indexes created huge savings in storage, allowing us to use up to eighty percent less disk space for tables. With SQL Server 2016, we have seen queries running up to two times faster on top of the 10x query performance gains from adopting columnstore” said Francesco L’Erario, Data Warehouse Architect, Saxo Bank.

M-Files, a popular SaaS EIM vendor, has its most valuable data in the SQL Server database. “Real-time operational analytics in SQL Server 2016 lets us run the analytical queries from the same data source. We experienced an immediate 10x boost in our most critical queries, with virtually no impact on the operational workload. Now we have our ‘analytics database’ in the single data source, with ground-breaking performance, automatically updated by SQL Server within the same transaction boundaries as our operational workload. It’s an unbelievable advantage to us,” said Antti Nivala, CTO and founder of M-Files.

SQL Server 2016 features were first released in Microsoft Azure and stress-tested in real-world through over 1.7 million Azure SQL DB databases. It is battle-tested and dozens of our customers have already been running on-premises production workloads on SQL Server 2016 prior to its general availability, a strong testament to its maturity as a product.

Unmatched security record

Solid security is an essential requirement for any business application. Databases contain a significant portion of sensitive corporate data and intellectual property. If database security is compromised, the entire business is potentially at risk.

For six years running2, SQL Server has had the least vulnerabilities of any of the major database platforms, according to the statistics compiled independently by the National Institute of Standards and Technology (NIST), the government agency that monitors security vulnerabilities by technology, vendor, and product. Now SQL Server 2016  takes security capabilities even further: Always Encrypted helps protect data at rest and in memory, Transparent Data Encryption encrypts all user data now with low performance overhead, and Dynamic Data Masking (DDM) and Row Level Security (RLS) allow developers to build applications that restrict access and protect data.

For example, DocuSign helps organizations build entire approval workflows without a single sheet of paper or filing cabinet in sight, so security and reliability are critical, as they are with every business today. Using SQL Server 2016 for internal telemetry and monitoring, DocuSign monitors customer experiences across the globe to identify and address issues quickly. DocuSign customers rely on them for mission critical workflows and DocuSign relies on SQL Server for their entire business. “We’ve picked SQL Server for our platform, because of its performance and ability to manage all types of data. With the largest banks, insurers and other financial institutions counting on us, we need to deliver secure software for our environment. New features in SQL Server 2016 like Always Encrypted allow us to deliver on this promise,” says Eric Fleischman, chief architect and vice president of platforms.

Unmatched value proposition

SQL Server 2016 ships as a comprehensive data platform with everything built-in. The powerful database engine with in-memory capabilities also includes unique features such as JSON support for building modern apps, Stretch Database for reducing storage costs while keeping data available for querying in Azure, significantly faster Geospatial query support, Temporal Tables for traveling back in time and a Query Store for ensuring performance consistency. We bundle in powerful data integration capabilities with SQL Server Integration Services (SSIS), Data Quality Services (DQS) and Master Data Services (MDS). We provide world-leading data warehousing capabilities, closely integrated with advanced analytics and machine learning through R. We provide a world-leading Business Intelligence engine with Tabular and Multidimensional models. We deliver end-to-end mobile BI solution on any device and you can add on self-service Power BI for a fraction of the total cost. The table below shows a simple competitive comparison that highlights the value of the SQL Server package.


Today customers can deliver intelligent applications that are faster performing, more secure than Oracle, and at less than one-tenth of the total cost of using Oracle to run the same transactional, data warehouse, data integration, business intelligence and advanced analytics workloads3. With our ongoing promotion to help more customers adopt SQL Server 2016, customers currently running applications or workloads on a non-Microsoft paid commercial RDBMS platform can migrate their existing applications with free SQL Server licenses.

Both industry analysts and customers recognize the breadth and depth of Microsoft’s capabilities in data, intelligence and the cloud. Microsoft is the only company recognized as a leader across data platforms and cloud by Gartner in both vision and execution in database, business intelligence, advanced analytics, data warehouse, cloud infrastructure and cloud application platforms, and the furthest in vision and ability to execute within the Leaders Quadrant  in the Gartner Magic Quadrant for Operational DBMS.

Gartner Magic Quadrant for Operational Database Management Systems, October 20154

The quickest way to experience the magic of SQL Server 2016 is to create a test environment using an Azure SQL VM. You can also experience the full features through the free developer edition. Visit the SQL Server 2016 webpage to gain insights about new features and download the SQL Server 2016 e-book.

With new capabilities for analytics, record-breaking performance, in-memory processing, unmatched security and an unbeatable value proposition, SQL Server 2016 is the best data platform available to gather and manage data, apply analytics that generate deep insights, and translate those insights into mission critical production. Adopt it today to transform your business with data driven intelligence.



1Comparison of TPC-H 3TB benchmark created using SQL Server 2016 (http://www.tpc.org/3319) to TPC-H 3 TB benchmark created using SQL Server 2014 on the same Lenovo System x3850 X6 (http://www.tpc.org/3313)

2National Institute of Standards and Technology, National Vulnerability Database statistics as of 2/1/2016, https://nvd.nist.gov/


4The above graphic was published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner document is available upon request. Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings. Gartner research publications consist of the opinions of Gartner’s research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.

from SQL Server Blog http://ift.tt/1Y2U08e