Official News from Microsoft’s Information Platform
Machine Learning Blog
Power BI
Microsoft recently announced several new ways to back up and recover SQL Server databases with Windows Azure. These features, now available in SQL Server 2014 CTP2 and as a standalone tool for prior versions, provide an easy path to cloud backup and disaster recovery for on-premises SQL Server databases. The capabilities for backing up to Windows Azure Storage help to reduce storage costs and unlock the data protection and disaster recovery benefits of cloud data storage.
Benefits of the new SQL Server Backup to Windows Azure include:
Simplified Cloud Backup
There are several new capabilities enabling users to utilize SQL Server Backup and Disaster Recovery Windows Azure.
Learn More
SQL Server SQL Server 2014’s goal is to deliver mission critical performance along with faster insights into any data big or small. At the same time, it will enable new hybrid cloud solutions that can provide greater data protection and positively impact your bottom line. Early adopters are already leveraging new hybrid scenarios to extend their backup and disaster recovery capabilities around the globe without the need for additional storage replication technologies.
If you would like to try the Backup and Recovery Enhancements in SQL Server 2014 CTP2, Thursday’s blog post will help you get started configuring and using these new capabilities. You can also preview the SQL Server Backup to Windows Azure Tool, enabling backup to Windows Azure for SQL Server 2005 and forward.
You can learn more about Hybrid Cloud scenarios in SQL Server 2014 by reading the SQL Server 2014 Hybrid Cloud White Paper, which is part of the SQL Server 2014 Product Guide.
SQL Server 2014 CTP1 introduced hash indexes for memory-optimized tables. Hash indexes are very efficient for point lookups, when you know exactly the value you are looking for. However, they do not perform well if you need a range of value, for example a date range, or if you need to retrieve the rows in a particular order.
Memory-optimized nonclustered indexes are a new index type for memory-optimized tables in CTP2. Nonclustered indexes support retrieving ranges of values, and also support retrieving the table rows in the order that was specified when the index was created. They can be used to optimize the performance of queries on memory-optimized tables that use inequality predicates like ‘<’ and ‘>’, and queries using an ORDER BY clause.
Nonclustered indexes also support point lookups, but hash indexes still offer far better performance. You should continue to use hash indexes to optimize the performance of queries with equality predicates.
Consider, for example, the following (simplified) CREATE TABLE statement. The table SalesOrderHeader_inmem has a hash index on the primary key column ‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced. Note that the BUCKET_COUNT should typically be set between one and two times the number of unique index key values.
CREATE TABLE [Sales].[SalesOrderHeader_inmem]( [SalesOrderID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) DEFAULT (NEWID()), [RevisionNumber] [tinyint] NOT NULL CONSTRAINT [IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)), [OrderDate] [datetime2] NOT NULL INDEX ix_OrderDate HASH WITH (BUCKET_COUNT=1000000) ) WITH (MEMORY_OPTIMIZED=ON)
If you run a query of the form:
SELECT * FROM Sales.SalesOrderHeader_inmem WHERE SalesOrderID = @ID
SQL Server will use the hash index on the column SalesOrderID to quickly identify the row corresponding to the parameter @ID. Witness the Index Seek operation in the query plan:
Hash indexes are very efficient for point lookup operations, because they require only a simple lookup in a hash table rather than traversing an index tree structure, as is required for traditional (non)clustered indexes. If you are looking for the rows corresponding to a particular index key value, using a hash index is the way to go.
Now, hash indexes do have some limitations. Because of the nature of hash tables, rows appear in the index in random order. This means it is not possible to retrieve ranges of values, or to scan rows in any particular order. Therefore, hash indexes do not support Index Seek operations on inequality predicates, and do not support ordered scans. The former results in a full index scan, and the latter results in a scan followed by a(n expensive) sort operator. All this results in a potential performance degradation when using such queries with hash indexes.
Consider the following two example queries:
Query with an inequality predicate
SELECT * FROM Sales.SalesOrderHeader_inmem WHERE OrderDate > @Date
The plan for this query does not use the index on OrderDate; it simply includes full index scan for the primary index. This means that SQL Server will process all the rows in the table, and only later filter out the ones with OrderDate > @Date.
Query with an ORDER BY
SELECT * FROM Sales.SalesOrderHeader_inmem ORDER BY OrderDate
The plan for this query includes a sort operator, which is very costly: after scanning the rows, all rows will need to be ordered to obtain the desired sort-order.
The new memory-optimized nonclustered indexes support both Index Seek operations using inequality predicates, and ordered scans. Consider the following amended CREATE TABLE statement, which now includes a nonclustered index on the column OrderDate.
‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced.
CREATE TABLE [Sales].[SalesOrderHeader_inmem]( [SalesOrderID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) DEFAULT (NEWID()), [RevisionNumber] [tinyint] NOT NULL CONSTRAINT [IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)), [OrderDate] [datetime2] NOT NULL INDEX ix_OrderDate NONCLUSTERED ) WITH (MEMORY_OPTIMIZED=ON)
Note that the keyword NONCLUSTERED is optional in this example. Also note that the syntax for memory-optimized nonclustered indexes is similar to the traditional disk-based nonclustered indexes. The only difference is that with memory-optimized indexes you need to specify the index with the CREATE TABLE statement, while with traditional indexes, you can create the index after creating the table.
Consider now the following two queries:
The plan for this query now uses the index on OrderDate. And when inspecting the properties of the Index Seek operator, you will see the inequality predicate OrderDate > @Date. This means that SQL Server will only need to process the rows with OrderDate > @Date.
The plan for this query does not include a sort operator. SQL Server will scan the rows in the index in order; in this case the sort-order of the index is the same as the sort-order required by the query, thus no additional sort operator is needed.
Note that, in contrast to disk-based nonclustered indexes, memory-optimized nonclustered indexes are uni-directional. This means that they support index scans only in the order that was specified when the index was created. If the ORDER BY clause in the above example would require OrderDate in DESC order, the index ix_OrderDate could not be used.
Memory-optimized indexes do have a few limitations in SQL Server 2014 that we hope to address in future versions. You do need to consider these limitations when deciding on an indexing strategy for your memory-optimized tables.
A few simple guidelines to help choose the type of index you need for your application:
Hash indexes require an estimate of the number of unique index key values, to determine an appropriate value for the BUCKET_COUNT – typically between 1X and 2X. Because nonclustered indexes do not require setting the BUCKET_COUNT, it can be tempting to simply use a nonclustered index instead of a hash index. However, this could lead to sub-optimal performance for point lookup operations. In many scenarios where point lookup performance is critical and there is no good estimate for the number of unique index key values, it is better to over-provision the BUCKET_COUNT: i.e., pick a very high bucket count that you know is going to be larger than the number of index keys. Note that over-provisioning does not affect the performance of point lookups, but under-provisioning does. However, over-provisioning does increase memory-consumption and it slows down full index scans.
For more details on usage of memory-optimized indexes see the Books Online article on Guidelines for Using Indexes on Memory-Optimized Tables.
Last week, we introduced you to SQL Server PDW, the version of SQL Server built specifically for high performance data warehousing that delivers performance gains of up to 50x compared to traditional data warehouses. The next logical question we often get is “how is this possible?” Is it just SQL Server running on special hardware? And the answer is yes…but there is a lot more to it than that.
PDW has a shared nothing, Massively Parallel Processing (MPP) architecture allows for better performance when loading and querying data simultaneously as well as performing the types of complex querying that is common amongst today’s analytically driven enterprises. The MPP architecture is also what enables PDW’s seamless linear scalability from terabytes to 6 petabytes of data. This is a fundamentally different architecture than traditional, Symmetric Multi-Processing (SMP) that traditional SQL Server is based on and this MPP architecture is really at the heart of what’s different about PDW and how it’s able to deliver the ground breaking performance that it does.
To learn more about how PDW works and some of the performance gains we’ve seen in typical data warehouse scenarios, we encourage you to download this whitepaper today!
Last week, we highlighted a whitepaper that focused on the performance benefits of SQL Server PDW and how it differs from traditional SQL Server. We’ve seen many SQL Server customers evolve to this latest PDW as their next generation platform for their data warehouse infrastructure for many of the reasons noted in that whitepaper and this week, we want to follow-up on that by sharing our guide for how to migrate from a traditional data warehouse to PDW.
This whitepaper is based of real world best practices and will showcase how you can leverage your existing knowledge and skills around SQL Server to deploy SQL Server PDW as your next generation solution for your data warehouse. Download the paper to find out more.
Starting with SQL Server 2012, the new offering of AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances (FCI) suite of features have enabled Tier-1 customers with mission critical applications to achieve their availability goals with SQL Server with an easy to deploy and manage solution. SQL Server 2014 builds on this success and offers enhanced AlwaysOn Availability Groups with up to 8 replicas, ability to access secondary replica for offloading reporting workload in disconnected scenario and hybrid scenario with Windows Azure.
In-memory OLTP is also targeting mission critical applications to deliver up to 30x better performance, and is integrated well with High Availability offerings SQL Server provides. The sections below review each of the High Availability offerings in the context of the in-memory OLTP solution. SQL Server 2014 offers four high-availability choices to customers as follows:
In-Memory OLTP is fully integrated with FCI. A database with memory-optimized tables can be configured and managed similar to how you would manage a database with no memory-optimized objects. However, memory-optimized tables in the database will likely add to the recovery time because these tables need to be loaded into memory before the database can be accessed. In-memory OLTP engine loads data/delta file in parallel to minimize the time taken. The time to load the memory_optimized tables depends upon the size of memory-optimized tables, the number of data/delta files, IOPS available to read data/delta files and number of CPUs/Cores available. It is a best practice to configure SQL Server instances with same amount of memory as, unlike for disk-based tables, insufficient memory will cause database recovery to fail on the new instance. Please refer to How to manage your Memory for In-Memory OLTP for managing memory for database with memory-optimized tables
In-memory OLTP is fully integrated with AlwaysOn Availability. You can setup a database with memory-optimized tables in an availability group following exactly the same step that you would for a database without memory-optimized tables. Key points
In-memory OLTP: Log shipping is fully supported on database with memory-optimized tables. A database with memory-optimized tables can be configured and managed similar to how you would manage a regular database
In-memory OLTP: Replication is supported on databases with memory-optimized tables but the limitation is that you cannot use a memory-optimized table as an article in a publication or as part of a subscription. We will look into addressing this in a future release. In spite of this restriction, you can use memory-optimized tables in read-scale scenarios for read-mostly workloads as described below
Scenario: Let us say you have couple of disk-based tables t_disk1 and t_disk2 that are accessed heavily by read workloads. For your workload, you want to replicate these two tables on the subscriber however you want read-workload to run memory-optimized tables. You can achieve this by creating corresponding memory_optimized t_memopt1 and t_memopt2 and populating them using DML triggers on disk1 and t_disk2 tables. Any insert/delete or the update of the row, the triggered action will update the t_memopt1 and t_memopt2 accordingly. Essentially, you have two copies of the same table, one is disk-based and other is memory-optimized. Now you can direct your read-workload to the memory-optimized tables.
As the SQL Server 2014 In-memory OLTP team began working with customers we recognized some common design patterns where the In-memory OLTP engine was a great fit. The intent of this blog is to call out one of those architectures which we call: High data input rate/Shock Absorber and how implementing SQL Server 2014 In-memory OLTP can help improve the performance of applications with this scenario.
What are some characteristics of this architecture?
A common application pattern we have experienced in working with customers is characterized by the need for the database to ingest a very high input rate of data. There are a wide variety of patterns, where the number of sources of data, frequency in which the data is input (burst or steady stream) and requirements around the amount of data input varies. However, the common characteristic is the need to handle either spikes or a constant rate in the incoming workload which may significantly exceed the capabilities of a classic RDBMS. We call this “Shock Absorber” as the scenario includes one or a few tables which are able to “absorb” this input rate (which is the “shock” to the system). Typically this part of the scenario may be a piece of a larger application architecture involving other tables, data and queries. However, in many cases this ingestion rate has become a primary bottleneck within the application.
There are also a number of applications which have exhibited more of an ETL scenario where the data might not just be inserted, but updates, deletes (and data transformation) may occur. In this case a common pattern would be to have a “staging” table to handle the initial load or loading directly into the end-table and scaling the writes and reads concurrently. The bottleneck around ingesting the data remains the same.
There are a wide variety of business solutions which we have found with this type of pattern and some intriguing scenarios where implementing In-memory OLTP can improve the overall application performance significantly.
How can In-memory OLTP help?
These scenarios can benefit from In-memory OLTP in a number of ways. First, in a number of cases the “shock” or overflow of the input workload is bottlenecked on latching or locking contention within the table structure. In some cases, people will architect around last page insert scenarios via reverse indexes or secondary indexing implementations. However they can only scale to a point and can lead to other consequences. With memory-optimized tables all the latching and lock contention is eliminated due to the way data is accessed in-memory and row-versioned. This can provide for much greater scale in particular when running into these contention points.
Another common bottleneck in these scenarios is due to the overhead of the log writes which can extend the time it takes to complete a transaction. This may be a bottleneck around IO capacity of the disk or just the amount of logging as an overhead. The advantages of memory-optimized tables in this case would include:
Furthermore, in cases where the transaction throughput into the database is unable to “keep-up” with the input and additional scale (i.e. adding additional clients or threads) are not able to help or be easily implemented, native compiled stored procedures can help improve the latency characteristics of the T-SQL execution. Other content may address this further, but it is important to call-out that the more performance critical code one can place into the native compiled stored procedure and the In-memory OLTP engine in general, the more chance you have to improve the overall execution performance.
My database doesn’t all “fit” in memory
In many scenarios where memory-optimized tables and native compiled stored procedures can be utilized to target the “hot” OLTP dataset where the bottleneck is exhibited there may be other data which is needed in the database but not necessarily desired to be stored in the In-memory OLTP engine. Some examples would include 1. A need for archive data 2. The query workload pattern is not OLTP and/or different indexing structures are required to satisfy the workload or 3. Physical memory limits would require some tables to be disk-based (standard b-tree) for larger datasets.
As In-memory OLTP is integrated into SQL Server and you can have both memory-optimized tables and disk-based tables in the same database, separating the data storage and retrieval mechanisms between these table structures is also a common pattern. Typically a user created “background” process can execute, for example, on a timed basis, to move data from the memory-optimized table to the disk based table and remove that data from the current memory-optimized table (or potentially just purge).
Ok, but how much does it really help?
Great question! As this is a common scenario we have worked with customers doing testing and here are some early results:
So, do you have scenarios you would like to use a relational database for, but running into issues with regards to the amount of data you can ingest into tables? Consider SQL Server In-memory OLTP engine for meeting your transaction volume and application performance with the database in the critical path.
Interested in more patterns where we have seen success with SQL Server 2014 In-memory OLTP? Stay tuned for a follow-up whitepaper which will include more scenarios and considerations to help you migrate to SQL Server 2014 In-memory OLTP.
Download SQL Server CTP1 and get started today, or see more blogs in the series introduction and index here!
This morning, during my keynote at the Professional Association of SQL Server (PASS) Summit 2013, I discussed how customers are pushing the boundaries of what’s possible for businesses today using the advanced technologies in our data platform. It was my pleasure to announce the second Community Technology Preview (CTP2) of SQL Server 2014 which features breakthrough performance with In-Memory OLTP and simplified backup and disaster recovery in Windows Azure.
Pushing the boundaries
We are pushing the boundaries of our data platform with breakthrough performance, cloud capabilities and the pace of delivery to our customers. Last year at PASS Summit, we announced our In-Memory OLTP project “Hekaton” and since then released SQL Server 2012 Parallel Data Warehouse and public previews of Windows Azure HDInsight and Power BI for Office 365. Today we have SQL Server 2014 CTP2, our public and production-ready release shipping a mere 18 months after SQL Server 2012.
Our drive to push the boundaries comes from recognizing that the world around data is changing.
In-memory in the box for breakthrough performance
A few weeks ago, one of our competitors announced plans to build an in-memory column store into their database product some day in the future. We shipped similar technology two years ago in SQL Server 2012, and have continued to advance that technology in SQL Server 2012 Parallel Data Warehouse and now with SQL Server 2014. In addition to our in-memory columnar support in SQL Server 2014, we are also pushing the boundaries of performance with in-memory online transaction processing (OLTP). A year ago we announced project “Hekaton,” and today we have customers realizing performance gains of up to 30x. This work, combined with our early investments in Analysis Services and Excel, means Microsoft is delivering the most complete in-memory capabilities for all data workloads – analytics, data warehousing and OLTP.
We do this to allow our customers to make breakthroughs for their businesses. SQL Server is enabling them to rethink how they can accelerate and exceed the speed of their business.
A closer look into In-memory OLTP
Previously, I wrote about the journey of the in-memory OLTP project Hekaton, where a group of SQL Server database engineers collaborated with Microsoft Research. Changes in the ratios between CPU performance, IO latencies and bandwidth, cache and memory sizes as well as innovations in networking and storage were changing assumptions and design for the next generation of data processing products. This gave us the opening to push the boundaries of what we could engineer without the constraints that existed when relational databases were first built many years ago.
Challenging those assumptions, we engineered for dramatically changing latencies and throughput for so-called “hot” transactional tables in the database. Lock-free, row-versioning data structures and compiling T-SQL and queries into native code, combined with making the programming semantics consistent with SQL Server means our customers can apply the performance benefits of extreme transaction processing without application rewrites or the adoption of entirely new products.
The continuous data platform
Windows Azure fulfills new scenarios for our customers – transcending what is on-premises or in the cloud. Microsoft is providing a continuous platform from our traditional products that are run on-premises to our cloud offerings.
With SQL Server 2014, we are bringing the cloud into the box. We are delivering high availability and disaster recovery on Windows Azure built right into the database. This enables customers to benefit from our global datacenters: AlwaysOn Availability Groups that span on-premises and Windows Azure Virtual Machines, database backups directly into Windows Azure storage, and even the ability to store and run database files directly in Windows Azure storage. That last scenario really does something interesting – now you can have an infinitely-sized hard drive with incredible disaster recovery properties with all the great local latency and performance of the on-premises database server.
We’re not just providing easy backup in SQL Server 2014, today we announced backup to Windows Azure would be available for all our currently supported SQL Server releases. Together, the backup to Windows Azure capabilities in SQL Server 2014 and via the standalone tool offer customers a single, cost-effective backup strategy for secure off-site storage with encryption and compression across all supported versions of SQL Server.
By having a complete and continuous data platform we strive to empower billions of people to get value from their data. It’s why I am so excited to announce the availability of SQL Server 2014 CTP2, hot on the heels of the fastest-adopted release in SQL Server’s history, SQL Server 2012. Today, more businesses solve their data processing needs with SQL Server than any other database. It’s about empowering the world to push the boundaries.
Quentin Clark Corporate Vice President Data Platform Group
The world of data is changing with businesses facing an explosion in information volumes and the need to mine different data sources and data types. More than ever, organizations must collect and make use of this volume of data to analyze overall business strategy and make timely decisions to gain a competitive edge.
While large enterprises have traditionally addressed this challenge with enterprise data warehouse deployments, the majority of mid-market customers do not have a formal data warehouse. The complexities and cost of planning, designing, purchasing, installing, and tuning a data warehouse can take months to complete and has been too daunting for small to medium sized organizations. Imagine the possibilities of a complete production-ready data warehouse solution that has everything (software, hardware, services, and support) pre-installed, optimized and sold at the price point you can afford?
This is possible with the new Dell Quickstart Data Warehouse Appliance with SQL Server 2012 offering from Microsoft and Dell. Furthering the partnership that already brought Dell Enterprise Data Warehouse (Parallel Data Warehouse) and Dell SQL Server 2012 Fast Track offerings for customers’ medium to large enterprise warehouse deployments, Dell Quickstart was purposely built for small and medium-sized businesses. Quickstart delivers more power and capacity with a smaller footprint and with less friction for customers to get up and running at a lower cost.
Highlights include:
With the Quickstart Data Warehouse Appliance, one price includes everything needed to quickly deploy and easily manage a data warehouse in a comprehensive and integrated solution. This appliance is an affordable all-inclusive package for mid-market and departmental customers who even with limited experience and resources can now afford a powerful data warehousing solution.
We invite you to learn more about Dell Quickstart Data Warehouse Appliance by reading:
With TechEd North America, June 11-14, 2012, and TechEd Europe, June 26-29, 2012, just around the corner, we wanted to share with you some of the exiting session content and speakers. This year, with the release of SQL Server 2012, we have an incredible line up of presenters and staff to bring you solid session content and top knowledge at both events.
We are also very excited to announce two SQL Server pre-conference seminars:
Pre-conference: Using BISM Tabular in Microsoft SQL Server Analysis Services 2012 (North America | Europe)
Microsoft SQL Server 2012 introduced new BI Services, like Power View and the BI Semantic Model (BISM) in Analysis Services, which offers two types of models, Tabular and Multidimensional. The Tabular model is required in order to publish data for Power View and it is based on concepts like tables and relationships that are familiar to anyone who has a relational database background.
In this seminar, learn from Marco Russo how to build a complete solution in Tabular, either by creating a new model from scratch or by importing an existing PowerPivot model. After an initial introduction of the overall architecture with the Microsoft platform, including SharePoint, PowerPivot and Power View, most of the time is dedicated to the definition of a BISM Tabular model. Learn about creating one from the ground up and then defining all the metadata required in order to obtain a rich semantic model that provides better user experience for data exploration in both Excel and Power View. You also learn the basics of DAX language and how to manage role based security and partitioning in a Tabular model. At the end of the day, you will be able to create your BISM Tabular models, fully exploiting the features available in a Corporate BI scenario. You will gain a solid background to enable you starting your next project with Tabular.
Pre-conference: Microsoft SQL Server Performance Tuning and Optimization (North America | Europe)
In this session, learn about SQL Server 2008 R2 and SQL Server 2012 performance tuning and optimization. Industry Experts Thomas LaRock and Denny Cherry guide you through tools and best practices for tuning queries and improving performance within Microsoft SQL Server. This session details real-life performance problems which have been gathered and tuned using industry standard best practices and real-world skills. At both TechEd North America and TechEd Europe, we will have some great opportunities for you to LEARN and help advance your depth of knowledge with SQL Server; CONNECT with Microsoft experts, partners, speakers, and community members; and SHARE information with peers – before, during, and after each event. Here are a few opportunities we would like to call out:
Where to Register?
Register today for TechEd North America or TechEd Europe! If you are already registered don’t forget to drill down into the entire session agenda list (North America or Europe) and prepare for your trip.
We look forward to seeing you at TechEd!
It has been 20 years since Microsoft started TechEd to provide hands on training from Microsoft experts across current and soon-to-release products, solutions and services. The event has grown to become Microsoft’s premier technology conference for IT Pros and Developers, and to mark our 20th year we are really excited to be presenting an awesome lineup of speakers for TechEd North America (June 11-14, 2012) and TechEd Europe (June 26-29, 2012)! In particular for TechEd North America, we are honored to have Satya Nadella (President, Server and Tools Business) as the keynote speaker and Quentin Clark (Corporate Vice President, Database Systems Group, Microsoft SQL Server) deliver his vision for The New World of Data: SQL Server and Hybrid IT. By attending TechEd North America’s Database and Business Intelligence track, you will be spoiled for choice for technical education.Planning to arrive in Orlando early to soak in all the TechEd goodness? If so, then get a jump start on learning and networking by attending our pre-conference sessions! In addition to 4 days of interactions at TechEd itself, you could join in our SQL Server pre-conference seminars. Gain tools and best practices through discussions on how real-life performance problems have been on tuned and optimized (SQL Server Performance Tuning and Optimization) or learn to exploit the features available in a corporate BI scenario by creating models using Tabular to publish data to Power View (Using BISM Tabular in Microsoft SQL Server Analysis Services 2012).
As you contemplate the rich content covered by the speaker sessions, it may be useful to know where your peers are going! Here are the top 10 most popular sessions to date picked by other TechEd North America attendees: DBI202 The 12 Reasons to Love Microsoft SQL Server 2012DBI307 Tips and Tricks for Building Rich Reports in Microsoft SQL Server 2012 Reporting ServicesDBI201 What's New in Microsoft SQL Server Reporting ServicesDBI308 Practical Uses and Optimization of New T-SQL Features in Microsoft SQL Server 2012DBI331 The Dirty Dozen: Windows PowerShell Scripts for the Busy DBADBI206 Business Intelligence and Data Visualization: Microsoft SQL Server 2012DBI328 Building the Fastest SQL ServersDBI311 Microsoft SQL Server Data Tools: Database Development from Zero to SixtyDBI317 Optimizing Microsoft SQL Server Performance in a Virtual EnvironmentDBI209 Big Data, Big Deal?
Interested in getting more experience and immersion in SQL Server 2012? Stop by S. Hall B to try out our self-paced hands-on labs. In addition, there will also be 3 technical workshops led by Brent Ozar on Bare Metal Microsoft SQL Server 2012 Deployment and Management.Check out the full list of speakers, sessions and activities on the online content builder (North America | Europe) to plan your schedule today. Be sure to join the conversation on Twitter using #msTechEd (for TechEd North America), #TEE12 (for TechEd Europe) and both #SQL2012 and #SQLServer hash tags for all things related to SQL Server at both events. We look forward to seeing you at the event!Update: General attendee registration for TechEd North America is now sold out! You can sign up for the registration waiting list here.
PASS Summit 2012 has arrived in Seattle and our #SQLFamily reunion is under way! In this final post of our Countdown to PASS Summit Series, we invite you to meet and greet the developers who worked on SQL Server 2012, for a personal, behind-the-scenes look at SQL Server 2012 in the making!
* * *
Yesterday, we introduced you to the Arkitektor, the newest superhero on the scene from the Data Barrier Agency (DBA), who you can see up-close on the walls of the Microsoft booth at PASS Summit this week. However, we also have a few more SQL Server Superheroes that you can meet in person this week during our “I Made That!” Developer Chalk Talks.
This week, at PASS Summit, join some of the top Microsoft developers who worked on SQL Server 2012 for some informal and unscripted talks about the functionalities and features they wrote. Relax and unwind with these SQL Server superheroes on comfy couches with snacks to share, and get the inside track on the development of SQL Server 2012. You can find the full schedule of Chalk Talks and SQL Server Superheroes here, but in the meantime, you can preview a few of the SQL Server all-star team members that you can mix and mingle with during the "I Made That!” Developer Chalk Talks:
Eric Hanson, Chalk Talk Host on Columnstore: 12:30 pm, Thursday, November 8th
Dr. Eric Hanson is a Program Manager Architect on the Big Data team in Microsoft SQL Server, where he focuses on data warehousing and the integration of data warehousing with Hadoop. He was instrumental in initiating the work that lead to the new columnstore index feature that shipped in SQL Server 2012. SQL Server Superpower: Columnstore performance tuning SQL Server Supergadget: Trace flag 2312 SQL Server Supermotto: If the heavy lifting is done in batch mode, you’re golden! Sunil Agarwal, Chalk Talk Host on Hekaton: 1:30 pm, Thursday, November 8th Sunil Agarwal is a Principal Program Manager in the SQL Server Storage Engine Group at Microsoft. Sunil has co-authored two books on SQL Server and owns/participates in the popular SQL Server Storage Engine Blog. Prior to joining Microsoft, Sunil worked at DEC, Sybase, BMC Software, Asera and DigitalThink, focusing primarily on core database engine technologies and related applications. SQL Server Superpower: Storage engine SQL Server Supergadget: Hekaton SQL Server Supermotto: In-memory data is king!
Dr. Eric Hanson is a Program Manager Architect on the Big Data team in Microsoft SQL Server, where he focuses on data warehousing and the integration of data warehousing with Hadoop. He was instrumental in initiating the work that lead to the new columnstore index feature that shipped in SQL Server 2012.
SQL Server Superpower: Columnstore performance tuning
SQL Server Supergadget: Trace flag 2312
SQL Server Supermotto: If the heavy lifting is done in batch mode, you’re golden!
Sunil Agarwal, Chalk Talk Host on Hekaton: 1:30 pm, Thursday, November 8th
Sunil Agarwal is a Principal Program Manager in the SQL Server Storage Engine Group at Microsoft. Sunil has co-authored two books on SQL Server and owns/participates in the popular SQL Server Storage Engine Blog. Prior to joining Microsoft, Sunil worked at DEC, Sybase, BMC Software, Asera and DigitalThink, focusing primarily on core database engine technologies and related applications.
SQL Server Superpower: Storage engine
SQL Server Supergadget: Hekaton
SQL Server Supermotto: In-memory data is king!
From xVelocity in-memory analytics and X-ray vision, to the ability to take on 1 million commands per second, I am Arkitektor, special agent of the Data Barrier Agency. In alliance with Microsoft SQL Server, the Data Barrier Agency’s goal is to fuse the top qualities of the world’s top DBA rock stars – top notch problem solving, technical prowess, and razor-sharp decision making – and amp them up to super hero status.
My mission: Introduce the people of SQL Server to the ultramodern SQL Server tools and capabilities available to DBA agents across the globe.
I invite you to visit the Microsoft SQL Server booth at PASS Summit 2012 in Seattle, Washington, to see my grand debut on the SQL Server scene. Be sure to follow my mission and my continuing adventures this holiday season through the SQL Server network dossier.
In the meantime, here is your mission, should you choose to accept this role:
Join me and the SQL team on Twitter and Facebook, where you’ll learn about other special PASS events and contests including chances to win an XBOX. Use #sqlpass and mention that Arkitektor sent you.
Last week marked the completion of a great week at PASS Summit 2012, the world’s largest technical training conference for SQL Server professionals and BI experts alike. During this year’s 3-day conference, nearly 4,000 attendees heard firsthand about the great advances being made toward managing big data. Over the course of two keynote speeches by Microsoft Corporate Vice Presidents Ted Kummert (Data Platform Group) and Quentin Clark (SQL Program Management), Microsoft announced the following:
What’s more, on the final day of PASS Summit 2012, attendees were treated to the presentation, “Big Data Meets SQL Server 2012” by Microsoft Technical Fellow David DeWitt.
All the while, conference participants attended a wide variety of technical sessions presented by industry experts in addition to a host of other programs. From on-site certification testing, to hands-on-labs, attendees were able to boost their technical skills using these resources, as well as work through technical issues with top Microsoft Customer Service and Support (CSS) engineers and get architectural guidance from the SQL Server, Business Intelligence and Azure Customer Advisory Teams (CAT). Of course, the learning didn’t stop there; attendees were invited to new, “I Made That!” Developer Chalk Talks, which featured 30 minute casual talks with the Microsoft developers who worked on specific features and functionalities of SQL Server 2012. The topics appealed to many, ranging from AlwaysOn to Hekaton. You can see more great photos from PASS Summit 2012 on the SQL Server Facebook page or access the video interviews with Dave Campbell, Quentin Clark, and David DeWitt available at the SQL Server virtual press room.
And so, as we close on another year of PASS Summit, it’s the perfect time to look back and see how far we’ve come since the launch of SQL Server 2012. Join us below, as we take a celebratory look at the milestones we’ve hit along the way, and let’s look together toward the bright future ahead!
Guest Blog Post by Chris Shaw, Xtivia. Chris is an MVP Award Recipient, the author of Pro SQL Server Practices 2012, and he can be reached at CShaw@xtivia.com or thought his blog..
At this time of year, I look forward to long visits with the relatives, eating more food than should be allowed by law and getting ready for the upcoming year. The last thing I want to do is take time away from friends and family, so I run one last checklist against my SQL Servers to make sure we are all ready for the holidays.
Before I sign out for my holiday vacation I double-check:
Lastly, I double check to make sure I have a gift for my spouse. I speak from experience when I say that the last thing you want to do not get a gift for your spouse because you were working on an emergency at work. They are kind to us all year, and they put up with the long hours and middle of the night DBA rescue meetings. During the holidays, you really don’t want to forget your spouse because you were busy working…again!
With that, I extend to you the warmest of holiday wishes! May your indexes be selective and un-fragmented, your backups be true and your consistency checks come back clean -- happy holidays to you and yours on your well-earned break!
Ideally the holidays are a time to catch up with friends, family and food. But end-of-year deadlines and work emergencies can also make it a stressful time. Between the festivities, you may face some tough choices. Do I go to John’s holiday beer bash, or do I finish up the playhouse I’m building for Suzy? I’m on call and I know an emergency may come in while I’m at the party? Should I really go? I’ll likely end up checking my email every thirty seconds.
We know these are tough calls to make, so the SQL Team wanted to offer some help.
We compiled a Holiday Gift Guide and a Holiday Etiquette Guide to assist. The Holiday Gift Guide is just that –a list of gifts a DBA would enjoy giving and getting this holiday season. We know you don’t have time to build everyone an original by hand, and while it’s tough to admit, many of your gifts may end up last minute purchases. Before jetting out, consult the list of gifts we’ve provided, so you make a choice out of inspiration not desperation.
And you’ll probably need a card to go with that. We got that covered too. The set of cards includes zombies and cats for the joker in the next cube and traditional warm greetings for your aunt. If your aunt is the joker in the next cube, then we have you really covered. (Share, or download and tweet or email the cards from Facebook.)
The Etiquette Guide lists some pointers for how to tactfully decline a holiday invitation due to a work emergency or any other work-related conflict. Be honest. Send a gift. Don’t over-explain yourself. Check out the guide for more.
We know balancing work and holiday duties is tricky. We hope to make it easier. You can find all of these nifty reads in the complete SQL Server Holiday Guide. Share it on Facebook or download the PDF.
The SQL Team wishes everyone a very happy holidays!
With just six days left until the start of PASS Summit 2012, the excitement is building among SQL Server and BI professionals, and we’re staring our celebration early with a special series of blogs to countdown the six days left until PASS Summit! Check back here daily to read what we have in store for you. Find exciting news, insightful information, and more -- all to get you revved up for PASS Summit 2012 in Seattle, Washington -- let the countdown begin!
Today’s Day 6 Countdown post features a Q & A session with Ross Mistry (Linkedin | Twitter), an author, Enterprise Architect with Microsoft and former SQL Server MVP. Ross, who has been working with SQL Server since version 6.5, speaks at technology conferences worldwide and designs SQL Server high availability and Private Cloud solutions for many organizations located in the Silicon Valley. We sat down with Ross and asked him a few questions about the many books he’s authored, including his latest book on SQL Server 2012, and all about his latest assignment -- the launch of the first Microsoft Technology Center in Canada.
Q. SQL Server: Over the past decade, you have authored many books on Windows, Exchange and SQL Server. Can you tell us about your latest books on SQL Server 2012?
A. I certainly can. This year I published two books on SQL Server 2012, Introducing SQL Server 2012 with Microsoft Press and SQL Server 2012 Management and Administration with SAMS Publishing.
Q. SQL Server: What are major differences between the two books and who are the intended audiences?
On behalf of the SQL Server Product Group, I co-authored Introducing SQL Server 2012 with SQL Server MVP and expert Stacia Misner - Twitter. This book explores the exciting enhancements and new capabilities engineered into SQL Server, ranging from improvements in mission critical availability, pervasive insight and cloud on your terms. This book provides a high level overview and it is for anyone who has an interest in SQL Server 2012 and wants to understand its new capabilities, including database administrators, application developers, and technical decision makers.
The second book, SQL Server 2012 Management and Administration, which is now in its 3rd release, is tailored towards DBAs who need to deploy, manage, or secure Microsoft SQL Server 2012. This book goes far beyond the basics, taking on the complex tasks that DBAs need to make the most of Microsoft’s first cloud-enabled database platform. Designed for maximum practical usability, it’s packed with step-by-step instructions, expert tips from industry specialists and the product group, and up-to-date real-world configuration guidance you simply won’t find anywhere else. Also, in this release SQL Server industry expert Kevin Kline – Twitter has written chapters and shares real world experiences pertaining to storage, performance turning and indexing. Based on the positive feedback received thus far, the following chapters and sections have resonated well with our readers; installing SQL Server on Server Core, transferring data from on-premise to the cloud, managing column store indexes with xVelocity, deploying AlwaysOn Availability Groups, configuring multi-subnet clusters and finally deploying SQL Server Private Clouds.
Q. Will you be speaking at the upcoming PASS 2012 Summit in Seattle this year?
I typically deliver a full day SQL Server workshop called “No DBA left Behind” in the Silicon Valley, which focuses on teaching DBAs how to design, implement and manage SQL Server 2012 upgrades, migrations, high availability, consolidation and private cloud solutions. I was requested to deliver this same full day workshop as a Pre-Con during the PASS 2012 Summit and deliver a breakout session focused on designing a SQL Server Private Cloud with Windows Server 2012. Unfortunately, I have taken on a new role and due to a scheduling conflict; I will not be able to deliver the workshop and session or attend the upcoming Summit in Seattle this year. I am very disappointed as I have been speaking at PASS for many years and was looking forward to catching up with MVPs, customers and friends from the Product Group.
Q. So tell us about your new role?
I recently accepted a new position as the National MTC Director in Canada. I will be responsible for launching and leading our first MTC in Canada, which will be located in greater Toronto, Ontario. The plan also calls for MTC Satellite offices located throughout Canada. I am also very excited as I get to hire a new team consisting of the deepest and brightest enterprise architects in Canada.
Q. What is a Microsoft Technology Center and what is its value proposition?
Microsoft Technology Centers (MTCs) are collaborative environments that provide access to innovative technologies and world-class expertise, enabling our customers, partners and consumers to envision, design, and deploy solutions to meet their exact needs. MTCs are located throughout the world and are staffed with solution-centered technology experts which solve some of the most challenging business problems for companies that drive the global economy. As mentioned above, I am proud to announce that I will be opening Canada’s first MTC in greater Toronto, Canada.
31 MTC Locations Worldwide
Q. Do the MTCs have offerings pertaining to SQL Server?
A. We offer solutions around many disciplines such as Application Platform Optimization, Business Productivity Infrastructure Optimization, Cloud Computing , Collaboration and Communication and Core Infrastructure Optimization. With the release of Windows 8, our teams are spending a lot of time delivering engagements around devices, interactive solutions and experiences. With regards to SQL Server, the Data Platform is a major investment at our MTCs. Our SQL Server architects offer:
MTC Envisioning Center - Come and see first-hand what the Microsoft can offer you today
MTC Server Display Area
As we emphasized in the previous blogs, with other in-memory technologies in the market today you have to migrate the entire database to memory or it's a separate database product that deals with in-memory, which means learning new APIs and dealing with application compatibility issues. Integrating in-memory technology right into the SQL Server engine might sound trivial at a first glance, but this architectural approach carries significant customer benefits which we will elaborate in this post.
First, there is no special installation requirements needed for In-Memory OLTP, as a matter of fact, when you install SQL Server 2014, you don’t even see an option referring to In-Memory OLTP as it is installed transparently with the SQL Engine. Also there is no special hardware requirement to start the development and test on In-Memory OLTP.
To summarize the steps to leverage In-Memory OLTP
a) Use the AMR tool to identify the hot tables and stored procedures
b) Migrate the hot tables to memory_optimized tables and access those tables through inter-op (term defined below)
c) Migrate the hot stored procedures to natively compiled
d) Iterative from a) since the performance signature has changed
Inter-op’ed access is an important concept in in-memory OLTP. As pointed out by the architecture blog, the in-memory OLTP storage engine can inter-operate with the traditional SQL Server relational engine (that is Query Optimizer and Query Processor), this allows an existing DB workload to treat the in-memory OLTP database as if they were traditional SQL tables in select, insert, delete and update, as well as join operations. Another key concept is the entire database doesn’t need to be migrated to reside in memory, instead, selected hot tables (from data access and contention point of view) can be migrated to In-memory OLTP and this requires only the additional memory allocation for those tables, for example, at 128GB while the whole database can have a 2 TB footprint on disk. This means you can optimize in-memory to fit your existing hardware, improving TPS without increasing hardware costs.
The AMR tool includes a lightweight performance gathering component that you can setup to collect the performance signature of either a live production system or a test system. It queries DMVs such as sys.dm_db_index_operational_stats and sys.dm_exec_query_stats to detect the system hot spots over a period of time and aggregate the information using data collector and MDW, and finally present the user with a set of reports to highlight the top candidates of tables and stored procedures for migration into In-Memory OLTP. We will provide more details on how to use the AMR tool in a following blog.
Table migration
Once you have determined the table(s) for migration, there are two steps needed before you can declare a table as memory_optimized
a) Required: declare a filegroup to contain the checkpoint files for in-memory OLTP – for example
ALTER DATABASE TicketReservations ADD FILEGROUP [TicketReservations_mod_fg]
CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE TicketReservations
ADD FILE (NAME = [TicketReservations_fs_dir],
FILENAME = 'C:\mounthead\Data\TicketReservations_fs_dir')
to FILEGROUP [TicketReservations_mod_fg];
b) Optional: configure the in-memory OLTP memory usage limit (coming in CTP2) to provide performance stability for the traditional disk-based workloads
There are two ways to migrate a table to be memory_optimized
a) Use Transact-SQL to rename the original table and then recreate the table as memory_optimized, then “insert into table_in_mem select * from table_on_disk” to migrate the data. If there are incompatible column data types (for example LOBs), some workaround is required and the most common method is to split the incompatible columns into a separate table and join them through inter-op . The existing indexes might need to be migrated as well. Please refer to this section of the BOL for unsupported data types and some workarounds. Hash index works well for point lookup, but if you need range scans or ordered scans, you should consider range index coming in CTP2. One migration example is below
--Original disk-based table:
CREATE TABLE TicketReservationDetail (
iteration int not null,
lineId int not null,
col3 nvarchar(1000) not null,
ThreadID int not null,
constraint sql_ts_th primary key clustered (iteration, lineId))
--In-memory OLTP table:
constraint sql_ts_th primary key
nonclustered hash (iteration, lineId) with (bucket_count = 100000)
) with (memory_optimized = on)
b) Coming in CTP2 – there will be a Memory Optimization Advisor wizard in SSMS to migrate the table. The migration will advise on incompatible data types and proceed with actual migration if the column types and etc are compatible with In-memory OLTP. The wizard will also create the FG if it’s not already in place. More details to come with CTP2.
Stored Procedure Migration
Even though the table migration alone with query inter-op access is a good stopping point, and solves common data access the contention problems at the storage engine level, in-memory OLTP provides native compilation, which optimizes the relational engine layer as well. The AMR tool provides insight into the top candidate of stored procedures (SPs) to migrate. There is no wizard available yet to migrate the SPs, so this will be a more manual process. Please note that natively compiled SPs can only operate on memory optimized tables but if the T-SQL used in the current procedure is fully compatible with in-memory OLTP, the change is trivial, and also note the ease of using atomic block – for example
--Original SP
CREAT PROC InsertReservationDetails(@Iteration int, @LineCount int, @CharDate NVARCHAR(23), @ThreadID int)
AS
BEGIN
DECLARE @loop int = 0;
BEGIN TRAN
BEGIN TRY
while (@loop < @LineCount)
INSERT INTO dbo.TicketReservationDetail VALUES(@Iteration, @loop, @CharDate, @ThreadID);
SET @loop += 1;
END
END TRY
BEGIN CATCH
--- Actions
END CATCH
COMMIT
--Natively Compiled SP (the many declarative setting in the header is to aggressively push optimization at the compilation time to reduce runtime costs)
CREATE PROC InsertReservationDetails(@Iteration int, @LineCount int, @CharDate NVARCHAR(23), @ThreadID int)
with native_compilation, schemabinding, execute as owner
BEGIN ATOMIC with (transaction isolation level = snapshot, language = N'us_english')
After each major step(s) of migration of tables and associated SPs, we recommend that you rerun the AMR tool and migrate additional tables and SPs iteratively. There is no fixed rule on how much of the database to migrate into in-memory OLTP, since it depends on how concentrated the workload is and how much ROI you want to achieve.
Integrated Tools
In addition - from the DBA’s perspective, existing tools such as SSMS will continue to work with full support for memory optimized tables and natively compiled SPs. In-memory objects’ backup is fully integrated into SQL’s backup and HADR will be fully integrated into AlwaysOn by CTP2. There are also added DMVs such as sys.dm_db_xtp_index_stats and sys.dm_db_xtp_checkpoint_files to monitor memory consumption and storage activities, as well as stock reports like the following.
For more information, download SQL Server CTP1 and get started today, or see the series introduction and index to read more posts in this series.
If you’re already familiar with database fundamentals and you’ve worked with SQL Server, the Querying Microsoft SQL Server 2012 Databases Jump Start course will help you take your skills to the next level. You’ll learn about data manipulation and how to alter tables, and you’ll see it all in action in the demos presented by the experts: Brian Alderman, CEO and Founder of MicroTechPoint, and Tobias Ternstrom, Microsoft Program Manager in the SQL Server division. If you’re working on a Microsoft certification, this course will help you prepare for Exam 70-461.
Register now for this course to get a detailed look at:
This course takes place:
Date: September 13, 2013 Time: 9:00am – 5:00pm Where: Live, online virtual classroom Cost: Free!
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:
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
In February 2013 Microsoft received the Common Criteria (CC) certificate for SQL Server 2012 SP1 Enterprise Edition (English) x64 (Version 11.0.3000.0) at EAL4+ and compliant with U.S. Government Protection Profile for Database Management Systems, Version 1.3, 24 December 2010. See the actual CC certification screen shot below.
This is the 7th CC certification for SQL Server, starting with SQL Server 2005 SP1 in 2007. These certifications enable governments and major enterprise customers understand the security functionalities and quality of those functionalities of SQL Server 2012.
This certification is formally recognized by the governments of 26 countries that have signed the Common Criteria Mutual Recognition Arrangement (CCRA) and by as many as 40 more governments on a product by product basis. The Common Criteria is more than just the concise definitions of security functionalities and assurance requirements. It is also a precise evaluation process defined in the Common Evaluation Methodology document. In addition, it is a formal and approved evaluation scheme for each nation performing CC evaluations. And it is a government certification based on government working with a private evaluation lab certified in that country.
For more detailed information about SQL Server certifications, please go to Security & Compliance site and Common Criteria site.
Edgenet provides optimized product data for suppliers, retailers, and search engine. Used online and in stores, Edgenet solutions ensure that businesses and consumers can make decisions based on timely, accurate product information. And that speed of information is critical - when a customer is searching online, they have to have a great experience, or they are just going to go to another website to buy the product they’re looking for. The experience needs to be very concise and give them the information they want in a quick manner.
Edgenet was already familiar with the advantages of Microsoft client in-memory technologies such as PowerPivot and were keen to see how they could extend the advantages to their OLTP systems. Edgenet realized it could take advantage of In-Memory OLTP to improve throughput and eliminate read-write locks. The company decided to become an early adopter of the technology, which would be built into SQL Server 2014. As Michael Steineke, VP of IT says ‘We knew In-Memory OLTP would be a game changer for us’.
The traditional system was updated once a day, and the process took a few hours. Using In-Memory OLTP the new system can be done 7 times faster in about 20 minutes, and the system isn’t down while it’s being updated. It’s all done real-time!
As well as improved speed, Edgenet were also pleased that In-Memory OLTP uses the same tool set that the developers and DBAs are used to. To them it looks, basically, like standard SQL Server, even though under the covers it’s completely different.
The company also expects to increase customer satisfaction with real-time access to information. “Our reputation is based on providing highly accurate and clean data for products,” says Steineke. “With In-Memory OLTP in SQL Server 2014, we can ensure that we’re giving the correct data to the end-customer or sales associate. It’s all about being presented with the right data, at the right time, and in a form that’s easy to use.”
Join us live June 21st for in-depth training on Microsoft’s Big Data solution set. This free Webcast from Microsoft Virtual Academy will be the first in a series tailored for architects and seasoned developers interested in a demo-heavy learning experience that will help you get valuable insights from large and complex data. The course will be taught by Saptak Sen who leads Microsoft’s high performance computing (HPC) evangelism efforts, and you will be able to have your questions answered in-real time by our team of subject matter experts in chat.
The course will cover setting up and loading data into Windows Azure storage and a Windows Azure HDInsight cluster, Windows Azure Data Management concepts (e.g., SQL Machines, SQL Databases, Tables, Blobs), using Hive to query Hadoop data using HiveQL commands, and how Hive leverages the entire Hadoop cluster. We will also explore ways to pull data from HDInsight and other sources into Excel using tools like PowerPivot, Data Explorer, Power View and Geoflow to visualize data models with self-service analysis.
We look forward to seeing you June 21st so register today!
Recently Quentin Clark announced SQL Server 2014 during TechEd North America. One of the key features to be announced was the in-memory online transaction processing (OLTP) engine, which would complement the in-memory technologies which have been available in the data warehouse and analytics engines in SQL Server 2012, and integrated into PowerPivot in Excel 2013.
During the announcement Quentin highlighted how Edgenet were using In-Memory OLTP to gain real-time access to retail product data with in-memory OLTP. At TechEd Europe, it’s a timely reminder to look back to a story we highlighted first at PASS Summit 2012, in which bwin announced the benefits that they were gaining from using In-Memory OLTP – at that time, still under it’s codename ‘Project Hekaton’.
Bwin is the largest regulated online gaming company in the world, and their success depends on positive customer experiences. They had recently upgraded some of their systems to SQL Server 2012 - a story you can read here. Bwin had already gained significant in-memory benefit in their data warehouse using in-memory column store, for example – a large report that used to take 17 minutes to render now takes only three seconds.
Given the benefits, they had seen with in-memory technologies, they were keen to trial the technology preview of In-memory OLTP. Prior to using In-memory, their online gaming systems were handling about 15,000 requests per second, a huge number for most companies. However, bwin needed to be agile and stay at ahead of the competition and so they wanted access to the latest technology speed.
Using In-memory OLTP bwin were hoping they could at least double the number of transactions. They were ‘pretty amazed’ to see that the fastest tests so far have scaled to 250,000 transactions per second. So how fast is SQL Server 2014 In-memory OLTP or as it was then Project ‘Hekaton’ – just ask Rick Kutschera, the Database Engineering Manager at bwin – in his words it’s ‘Wicked Fast’!
Watch ‘bwin Wins with SQL Server 2014 below and download SQL Server 2014 CTP1 today!
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
Today at the PASS Business Analytics Conference, Microsoft technical fellow Amir Netz and partner manager Kamal Hathi took to the stage to demonstrate how people experience insights. As mentioned in yesterday’s blog post, understanding how people experience insights is critical to Microsoft as we look to build and improve our data platform.
Today we’re pleased to add another exciting business analytics tool to help customers gain valuable insight from their data. Project codename “GeoFlow” for Excel, available in preview now, is a 3D visualization and storytelling tool that will help customers map, explore and interact with both geographic and chronological data, enabling discoveries in data that might be difficult to identify in traditional 2D tables and charts. With GeoFlow, customers can plot up to a million rows of data in 3D on Bing Maps, see how that data changes over time and share their findings through beautiful screenshots and cinematic, guided video tours. The simplicity and beauty of GeoFlow is something you have to see to understand – check out the video demo and screenshots below. You can also download and try it out firsthand today. It’s an entirely new way to experience and share insights – one we think you’ll enjoy.
For more information on GeoFlow, check out the Excel team’s blog and visit the BI website.
We’ve put a lot of effort into making Excel a self-service BI client, and have worked to equip users with the capabilities they need to take analytics to the next level. Through compelling visualization tools like those in Power View, the modeling and data mashup functionalities in PowerPivot, and the incorporation of a broader range of data types through February’s project codename “Data Explorer” preview, we move toward the democratization of data, and the democratization of insight. GeoFlow takes this one step further, by providing a seamless experience with our other BI tools, and enabling visual storytelling through maps.
Check back here tomorrow for the final installment of this series, where we’ll share some real-life examples of how Microsoft technology contributes to more effective analytics, or review the complete series through the links below:
Eron Kelly General Manager SQL Server