SQL Server Team Blog

Self-Service BI with the Microsoft Data Explorer Preview for Excel 2013: 5 Reasons to Get Started Right Away

SQL Server Team — Thu, 23 May 2013 16:00:00 GMT

Using a variety of data sources has been a daunting task for mere mortals. Now, with the Data Explorer add-in for Excel, you don’t need to be a super hero to unlock data gems. The Microsoft Data Explorer Preview for Excel is now available for download.

Here are the Top 5 Reasons to investigate this new add-in:

Discover the World’s Data
Connect to a wide variety of Data Sources
Combine data from multiple data sources
Reshape and transform your data effortlessly
Refresh your data anytime

Sound interesting? Head on over to the Microsoft Business Intelligence blog post, “5 Things You Need to Know about Microsoft Data Explorer Preview for Excel” for more details and become a super hero in your organization!

Now Available: HDP for Windows from Hortonworks

SQL Server Team — Tue, 21 May 2013 16:00:00 GMT

Today Hortonworks announced the general availability of Hortonworks Data Platform (HDP) for Windows, the industry’s first 100 percent Open Source Apache Hadoop-based distribution for Windows. The beta version of HDP for Windows was released two months ago at the Strata Conference in Santa Clara Hortonworks and was well received by industry press.

This is a very exciting release as it marks an important milestone in our partnership with Hortonworks and our overall big data approach. We are deeply invested in ensuring customers have choice and interoperability when building and running Hadoop-based solutions -- HDP for Windows is a direct result of the joint engineering partnership between our two companies and today’s release helps to accelerate the adoption of Hadoop in the enterprise by diversifying the number of platforms that it can run on. With HDP for Windows customers can deploy Hadoop today on Windows Server in their own data centers.

HDP for Windows offers customers a 100 percent Open Source Apache Hadoop distribution that is Enterprise-ready and interoperable across Linux, Windows Server and Windows Azure. For more details visit the Hortonworks website.

In addition, HDP for Windows offers easy migration to HDInsight: Customers can migrate seamlessly from HDP for Windows to Windows Azure HDInsight Service to take advantage of the elasticity and low cost of Hadoop in the cloud.

Download HDP for Windows today from Hortonworks and start testing. Visit the Windows Azure website for more information on Windows Azure HDInsight Service.

Microsoft Invites You to Join the Cloud Conversation!

SQL Server Team — Thu, 16 May 2013 16:00:00 GMT

The 3^rd Annual Future of Cloud Computing Survey has just been launched by North Bridge and GigaOM Research.

We are participating in this survey because your feedback on cloud computing is important to us and the industry. We want to hear about your plans for cloud implementation, where the cloud is making an impact across your software eco-system, what critical issues you might be facing around IT and business, drivers and inhibitors to cloud adoption, etc.

We invite you to be among the first to TAKE THE SURVEY and share it with your network. By doing so you will help shape the industry view of cloud computing and identify emerging trends. Results of the survey will be announced at the GigaOM Structure in June and we will be back at that time to discuss the findings.

Learn more about the Cloud OS – Microsoft’s vision for the new era, a consistent, modern platform for the world’s apps.

Companies Improve their Bottom Line by Switching to Microsoft Big Data Technology

SQL Server Team — Tue, 07 May 2013 16:00:00 GMT

Here on the SQL Server Blog, we spend a lot of time talking about the features and technical “how to” behind our products. The only thing we love more than getting into the details of our products is sharing real-world examples of how they help our customers achieve business goals. There’s been a few great examples of this recently, as customers like The Charles Machine Works, Holland America Line and Retail Solutions Inc. made the switch to SQL Server from competitors Oracle and SAP.

Being able to get to data faster can give businesses an edge in today’s fast paced, competitive environment. The Charles Machine Works (CMW), a manufacturer of underground construction equipment based in Oklahoma, turned to SQL Server in order to improve business intelligence reporting and speed access to data from its existing SAP architecture. Working with data was so difficult that most employees relied on a lone data warehouse analyst to generate reports, and waited hours – or days – for information, slowing the pace of business. Based on both cost savings and ease of use, CMW switched to a solution based on SQL Server. For about 25% of what SAP Business Objects and SAP HANA would have cost, the SQL Server solution provides more employees with access to accelerated BI analysis, meaning access to real-time data and the ability to make faster, better business decisions.

Better insights into customer behavior can be used to improve customer service. Cruise-ship company Holland America Line, based in Washington, uses information from their BI system to identify best practices onboard the ship that contribute to increased revenue and improved customer experience. Unfortunately, their Oracle BI system was so difficult to use, only a handful of analysts used the software to get data, which could vary between ships and shore staff. In order to encourage broader BI use and create a way for groups to collaborate, Holland America Line decided to switch to SQL Server 2012. Since implementing the new solution, Holland America Line has tripled employee BI use, empowered its business users to discover insights about passenger behavior, and as a result of this information, make decisions about onboard programs and offerings to enhance passengers’ experience.

Having the right technology to manage, analyze and scale to meet demanding data needs can improve a company’s bottom line. The California-based Retail Solutions Inc. provides software-as-a-service solutions for more than 500 consumer product goods suppliers and 70 retailers worldwide, with the goal of helping customers accurately forecast product supply and consumer demand, as well as evaluate supply-chain efficiency. In order to better manage 100 TB of core data as well as the 1.5 billion records it imports daily, Retail Solutions replaced its Oracle platform with Microsoft SQL Server 2008 R2. Both Retail Solutions and its customers are seeing the benefits of the switch: Customers are able to boost sales by up to 4% with the new analytics capabilities, resulting in millions of dollars in ROI, and the solution’s scalability has allowed Retail Solutions to support its rapid growth (40% annually) in recent years.

For more information on how The Charles Machine Works, Holland America Line and Retail Solutions are using SQL Server, read the full case studies (linked). And, check out the business intelligence and big data pages to learn more about Microsoft’s solutions.

Leveraging Flash across the Microsoft SQL Server Stack

SQL Server Team — Wed, 01 May 2013 16:00:00 GMT

Guest blog post by @SamMarraccini of EMC Corporation. To read more from Sam, please visit Sam’s blog at www.insideflash.com.

Flash technology is changing everything, nothing new there. Chances are the device you are reading this BLOG on contains some type of Flash memory. Within the datacenter, it has been well documented that the increasing performance capabilities of today’s CPUs have created an I/O performance gap. Processor speeds and network bandwidth have been able to keep pace with new enterprise application requirements. Spinning disk drives just can’t keep up with these new performance needs. Most performance trouble shooting today points to I/O resource requirements, or the lack thereof.

With Flash comes the promise of never needing to think about storage design; it doesn’t matter… no spinning media, no seek time, no rotational delay. It just works, right?

Flash does indeed work, but the solution isn’t quite that easy. While server side PCIe Flash can provide latency as low as 50 microseconds, you may be sacrificing database availability and service levels to gain that performance. While being able to achieve phenomenal response times, the focus of our solutions portfolio here at EMC remains consistent: Performance, Protection and Intelligence! EMCs Flash portfolio is designed to put application and database design criteria before hardware. With an “Xtrem” portfolio that includes Server Side PCIe Flash (XtremSF), Server Side caching (XtremSW Cache), Hybrid Storage arrays (VMAX, VNX), and an all new all Flash array (XtremIO)

EMC offers the right technology based on your applications and database needs. Whatever your needs, we have an Xtrem solution to match the performance and availability needs.

EMC changes the game via the first Hybrid Arrays (circa 2008)

EMC first introduced Flash technology into our enterprise storage arrays back in 2008. The hybrid array was born. Just as important now as it was then, the software designed to complement the Flash hardware. Software like “FAST” Fully Automated Storage Tiering, designed to move the most frequently referenced data to a tier of Flash drives inside the array, while lesser referenced data migrated to lower cost resources within the array. Microsoft SQL Server Datasets were none the wiser to the data movement happening within the array, just that IO response time improved significantly. More important with the hybrid solution, you still see the benefit of the enterprise storage platform (multi-site active/active hyper-v clustering for example).

EMC XtremSF (Server side PCIe Flash)

The introduction of PCIe Flash moves the storage resource as close to the CPU as possible, providing tremendous gains in IOPS (I/O per second) and reduced response time (measured in microseconds). The closer you can get to the CPU, the more performance gain you’ll see. EMCs XtremSF (Server Flash) does exactly that, moving IO as close to the CPU as possible. The EMC XtremSF 2200 can provide around 208,000 IOPS at 87 microsecond response time (70/30 read/write 4k block). WOW!!! The downside is its local storage and needs to be protected and treated as local storage.

A whitepaper from the EMC Global Solutions Team “EMC XtremSF: Delivering Next Generation Storage Performance for Microsoft SQL Server” highlights the deployment considerations for local PCIe Flash storage. The raw performance gains over local storage are incredible. The local spinning disk was able to generate 1,920 transactions while the XtremSF Server Flash card produced 58,203 (over 300x increase). (On a side note, the bottleneck moved from IO to the CPU, so the 300x increase was limited based on the hardware tested).

Obviously, XtremSF provides superior application performance, but like any other PCIe Flash device, it’s DAS (Direct Attached Storage) and lacks the HA and enterprise features DAS brings. One option is to take advantage of Microsoft SQL Server AlwaysOn, integrated HA found in Microsoft SQL Server 2012. There are lots of variables to be considered before deploying such a solution: The volume of transaction logs to be shipped, the distance between the primary and secondary sites and the quality of the interconnect or log replication network between sites. A hybrid approach is possible by implementing XtremSF as local storage for the primary site while leveraging an enterprise storage array at the target. The enterprise array can then provide the secondary copy while cloning features can be used to create reporting instances and copies of the production data.

EMC XtremSW Cache (Cache Software)

So, you can have the Xtrem performance of XtremSF Server Flash (Local Storage), or the enterprise functionality, protection and performance of an EMC VMAX or VNX Hybrid storage array, right? Wrong, you can have BOTH!! How? EMC XtremSW Cache, host based intelligent caching software leveraging local Flash devices to accelerate performance while maintaining database protection in the underlying storage array. XtremSW Cache software caches the most frequently used data on the server-based Flash device, thereby putting the data closer to the application, reducing the need to access data across the storage network to the array, increasing performance and reducing latency. The XtremSW Cache software automatically adapts to changing workload by identifying which data is most frequently referenced and promoting it to the server Flash cache. The “Hottest” data is serviced from the local PCIe Flash device, all while writes are committed to your existing enterprise storage array. This write through cache provides the perfect solution for reporting instances of Microsoft SQL Server or Microsoft SharePoint BI implementation. A side effect of that performance gain is an increase in write transactions. In reality, the reads have been moved from the storage array to the host, allowing the array to spend cycles on writes. On a side note, EMC XtremSW Cache doesn’t require an EMC Storage array; of course, there is advanced integration via Storage Tiering, monitoring, reporting services and even phone home supportability. EMC XtremSW Cache can be used to move read operations as close to the CPU no matter the storage array.

EMC XtremSF & XtremSW Cache (Better Together)

XtremSF Server Flash, the fastest anywhere. XtremSW Cache software, move the most frequently referenced data to a local Flash device (XtremSF or other). The flexibility of the EMC Xtrem Portfolio allows the use of BOTH!! Here’s an example… An XtremSF Server Flash card, say 2.2TB, installed in a Windows 2012 host and partitioned as two 1.1TB drives. The first partition, Microsoft SQL Server TempDB (Who doesn’t need 208,000 IOPS for Temp), the second configured as a read cache leveraging XtremSW Cache Software. Performance (for TempDB), protection (for .DBFs) and service levels that are based on the back end storage infrastructure. This provides a great option for clustered environments as well. The data volumes live on shared storage while temp space, no longer a cluster resource, receives the full benefit of local flash, Nice!

Need more options? Just wait for XtremIO! EMCs all Flash Array built from the ground up to support and exploit Flash technologies. That’s the subject for another blog. Until then, be sure to visit my blog at www.insideflash.com and follow me on twitter @SamMarraccini for the latest in Flash developments here at EMC. Headed to EMC World or Microsoft Teched, look me up, and don’t forget, you’ll find everything you need to know about EMC and Microsoft at www.emc.com/everythingmicrosoft

Viewing and Interacting with SSRS Reports on an iPad

SQL Server Team — Fri, 26 Apr 2013 18:00:00 GMT

This video demonstrates how to view a Reporting Services report on an Apple iPad that has Apple iOS 6 and Apple Safari. You'll learn how to access reports from the report server and from email, use touch to collapse and expand row groups, sort columns, and filter data using parameters, and export the report to different formats.

For more information about viewing reports on mobile devices, see View Reporting Services Reports on Microsoft Surface Devices and Apple iOS Devices.

For an overview of browser support for Reporting Services and Power View, see Planning for Reporting Services and Power View Browser Support.

Insight Through Integration: SQL Server 2012 Parallel Data Warehouse - PolyBase Demo

SQL Server Team — Thu, 25 Apr 2013 17:00:00 GMT

SQL Server 2012 PDW has a feature called PolyBase, that enables you to integrate Hadoop data with PDW data. By using PDW with PolyBase capabilities, a user can:

Use an external table to define a table structure for Hadoop data.

Query Hadoop data by running SQL statements

Integrate Hadoop data with PDW data by running a PDW query that joins Hadoop data to a relational PDW table.

Persist Hadoop data in PDW by querying Hadoop and saving the results to a PDW table.

Use Hadoop as an online data archive by exporting PDW data to Hadoop. Since the data is stored online in Hadoop, user will be able to retrieve the data by querying it from PDW.

In the video below, which highlights a solution to a problem that involves sending help to evacuate potential victims of a hurricane, Microsoft SQLCAT Senior Program Manager Murshed Zaman demonstrates how to solve a customer question using relational data from SQL Server Parallel Data Warehouse 2012 (PDW 2012) and non-relational data stored inside Hadoop. The demo will show how you can analyze data by combining the capabilities of Power View and Power Pivot for Excel, Hadoop, and PDW. This video focuses on the PolyBase feature of SQL Server Parallel Data Warehouse 2012. PowerPivot and PowerView were added to the demonstration to help visualize the data results.

For step-by-step instructions on creating the PowerView report please visit Cindy Gross' blog "Hurricane Sandy Mash-Up: Hive, SQL Server, PowerPivot & Power View."

For more information on SQL Server Parallel Data Warehouse Appliance visit http://www.microsoft.com/en-us/sqlserver/solutions-technologies/data-warehousing/pdw.aspx

Let’s Call it a Wrap on the PASS Business Analytics Conference

SQL Server Team — Mon, 22 Apr 2013 16:00:00 GMT

Looking back at this month’s inaugural PASS Business Analytics Conference in Chicago, we can’t believe what an amazing experience it has been! This event included 70+ sessions over the course of two and a half days and was attended by more than 1,100 data professionals, ranging from business analysts and data scientists, to BA/BI practitioners, architects, and IT Pros.

The conference objective was to teach data professionals how to take their companies to the next level with better data-driven decisions, real-world insights and solutions, prescriptive guidance, best practices, and strategic vision for analyzing, managing, and sharing business information and insights through Microsoft’s collaborative BA platform. We believe PASS BAC accomplished all that and more! Microsoft keynote speakers Amir Netz and Kamal Hathi created quite a stir with their announcement of the public preview of project codenamed GeoFlow, setting the Twittersphere abuzz!

You can view the keynote speeches here, or you can read the announcement that was featured in our special three-part blog series on The Art of Analytics below:

Another major conference highlight was the keynote and book signing from Dr. Steven Levitt, award-winning economist and bestselling co-author of Freakonomics and SuperFreakonomics. Dr. Levitt spoke to an attentive crowd and he stressed the importance of analytical work, "You guys are the future. What you're doing is the key to a business' success or failure."

PASS BAC proved to be a very exciting week, giving us a fresh perspective on the world of data -- you can see more photos from the event on our Facebook page here. We enjoyed the opportunity to connect with so many professionals from around the world of business intelligence and analytics, and we look forward to seeing you all in Charlotte, this fall at PASS Summit 2013!

Develop and Test New SQL Server Apps, Scale Existing Apps and Unlock Hybrid Scenarios with Windows Azure Infrastructure Services

SQL Server Team — Tue, 16 Apr 2013 19:00:00 GMT

Today Microsoft announced the general availability of Windows Azure Infrastructure Services, which includes Virtual Machines and Virtual Networks to keep your Windows Azure connected to your on-premises infrastructure and applications.

Windows Azure Infrastructure Services provide the robust cloud infrastructure that is needed to run SQL Server and many of the applications that rely on SQL Server. With Windows Azure Virtual Machines, Virtual Networks, Data Sync services and full SQL Server compatibility you can now enable the following scenarios:

Develop & Test SQL Server Applications in Windows Azure

With full SQL Server compatibility you can develop your SQL Server application quickly, while reducing costs of provisioning additional hardware. In addition you can choose to deploy your new application in Windows Azure or back on-premises with minimal effort.

Move your Existing On-Premises SQL Server Applications

Virtual Machines offer many compute and memory configurations to choose from, you can find the one that fits your existing on-premises SQL Server application requirements. For example, your existing departmental SQL Server Line of Business applications that have already been virtualized are good candidates to move to Windows Azure. Once you have selected the appropriate configuration, Windows Azure makes moving your on-premises application easy with Azure tools, or if you have the latest version of System Center, you can use that as well to upload to Azure. Again, with full SQL Server compatibility you can utilize features like Transparent Data Encryption for database security, Full Text Search and AlwaysOn for high availability of your databases running in Virtual Machines.

Backup & Restore on-premises SQL Server Databases

Using a combination of Windows Azure Storage and Virtual Machines, you can create a cost effective way to backup and restore your on-premises SQL Server databases. With the recent SQL Server 2012 SP1 CU2 update, you can now backup and restore directly to a Windows Azure Storage URL, making it a one step process to backup and restore to Azure.

Unlock Hybrid SQL Server Scenarios

Windows Azure Virtual Machines and Virtual Networks unlock new hybrid application scenarios where an instance of SQL Server running on-premises is connected to a SQL Server instance running in Windows Azure Virtual Machines for additional on-demand scale and broader global reach with worldwide Azure datacenters. In addition, Windows Azure can uniquely provide you the type of support that is needed for hybrid applications by fully supporting both your on-premises SQL Server instances and SQL Server instances running in Windows Azure Virtual Machines. This significantly simplifies troubleshooting any hybrid scenarios where it may not be clear initially if the issue is with the on-premises instance or the cloud instance.

Create Multi-Tiered Cloud Applications

You can create multi-tiered cloud applications in Windows Azure where the core database tier utilizes SQL Server running in a Virtual Machine, and the application tier uses Windows Azure SQL Database (formerly known as SQL Azure) as a temporary database for the application tier to take advantage of its unique, dynamic scale-out capabilities.

SQL Server is the ideal data platform for your Hybrid IT environment as it enables end-to-end data platform scenarios that span on-premises and cloud. The scenarios above represent just the beginning of what you can do with SQL Server on-premises and Windows Azure Infrastructure Services in the cloud. Go to the Infrastructure Services page on WindowAzure.com to try these SQL Server scenarios today.

In coming weeks, you will find more in-depth blogs covering each of the above SQL Server scenarios as well as best practices when it comes to optimizing security, high availability and performance of your SQL Server applications running in Windows Azure Virtual Machines. You can also reference best practice documentation on MSDN for implementing the SQL Server scenarios in Windows Azure Virtual Machines. We hope you are as excited as we are about this release!

Day 3: PASS Business Analytics Conference, Analytics in Action

SQL Server Team — Fri, 12 Apr 2013 14:00:00 GMT

It’s the final day of the PASS Business Analytics Conference. It’s been a valuable opportunity to learn from other industry leaders, partners and customers, all of whom have great ideas and insight on what the future of analytics looks like. As far as what it looks like for Microsoft, we’ve spent the last couple of days talking about not only the immediate view, with the availability of the preview of project codename “GeoFlow” for Microsoft Excel, but also about our strategy to make data accessible to everyone using familiar tools. As we wind down at the show, we’d like to share some real-world examples of how customers are seeing success today with analytics.

The sixth largest commercial bank in Israel is the Union Bank of Israel. They use BI to evaluate their services and operations, combining financial, operational and transactional data. Prior to implementing their new Microsoft solution at the end of 2012, they used a third party solution that required custom reports that could only be performed by the IT department. Today, their employees are taking advantage of PowerPivot to create rich reports and visualizations in minutes and produce insights in half the time. Reports are more accurate as they can look at the thousands of calculations behind report values. And, collaboration across the IT and finance departments has changed radically – business employees can create self-service sophisticated BI enabled by IT, and IT can instantly see how changes to the database impact reports.

Another example is German airfreight container management company Jettainer, which has the largest fleet of loading devices in the world. Managing 85,000 containers at 350 airports worldwide is a big task in itself, but to further complicate things, Jettainer used separate data platforms for inventory control and business intelligence, meaning employees had to learn and manage two different systems. The company moved to SQL Server 2012 for both systems and uses Master Data Services to create and maintain a single version of the truth for employees, who can now track inventory development and arrange for additional supply through one system. The ability to use Office tools to create reports through PowerPivot and Power View has increased adoption among employees, meaning BI – and resulting insight – is in more hands.

While the solutions and deployments we help to create vary widely, the common goal across all of our customers boils down to getting the most complete insight as quickly and efficiently as possible. Union Bank of Israel and Jettainer are just a few examples of customers working with Microsoft to conquer their analytics challenges.

Read more from The Art of Analytics series here:

Eron Kelly
General Manager
SQL Server