Each new SQL Server release offers a myriad of new features that are designed to make SQL Server DBAs’, developers’, and business intelligence (BI) pros’ work easier. SQL Server 2012 (formerly code-named Denali) is no exception. SQL Server Magazine’s Executive Editor Megan Keller and Senior Technical Director Michael Otey talked with Doug Leland, general manager of Microsoft’s Product Management, Business Platform Marketing Group, about some of these new features. Leland provided a glimpse into how SQL Server 2012’s AlwaysOn high-availability solution, Data Quality Services, development tools, columnar indexes, and cloud computing options enable you to build and secure mission-critical applications, better manage the data life cycle, have greater insight into your data, and store data more efficiently.

SQL Server Magazine: Doug, can you tell our readers about some of the key features in SQL Server 2012?

Leland: I’ll highlight some of the new areas in CTP3 specifically, because it’s an incredibly rich release with a number of new features that weren’t available in previous CTPs. The new features fall into three areas. One is enabling our customers to build mission-critical applications. That’s certainly a journey that we’ve been on for a number of years, and SQL Server 2012 is significantly raising the bar. The second area that’s really differentiating for us is enabling breakthrough insights. This is really about empowering a much broader user base by giving users access to the BI tools they need to turn information into business insights that will impact and fuel the business as it grows. The third area is really all about the cloud—specifically, the “cloud on your terms.” When we talked with customers, we found that they’re keen on taking advantage of the cloud’s economic and scalability benefits, but they’re certainly not looking at a wholesale overnight move to the cloud. They want to move to the cloud on their own terms so that they’re able to control the environment and control the pace.

In the mission-critical area, the real flagship feature that’s available in CTP3 is SQL Server AlwaysOn, which is an integrated high-availability and data-protection solution. It really builds on all the work we’ve done in the past with SQL Server 2008 and SQL Server 2008 R2. This robust and flexible offering provides customers with the five nines of uptime they need at the right price.

In terms of the BI breakthrough insights, this release will include a rich portfolio of new capabilities in that area. I don’t know if you’ve had the opportunity to look at Power View (formerly called Project “Crescent”) yet. It’s something that is better seen than described. It’s a highly interactive web-based data visualization and exploration tool that builds on the success of PowerPivot. It brings a new class of visualization and exploration capabilities on top of the SQL Server platform, so we’re very excited to be able to release that in CTP3.

We’ve also unveiled a new development tool, which is SQL Server Data Tools (formerly called Project “Juneau”). It lets customers build BI applications, traditional database applications, and web applications in the same IDE. It also provides customers with the ability to build once, then deploy anywhere. What I mean by that is we’re providing a common tooling infrastructure for both SQL Server and SQL Azure. So, customers can carry forward all their skills and build an application, then have options in terms of where they’ll ultimately deploy it.

We’re also introducing Data Quality Services, which is a complement to SQL Server Integration Services and Master Data Services in SQL Server 2008 R2. Data Quality Services rounds out the portfolio of being able to address the entire life cycle of data—from being able to manage it, cleanse it, assess it, analyze it, and ultimately integrate it across different data sources.

SQL Server Magazine: Does SQL Server Data Tools replace Business Intelligence Development Studio, and how does it relate to SQL Server Management Studio?

Leland: Great question. It actually brings all those together into one integrated environment so that you can build database applications, do everything that you’ve traditionally done in SQL Server Management Studio, and do what you’ve historically done with the BI tool. This consistent management environment is built and designed as a plug-in for Visual Studio. So, not only do you get the richness of the Visual Studio design environment, but you also get the power of the design environment. You’ll be able to use Visual Studio’s complete set of development, editing, and debugging tools for your database projects.

SQL Server Magazine: Were there a lot of changes made to the columnstore index?

Leland: I’m glad you brought that up because I forgot to mention it when we were talking about the major capabilities we’re incorporating into SQL Server 2012. The changes made to the columnstore index are part of a project code-named Apollo. Apollo is an in-memory column store. What that really means is that instead of storing the data in rows, the data is stored in columns, which lends itself to data warehousing and BI-type queries. It has the capability to put that data in memory. So, not only do you get the performance advantages of being able to access data in column form but you’re also accessing it in memory.

We’re seeing dramatic performance gains with this capability. We’ve consistently seen a 10x improvement in terms of query performance, and in some cases, we’ve even seen up to 100x improvement. Customers are pretty excited about it.

SQL Server Magazine: AlwaysOn seems to be the main feature in SQL Server 2012. What are some of the advantages that it has over database mirroring?

Leland: I would say that AlwaysOn certainly is the premier feature in the mission-critical area. When you think about mission-critical applications, you’re talking about scalability, reliability, security, and high availability.

So how does AlwaysOn differ? We have a set of technologies in the product today that customers have been using to build high-availability and data- protection solutions. They are mirroring, clustering, log shipping, and replication. The challenge is twofold. First, we have an embarrassment of too many technologies, and there’s no unified way to set them up, configure them, and manage them. Second, the person setting up the environment has to know a lot about both the operating system and the way the database functions in order to set up all those technologies. With AlwaysOn, we’re providing an integrated solution that’s built from those technologies. In this integrated solution, there’s a new management interface, which is wizard based, that basically walks you through the process of setting it up. We do a lot of the work under the covers to ensure the appropriate setup and validation. There’s also a dashboard that allows you to monitor the health and hygiene of the high-availability environment over time. We’re bringing the technologies together into a single solution so you don’t have to worry about the different pieces.

We’re also adding a bunch of new features that weren’t available before, like multiple secondaries, so you can have multiple copies at the same time. They’re not just passive secondaries. They’re active, so they can play different roles. For example, you can take your reporting infrastructure off the primary and put it on one of the secondaries. You can move your backup infrastructure from the primary to a secondary. You can do a lot of sophisticated load balancing by distributing your overall workload across the various secondaries that you have.

We’ve also introduced the concept of asynchronous secondaries. Asynchronous secondaries will allow a company to set up either a regional or global high-availability environment, where the secondaries are spread across data centers. This solution can provide a buffer for catastrophic site failures. So, if one of your data centers were to experience an outage or an earthquake or something along those lines, it would immediately fail over to another data center, which could be in another state or even in another country. All of this is rolled up into a single environment—and the new management interface lets you easily set it up, configure it, and run it.

SQL Server Magazine: That sounds like a great improvement.

Leland: One of the things that I think we’re going to find as we work with customers is that a broader range of applications are requiring high availability. Historically, I think high availability has mainly been relegated to high-end, mission-critical data center applications, and that’s pretty much been driven by the cost and complexity of setting up a solid high-availability disaster recovery solution. By simplifying it, customers will be able to deploy high-availability solutions for a much broader range of applications, which is exactly what they’re looking for. Customers will be able to easily set up these solutions and at a significantly lower cost. This will meet a very specific set of needs, whether it be for mission-critical applications, business-critical applications, or cloud applications where you must have 24 × 7 operational characteristics.

SQL Server Magazine: Earlier you mentioned the new data visualization tool Power View. What users are you targeting that tool for? Is it for knowledge workers?

Leland: Yes—we talk about Power View in terms of self-service BI, which empowers information workers. They’re able to use the analysis tool that they love, which is essentially Excel, to tap into the power at the back end of SQL Server. Power View is the next major step forward in terms of visualization and exploration, really kind of breaking these boundaries in terms of interactivity. Besides Excel users, it’s targeted toward PowerPivot and PowerPoint users, who are also information workers. Again, it follows the theme of self-service BI—putting an incredible power in the hands of an information worker.

SQL Server Magazine: Is Power View integrated with SharePoint?

Leland: All of our BI apps—PowerPivot as well as Power View—will be integrated with SharePoint, so you have the ability to create, secure, and share reports using SharePoint. We’re continuing to build on the triumvirate of SharePoint, SQL Server, and Office to deliver this experience. Today you have the situation in which a relatively small percentage of users in an organization actually have access to the BI tools that they need—roughly around 28 percent. Our goal is to take that to 100 percent. The only way to do that is to give them the tools built around the products that they actually know versus introducing a complete new toolset. So, we’ll continue the strategy of building off of SharePoint for collaboration and building off of Office for the user environment. SQL Server is the back-end engine that’s going to power it all.

SQL Server Magazine: Can you go into more detail about the “cloud on your terms”?

Leland: Core to our strategy is enabling what we call hybrid IT, which is basically providing customers with the ability to build applications that they’ll deploy in a private cloud, run on premises, or deploy in a public cloud. That means providing customers with the database infrastructure, development tools, and management framework needed to not only build new applications but also extend existing applications to the cloud.

There are several different app scenarios that are developing. One that’s becoming common is the scenario in which you have seasonality in your workload and you want to use the cloud for that excess capacity. Or you may want an app to integrate with the Windows Azure marketplace—the data market—and consume data that’s living in a public cloud so that you’re basically creating a hybrid application. Then there’s the migration scenario in which you migrate an on-premises application to the cloud, running it virtualized or unvirtualized.

Right off the bat, SQL Server 2012 comes cloud enabled for a number of the services inside it. For example, Data Sync lets you synchronize databases between your on-premises SQL Server machines and SQL Azure. This is both a major IT transformation for the industry in terms of the cloud and a major differentiator for SQL Server 2012 as we go to market. Right now there’s no other database sector that’s articulating a strategy that says, “Look, Mr. Customer or Ms. Customer, we can provide you with an information platform that is cloud ready and that enables you to build and deploy apps on premises, build and deploy apps in the cloud, and extend or migrate existing apps to the cloud.”

SQL Server Magazine: What do you consider to be the main benefit of upgrading to SQL Server 2012?

Leland: This release is all about enabling customers so that they can build mission-critical apps, have breakthrough insights, and build apps for both the private and public cloud. These things encapsulate the entire release.