I recently had the chance to interview Mark Souza, Microsoft’s Partner Director of the Business Platform Division Customer Experience Team for The SolidQ Journal. He is one of the most senior SQL Server executives and decision makers in all of Microsoft. Among other things, Mark created the SQL Server Customer Advisory Team (SQLCAT) many years ago, and the team still rolls up to him. Mark has worked with SQL Server longer than most people in the world, so I was curious about which SQL Server 2008 R2 features Mark thought were the most important.

Related: SQL Server 2008 R2 - 5 Cool New Features

Brian: With SQLCAT getting up close and personal with SQL Server 2008 R2 at customer sites around the world this past year, what are your top three favorite features in the new release?

Mark: I think the number one feature is pretty obvious: I think PowerPivot really is going to change the game of analytics quite a bit. The reason why is twofold. One is that Excel is by far the number one client in the world. Today, more analysis is done—and more data warehousing and data marts, for that matter, are done—inside Excel spreadsheets than anywhere else. Of course, these power users cannot get enough. They need fast access to data. They need data updated more often. They need an easier way to share it with their friends. They just want more, and more, and more.

And two is that at the same time, our IT friends are going nuts because there is more data in Excel being passed across the company than there is in their databases. It’s not managed. It’s not secure. It’s passed around, duplicated, quadrupled. There are 18 copies. And it’s not up-to-date, so they are making decisions off static data.

PowerPivot really solves these two major problems in the analytic world. It gives the power users more power, more capability, and more analytics than they have ever had in the past by moving the Analysis Server in-memory engine into Excel. Now they can \[analyze\] hundreds of millions of rows, which scares the heck out of a lot of IT folks. I can now, with \[the click of\] a single button, put it on SharePoint—which, by the way, is stored in SQL Server—and I can put security contacts around that. I can add automatic updates on a timed basis or automatically refresh when somebody opens it so the data will be up-to-date and not static. I can actually even back it up and put database context around it as well. So for me, PowerPivot is by far the number one game changer.

Number two is application and multiserver management. Internally, we code-named this Synthesis. It is really the foundation we are building now. There are two big things we are doing. One is the Universal Control Point (UCP), where I am going to have a server manage other servers. We tried this before. We have our Enterprise Manager product, which we can connect 50 servers to, and it is graphical and pretty easy to use, but it is one server at a time. I connect to Server A and I add a user. I connect to Server B and add a user. Of course, smart guys like you and me will write scripts and send it all out, but it’s still one server at a time. This multiserver manager truly starts changing that. It actually treats a group of servers like one server, and I can start applying commands like ADD USER to the group, and it will add it to all the servers. That is the first problem we are tackling.

The second problem is even more exciting to me. It is called the DAC. When you need to move your database from node 1 to node B for whatever reason—maybe test reduction or moving from a busy system to a non-busy system—you need to put your thinking cap on. You need to take your user database. Don’t forget about your master database—that is where your user logins and your agent jobs are. And don’t forget about your applications tied to that; now you have to switch the connection points to a different database or a different server. Of course, the end users now also have to switch to point to the new server.

The DAC controls all of this. Basically, it contains within this component all the things related to the database application: the application itself from Visual Studio, the database, all the user logins, all the management tasks, agent jobs… Everything you need is now scripted out and contained together in this thing called DAC. I can now say, “Hey, move from test to production” and not forget something, not forget logins and things like that. On top of that, we are adding technology, so we are making the user transparent as well. The user does not have to change anything; they just connect and access the server on the other side. It is not perfect Version 1, but it is a really great foundation to build off. The idea is right, and it’s about time.

For number three, and it is hard choosing just three R2 favorites, I am going to choose StreamInsight, which people know as complex event processing. The world is changing. One of the things that are changing in the database world is that people just need access to information faster, faster, and faster. The good old days of once a month storing data and accessing and writing a few queries are gone. It’s even to the point now where many businesses can’t even wait for the data to get in a database. Think about that. It takes a few milliseconds to put data into the database, and then I have to turn around and process it. That’s too long. These customers need to process data before it even gets into the database.

Think of streaming things like financial data coming across, or data coming off a manufacturing line, or even events coming off servers if you are in an operations center and you’re monitoring 2,000 servers across the world. Think about events coming from those servers when something is wrong. The earlier you can analyze data, the faster you can respond.

StreamInsight is complex event processing. It’s basically in-memory forming a stream, creating time slices within that stream, and analyzing information before it even gets into a database. The database becomes part of the persistent store, so you have all the database context around that as well. This is exciting. Its applicability is huge. Today, we are in production with StreamInsight on NFL.com, if anybody watches that on the web. We are rolling it out for the Olympics, and we will be able to understand who is viewing what at what time and what their responses are. Those are probably the three most exciting features for me. A parallel data warehouse is probably my fourth—actually, it could be my first. But it is not going to be in the same SKU, so I’ll leave it out for now.

Now that we’ve heard what Mark’s 3 favorite SQL Server 2008 R2 features are, what are your top 3 features?