SQL Server 2012 - Top Features

The next item is Always On Availability Groups. This is a great feature that is a result of SQL Azure and the way Microsoft maintains backups. This feature has been rolled down and incorporated at the on-site level. In a normal failover situation, you have to failover a single database at a time or the entire instance by using clustering. With the new Availability Groups feature, you can now create groups of multiple databases and have them all failover at the same time. So if you have four databases on your server that work together in some fashion, you can create an availability group of those four databases and have them failover at the same time if anything goes wrong.

You can set the level of the issue that causes the failover from major events with the server or outside server issues like connectivity problems. When these groups failover you can then split them across separate servers if you wish. That way you are not forced to have a standby machine capable of handling the full load of the production server, but rather a couple of smaller cheaper machines that the load could be spread over evenly. Second, the “Always On” portion of this feature allows you to create multiple read-only actively up-to-date copies of the production data. In the event that something does happen (hardware failure within the server to a software failure within SQL Server) the availability group changes the read-only copies to the active copy and all updates are then sent to the newly active versions.

This is all done behind the scenes and there are no connectivity concerns with the software as it doesn’t know there was a change. Also these read-only copies can be used to perform back-up operations as well as reporting and read-only data pulls can be pulled from them. This helps take the load off of the production server as well. This new feature gives us much more power and flexibility in how we handle disaster recovery and balancing the workload on the production server.
 

 

For any DBAs out there I’m sure you have had to migrate a single database from one instance to another. This might be due to moving everything over to a newer more powerful server or simply moving from development to production. Well it’s never as easy as just moving the database from one instance to another. There is a lot of baggage stored for that database at the instance level. With SQL Server 2012 we have something called Contained Databases. This new feature helps reduce or eliminate extra baggage that is stored at the instance level for a particular database. Now you can store items like the user information at the database level. This will not only help in moving this database, but it will also help cut down on the occurrences of orphaned user’s at the instance level. While this feature is great it’s not quite complete yet, but it is a giant leap forward.

Another new feature for DBAs out there is User-Defined Server Roles. We have always been able to create roles on the server, but never server-wide roles. This new feature will allow a DBA to create a role with, say read-write access for the 10 databases being used in a particular project, and assign that role to the people on the development team. This new feature simplifies and builds in this feature, as opposed to the DBA possibly using undocumented measures to keep control of who has access at a sever level.
 

Having worked on database tables containing billions of rows and growing at a rate of over a million rows daily, I realize that getting the needed data out of the database can sometimes be hard work. In SQL Server 2012 you get a new index type known as Column-Store Index. It is exactly what the name states. The index is column-based and not row-based. The data is stored in a flat, compressed column index. Let me give you an example of the power of this index. I was at a SQL event and sat in a one-hour session that covered just this one item. The presenter gave a demonstration at the end that pulled a SELECT COUNT(*) on a table with several billion rows; the query took less than a second. The screen blinked and the time was still sitting at zero. Keep in the mind that the data was stored on an external USB drive and not on the laptop itself.

This is a very powerful indexing tool. Now that I have been giving this item tons of praise let me give you the downside to it. This index is read-only and has to be rebuilt if there is any change in the data. Therefore, it is meant for data warehouse situations and not live actively-changing data. Also you can only have one index per table although you can include every column. This really isn’t an issue, but you should know this up front.
 

Working with SQL data I have often wished T-SQL would do one thing or another automatically, so I wouldn’t have to create additional code to achieve a desired result. Some of my wishes have been answered. There are several T-SQL Enhancements in SQL Server 2012; I have picked four to describe here. 

1. OFFSET/FETCH. An example of this is when you visit a web page and it listed results as X number per page. This feature allows you to pull only the data the user wants to see and not pull back the entire dataset. Basically the code is ‘OFFSET xx ROWS’ and ‘FETCH NEXT XX ROWS ONLY’. Before you had to use either joins or sub-queries and it could get complicated pretty fast. Now, you simply tell T-SQL how many rows to offset and then how many rows to fetch back. This certainly helps make the code easier to write and read.
2. SEQUENCE. This is similar to the identity object you are currently using, but sequence can be used across several tables and addresses several limitations of using identity alone. For example, you can get the new value at any time even during an update. It’s possible to alter the properties of a sequence object and you can define the minimum and maximum values as well cycle through those values. There are some other differences, but this gives you a built-in feature that was hard to achieve before.
3. THROW. In previous versions you could use RAISEERROR to show an error message, but this required an error number to exist in sys.messages and you couldn’t re-throw the error. In SQL 2012, the error number doesn’t have to exist in sys.messages. Basically it allows you to throw the error back up to the application level. This allows the error to be handled more gracefully at the application level and makes it easier on the developer. One thing to note is RAISEERROR is being deprecated as well. So it should be used at a minimum going forward.
4. TRY_CONVERT. This gives you the ability to test whether a value can be converted and if not, simply return a NULL value in lieu of causing an error. For example, the value ‘e’ is considered numeric, but it cannot be converted and would cause an error. This type of error has caused issues for me in the past, but now I have the ability to make my code smarter in how it will handle these situations.

I mentioned two of the items above for two reasons. Numbers 1 & 2 were mentioned because of the added functionality they provide, but also because they may be familiar with some people as they have been standard in other database engine products. The OFFSET/FETCH has been part of MySQL for years and the SEQUENCE has been part of Oracle. I wanted to point this fact out because it allows developers who have experience with other database engines an easier transition when coming over to SQL Server 2012.

The last main topic I wanted to cover was the BI side of SQL. In the past I didn’t think much about any of the different services that were available as far as providing data to the end-user. The reason being, more than anything, was my lack of experience as compared to the DBA and developer roles. Most people at an executive level don’t have a lot of time on their hands to drill through lines of data in a report. They would prefer to have something visual and graphical in front of them that they can take a quick glance at and see what is important to them.

Let me introduce you to PowerView. Now I am not a graphical or artistic person, but after using PowerView it makes me think I am. This new tool is a brand new browser-based Silverlight report authoring tool. It provides a high level of interactive reporting that allows you to access, explore, and most important of all, visualize your data. If you really want to impress, you can tie in a timeline, and then PowerView will dynamically change all the charts or graphs tied to it so you can visually see how the data is changing over time. This is a powerful tool; not just to show statistics and numbers, but visually show the change to the end-user.
 

The last item I want to mention is Data Alerts. This is a new data-driven alert feature that allows you to setup and define alerts using existing Reporting Services reports. These alerts can be setup based on criteria or scheduled to notify the user without having to execute anything manually. For example, if you have a table that should have data inserted on a regular basis, you can set this up to send you an alert if there were no updates/inserts in a specific time frame. That way you have a heads-up before a small issue turns into a much larger one. Also the reverse is true, if you have a table that should not have the data changed. There are numerous applications for this new feature due to the possible increase in productivity it allows all developers.

I know this is only the tip of the iceberg for the new features and advancements that have been made in the latest version of SQL Server and there are many, many more that are worth looking into. I hope you feel as I did when I saw these items for the first time . I had that wow feeling and thought ‘That’s going to make my life so much easier’. If you are looking to upgrade your current SQL Server I feel that most of the items that I mentioned alone are good arguments to make the jump to SQL Server 2012. But when you consider that all of them are available, plus all the features I didn’t mention it seems like a no brainer!