Presentation Transcript: Introduction to SQL Server 2012

Article
1/17/2024

SQL11UPD00-TSCRIPT-01

This wiki is a transcript of a previously recorded video.

Slide: Introduction to SQL Server 2012 From a Developer Perspective

Hi I’m Roger Doherty, Sr. Program Manager with the SQL Server Customer Experiences Team. Thanks for tuning in. This is the first presentation in a whole collection of content that we’re shipping out there to help people learn how to adopt a lot of the great new improvements that are coming in SQL Server 2012. In this first presentation we’re going to give you a high level overview of all the great stuff that is coming in this release, and then what follows is a whole series of presentations and demos and hands-on labs that you can use to learn the important new capabilities in the product and how you can exploit that in your own solution. So let’s get right into it.

Slide: New Platform Options – Managed Services

So there’s an important change that’s happening in the industry today, I like to call this a shift towards what I would call managed services. So what you see on the first stack here is our existing self-managed platform where we as IT Professionals and Developers have to worry about purchasing, deploying, installing, securing, maintaining, patching, updating pretty much every layer of this stack. And we are all pretty comfortable doing this, it’s something that is well understood. This platform is alive and well and SQL Server 2012 is designed to work great on bare metal in this type of environment.

But there’s also an important shift that’s going on in the industry. More and more IT shops are looking to localize their compute and storage resources in their organization and get out of the business of having to provision servers every time they need a new web server or a new database server. We would call this trend “Infrastructure as a Service” and really it’s about dipping into a pool of storage and compute and provisioning new servers in minutes instead of days, weeks or hours. SQL Server 2012 is designed to function really well in this environment as well. We’ve done a lot of testing on top of Hyper-V, and our resource governor technology in the product really understands how to work well in a virtualized environment. So there are quite a few workloads that are perfectly acceptable to run in a virtualized kind of way. That whole “Private Cloud” play or story is happening both on-premise with technologies like System Center Hyper-V Manager, and also it’s happening in the public cloud as well so keep your eye on that space. You should be in the position where you can just build out a virtual machine and run that on your own private cloud infrastructure internally or fork lift that thing up and run it in somebody else’s public cloud infrastructure as well and it’s giving people a lot of flexibility in terms of where they deliver their existing solutions.

Now there’s another important trend that’s happening in the industry towards “Platform-as-a-Service” and really this is to take those same economies of scale where we’ve abstracted out the compute and storage and push that even further up the stack and get you out of the business of having to provision and license operating system software and database server software. Where this is happening from Microsoft’s perspective in the public cloud is Windows Azure. Windows Azure gives you an environment where you can go very quickly and easily provision compute and storage services and consume those services and get all kinds of guarantees when you start using those services. So for example with SQL Azure Database, when you provision a new SQL Azure database you automatically get three replicas for that database and high availability is sort of baked in. If your primary goes down we will automatically promote one of your replicas and route all of your connections to the new primary. These are things that require a lot of time and energy to do on-premise, but it is just kind of baked into the Platform-as-a-Service architecture that we’re deploying in Windows Azure. So between those two motions there, Infrastructure-as-a-Service and Platform-as-a-Service, it gives you a great set of choices on whether you want to take an existing solution and replatform it, or whether you want to build a new solution that abstracts out all those lower layers and puts the burden on another provider like Microsoft to deliver on their SLA. The hope is at the end is that you are going to build great Software-as-a-Service that you can provide to your customers and they can consume and pay as they go, and if they need more and use more, they pay more, or if they need less, they let some resources go and pay less and that’s kind of the whole promise of this bursting capability that we see happening in the public cloud space.

Slide: Data Platform Requirements

So when I talk about SQL Server 2012 I like to break it down along these categories that you see along the top of the screen. We like to talk about the core storage and search capabilities of the environment, how you can build mission critical solutions that you can write an SLA behind, that’s really that whole federation category there, how you can blend data from multiple places into a common store and integrate that data, that’s all about composition, and we’ve got a ton of capabilities there. Insight is all about treating your data as an asset and being able to crack open that asset and gain some insights out of it that you didn’t have beforehand, so that’s all of our business intelligence technologies there and there is a ton of exciting things happening. Delivery is all about choice. Do I want to deliver this on bare metal hardware that I’ve provisioned in my own data center. You can do that. Do I want to deliver this in a virtual machine in my private cloud infrastructure. You can do that. Do I want to deliver this on a SQL Server appliance that I purchase the hardware that has all the SQL Server infrastructure pre-installed. You can do that as well. At the other end of the equation, do I want to just take a dependency on a database service in the public cloud, and that’s where SQL Azure comes into play. You have this really incredible range of choices now across all of our offerings. And finally you need awesome developer tools to build the next generation of solutions that leverage all of these capabilities. So the goal from our perspective is to deliver these capabilities in a symmetrical way across both our self-managed platform there on the left as well as our Platform-as-a-Service world in Windows Azure on the right. And the good news is that as a .NET developer or a SQL Server developer you already have all the skills you need to get started building solutions for both of these worlds. It’s an exciting time.

Slide: SQL Release Timelines

If you look at the release schedule on the slide you’ll see the release cadence for SQL Server on the top has been from the smallest a couple of years or eighteen months and some long ones have been up to five years. We’ve been delivering great new releases of SQL Server now for almost twenty years, and I’ve been fortunate to have been a part of that entire ride. When I first started with Microsoft it was called the “Microsoft, Ashton-Tate, Sybase SQL Server version 1.0 for OS/2” and it came on a big stack of floppy disks and it was this crazy experiment to see if you could use a PC as a database server. Now we’re running some of the highest-end Tier 1 mission-critical workloads in the world, we have an awesome set of business intelligence capabilities, and this technology is really pervasive across the planet so it’s really an interesting time. At the same time this important shift is happening towards managed services and it’s really requiring a more rapid pace in terms of innovation and new functionality that you provide. So in the timeline on the bottom you can see the cadence between our recent SQL Azure releases and its measured in months not in years. So really the focus for us moving forward is how can we deliver really significant value in shorter sprints and get those into the hands of our customers. So that’s happening as we speak.

Slide: Data Platform – SQL Server 2012

When I talk about SQL Server 2012 I like to break it down into these four different categories that you see here. On the left we’ve got Database Services, the crown jewel of SQL Server technologies. We’ve got the core relational database technology which in the on-premise world we call SQL Server and in the Platform-as-a-Service world we call SQL Azure. We’ve got great new features and capabilities available in both of those during the SQL Server 2012 wave. We’ve also got a set of technologies for doing replication and synchronization. Not a lot of changes with replication in SQL Server 2012, but in the public cloud space we do have a new synchronization technology that we’ve introduced that’s currently in preview called SQL Azure Data Sync that allows you to set up a hub and spoke topology for synchronizing data sets between SQL Azure databases in different Azure data centers as well as to SQL Server databases that might be running on-premise. That’s a pretty cool technology and I encourage you to check it out. We also have a batch of technologies there in the Database Services stack around full text indexing. Full text really is a cool technology that lets you do more powerful searches against unstructured content such as documents for example. The standard relational queries with joins and where clauses don’t really work when you’re looking for more complex patterns of words in a document, so full text really nails that for us and there are a lot of great improvements there in 2012. We’ve added an exciting new capability called semantic search on top of that that really helps get to the meaning of documents not just the words that are in them, and allows you to compare one document to another to see if it’s similar in terms of what the content of that document is and also to do searches that say “Hey here’s a document that I’m interested in, go find me similar documents based upon these characteristics.” So semantic search makes those kinds of searches easier than ever to do.

The next pillar that I’ll drill down on is what I would call Data Integration Services and really there are four technologies in there of interest. The first is Integration Services which is a set of tools and services and APIs for handling ETL workloads. ETL means “Extract, Transform and Load”. This is where you are moving large amounts of data form one place to another and you are scrubbing it up and reshaping it so it can fit the destination target. This is very commonly used in Data Warehousing, but lots of people use this for all kinds of different data transformation workloads. There are tons of really cool improvements in this release of SQL Server 2012, I’ll talk about them a little in this deck but I certainly encourage you to watch the content we have further down through these modules on Integration Services.

Master Data Services was introduced in SQL Server 2008 R2, and this is a set of tools and services that a systems integrator can use to wire together disparate information systems that share common data sets. Lots of large customers have this problem that they have these really key information systems that share common data like department codes. Do you have to go maintain those department codes in every single one of those systems, or should the person who actually decides what the valid list of department codes is be the data steward of those things. And shouldn’t you have some workflow around it so when that data steward approves a change to department codes that it flows back into your ERP system and your HR system and your CRM system. That’s what a “Master Data Management” solution is all about, and we’ve got a nice set of tools and services right in the box with SQL Server 2012 in Master Data Services to help you pull that off.

Data Quality Services is a brand new offering in SQL Server 2012 that’s really targeted at the domain experts for data. These are people like compliance officers, data analysts, auditors. They are the people that really understand what is good data and what is bad data. Often the developers or the people building the Integration Services package aren’t those people. So Data Quality Services is a nice set of end user tools that allows these domain experts of the data to iteratively build a knowledge base to scrub and cleanse your data so that it can be reintroduced into the system and be compliant. Once they build that knowledge base and continue to evolve it, they can give the developers access to it in their SSIS packages to help in the data scrubbing and data cleansing process.

Finally at the bottom there is StreamInsight. StreamInsight was new in the SQL Server 2008 R2 wave. This helps us address a whole new workload for SQL Server. That workload is what we call “Complex Event Processing”. Basically the idea is that if you have massive amounts of event streaming data flying at you so fast that you don’t even have time to persist it, but you still need to query it in real time, StreamInsight handles that exact scenario. So StreamInsight allows you to build standing queries that sit over top off event stream data as its flying by you at a very high rate of speed and allows you to reason over top of it. Those standing queries are continuously evaluating and they pop out results every so often, and then you can take those results and do things with them. You can run reports off of them, send alerts, do a real time graph. Whatever visualization you need you have total flexibility with what you do with that output. So StreamInsight is an important new capability and an important new workload in the platform. If you haven’t checked it out I encourage you to definitely look at it because it’s going to help you solve some very difficult problems. The other thing that we are doing with StreamInsight is that we are projecting it into the public cloud. We have a project called “Project Austin” where the StreamInsight team is looking at giving you some services where StreamInsight is pre-provisioned along with some templates that will allow you to do telemetry based applications in the cloud very easily in a very straight forward way. So check that out it’s coming soon.

There’s a whole bucket around number crunching or “Analytical Services” as I call it. This is all about grinding through lots of numbers and doing great high functioning analysis over top of it. We have a brand new release of Analysis Services with a ton of new improvements including the introduction of the BI Semantic Model which I’ll talk about in a bit, integration of the VertiPaq technology, which was pioneered in PowerPivot, in the new Tabular Models in Analysis Services. So Analysis Services gives you the best of the multi-dimensional MDX OLAP world, and we’re starting to bridge in this new columnstore VertiPaq DAX world that was pioneered by PowerPivot. And we are going to offer both of those capabilities out of Analysis Services, which is pretty exciting. Data Mining hasn’t changed too much in SQL Server 2012 but that’s an important part of the product. And then PowerPivot. PowerPivot is about really improving the analytical experience of that Excel user. So we’ve got a new PowerPivot client for Excel 2010 that really supercharges Excel as a data analysis tool, and then we’ve got PowerPivot Server for SharePoint 2010 which allows you to take your PowerPivot models developed in Excel 2010 and then publish them up to a server and share them with your coworkers with lots of great interactivity. So there’s a brand new release of all of those technologies as well as a new release of the underlying Data Analysis Expressions syntax and query language that those use to do their analytics.

Last but not least we’ve got a great bucket there on Reporting Services. At the top we’ve got Reporting Services which is a great infrastructure for developers to build pre-built reports that have great interactivity and great data visualization in them and can be rendered in a variety of formats , and then publish those out for users to consume them either from a custom application or from a SharePoint portal for example. That’s the whole idea behind Reporting Services. We do have a new release of that technology in SQL Server 2012 that supports a new end-user data alerting capability, and also we offering SQL Azure Reporting, which is Reporting Services technology delivered as a service in Window Azure. So now you can go up and provision your own Report Server in the cloud, deploy your reports to it, and not worry about provisioning hardware or licensing software to do that. You just pay as you go with SQL Azure Reporting. That’s currently in preview, if you are a Reporting Services shop I would definitely recommend that you check that out. Report Builder is not changing too much in this release, that’s the standalone report authoring tool for folks who don’t want to author reports in Visual Studio, we have a nice alternative for them in Report Builder. Not too many changes there. And then Power View is probably one of the most exciting things that’s happening in SQL Server 2012. Power View is all about a new self-service data visualization experience that we are providing for users in SharePoint. Users can sort of drop data onto a palette, visualize it in different ways, and present it in the most compelling way possible with lots of great interactivity in real time. So we are really working hard on delivering on that self-service reporting and analysis story in this release.

Slide: SQL Server 2012 – Storage and Search

So drilling down a little bit into some of the different categories of improvements, let’s start with storage and search. There are a lot of nice improvements in both the database engine and in Analysis Services from a storage perspective. One of the most interesting things is that we now have support for SMB when talking to database files. So you can create a database on a network share. And with a lot of the new improvements that are coming in Windows Server 8 and new versions of the SMB protocol we can guarantee a very high performance experience there, provided you have the right networking and storage infrastructure in place, that gives you a lot of flexibility in terms of how you actually deploy your data files. In the past you either had your own direct attached storage or you had a SAN, in the future you will also have a StorageSpaces option with Windows Server 8. And if you store your database files off in those StorageSpaces you can get super high performance out of a pool of storage really improving the total cost of ownership of your storage solution. So that’s something to key your eye on. All of the core improvements are there in SQL Server 2012 to take advantage of those improvements coming in Windows Server 8. New character encoding capabilities, improvements to table partitioning, FILESTREAM which is our database blob storage capability in SQL Server has really grown up in this release. So those are just a few things that are happening from a storage perspective.

From an indexing perspective, the big news is column store. So column store adds a new “columnwise” indexing technology right into the relational database engine that allows you to supercharge your data warehousing queries and get unbelievable performance improvements for queries against large datasets. We basically pack the data down into a column store so that much more of it can fit in memory at one time. Some of the performance numbers we are seeing out of this will blow your mind so keep your eye on that space when the real benchmark numbers come out. Lots of improvements in full text, we already talked about those and semantic search. We’ll have some more content on that in the presentations and demos that follow.

Slide: SQL Server 2012 – Federation (1)

Moving on, let’s talk about what it takes to build those mission critical services that you need to write an SLA behind so that someone can take a dependency on them. If somebody is going to take a dependency on your service, it better be continuously available, it better be responsive, it better be secure, and it better comply with your standards. So all of those things have to do with this federation category. So the first thing that we’ll talk about in the database engine are some nice security improvements that have shown up. The ability to create your own custom server roles, improvements in the security infrastructure around our Data-tier Application Framework, lots of improvements around the ability to define the default schema for users instead of having to go and do that on a user by user basis, you can now do that at the group level which is a nice administrative improvement.

The VertiPaq engine which is that underlying storage engine underneath PowerPivot that forms the new Tabular Models in Analysis Services is beginning to grow up and get more enterprise like capabilities like its big brother Analysis Services / MOLAP has. It will support, for example, a new role based security model which will improve the enterprise grade capabilities if you decide to use that VertiPaq storage engine.

High availability is probably one of the biggest and most important things that is happening in the SQL Server 2012 release. We have a set of tools, capabilities and services that we brand as a whole “AlwaysOn”. There are a lot of different aspects to AlwaysOn so you will see me mention it in a few different places in this presentation. The first way that we will talk about it is high availability. What we’ve done is rationalize both our database mirroring technology and our failover clustering technology and we’ve provided you a much more cohesive set of tools and services for delivering high availability at the application level. In the past the focus has always been on the node level, which is failover clustering, or at the database level, which is database mirroring. Most customers are more concerned about whether or not their application is running so we’ve got lots of great economies of scale around that application level. So this is an integrated solution across both shared and non-shared storage. We’re going to give you the capability to have multiple secondary replicas of a database when you are using what’s called an Availability Group. We will also support multi-site failover clusters now in a much more cohesive way. And if you are using advanced capabilities like FILESTREAM database blob storage, those can participate in your high availability configuration. So our support for a wider range of features in SQL Server is important here in AlwaysOn.

Some other interesting things we are doing here with federation have to do with how well we are utilizing your hardware resources. From one perspective, high availability and disaster recovery, customers are asking us to use all those redundant nodes they have waiting for a failover. So with AlwaysOn, you can actually take a replica, and make it a readable secondary, and route your read only workloads to those readable secondary’s. A read only workload can be a reporting workload, a backup workload, all of those things can be routed to a readable secondary giving you better resource utilization of that hardware so it’s not just sitting there waiting for a failover event all the time.

We also on the low end have a new deployment option of SQL Server 2012 Express called the Local Database Runtime. It’s super small, installs in five minutes or less, it’s a shared component that does not run as a service. It’s an ideal low footprint SQL solution for an embedded database for a tool or application. So keep your eye on that there are some really cool things happening with the Local Database Runtime.

Slide: SQL Server 2012 – Federation (2)

Federation is also about managing these solutions and this infrastructure at scale. You have to have great tools and great capabilities to keep the system healthy and reduce your overall total cost of ownership. So there are a ton of things we are doing here to improve supportability. Our support for Windows Server Core means that there is less of a surface area that you have to worry about patching. So you can install right onto Windows Server Core and limit the number of times you have to deploy updates because it’s a smaller surface area.

Our setup technology now supports slipstream upgrades, so when you install SQL Server for the first time, we’ll go up and check to see if there are new versions available and go ahead and let you use those new bits from the get go instead of making you go back and install service packs and that type of thing making you more efficient in the deployment area.

From a manageability perspective, AlwaysOn adds this concept of Availability Groups. This is what I was talking about when I was talking about high availability at the application level. Most applications use more than one database, so with AlwaysOn you can create what’s called an Availability Group, and add all the databases that are important to a particular application to that Availability Group, and then apply your high availability policy to that as a unit instead of having to do it on a one-off basis across all of your individual databases. Big improvements there in terms of usability and manageability.

We have a lot of improvements in Integration Services for the DBA. So in the past, “Integration Services” were these packages that you weren’t really sure what was happening inside of them. We’ve added a whole server based infrastructure for deploying, monitoring, managing, and versioning your SSIS packages. So the people that are managing that database server have great visibility into what is actually going on, and how to control those packages and troubleshoot them if there are problems. So lots of great tooling there for Integration Services.

So Reporting Services is going to offer lots of great new improvements when you install it in SharePoint Integrated mode. In the past it was this foreign thing that was outside of SharePoint. SharePoint admins weren’t really comfortable with it. With SQL Server 2012 it actually installs as a shared service, so your SharePoint admins won’t think of it as a crazy foreign thing and it offers a lot better integration with the native SharePoint environment from both an administration and functionality perspective.

From a governance perspective, this is all about making sure that your data is compliant, we are adding some important new capabilities here. We are adding support for database audit to all SQL Server SKU’s. That used to be an enterprise edition feature. Now it’s available across all the SKUs. And we’re adding this new Data Quality Services capability that I talked about earlier that lets those domain experts build out those knowledge bases for scrubbing your data to make sure that it’s compliant.

Slide: SQL Server 2012 – Insight and Collaboration

From a BI perspective, the big news here is the introduction of the BI Semantic Model, we have a lot of content on that following this presentation. We think that we’ve come up with a really good model to drive all of our user experiences from reporting, to analytics to scorecards to dashboards. And you as developers will be the people responsible for making sure that the underlying model gets built, and gets instrumented to drive the best experience possible depending upon the tool the user is using. So that’s a big improvement coming in SQL Server 2012 that all you BI professionals should learn about.

We have a brand new PowerPivot release. We’ve got the new Power View functionality which again is that highly visual, interactive data exploration tool. The tag line there is “presentation-ready at all times”. When you are looking at your Power View surface you should be looking at something fairly compelling at every moment when you are constructing it, get real time feedback right on top of your data, and get it to a form where it really conveys the information that you need it to without having to fight with it too much. So there is a great new end user data exploration experience with Power View.

We talked a little bit about Reporting Services, the big new functionality there is user-defined data alerts. So a user can go in and ask to be alerted if a particular condition or threshold is reached in a report that they care about.

Slide: SQL Server 2012 – Data Programmability (1)

Finally, to wrap things up, Data Programmability is a big part of this release as well. We’ve got a lot of great new improvements in Transact-SQL to improve our ANSI standard compatibility, which makes it easier to migrate solutions to SQL Server. We’ve got a great new error handling capability. We’ve got a lot of improvements in our Data-tier Application Framework which will be of interest to developers so I encourage you to check out that content following this presentation. There are some nice new improvements in the Entity Framework, so for example developers can now write their classes and frameworks first, and we’ll reverse engineer all the SQL Server data structures underneath to support that, so-called “Code First” functionality. And we also have a nice REST-based API that sits on top of that Entity Framework which will be really important with Windows 8 coming and devices becoming more prevalent. You need to be able to get at your data over a light weight HTTP based protocol. OData is the way to do that and it’s very tightly integrated with the Entity Framework. So developers have a very seamless story all up and down the stack.

Slide: SQL Server 2012 – Data Programmability (2)

Probably the biggest news from a developer perspective is the introduction of the new SQL Server Data Tools. SQL Server Data Tools is a couple of different things. I’m talking about a new set of designers and project systems that integrate with Visual Studio or run in a stand-alone way, that make it possible for you to build database applications using a much more modern experience. So we want to give you a declarative model based approach for building your solutions, make it very easy to refactor, as soon as you make a change in your project you should get immediate feedback on the impact of that change in terms of dependencies to tables, stored procedures and views in the same project. So we are really beefing up and providing you with a more modern developer experience with SQL Server Data Tools, which will be available free via web platform installer so you can get started building SQL Server applications without even installing SQL Server. You can also install SQL Server Data Tools in an integrated fashion with your existing Visual Studio 2010 install, or you can install it integrated with all the BI development tools that come with SQL Server 2012 so you can get the full suite of development tools that you need. Database Applications with SQL Server Data Tools, Reporting Services, Analysis Services and Integration Services, all of them supporting Visual Studio 2010 in a common environment. We are going to have a whole presentation on SQL Server Data Sools so stick in there I’m not really doing it justice here.

There is a brand new release of our Integration Services package designer with a ton of developer improvements.

And finally we are really interested in making sure that those non-Microsoft developers feel comfortable targeting SQL Server, so we’ve got really highly functional JDBC and PHP drivers out there, and we also have new ODBC drivers for all those native code developers. We want to make sure that regardless of your choice of languages that we have great interfaces into both SQL Server and SQL Azure. This is a big focus for us during this wave.

Slide: Resources

So finally here are a few resources for you to check out. You can find out all the latest information with what’s happening with SQL Server 2012 at microsoft.com/sqlserver. All of this content and all the subsequent presentations and demos you can find the latest information about those at bit.ly/sql2012cookbook. That’s the SQL Server 2012 Early Adoption Cook Book.

Slide: Microsoft

Thanks a lot for all of your attention; hopefully that gives you a high level understanding of the surface area of this new release. In the subsequent presentations and demos we are going to drill down and give you a good flavor for all of the cool things that are happening with SQL Server 2012. Thanks for tuning in.

Corrections

Slide: SQL Server 2012: Federation: Server auditing capabilities have been added to all SQL Server editions, however database audit is still an enterprise edition only capability.

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]

Share via