quarta-feira, 20 de julho de 2016

[From Technet] Microsoft drivers 4.0 for PHP for SQL Server with PHP 7.0 support released

Dear PHP Community,

We wanted to extend a massive ‘thank you’ for providing feedback for our preview releases over the last few weeks. We’ve been working hard to incorporate the feedback you have provided us. You will find that we’ve fixed many issues you reported, and we are proud to be able to release the final build of our 4.0 drivers. We will continue to fix bugs and ship regular updates to the GitHub repository. The new driver enables access to SQL Server 2008+, Azure SQL Database and Azure SQL DW from any PHP 7 application.

The major highlights of this release include: support for SQL Server 2016, PHP7, bug fixes, and better test coverage.

Improvements from our previous release:

  • Fixed a heap corruption when binding parameters in a prepare statement with error
  • Fixed leaks in SQLSRV streams and output parameters handling
  • Fixed leaks in SQLSRV fetch object
  • Fixed leaks in SQLSRV binding object parameters
  • Fixed leaks in SQLSRV buffered result set
  • Fixed leaks in SQLSRV getting datetime and stream fields
  • Fixed leaks in PDO_SQLSRV field cache
  • Fixed leaks in PDO_SQLSRV construct when connecting with error
  • Fixed leaks in PDO_SQLSRV exception handling

We will continue to make bug fixes and adding new features on your feedback on GitHub.

Future plans

Going forward we plan on improving the current Linux port, expand SQL 16 Feature Support (example: Always Encrypted), build verification/fundamental tests, and bug fixes reported on GitHub.

Getting the product ready for release

You can find the latest bits on our Github repository, at our existing address. We provide support for any bugs reported on our Github Issues page. As always, we welcome contributions of any kind, be they Pull Requests, or Feature Enhancements. Additionally, you can also get the pre-packaged exe. from the Download Center.

I’d like to thank everyone on behalf of the team for supporting us in our endeavors to provide you with a high-quality driver. Happy downloading!

Meet Bhagdev (meetb@microsoft.com)


from SQL Server Blog http://ift.tt/2agGe1C

segunda-feira, 18 de julho de 2016

[From Technet] SQL Server 2016 posts world record TPC-H 10 TB benchmark

SQL Server 2016 delivers unparalleled performance and security built-in for your most mission critical transactional systems and data warehouses, along with an integrated business intelligence and advanced analytics solution for building intelligent applications.  Blazing-fast performance is key to ensuring you can deliver a flawless transactional experience while at the same time support demanding real-time operational analytics over the data as fast as the data is coming in.

Recently, Lenovo announced the number one TPC-H 10TB benchmark world record1 using SQL Server 2016 and Windows Server 2016 on Lenovo System x3850 X6 using the latest the latest Intel Xeon E7 processor technology. In May 2016, Lenovo also published a new number one TPC-H 30TB world record2 using SQL Server 2016 and Windows Server 2016 on Lenovo System x3950 X6. These results, in addition to recent benchmarks by software and hardware partners, as well as key applications, show that SQL Server 2016 is the fastest in-memory database on the planet for your applications.3

SQL Server 2016 owns the top TPC-E performance benchmarks4 for transaction processing, the top TPC-H performance benchmarks for data warehousing, and the top performance benchmarks with leading business applications. 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. KPMG, a leader in audit, tax, and advisory solution, posted 2.5x faster execution time with ten times the table compression with their solution using SQL Server 2016.

Customers can also gain tremendous performance improvement by simply upgrading to SQL Server 2016 without application changes (e.g. queries will run up to 34x faster)5. In addition to leading performance benchmarks, SQL Server 2016 also delivers top price/performance for both workloads providing customers with significantly reduced total cost of ownership.

Easily experience SQL Server 2016 by creating a test environment using an Azure SQL VM. You can also experience the full features through the free developer edition (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2016 Developer Edition). Visit SQL Server 2016 to learn more about new features and download the SQL Server 2016 e-book.

SQL Server 2016 performance


1Non-clustered TPC-H 10TB. http://ift.tt/29P8jHJ. http://www.tpc.org/3325.

2Non-clustered TPC-H 30 TB. http://ift.tt/29Vo2Zw. http://www.tpc.org/3321.

3Learn more about how your organization can scale to handle the increasing amount of data being stored in modern data warehouses by reading the Intel whitepaper entitled “Accelerating Large-Scale Business Analytics,” which illustrates the integration of Microsoft SQL Server 2016 and Intel® Xeon® E7 platform driving advanced analytics on a large 100TB dataset.


5Based on internal tests from Microsoft, customers who upgrade to SQL Server 2016 will also experience tremendous performance gain including faster real-time analytics with up to 34x performance on in-memory columnstore queries, faster synchronization and greater availability with up to seven times faster AlwaysOn throughput, 3.6x faster reporting on AlwaysOn replicas and seven to ten times faster on database maintenance (DBCC).

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

quinta-feira, 14 de julho de 2016

[From Technet] Microsoft JDBC Driver 6.0 for SQL Server is now released!

This post was authored by Andrea Lam, Program Manager, SQL Server.

We are pleased to announce the full release of the Microsoft JDBC Driver 6.0 for SQL Server! The updated driver provides robust data access to Microsoft SQL Server and Microsoft Azure SQL Database for Java-based applications.

What’s new

Always Encrypted

You can now use Always Encrypted with the Microsoft JDBC Driver 6.0 for SQL Server. Always Encrypted is a new SQL Server 2016 and Azure SQL Database security feature that prevents sensitive data from being seen in plaintext in a SQL instance. You can now transparently encrypt the data in the application, so that SQL Server or Azure SQL Database will only handle the encrypted data and not plaintext values. If a SQL instance or host machine is compromised, an attacker can only access ciphertext of your sensitive data. Use the JDBC Driver 6.0 to encrypt plaintext data and store the encrypted data in SQL Server 2016 or Azure SQL Database. Likewise, use the driver to decrypt your encrypted data.

Azure Active Directory (AAD)

AAD authentication is a mechanism of connecting to Azure SQL Database v12 using identities in AAD. Use AAD authentication to centrally manage identities of database users and as an alternative to SQL Server authentication. The JDBC Driver 6.0 allows you to specify your AAD credentials in the JDBC connection string to connect to Azure SQL DB.

Table-Valued Parameters (TVPs)

TVP support allows a client application to send parameterized data to the server more efficiently by sending multiple rows to the server with a single call. You can use the JDBC Driver 6.0 to encapsulate rows of data in a client application and send the data to the server in a single parameterized command.

Parameterized queries

Extended support for retrieving parameter metadata with prepared statements for complex queries such as sub-queries and/or joins.

Internationalized Domain Names (IDNs)

IDNs allow your web server to use Unicode characters for server name, enabling support for more languages. Using the new Microsoft JDBC Driver 6.0 for SQL Server, you can convert a Unicode serverName to ASCII compatible encoding (Punycode) when required during a connection.

AlwaysOn Availability Groups (AG)

The driver now supports transparent connections to AlwaysOn Availability Groups. The driver quickly discovers the current AlwaysOn topology of your server infrastructure and connects to the current active server transparently.

Next steps

You can download the JDBC Driver 6.0 for SQL Server here.

Learn how to pick the right JDBC jar file based on your system requirements here and read up on more documentation here.


We are committed to bringing more feature support for connecting to SQL Server, Azure SQL Database and Azure SQL DW. We invite you to explore the latest the Microsoft Data Platform has to offer via a trial of Microsoft Azure SQL Database or by trying the new SQL Server 2016.

Please stay tuned for upcoming releases that will have additional feature support. This applies to our wide range of client drivers including PHP 7.0, Node.js, ODBC and ADO.NET which are already available.

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

terça-feira, 12 de julho de 2016

[From Technet] SQL Server 2014 SP2 is now available

The SQL Server team is excited to bring you SQL Server 2014 Service Pack 2 (SP2). This service pack is available on the Microsoft Download Center and will also be available on MSDN, MBS/Partner Source and VLSC in the coming weeks. As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments.

SQL Server 2014 SP2 includes a rollup of released hotfixes as well as more than twenty improvements centered around performance, scalability and diagnostics based on feedback from customers and the SQL community. These improvements enable SQL Server 2014 to perform faster and scale out of the box on modern hardware design, and showcase the SQL Server Team’s commitment to provide continued value into in-market releases.

SQL Server 2014 SP2 will include:

  • All fixes and CUs for SQL 2014 released to date.
  • Performance, scale and supportability improvements.
  • New improvements based on connect feedback items filed by the SQL community.
  • Improvements originally introduced in SQL 2012 SP3, after SQL 2014 SP1 was released.

Find more information on SQL Server 2014 SP2 by reading the SQL Server Release Services Blog post and What’s New in SQL Server 2014 SP2. To obtain SQL Server 2014 SP2, the options below are available:

SQL Server 2014 SP2

SQL Server 2014 SP2 Express

SQL Server 2014 SP2 Report Builder

SQL Server 2014 SP2 Master KB

SQL Server 2014 SP2 Feature Packs

SP2 Reporting Services Add-in for Microsoft SharePoint 

SP2 Semantic Language Statistics 

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

[From Technet] The elastic future of data warehousing

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

Announcing the general availability of Azure SQL Data Warehouse, an elastic, parallel, columnar data warehouse as a service.

A defining characteristic of cloud computing is elasticity – the ability to rapidly provision and release resources to match what a workload requires – so that a user pays no more and no less than what they need to for the task at hand. Such just-in-time provisioning can save customers enormous amounts of money when their workloads are intermittent and heavily spiked. And in the modern enterprise, there are few workloads that have a desperate need for such elastic capabilities as data warehousing. Traditionally built on-premises with very expensive hardware and software, most enterprise Data Warehouse (DW) systems have very low utilization except during peak periods of data loading, transformation and report generation.

With the general availability of the Azure SQL Data Warehouse, we are delivering the true promise of cloud elasticity to data warehousing. It is a fully managed DW as a Service that you can provision in minutes and scale up to 60 times larger in seconds. With a few clicks in the Azure Portal, you can launch a data warehouse, and start analyzing or querying data at the scale of hundreds of terabytes. Our architecture separates compute and storage so that you can independently scale them, and use just the right amount of each at any given time. A very unique pause feature allows you to suspend compute in seconds and resume when needed while your data remains intact in Azure storage. And SQL Data Warehouse offers an availability SLA of 99.9% – the only public cloud data warehouse service that offers an availability SLA to customers.

According to Gartner, “For years, many data warehousing vendors have been operating from a playbook of tightly balanced storage and compute configuration units. Cloud architectures are forcing a shift in this approach, with vendors starting to decouple storage and compute, and allowing them to independently scale. We believe this approach to be the correct one, and that other vendors in the space will need to adopt this methodology if they are to stay competitive.1

Azure SQL Data Warehouse uses an elastic massively parallel processing (MPP) architecture built on top of the industry-leading SQL Server 2016 database engine. It allows you to interactively query and analyze data using the broad set of existing SQL-based tools and business intelligence applications that you use today. It uses column stores for high performance analytics and storage compression, a rich collection of aggregation capabilities of SQL Server, and state of the art query optimization capabilities. With built-in capabilities such as Polybase, it allows you to query Hadoop systems directly, enabling a single SQL-based query surface for all your data.

Azure SQL Data Warehouse is also part of the Cortana Intelligence Suite, which is a fully managed big data and advanced analytics suite to transform your data into intelligent action. It easily integrates with components of the suite such as Azure Data Factory for data integration pipelines, with Azure Machine Learning for predictive analytics, Power BI for business intelligence, HDInsight for big data insights, R and Spark for big data analytics. For an example of such integration, see the airline industry sample on PowerBI.com. This shows a Power BI report based on a real world predictive maintenance solution for a major airline. The data for this report comes from a variety of sources including IoT streams from aircraft engines, air traffic control information, route restrictions and fuel usage data. All this is integrated and landed into a Azure SQL DW and processed with Azure Machine Learning to detect operational anomalies and trends.  The report is “live” and you can interact with it and experience Power BI in conjunction with Azure SQL DW and Azure ML.

The distinct capabilities of Azure SQL Data Warehouse include:

Data warehousing as a service. Gone are the pains associated with administering, managing, patching and manual tuning of data warehouses. There are no knobs to turn, no physical or virtual infrastructure to manage and the service is simple, resilient and secure with reliable storage. This enables the focus on driving the analytics and getting the value from your data rather than on managing your data warehousing software and hardware; Azure SQL Data Warehouse handles it all for you.

Unmatched security and access control. With malicious and even insider attacks becoming a key concern for enterprises, an alarm system over your critical enterprise data is a must have to avoid serious damage to your business and reputation. Only Azure SQL Data Warehouse delivers auditing and threat detection built into the service, with advanced machine learning to detect abnormal query patterns and alert you of potential security issues before it is too late. Data at rest is protected by Transparent Data Encryption.

Additionally, SQL Data Warehouse is the only cloud data warehouse service that works seamlessly with Azure Active Directory which currently supports 1.3 billion daily authentications across 600 million user accounts. This enables Single Sign-On (SSO) and role-based access control. You can even have finer-granularity permissions that let you control which operations a user can do on individual columns, tables, views, procedures, and other objects in the database. These features further protect data by ensuring just the right users have access to the right data—a critical capability when centralizing vast amounts of proprietary and sensitive data for analytics in an enterprise.

Multidimensional elasticity. Currently the majority of cloud database and data warehouse services are provisioned with fixed storage and compute resources. Resizing of resources typically compromises availability and/or performance. This means that service users typically end up with over-provisioned and expensive underutilized resources to accommodate possible peak demand or in the worst case, under-provisioned resources unable to handle sudden work overloads.

Unlike existing cloud services which can take anywhere from a couple of hours to a couple of days to do the data warehouse resizing, SQL Data Warehouse’s unique elastic technology decouples storage and compute, enabling each layer to become independently scalable almost instantaneously. This makes it possible to provision one or more data warehouses in minutes, and then independently scale users, data, and workloads in seconds to optimally match the demand. Further, elastic scaling also makes it possible to simultaneously load and query data, because every user and workload can have exactly the resources needed, without contention, and with minimal impact to production queries.

Getting featured in the iOS App Store was a big deal for a small company like ours as our users increased from 3,000 to 300,000 in 48 hours. To keep up with this 100x increase in workload, we simply added data warehouse compute capacity by moving a slider and our services just scaled in minutes—we didn’t miss an insight,” notes Paul Ohanian, CTO, PoundSand.

Save as you go, with fast pause and resume. Starting and shutting data warehouse clusters may take a considerable amount of time. Leaving the data warehouse running continuously incurs potentially high and unnecessary costs, especially if you are running your jobs periodically and the data warehouse is sitting idle in-between for extended periods of time. Now you can pause your data warehouse for the required time, saving compute costs, and quickly resume it later when needed. You can even write a PowerShell script, then automate the schedule with Azure Automation to automatically pause or resume the cluster based on the specific needs of your business.

When we learned about the pause and resume capabilities of SQL Data Warehouse and integrated services like Azure Machine Learning and Data Factory, we switched from AWS Redshift, migrating over 7TB of uncompressed data over a week for the simple reasons of saving money and enabling a more straight-forward implementation for advanced analytics. To meet our business intelligence requirements, we load data once or twice a month and then build reports for our customers. Not having the data warehouse service running all the time is key for our business and our bottom line,” said Bill Sabo, managing director of information technology at Integral Analytics.

Seamless querying of structured and unstructured data. An increasing amount of data in today’s rapidly digitizing world is unstructured data such as clickstreams, sensor data, location data, customer support emails and chat transcripts, much of which is harnessed for analysis in big data systems. The ability to integrate and join such data with your core relational enterprise data is often a highly desired capability. With built-in PolyBase technology, SQL Data Warehouse allows you to access and combine both non-relational and relational data. You can run queries on external data in Hadoop or Azure blob storage using familiar SQL, often without making any changes to the existing queries. Underneath, the queries are optimized for optimal execution without any burden on the user for tuning. Furthermore, you can quickly import and export data back and forth between relational tables in SQL Data Warehouse and non-relational data in Hadoop or Azure Blob Storage using simple T-SQL statements. The rich SQL programmability support (stored procs, functions and PolyBase) empower users to query the data however they want.

Christoph Leinemann, senior director data engineering at Jet.com says, “with Azure SQL Data Warehouse, we use PolyBase to ingest data from HDInsight then run thousands of analytical queries per day over tens of billions of records—about 20TB of data. This enables us to monitor price history and market dynamics to adjust pricing and ensure we’re offering our customers the best price.”

Integration with the SQL Server tool ecosystem you already use and love. Azure SQL Data Warehouse already fits into the tool ecosystem you already use, with native JBDC and ODBC connectors, and with a broad set of independent software vendors and partners who already support SQL Server, such as Altyrex, Attunity, Informatica, Redgate and SnapLogic. For BI capabilities, it integrates with the industry-leading Power BI service in Azure, and even with Microsoft Excel. For a beautifully visualized walkthrough of Microsoft Power BI and SQL Server 2016 Reporting Services including Mobile BI, please watch this demo. Microsoft also works with a set of popular BI partners to ensure the tools your teams use work great with SQL Data Warehouse, including Looker Data Sciences, Tableau Software and Qlik Technologies.

Experience modern data warehousing in the cloud for yourself

Today we have thousands of customers who are already using Azure SQL Data Warehouse. Many of these customers are experiencing significant performance gains over existing multi-million dollar data warehouses on-premises. With SQL Data Warehouse, some multi-hour queries in our customer environments finish now in under an hour, and some queries that took five to ten minutes now complete in seconds. Get started with SQL Data Warehouse today and experience the speed, scale, elasticity, security and ease of use of a true modern data warehouse as a service for yourself.

– Joseph


1Source: Gartner, The Data Warehouse and DMSA Market: Current and Future States, 201, June 16, 2016.

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

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